SQL Interview Question Based on Full Outer Join | Asked in Deloitte

In this video we will learn how to deal with full outer joins. In full outer join we need to deal with lot of null values so it is important to understand this concept.
This question was also asked to me in Deloitte Interview way back in 2017.
Here is the ready script:
create table emp_2020
(
emp_id int,
designation varchar(20)
);
create table emp_2021
(
emp_id int,
designation varchar(20)
)
insert into emp_2020 values (1,'Trainee'), (2,'Developer'),(3,'Senior Developer'),(4,'Manager');
insert into emp_2021 values (1,'Developer'), (2,'Developer'),(3,'Manager'),(5,'Trainee');
#sql #interview #fullouterjoin #outerjoin #fulljoin

[@wizardop2100]

Your ability to divide complex problems into simpler ones is phenomenal. Thank you so much for the sql content. keep going

[@ankitbansal6]

Cheers

[@aj-lan284]

Kya SQL coding skill hai bhai😍😍😍 Ekdm kadak

[@deepthimurali962]

Thank you Ankit for clear explanation! Keep the videos coming :) Its super useful.

[@brijendramohangupta9928]

Loved your step-by-step approach towards solving complex SQL queries. Hope to see more similar videos in the coming days and really appreciate your hard work in making such a helpful content.

[@ankitbansal6]

Thanks bro 😊

[@DreamHome_Shorts]

great example to understand join as well as switch case.

[@LS8636]

I really appreciate your efforts bhai. All your tutorials are very information, best SQL content on youtube.

[@ankitbansal6]

Thank you 😊

[@DRahulRajuYadav]

Excellent Explanation!!!!

[@milindzuge906]

Goldmine Content bro..!!❣

[@kadamteja5743]

Hi sir, Really appreciate your efforts in making the things simpler to understand the complex SQL queries. A big thanks for your content on SQL. Waiting for more scenarios based questions.....Thanks!!!!!👏👏

[@ankitbansal6]

Thank you for watching 🙂

[@gameply347]

Amazing video got to learn so many things

[@mohit231]

Really Thankful for your content.

[@abhisharma7780]

select COALESCE (e1.emp_id, e2.emp_id), case when e2.emp_id is null then 'Resigned' when e1.emp_id is null then 'Newly joined' else 'Promoted' end as status from emp_2020 e1 full outer join emp_2021 e2 on e1.emp_id=e2.emp_id where (e1.emp_id is null or e2.emp_id is null) or (e1.designation e2.designation)

[@deeptisharma90]

i was able to write the query except using isnull for the emp_id in the very end. Proud of my persistence. Thank you for providing great informative content for free!

[@ankitbansal6]

Excellent!

[@somanathking4694]

Thank you for wonderful teachings sir! I have tried this.. WITH temp AS ( SELECT e1.emp_id AS emp_id_2020, e2.emp_id AS emp_id_2021, CASE WHEN e1.designation = 'Trainee' AND e2.designation = 'Developer' OR e1.designation = 'Senior Developer' AND e2.designation = 'Manager' THEN 'promoted' WHEN e2.designation = 'Trainee' AND e1.designation IS NULL THEN 'New' END AS new_column FROM emp_2020 AS e1 FULL OUTER JOIN emp_2021 AS e2 ON e1.emp_id = e2.emp_id ) SELECT emp_id_2021,new_column FROM temp WHERE emp_id_2021 is not null and new_column is not null;

[@bhavani-gy6ts]

clearly explained thank you so much ankit sir. for those who are writing in mysql... In mysql, full outer join is not available but can achieve using union. below sol is in mysql with cte as (select e1.emp_id as id1,e1.designation as d1,e2.emp_id as id2,e2.designation as d2 from emp_2022 e1 left join emp_2023 e2 on e1.emp_id=e2.emp_id union select e1.emp_id as id1,e1.designation as d1,e2.emp_id as id2,e2.designation as d2 from emp_2022 e1 right join emp_2023 e2 on e1.emp_id=e2.emp_id) select ifnull(id1,id2) as emp_id, case when d1!=d2 then 'promoted' when d2 is null then 'Resigned' else 'New Joined' end as comment from cte where ifnull(d1,'xx') != ifnull(d2,'yy');

[@Moon_Rise6393]

Awesome Bansal Bro 👍👍👍

[@ajinkya-eigteen5144]

Very Useful Thank you so much

[@sauravips]

Crisp and clear explanation. Very well explained

[@ankitbansal6]

Glad you liked it✌️

