SQL Full Outer Join Using UNION For MySQL

MySQL does not support full outer join. In this video we will see how we can make use of UNION to get full outer join.

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 #mysql

[NitishKumar-xrtx]

The child in the background will proud to watch these lectures in the Future😜

[karangupta_DE]

Hi Ankit, could you kindly make a video and talk about EXISTS and NOT EXISTS.

[explorer_baba]

Last week, One interviewer asked same question from me. 🙂

[navejpathan]

I think UNION will also give same result. So no need to use where condition if we use UNION only, because UNION will remove duplicate. Please correct me if I am wrong.

[anshulgupta]

Thanks @Ankit. This is very clear. those are looking for DDL-
--DDL Statement
Drop table Customer
Drop table Customer_order
Create Table Customer (Customer_id int, customer_name Varchar(20))
Create Table Customer_order (Customer_id int, orderDate date)

Insert into Customer Values (1, 'A')
Insert into Customer Values (2, 'B')
Insert into Customer Values (3, 'C')
Insert into Customer Values (4, 'D')
Insert into Customer Values (5, 'E')

Insert into Customer_order Values (1, '2022-01-05')
Insert into Customer_order Values (2, '2022-01-06')
Insert into Customer_order Values (3, '2022-01-07')
Insert into Customer_order Values (4, '2022-01-08')
Insert into Customer_order Values (6, '2022-01-09')


Select * From Customer
Select * From Customer_order

--FULL outer JION
Select C.*, CO.*
From Customer C FULL OUTER JOIN Customer_order co
ON C.Customer_id = co.Customer_id

--UNION ALL

Select C.*, CO.*
From Customer C LEFT JOIN Customer_order CO
ON C.Customer_id = co.Customer_id
UNION ALL
Select C.*, CO.*
From Customer C RIGHT JOIN Customer_order CO
ON C.Customer_id = co.Customer_id
WHERE c.Customer_id IS NULL

--UNION only
Select *, ROW_NUMBER() over (ORDER BY countera) as counterB
From
(
Select C.Customer_id as CCustomerID, c.customer_name, CO.Customer_id as COCustomerID, Co.orderDate, ROW_NUMBER() over (ORDER BY C.Customer_id) as countera
From Customer C LEFT JOIN Customer_order CO
ON C.Customer_id = co.Customer_id
UNION
Select C.Customer_id as CCustomerID, c.customer_name, CO.Customer_id as CoCustomerID, Co.orderDate, ROW_NUMBER() over (ORDER BY CO.Customer_id) as countera
From Customer C RIGHT JOIN Customer_order CO
ON C.Customer_id = co.Customer_id
) X

[ayushishukla]

Requesting you please do a video on UNION and UNION ALL. I sometimes find it really confusing like here why did we go with union all and not union. Even tho when UNION gives us the unique entries not like UNION ALL that gives duplicates???

[aniketraut]

Thank you Ankit, your videos are helping me a lot to learn new things related with sql. keep doing the good work. God bless you.

[abhishek_grd]

Ankit ! Can you share the DDL for this as well?

[asthapatel]

I have to join two tables is it wrong if i write table 1 union all table 2 instead of table 1 left join table 2 union all table 2 left join table 1

[kolisettysasiram]

union of left join and right join also give same result right... since we use union, it will remove duplicates.. now my doubt is why you choose where condition for this question.. Any reason behind that rather than using union of right & left join ??

[nikhilarora]

I want to calculate TTM (Trailing Twelve month Revenue), I have each quarter revenue, , time how could i calculate this TTM?

[SuperMohit]

In MYSQL there is not separate command for full outer join. Anytime I do a full outer join I use this method!!

[laxmanrao]

Thanks a lot for the video
May i know the difference between union and full outer join with some example

[ammani]

Sir can we do left join and right join then combining by union to get full outer join?

[sivasrimakurthi]

this is 1 comprehensive video about the Window Frames class, it did explain the aggregation logic on windows, Frames etc very well. Loved the content, keep rocking @AnkitBansal 🙂

[architgarg]

If we use union instead of union all then we dont need to use null condition right??

[saivaibhav]

Please share the script of these two tables.