SQL - ADVANCED JOINS

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.

http://itjunction4all.blogspot.com/2013/08/joins-we-want-to-fetch-data-from-two-or.html

                               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;

If you like this post, don't forget to share and post comment.