[@SudhirKumar-rl4wt]

thanks for the question and solution

[@priyankasrivastava3247]

love your videos

[@prashantjaiswal5260]

very good and indepth video ...Thanku sir for making this available to us

[@ankitbansal6]

It's my pleasure

[@jaybharat3949]

Omg😮😮…thanks sir❤

[@vandanasharma4738]

loved the way you are explaining. Thank you for sharing knowledge. More power to you!! . Keep guiding us. select e20.*, e21.*,coalesce(e20.emp_id, e21.emp_id) as finalid, case when e21.designation e20.designation then 'Promoted' when e21.emp_id not in (select emp_id from emp_2020) then 'New' when e20.emp_id not in (select emp_id from emp_2021) then 'Resigned' end as designation from emp_2020 e20 full outer join emp_2021 e21 on e20.emp_id = e21.emp_id where NVL(e21.designation, 'x') != NVL(e20.designation, 'y');

[@ankitbansal6]

It's my pleasure

[@moyeenshaikh9915]

still not getting why isnull is required in last statement manager was euqla to manager so it should not have come na emp id 2 one?

[@vutv5742]

Completed ❤

[@rajorshi1000]

A complex problem made very easy. isnull is really very powerful as shown by you.

[@ankitbansal6]

Yes it is very handy 😊

[@sjain5]

This channel is Goldmine

[@ankitbansal6]

Thank you 😊

[@ahsan_habib_sunny]

we also can join the two table on emp_id and filter based on designation: select coalesce(a.emp_id,b.emp_id), case when a.designation b.designation then 'Promoted' when a.designation is not NULL and b.designation is NULL then 'Resigned' else 'New' end as Comment from emp_2020 a full join emp_2021 b on a.emp_id = b.emp_id where a.designation b.designation or a.designation is null or b.designation is null

[@Alexpudow]

Hi Ankit, good explanation thanks a lot. I have my own one. with a as ( select designation, rank() over(order by emp_id desc) ernk from emp_2020), b as ( select a.emp_id, a.designation, a1.ernk from emp_2021 a join a a1 on a.designation = a1.designation), c as ( select a.emp_id, a.designation, a1.ernk from emp_2020 a join a a1 on a.designation = a1.designation) select case when c.emp_id is null then b.emp_id when b.emp_id is null then c.emp_id else c.emp_id end emp_id ,case when c.ernk is null then 'new' when c.ernk>b.ernk then 'promoted' when c.ernk=b.ernk then 'same' else 'resigned' end comment from c full join b on c.emp_id=b.emp_id order by 1

[@meghnasoni]

I tried solving it this way, however Ankit's approach is obviously far better: SELECT *, case when e20.designation = e21.designation then 'same' when e21.designation is null then 'resigned' when e20.designation e21.designation then 'promoted' when e20.designation is null then 'New Joinee' end as flag from emp_2020 e20 full join emp_2021 e21 on e20.emp_id=e21.emp_id In the real world , promoted case statement logic will not work as people can be demoted, moved to different departments and we'd have to write separate logic for those cases

[@chaitanyasalagala1661]

can't we do union instead of using is null?

[@BHS0668]

Great video 👍

[@ankitbansal6]

Thank you 😊

[@innominatesoloist1597]

brilliant

[@anishchhabra6085]

I solved this question using 2 case-when statements, can you let me know if this is a good approach or not? with cte as ( select e20.emp_id as e20_emp_id,e21.emp_id as e21_emp_id, case when e21.emp_id = e20.emp_id and e21.designation e20.designation then 'Promoted' when e21.emp_id not in (select distinct emp_id from emp_2020) then 'New' when e20.emp_id not in (select distinct emp_id from emp_2021) then 'Resigned' else 'NA' end as pos from emp_2020 e20 cross join emp_2021 e21 ) select distinct case when pos = 'New' then e21_emp_id when pos = 'Resigned' then e20_emp_id else e20_emp_id end as emp_id, pos from cte where pos 'NA' order by emp_id asc;

[@dikshachourasiya5561]

Very nice

[@ankitbansal6]

Thanks

[@codinguniverse9716]

AWESOME BRO

[@ankitbansal6]

Thanks 🤗

[@vijaypalmanit]

Bhai bhai bhai ♥️

[@ankitbansal6]

Aree bhai 🙂

[@alex45688]

wow awesome

[@ankitbansal6]

Thanks 🤗

[@abhishek_grd]

@Ankit, earlier i was able to switch to the quality of the videos now looks like it was either disabled or saying unavailable. Can you have a look at it.

