Monday, November 29, 2010

Visual Representation of SQL JOINS

Shout it kick it on DotNetKicks.com
In this post I am going to show the types of join available in SQL by the van diagram representation


Basic types of join
  • INNER JOIN
  • OUTER JOIN
    1. LEFT OUTER JOIN
    2. RIGHT OUTER JOIN
    3. FULL OUTER JOIN
  • CROSS JOIN
Following table we are going to use in this discussion
TableA Structure
TableA Data

TableB Structure
TableB Data



INNER JOIN
A Join return tose data which are match condition applied in ON clause of the query so it display data which is common in bot TableA and TableB as you see in below image  
SELECT select_column_list FROM
TableA INNER JOIN TableB
ON TableA.ID = TableB.TableA_Id
Example:



LEFT OUTER JOIN
A Join return all rows which match condition of ON clause as well as all row form left table i.e TableA. Un-match row of right table i.e TableB replace by the NULL value which you can see in below image of output window

SELECT select_column_list FROM
TableA LEFT OUTER JOIN TableB
ON TableA.ID = TableB.TableA_Id
Example :



RIGHT OUTER JOIN
A Join return all rows which match condition of ON clause as well as all row form right table i.e TableB. Un-match row of left table i.e TableA replace by the NULL value which you can see in below image of output window

SELECT select_column_list FROM
TableA RIGHT OUTER JOIN TableB
ON TableA.ID = TableB.TableA_Id
Example:



FULL OUTER JOIN
A Join return all rows which match condition of ON clause as well as all row form both table. Un-match row of left table i.e TableA replace by the NULL value and Un-match row of right table i.e TableB replace by the
Null value, which you can see in below image of output window

SELECT select_column_list FROM
TableA FULL OUTER JOIN TableB
ON TableA.ID = TableB.TableA_Id
Example:



CROSS JOIN
A join wose result set includes one row for eac possible pairin of rows form te two tables.
SELECT select_column_list FROM
TableA CROSS JOIN TableB

1 comment:

  1. Hi there, I discovered you on google and this is a well written article. I saved it to my bookmarks and have shared it with a couple of buddies. Looking forward to any revisions you might do. Thanks! Ok time to get back to the beach!

    ReplyDelete