SQL Database Joins

If you work with multiple tables at the same time, there is a high chance you will need to join then through queries. In relational databases, we do this by using Left Joins, Right Joins, Outer Joins and  Inner Joins. Since there are so many options, the question is, which join to use in what scenario. Because if used incorrectly, the queries from these joins can produce erroneous data.


Let's assume we have two tables, Table A and Table B respectively.

Here is how different joins will affect the final result-set from these tables:

Left Joins

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

A simple Left Join query will return all the records that belong to the Table A and the records that intersect with table B.



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

Left Join can also be made by excluding all the records that intersect between the two tables.

Right Joins

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

Right Joins are similar to the Left Joins except that they take the tables on the right of the query. A simple Right Join query will return all the records that belong to the Table B and the records that intersect with table A.



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

Right Join can also be made by excluding all the records that intersect between the two tables and retrieving only what is left in Table B.

Inner Joins

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

Inner Join is basically all the records that intersect between the two tables.

Outer Joins

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

In a full Outer Join, we will get the summation of records between the two tables.




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

The same Outer Join query can be written with a NULL clause to exclude intersecting records.