[@ankitbansal6]

Abhishek I am able to switch to HD .. not sure what could be the issue with your system

[@Mysingh9767]

Thanks...

[@ankitbansal6]

You're welcome!

[@florincopaci6821]

All your problems are good and useful not only this!

[@ankitbansal6]

Thank you. I got the solution for bus passenger but it is bit complex. I am on it to find a simpler solution.

[@florincopaci6821]

@@ankitbansal6 You welcome! I learned a lot from you ,from your videos and only if is possible to help with that problem! Thank you for all the videos! In this way every trainee in the companies should explain Sql but unfortunately is not happened like this. I hope you will find a solution to that problem! All the best! and good luck!

[@ShivaKumarTeam]

in the above video first part, we have used "is null" and "isnull"- what is the dif between "is null" and "isnull", is both are same? how to identify which one to use and when to use?

[@ManpreetSingh-tv3rw]

isnull is a function which requires 2 arguments , is null is an operator.

[@SaurabhHiteshbhaiBhoi]

I am unable to do full outer join in MySQL and I have already tried union of left and right join but the result I got is not same as yours what should I do?

[@ankitbansal6]

kzbin.info/www/bejne/kHfbdJquiJaVbbs

[@prajjwaljaiswal3419]

In MySQL workbench Full outer join doesn't work. Using (left + right) but getting stuck. Any alternate solution without using full outer join?

[@ankitbansal6]

Check this out kzbin.info/www/bejne/kHfbdJquiJaVbbs

[@mohammadabdullahansari6314]

Check my query: select e1.emp_id, case when e1.designation != e2.designation then 'Promoted' when e1.designation is null then 'New' else 'Resigned' end as comment from emp_2020 e1 left join emp_2021 e2 on e1.emp_id = e2.emp_id where ifnull(e1.designation,'xxx') != ifnull(e2.designation,'yyy') union select e2.emp_id, case when e1.designation != e2.designation then 'Promoted' when e1.designation is null then 'New' else 'Resigned' end as comment from emp_2020 e1 right join emp_2021 e2 on e1.emp_id = e2.emp_id where ifnull(e1.designation,'xxx') != ifnull(e2.designation,'yyy')

[@iamsatyadeep]

Hi Ankit, while comparing designations in case statement, how can we compare 'null' designation with designation having some values?

[@ankitbansal6]

You can do null handling using isnull or coalesce function . By giving some default value you can compare with not null values

[@iamsatyadeep]

@@ankitbansal6 true. But in your query in case statement you haven't handled null values still the output was correct, how? Also in joining condition if we haven't put null handling the row was getting dropped then why it's happening in case statement? I hope you get my question.

[@ankitbansal6]

@@iamsatyadeep in case statement it is going into else condition.

[@Buzzingfact]

Nice video.. but can you create videos on store procedure use cases in data transformation/real scenarios as this is very less available in youtube.

[@ankitbansal6]

Okay

[@sumanacharya461]

I actually tried the question before watching the video , so my solution I think is bit messy select (case when emp1=emp2 then emp1 when emp1 is NULL then emp2 else emp1 end), (case when des1 is NULL then 'New' when des2 is NULL then 'Resigned' else 'Promoted' end) from (select e20.emp_id as emp1,e21.emp_id as emp2, e20.designation as des1 ,e21.designation as des2 from emp_2020 as e20 full outer join emp_2021 as e21 on e20.emp_id = e21.emp_id where e20.designation is NULL or e21.designation is NULL or e20.designation != e21.designation)

[@uttamthakur2016]

Any videos where you Explained where clause and ON clause difference , Please let me know the link , I need to understand .

[@ankitbansal6]

kzbin.info/www/bejne/j6Hal6RrqbOqY5Y

[@parth_pm16]

I've got a lengthy solution, but after seeing your query, today I got to know a new thing which is "isnull" as a function. My sloution: with cte as( select emp_2020.emp_id as id_20, emp_2021.emp_id as id_21 ,case when emp_2020.designation!=emp_2021.designation then 'Promoted' when emp_2021.emp_id is null then 'Resigned' when emp_2020.emp_id is null then 'Traniee' end as designation1 from emp_2020 full outer join emp_2021 on emp_2020.emp_id = emp_2021.emp_id) select id_20 as emp_id,designation1 from cte where id_20 is not null and designation1 is not null union select id_21 as emp_id,designation1 from cte where id_21 is not null and designation1 is not null

[@reshabsharma5711]

with cte is not working in my case

