Independent vs Correlated Subquery | Advanced SQL Tutorial For Beginners

  Рет қаралды 13,121

Ankit Bansal

Ankit Bansal

Күн бұрын

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:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #dataengineer #analytics

Пікірлер: 31
@ankitbansal6
@ankitbansal6 7 ай бұрын
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 : www.namastesql.com/courses/SQL-For-Analytics-6301f405e4b0238f71788354
@jayselokar
@jayselokar 7 ай бұрын
also correlated is more time consuming
@UnrealAdi
@UnrealAdi 7 ай бұрын
Hi @ankitbansal6, the script provided don't match with the result set of the emp table! Fixing it may be useful. Also, thanks for the incredible content as usual!
@shivammishra-mk9jp
@shivammishra-mk9jp 29 күн бұрын
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-jx5si
@DEEPAK-jx5si 7 ай бұрын
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
@prajjwaljaiswal3419
@prajjwaljaiswal3419 7 ай бұрын
Best video in internet till now explaining the difference
@avi8016
@avi8016 7 ай бұрын
Great explanation 💯 Would you like more videos like this where you cover important topics. Thankyou!!😊
@ankitbansal6
@ankitbansal6 7 ай бұрын
Sure 😊
@vandanaK-mh9zo
@vandanaK-mh9zo 7 ай бұрын
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
@gazart8557
@gazart8557 7 ай бұрын
Excellent
@ayyappansri
@ayyappansri 2 ай бұрын
Ultimate explanation ❤
@ankitbansal6
@ankitbansal6 2 ай бұрын
Thanks a lot 😊
@Shri-RAM-JaiBajrangBali
@Shri-RAM-JaiBajrangBali 7 ай бұрын
Hi Ankit, Please also explain inline queries..
@sandipansarkar9211
@sandipansarkar9211 7 ай бұрын
finished watching
@ritudahiya6223
@ritudahiya6223 7 ай бұрын
@ankit bansal please tell for analytics point of view which platform is better for practicing sql.. Leetcode or data lemur?
@ankitbansal6
@ankitbansal6 7 ай бұрын
DataLemur
@alkalisblaze
@alkalisblaze 7 ай бұрын
Hey @ankitbansal6, you missed a paranthesis ')' in your insert statement while inputting for Ankit's entry.
@ankitbansal6
@ankitbansal6 7 ай бұрын
Thanks 🙏
@anjibabumakkena
@anjibabumakkena 7 ай бұрын
Hi Ankit, Can You explain Performance tuning.
@ankitbansal6
@ankitbansal6 7 ай бұрын
Sure
@chiranjeevic8384
@chiranjeevic8384 7 ай бұрын
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 ?
@ankitbansal6
@ankitbansal6 7 ай бұрын
Not so important
@tumusaikarthik6632
@tumusaikarthik6632 7 ай бұрын
Mostly core sql is used 95% the select queries
@vinodpaluvuri54
@vinodpaluvuri54 7 ай бұрын
Hi Ankit, can you let us know when to use correlated subquery.
@ankitbansal6
@ankitbansal6 7 ай бұрын
Check my pinned comment
@navneethks8677
@navneethks8677 3 ай бұрын
Very Helpful 🫂🫂
@parth_pm16
@parth_pm16 7 ай бұрын
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
@shruthi.hshruthi3965
@shruthi.hshruthi3965 7 ай бұрын
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)
@hamzarahman7763
@hamzarahman7763 7 ай бұрын
if you want to solve this way you should Learn Self Join first... Hint: you have to separate avg(salary) into different intermediate table, because code don't know whats is avg(salary)..
@suriyas6338
@suriyas6338 4 ай бұрын
Hey, To know this, please get the understanding of sql order of execution. You'll be able to understood
@sandipansarkar9211
@sandipansarkar9211 7 ай бұрын
finished watching
Amazing weight loss transformation !! 😱😱
00:24
Tibo InShape
Рет қаралды 62 МЛН
Fast and Furious: New Zealand 🚗
00:29
How Ridiculous
Рет қаралды 41 МЛН
Викторина от МАМЫ 🆘 | WICSUR #shorts
00:58
Бискас
Рет қаралды 3,1 МЛН
SQL | NOT IN Vs NOT EXISTS (Which one to use?)
10:03
Learn at Knowstar
Рет қаралды 10 М.
Amazing weight loss transformation !! 😱😱
00:24
Tibo InShape
Рет қаралды 62 МЛН