SQL joins.

Most commonly known joins are:

1. JOIN or INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. OUTER JOIN
5. CROSS JOIN

1. JOIN or INNER JOIN :

In this kind of a JOIN, we get all records that match the condition in both the tables, and records in both the tables that do not match are not reported.
In other words, INNER JOIN is based on the single fact that : ONLY the matching entries in BOTH the tables SHOULD be listed.

SELECT <select_list>
FROM Table_A A
INNER JOIN Table_B B
ON A.Key = B.Key

INNER_JOIN

2. LEFT JOIN:

This join returns all the rows from the left table in conjunction with the matching rows from the right table.

SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key

LEFT_JOIN

3. RIGHT JOIN:

This join returns all the rows from the right table in conjunction with the matching rows from the left table.

SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key

RIGHT_JOIN

4. OUTER JOIN:

This join combines left outer join and right outer join. It returns row from either table when the conditions are met and This Join can also be referred to as a FULL OUTER JOIN or a FULL JOIN. This query will return all of the records from both tables, joining records from the left table (table A) that match records from the right table (table B). This Join is written as follows:

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key

FULL_OUTER_JOIN

5. CROSS JOIN:

It is the Cartesian product of the two tables involved. The result of a CROSS JOIN will not make sense in most of the situations. Moreover, we wont need this at all (or needs the least, to be precise).

Also it is need to be noticed that there are more OUTER JOINS available.

LEFT OUTER JOIN and RIGHT OUTER JOIN

There is no difference between RIGHT JOIN and RIGHT OUTER JOIN. Both are the same. That is, LEFT JOIN and LEFT OUTER JOIN both are the same.

OUTER EXCLUDING JOIN

This query will return all of the records in the right table (table B) that do not match any records in the left table (table A). This Join is written as follows:

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL OR B.Key IS NULL

OUTER_EXCLUDING_JOIN

Share this post:Tweet about this on TwitterShare on Facebook0Share on LinkedIn0Share on Google+0Share on Reddit0Email this to someoneDigg this