[@parth_pm16]

@@reshabsharma5711 What error you facing?

[@reshabsharma5711]

not exactly error, I think cte doesn't work in postgress@@parth_pm16

[@parth_pm16]

@@reshabsharma5711 Thank you for your response!

[@ManpreetSingh-tv3rw]

Solved a similar question on Leetcode using above concept. Write an SQL query to report the IDs of all the employees with missing information. The information of an employee is missing if: The employee's name is missing, or The employee's salary is missing. select isnull(e.employee_id,s.employee_id) as employee_id from employees e full outer join salaries s on e.employee_id = s.employee_id where e.name is NULL or s.salary is null order by employee_id

[@UnrealAdi]

Tried in MySQL: select *, case when e.designation != e1.designation then 'Promoted' when e.designation = e1.designation then 'No Change' when e.emp_id is null then 'New Joinee' when e1.emp_id is null then 'Resigned' end stat from emp_2020 e left join emp_2021 e1 on e.emp_id = e1.emp_id where e.designation != e1.designation or e1.emp_id is null union select *, case when e.designation != e1.designation then 'Promoted' when e.designation = e1.designation then 'No Change' when e.emp_id is null then 'New Joinee' when e1.emp_id is null then 'Resigned' end stat from emp_2020 e right join emp_2021 e1 on e.emp_id = e1.emp_id where e.designation != e1.designation or e.emp_id is null ; Please, feel free to optimize the query. Also, excellent work Ankit! Kudos!

[@akshayjoshi4106]

Hi Ankit, full outer join not giving same results as in oracle sql . Any idea

[@tiyashachakraborty7512]

Do left join twice and then use Union all

[@ratneshraj4653]

What if here we also had to check for demotions .. assuming few candidate were demoted?

[@ankitbansal6]

Thats the assignment for you

[@narenkrishh7412]

Hi brother! All your videos are nice and refreshing my basics. Currently I am working as SQL developer developing reports for fintech AMC companies for past two years. What can I learn to progress more in this domain. Some are saying snowflake some are saying Hadoop spark and also some with plsql..I am very much confused with what to chose and study. Pls suggest me a good career path.. Thanks in advance!

[@ankitbansal6]

Don't think too much. Pick one and learn it. There are opportunities in all technologies. Hadoop spark or snowflake or AWS or GCP or azure. Pick any one whichever you find interesting.

[@ankitbansal6]

If you are into reporting you can choose BI or Data analyst path . Learn tableau or powe bi

[@abhishek_grd]

And what if the employee gets demoted. How to handle then ??

[@ankitbansal6]

Then we need to have some hierarchy so we can identify if it is promotion or demotivate. Based on that we can change case when.

[@shyamshivakumar7807]

with cte as ( select * ,'2020' as year_status from emp_2020 union all select * ,'2021' as year_status from emp_2021 order by emp_id asc ) ,cte1 as ( select * ,row_number() over(partition by emp_id) as rnk from cte order by emp_id asc,rnk desc ) select emp_id ,case when count(distinct designation)='2' and rnk='2' then 'Promoted' when count(distinct designation)='1' and rnk='2' then 'Same Designation' when rnk='1' and year_status='2020' then 'Resigned' when rnk='1' and year_status='2021' then 'New_Joiner' end as emp_status from cte1 group by emp_id Different method but not optimized query... Hope it is easy to understand and this will work in all input cases..

[@Venom-yk3wu]

is fulljoin n full oter the same ?

[@ankitbansal6]

Yes

[@mradulgupta]

select a.emp_id,a.comment from (select coalesce(a.emp_id,b.emp_id) emp_id,a.designation as prev_designation,b.designation as curr_designation, case when b.designation is null then 'Resigned' when a.designation!=b.designation then 'Promoted' when a.designation is null then 'New' end as comment from emp_2020 a full outer join emp_2021 b on a.emp_id=b.emp_id) a where a.comment is not null

[@ujjwalvarshney3188]

select nvl(a.emp_id,b.emp_id) ,Case when a.designation = b.designation then 'Not Promoter' when a.designation is null then 'New Employee' when b.designation is null then 'Resigned' else 'Promoted' end as Comment from emp_2020 a full join emp_2021 b on a.emp_id = b.emp_id order by 1;

[@NitishKumar-xr9tx]

