SQL Left Outer Join Master Class | SQL Advance Tutorial Zero to Hero

In this video we are going to discuss SQL left outer join in detail. Watch this video to master the left join concept in SQL.

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:

script:
create table emp(
emp_id int,
emp_name varchar(20),
dep_id int,
salary int,
manager_id int,
emp_age int);
delete from emp;
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',500,7000,6,50);

#sql #leftjoin #masterclass

[swagatamchakraborty]

Thanks for this masterclass sir! Below is my understanding from the above video :
1.Join condition on right table -> First apply filter on right table and then perform left join with left table
2.Where condition on right table -> First perform left join and then apply filter on the result set
For where condition on right table, inner join is preferred over left join since left join will also return records where there will be NULL values in right table's columns(no match between left and right tables). Since those records will be anyways filtered out due to where condition on right table, so using inner join will ensure those records with NULL values in right table's columns are not coming at all.
3.Join condition on left table -> First filter records from left table and then perform left join. But this will also return records where other records from left table with NULL values in the right table's columns will come in the result set
4.Where condition on left table(preferred over join condition on left table) ->
First perform left join and then apply where condition on left table. Here left join is the correct join since inner join can result in losing records from the left table for which the matching value is not present in the right table

[sharu]

We ignore or take some simple concepts like these for granted and don't give them much thought! This kind of video really makes us think about how code works internally. Thank you once again, Ankit for spending your valuable time on this 😇

[rahulsuwasiya]

SQL Script for dept table:
create table dept(
dep_id int,
dep_name varchar(20));

INSERT INTO dept VALUES (100, 'Analytics'), (200, 'IT'), (300, 'HR'), (400, 'Text Analytics');

[vandanasharma]

this is one of the best video i found on left join till now. Thanks a ton. I used to replicate many oracle sql query into SNOWFLAKE syntax and this was the confusing part everytime. I used to put "where" clause randomly without any conceptual clarity. this video helped me a lot.
My understanding from this video:
1. if you want to filter values from LEFT table, then that condition should be in WHERE clause
2. If you want to filter values from RIGHT table, then that condition should be in AND condition

Please correct.

[Ankansworld]

Thank you Ankit!

What I understood:
Case 1: join having a condition ( on A and B)--> filter on right table and then join.
Case 2: join first and then filter( where clause) on the entire result--> only those rows displayed after join happens which satisfies the where clause. Case 2 works like an inner join + where clause.

[yosupalex]

Very informative!! For ppl who want to practice this concept in leetcode, the problem no. are #262, #550, #1158

[BeingSam]

After Kudvenkat, you're now my favorite in SQL. He is excellent in explaining the concepts and you're amazing in explaining the rest, and this 'rest' is alot, very useful in terms of interview etc.

[KavyashreeAravind-jmik]

Amazing lecture Ankit bhai. Cleared all my doubts. Thank you!

[kashmirshadows]

Thank you Ankit…. Keep sharing and adding value to professional lives

[florincopaci]

When you post a video, you make my day! Thank you!

[dheerajlakkakula]

Hi sir, if we want to do filter records after joining means its better to mention in where clause, not in ON condition?

[vsagarb]

Nice And Clear Explanation on ON and Where conditons. Thanks

[kingsleyelijah]

create table employ(
emp_id int,
emp_name varchar(20),
dept_id int,
salary int,
manager_id int,
emp_age int
);
insert into employ values(1, 'Ankit', 100, 10000, 4, 39);
insert into employ values(2, 'Mohit', 100, 15000, 5, 48);
insert into employ values(3, 'Vikas', 100, 10000, 4, 37);
insert into employ values(4, 'Rohit', 100, 5000, 2, 16);
insert into employ values(5, 'Mudit', 200, 12000, 6, 55);
insert into employ values(6, 'Agam', 200, 12000, 2, 14);
insert into employ values(7, 'Sanjay', 200, 9000, 2, 13);
insert into employ values(8, 'Ashish', 200, 5000, 2, 12);
insert into employ values(9, 'Mukesh', 300, 6000, 6, 51);
insert into employ values(10, 'Rakesh', 500, 7000, 6, 50);
select * from employ;
create table dept1(
dep_id int,
dep_name varchar(20)
);
insert into dept1 values(100, 'Analytics');
insert into dept1 values(200, 'IT');
insert into dept1 values(300, 'HR');
insert into dept1 values(400, 'Text Analytics');
select * from dept1;

[juanmicolta]

Almost 2025 and your videos keep relevant. Please continue sharing your wisdom with the world

[abhishek_grd]

Perfect explanation ! All cleared and brushed up .

[senthamilselvan]

Very useful.. keep doing the good work 😀

[tupaiadhikari]

Great Video Ankit. Thanks for the Clarification.

[AYUSHGUPTA-fdon]

Hi @Ankit If we are applying filter clause at the end of left join then why it is not shifting the where condition up I mean Predicate push down will happen right?

[Happusingh]

Hi Ankit,
Thank you for your continuous commitment to teach us.. Just a quick question..
Are both the queries same?(If there is any catch or any exception when this could be same or different then plz tell us that as well)

1. select * from emp e
Inner join dept d
on e.id = d.id
and e.key = d.key;

2. select * from emp e
Inner join dept d
on e.id = d.id
where e.key = d.key;

Looking forward for your response!
Thanks in advance!

[abhishekarora]

Thanks for making a video on my suggestion (maybe others as well) !
I would really appreciate it if you can make more python related videos as well!