SQL JOIN's are used to query data from two or more tables, based on a relationship between certain columns in these tables.SQL joins are used to combine rows from two or more tables.

In the examples below I am going to take following two tables.

In this blog I am going to discuss about two JOIN type:

1. INNER JOIN
2. OUTER JOIN

INNER JOIN

The INNER JOIN keyword return rows when there is at least one match in both tables.

Example

SELECT * FROM Table1 t1 
INNER JOIN Table2 t2 
ON t1.ID = t2.ID

Output

OUTER JOIN

There are three types of OUTER JOIN’s

1. LEFT OUTER JOIN
2. RIGHT OUTER JOIN
3. FULL OUTER JOIN

LEFT OUTER JOIN

LEFT OUTER JOIN returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

Example

SELECT * FROM Table1 t1 
LEFT OUTER JOIN Table2 t2 
ON t1.ID = t2.ID

Output

RIGHT OUTER JOIN

RIGHT OUTER JOIN returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

Example

SELECT * FROM Table1 t1 
RIGHT OUTER JOIN Table2 t2 
ON t1.ID = t2.ID

Output

FULL OUTER JOIN

FULL OUTER JOIN returns rows from either table when the conditions are met and returns null value when there is no match.

Example

SELECT * FROM Table1 t1 
FULL OUTER JOIN Table2 t2 
ON t1.ID = t2.ID

Output

You can also visit these related post