MySQL Solution: with cte as( select e1.emp_id id_2020, e1.designation des_2020, e2.emp_id id_2021, e2.designation des_2021 from emp_2020 e1 Left JOIN emp_2021 e2 on e1.emp_id = e2.emp_id UNION select e1.emp_id id_2020, e1.designation des_2020, e2.emp_id id_2021, e2.designation des_2021 from emp_2020 e1 Right JOIN emp_2021 e2 on e1.emp_id = e2.emp_id ) select case when id_2020 is Not Null then id_2020 else id_2021 end as emp_id, case when des_2020 is NULL then 'new' when des_2021 is NULL then 'resigned' when des_2020 des_2021 then 'promoted' end as new_des from cte where des_2020 des_2021 or des_2020 is NULL or des_2021 is NULL;

[@RIKKY988]

Your voice quality is not good bro .pls change your microphone

[@ankitbansal6]

Thanks for pointing out. I will check.

[@dip_agr]

@ankitbansal6 brother In mysql workbench, its succesfully running the following code but also resulting that developer column which was same, which was suppose to be removed right, can you please help me with it. select * from emp_2020; select * from emp_2021; select e20.*, e21.* from emp_2020 e20 left join emp_2021 e21 on e20.emp_id=e21.emp_id union select * from emp_2020 e20 right join emp_2021 e21 on e20.emp_id=e21.emp_id where coalesce(e20.designation,'xxx') != coalesce(e21.designation,'yyy')

[@imnishantm]

select * from (select isnull(e1.emp_id,e2.emp_id) as emp_id, case when e1.designation!=e2.designation then 'Promoted' when e1.designation is null then 'New' when e2.designation is null then 'Resigned' end as Comment from emp_2020 e1 full outer join emp_2021 e2 on e1.emp_id=e2.emp_id)t where Comment is not null

[@grim_rreaperr]

WITH CTE AS ( SELECT COALESCE(e1.emp_id, e2.emp_id) AS emp_id, e1.designation AS initial, e2.designation AS final FROM emp_2020 AS e1 FULL OUTER JOIN emp_2021 AS e2 ON e1.emp_id = e2.emp_id ) SELECT emp_id, initial,final, (CASE WHEN initial = final THEN 'same' WHEN initial IS NULL THEN 'New' WHEN final IS NULL THEN 'Resigned' ELSE 'Promoted' END) AS status FROM CTE

[@umakantkhandekar5748]

with c as( select n.emp_id new_emp_id ,n.designation new_design, o.emp_id old_id,o.designation old_desig from emp_2021 n full outer join emp_2020 o on n.emp_id=o.emp_id ) select new_emp_id,'Promoted' from c where isnull(new_design,'')!=isnull(old_desig,'') and new_design is not null and old_desig is not null union select old_id,'Resigned' from c where new_design is null and old_desig is not null union select new_emp_id,'new' from c where new_design is not null and old_desig is null

[@kartikpidurkar9590]

MySQL Solution: with cte as( select e1.emp_id,e1.designation as 'D1',e2.designation as 'D2' from emp_2020 e1 left join emp_2021 e2 on e1.emp_id=e2.emp_id UNION select e2.emp_id,e1.designation,e2.designation from emp_2020 e1 RIGHT join emp_2021 e2 on e1.emp_id=e2.emp_id) select emp_id, CASE WHEN D2 IS NULL THEN 'Resigned' WHEN D1=D2 THEN 'NO_Promotion' WHEN D1 IS NULL THEN 'New_Joinee' ELSE 'PROMOTED' END AS 'STATUS' FROM cte;

[@subhojitchatterjee6312]

Solution using MySql. The isnull trick didn't click so I went the long route!!! WITH CTE AS ( SELECT T1.EMP_ID AS E1,T1.DESIGNATION AS D1,T2.EMP_ID AS E2,T2.DESIGNATION AS D2 FROM emp_2020 T1 LEFT JOIN emp_2021 T2 ON T1.EMP_ID=T2.EMP_ID), CTE_2 AS ( SELECT T1.EMP_ID AS E1,T1.DESIGNATION AS D1,T2.EMP_ID AS E2,T2.DESIGNATION AS D2 FROM emp_2020 T1 RIGHT JOIN emp_2021 T2 ON T1.EMP_ID=T2.EMP_ID) SELECT * FROM (SELECT E1 AS EMP_ID ,CASE WHEN D1D2 THEN "PROMOTED" WHEN D2 IS NULL THEN "RESIGNED" END AS STATUS FROM CTE UNION SELECT E2 AS EMP_ID,CASE WHEN E1 IS NULL THEN "NEW" END AS STATUS FROM CTE_2) T1 WHERE STATUS IS NOT NULL;