SQL JOINS

SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables. 
There are different kinds of joins.

1. INNER JOIN :
       A join that displays only rows that have a match in both the joined tables is known as inner join. This is default type of join.

SELECT * FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON T1.COL1=T2.COL1

2. OUTER JOIN :
      A join that includes rows even if they do not have related rows in the joined table is an outer join.
      There are 3 different type of outer joins to specify the unmatched rows to be included.

(A) Left Outer Join : All rows in the first named table i.e. left table, which appear leftmost in join clause are included. Unmatched rows in the right table do not appear. (If in the left table any row has no matching record in right side table then that row returns null column values for that particular tuple. )

SELECT * FROM TABLE1 T1 LEFT OUTER JOIN TABLE2 T2 ON 
T1.COL1 = T2.COL1

(B) Right Outer Join : All rows in the second named table i.e. right table, which appear rightmost in join clause are included. Unmatched rows in the left table do not included.

SELECT * FROM TABLE1 T1 RIGHT OUTER JOIN TABLE2 T2 ON T1.COL1=T2.COL1

(C) Full Outer Join : All rows in all the joined tables are included, whether they are matched or not.

SELECT * FROM TABLE1 T1 FULL OUTER JOIN TABLE2 T2 ON T1.COL1=T2.COL1

* Right Outer Join – Where Null :
     
SELECT * FROM TABLE1 T1 RIGHT OUTER JOIN TABLE2 T2 ON T1.COL1=T2.COL1 WHERE T1.COL1 IS NULL

* Left Outer Join – Where Null :
     
SELECT * FROM TABLE1 T1 LEFT OUTER JOIN TABLE2 T2 ON T1.COL1=T2.COL1 WHERE T2.COL1 IS NULL

* Outer Join – Where Null :
     
SELECT * FROM TABLE1 T1 OUTER JOIN TABLE2 T2 ON T1.COL1=T2.COL1 
WHERE T1.COL1 IS NULL AND T2.COL1 IS NULL

Advertisements

One thought on “SQL JOINS

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s