Join Queries

INNER JOIN

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

Example:

select table1.Column1,table1.Column2,table2.Column1 from table1 inner join table2 on table1.PK_Id=table2.FK_Id

LEFT JOIN

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). 

Example:

select table1.Column1,table1.Column2,table2.Column1 from table1 left join table2 on table1.PK_Id=table2.FK_Id

RIGHT JOIN

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1).

Example:

select table1.Column1,table1.Column2,table2.Column1 from table1 right join table2 on table1.PK_Id=table2.FK_Id

LEFT OUTER JOIN

In some databases LEFT JOIN is called LEFT OUTER JOIN.

Example:

select table1.Column1,table1.Column2,table2.Column1 from table1 left outer join table2 on table1.PK_Id=table2.FK_Id

RIGHT OUTER JOIN

In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

Example:

select table1.Column1,table1.Column2,table2.Column1 from table1 right outer join table2 on table1.PK_Id=table2.FK_Id

FULL OUTER JOIN

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).  The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

Example:

select table1.Column1,table1.Column2,table2.Column1 from table1 Full outer join table2 on table1.PK_Id=table2.FK_Id