SQL Full Outer Join Using UNION For MySQL

Thanks bhai😊😊😊

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

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

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

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.

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

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 🙂

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

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

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

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

Very nice video, thanks!

Please share the script of these two tables.

can we do using the below code?

Select c.customer_id , c.customer_name , co.customer_id , co.order_date from customers c
Left join
Costomers_orders co
On c.customer_id = co.customer_id

Union all

Select c.customer_id , c.customer_name , co.customer_id , co.order_date from customers c
Right join
Costomers_orders co
On c.customer_id = co.customer_id;

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.

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

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

Amazing as usual ! Thanks

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

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

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

seems interesting

Seems interesting