In my earlier SQL- JOINS post , I had discussed about INNER JOIN , LEFT OUTER JOIN, RIGHT OUTER JOIN, SELF JOIN and CROSS JOIN. In this post, I am going to discuss about ADVANCED JOINS. Please visit my earlier post on JOINS in below URL if you have not visited.
We have seen that LEFT OUTER JOIN includes matching rows between left table and right table and non matching rows from left table. This is illustrated with table and venn diagram as below:
Select Name,Productname,Quantity,
|
P.Productid,Mrp
|
From Customerinfo C
|
LEFT Outer Join Productinfo P
|
On C.Productid=P.Productid;
|
Now we want to display only non matching records from left table. NULL value is populated for ProductID in non matching rows. 8th and 9th row is having ProductId as NULL in ProductInfo table. So to display only non matching rows, we can filter it by simply adding WHERE clause to original LEFT JOIN query as shown below.
Select Name,Productname,Quantity,
|
P.Productid,Mrp
|
From Customerinfo C
|
LEFT Outer Join Productinfo P
|
On C.Productid=P.Productid
|
Where P.Productid Is Null;
|
We have seen that RIGHT OUTER JOIN includes matching rows between left table and right table and non matching rows from right table. This is illustrated with table and venn diagram as below:
Select Name,Productname,Quantity,
|
P.Productid,Mrp
|
From Customerinfo C
|
RIGHT Outer Join Productinfo P
|
On C.Productid=P.Productid
|
Now we want to display only non matching records from right table. NULL value is populated for ProductID in non matching row. 8th row is having ProductId as NULL in CustomerInfo table. So to display only non matching rows, we can filter it by simply adding WHERE clause to original RIGHT JOIN query as shown below.
Select Name,Productname,Quantity,
|
P.Productid,Mrp
|
From Customerinfo C
|
RIGHT Outer Join Productinfo P
|
On C.Productid=P.Productid
|
Where C.Productid Is Null;
|
We have seen that FULL OUTER JOIN includes matching rows between left table and right table, non matching rows from both left and right table. This is illustrated with table and venn diagram as below:
Select Name,Productname,Quantity,
|
P.Productid,Mrp
|
From Customerinfo C
|
FULL Outer Join Productinfo P
|
On C.Productid=P.Productid
|
Now we want to display only non matching records from both left and right table. NULL value is populated for ProductID in non matching row. 8th and 9th row is having ProductId as NULL in CustomerInfo table and10th row is having ProductID NULL in ProductInfo Table. So to display only non matching rows, we can filter it by adding WHERE clause to original FULL JOIN query as shown below.
SelectName,Productname,Quantity,
|
P.Productid,Mrp
|
From Customerinfo C
|
FULL Outer Join Productinfo P
|
On C.Productid=P.Productid
|
Where C.Productid Is Null
|
OR P.Productid Is Null;
|
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjI6_P9hbav6gNVCM-OP4b1NVfpDITlVBLovK9HC1RUZsvPGFPKPmAyiss697MQSXWOh2FpAJWPVJDq2J-9vP5BoRoN4W_YEbNJ2dyOD-4CBuccsnTaDe3VKcd_1fU3DIfKHb3uPBC3ex4/s200/A-B+B-A.jpg)
If you like this post, don't forget to share and post comment.
No comments:
Post a Comment