Independent vs Correlated Subquery | Advanced SQL Tutorial For Beginners

In this video we are going to discuss the difference between independent and correlated subquery. This is a advanced SQL topic. We are going to solve a problem using both the methods. Here is the script:
create table emp(
emp_id int,
emp_name varchar(20),
department_id int,
salary int,
manager_id int,
emp_age int);

insert into emp
values
(1, 'Ankit', 100,10000, 4, 39;
insert into emp
values (2, 'Mohit', 100, 15000, 5, 48);
insert into emp
values (3, 'Vikas', 100, 10000,4,37);
insert into emp
values (4, 'Rohit', 100, 5000, 2, 16);
insert into emp
values (5, 'Mudit', 200, 12000, 6,55);
insert into emp
values (6, 'Agam', 200, 12000,2, 14);
insert into emp
values (7, 'Sanjay', 200, 9000, 2,13);
insert into emp
values (8, 'Ashish', 200,5000,2,12);
insert into emp
values (9, 'Mukesh',300,6000,6,51);
insert into emp
values (10, 'Rakesh',300,7000,6,50);

Zero to hero(Advance) SQL Aggregation:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

#sql #dataengineer #analytics

[ankitbansal]

99 percent of the time you can solve a problem using an independent query with a combination of cte and window functions.
As the correlated sub queries are not so performance always try to avoid them.

If you are looking to learn SQL from basic to advanced with lots of practice material and projects do consider my 0 to hero SQL course :

[shivammishra-mkjp]

I saw multiple videos on this topic, but trust me guy's no one can simplify the lang. or concept other than Mr. Ankit Bansal. Respect you sir for a reason 🙏🙏 thanks a lot❤

[DEEPAK-jxsi]

Here is my Solution
select * from
(select *,
avg(salary) over(partition by department_id ) as avg_dep_sal
from emp) e
where e.avg_dep_sal < e.salary

[prajjwaljaiswal]

Best video in internet till now explaining the difference

[avi]

Great explanation 💯
Would you like more videos like this where you cover important topics.
Thankyou!!😊

[vandanaK-mhzo]

why we need subqueries: when the required format of the data is not given/not available, then we need to derive it and the join it back with the main data.
Independent Subquery - 1. it can be run independently 2. it runs only once
Correlated Subquery - 1. It cannot run independently because it has the reference of the main query 2. it runs for every record of the main query

[ritudahiya]

@ankit bansal please tell for analytics point of view which platform is better for practicing sql.. Leetcode or data lemur?

[anjibabumakkena]

Hi Ankit, Can You explain Performance tuning.

[alkalisblaze]

Hey @ankitbansal6, you missed a paranthesis ')' in your insert statement while inputting for Ankit's entry.

[parth_pm]

Solution using "AVG(salary) over ()"
Solution:
select *
from (select *
, avg(salary) over(partition by department_id) avg_dep_salary
from emp_6_dec) s
where salary>avg_dep_salary

[Shri-RAM-JaiBajrangBali]

Hi Ankit, Please also explain inline queries..

[chiranjeevic]

Hi Ankit, is the PLSQL concepts like stored procedures, triggers important for a data engineer ? If it's important, can you pls make a vedio on it ?

[vinodpaluvuri]

Hi Ankit, can you let us know when to use correlated subquery.

[shruthi.hshruthi]

Hi @ankitbansal6 i m new to sql can anyone explain why cant we use
select department_id, avg(salary) as avgsal
from emp
groupby department_id
having salary> avg(salary)