[@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 😊

[@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)

[@gameply347]

Amazing video got to learn so many things

[@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 🙂

[@mohit231]

Really Thankful for your content.

[@DRahulRajuYadav]

Excellent Explanation!!!!

[@milindzuge906]

Goldmine Content bro..!!❣

[@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');

[@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!

[@Moon_Rise6393]

Awesome Bansal Bro 👍👍👍

[@sauravips]

Crisp and clear explanation. Very well explained

[@ankitbansal6]

Glad you liked it✌️

[@prashantjaiswal5260]

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

[@ankitbansal6]

It's my pleasure

[@ajinkya-eigteen5144]

Very Useful Thank you so much

[@SudhirKumar-rl4wt]

thanks for the question and solution

[@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

[@rajorshi1000]

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

[@ankitbansal6]

Yes it is very handy 😊

[@priyankasrivastava3247]

love your videos

[@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

[@vutv5742]

Completed ❤

[@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

[@jaybharat3949]

Omg😮😮…thanks sir❤

[@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

[@innominatesoloist1597]

brilliant

[@sjain5]

This channel is Goldmine

[@ankitbansal6]

Thank you 😊

[@BHS0668]

Great video 👍

[@ankitbansal6]

Thank you 😊

[@vijaypalmanit]

Bhai bhai bhai ♥️

[@ankitbansal6]

Aree bhai 🙂

[@codinguniverse9716]

AWESOME BRO

[@ankitbansal6]

Thanks 🤗

[@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?

[@alex45688]

wow
awesome

[@ankitbansal6]

Thanks 🤗

[@Mysingh9767]

Thanks...

[@ankitbansal6]

You're welcome!

[@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!

[@dikshachourasiya5561]

Very nice

[@ankitbansal6]

Thanks

[@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!

[@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!

[@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)

[@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

[@chaitanyasalagala1661]

can't we do union instead of using is 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;

[@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

[@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

[@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..

[@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

[@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.

[@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.

[@uttamthakur2016]

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

[@ankitbansal6]

ua-cam.com/video/Ypwfq5tNw-4/v-deo.html

[@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]

ua-cam.com/video/ZFxCgxS1b7Y/v-deo.html

[@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;

[@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
ua-cam.com/video/ZFxCgxS1b7Y/v-deo.html

[@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')

[@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

[@ratneshraj4653]

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

[@ankitbansal6]

Thats the assignment for you

[@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

[@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;

[@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.

[@Venom-yk3wu]

is fulljoin n full oter the same ?

[@ankitbansal6]

Yes

[@RIKKY988]

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

[@ankitbansal6]

Thanks for pointing out. I will check.

[@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

[@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')

[@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;