The child in the background will proud to watch these lectures in the Future😜
@ankitbansal6
Жыл бұрын
😊
@anshulgupta1611
Жыл бұрын
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
@karangupta_DE
2 жыл бұрын
Hi Ankit, could you kindly make a video and talk about EXISTS and NOT EXISTS.
@navejpathan
2 жыл бұрын
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.
@mohammadabdullahansari6314
2 жыл бұрын
You are correct
@vigneshnagaraj7137
2 жыл бұрын
Actually if you do UNION it will give same result. I agree. But null values will come at the top which won't look good. In case of union all , take the left join first , take the right join next using where particular column is null ( which will produce only null values ). Now try using UNION ALL to get the desired result. In second case you will get the null values at the end which is same full join.
@vandanasharma4738
Жыл бұрын
@@vigneshnagaraj7137 I tried this, but this is not the case. Even for UNION, NULL values are coming in the last. FYI, I am working on SNOWFLAKE.
@explorer_baba2750
2 жыл бұрын
Last week , One interviewer asked same question from me. 🙂
@ayushishukla2333
2 жыл бұрын
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???
@ankitbansal6
2 жыл бұрын
Okay
@aniketraut6864
Жыл бұрын
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.
@ankitbansal6
Жыл бұрын
Glad to hear that
@abhishek_grd
2 жыл бұрын
Amazing as usual ! Thanks
@utkarshdixit3502
2 жыл бұрын
Very nice video, thanks!
@abhishek_grd
2 жыл бұрын
Ankit ! Can you share the DDL for this as well?
@anshulgupta1611
Жыл бұрын
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
@sivasrimakurthi206
Жыл бұрын
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 🙂
@kolisettysasiram7732
2 жыл бұрын
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 ??
@ankitbansal6
2 жыл бұрын
UNION is not good for performance. With union all we can just get exact data without duplicates so we don't have to use union. For second query we need records which are present only in right table so that's why null condition.
@kolisettysasiram7732
2 жыл бұрын
@@ankitbansal6 Thanks for the clarification
@virencablenet9546
2 жыл бұрын
Union all may be used i believe Correct me if I am wrong
@vigneshnagaraj7137
2 жыл бұрын
Actually if you do UNION it will give same result. I agree. But null values will come at the top which won't look good. In case of union all , take the left join first , take the right join next using where particular column is null ( which will produce only null values ). Now try using UNION ALL to get the desired result. In second case you will get the null values at the end which is same full join.
@laxmanrao9394
Жыл бұрын
Thanks a lot for the video May i know the difference between union and full outer join with some example
@ankitbansal6
Жыл бұрын
Check this out kzitem.info/news/bejne/0qqYrqWHj4qYaoI
@SuperMohit95
2 жыл бұрын
In MYSQL there is not separate command for full outer join. Anytime I do a full outer join I use this method!!
@ankitbansal6
2 жыл бұрын
Yup
@vutv5742
8 ай бұрын
Completed ❤
@asthapatel2924
Жыл бұрын
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
@ankitbansal6
Жыл бұрын
It depends on your use case
@ammani2618
Жыл бұрын
Sir can we do left join and right join then combining by union to get full outer join?
@ankitbansal6
Жыл бұрын
Yes
@architgarg97288
2 жыл бұрын
If we use union instead of union all then we dont need to use null condition right??
@ankitbansal6
2 жыл бұрын
Union will not help
@karangupta_DE
2 жыл бұрын
union would work when there are no duplicates in either of the two tables, but union will not give the correct result in case when you have a duplicate in one or both tables, if we do 'a' left join 'b' union 'b' left join 'a' , then it will drop the duplicates from the result and give you lesser rows compared to what a full outer join would have produced.
@arihantmaloo5089
2 жыл бұрын
@@ankitbansal6 can you please explain why Union will not help
@ankitbansal6
2 жыл бұрын
@@arihantmaloo5089 You don't need union here . We do not have any use case here to remove duplicates.
@anshulgupta1611
Жыл бұрын
I see union will work but yes NULL will be coming first. but this can also be handled with ORDER BY and other costume order BY ex:- 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
@hackingrabi458
2 жыл бұрын
Seems interesting
@nikhilarora7549
2 жыл бұрын
I want to calculate TTM (Trailing Twelve month Revenue), I have each quarter revenue,, time how could i calculate this TTM?
@pavan5208
2 жыл бұрын
Considering your table has 2 columns: Qtr and Revenue, this should work. SELECT QTR ,Revenue ,SUM(REVENUE) OVER(ORDER BY QTR ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) AS TTM_Revenue FROM your_table
Пікірлер: 47