SQL: Join

In this post we are going to learn how to work with multiple tables. In most databases there are multiple tables in which information is kept and we have to many times extract information from multiple tables based upon certain conditions. This is achieved through JOIN.

SQL Join statement is used to combine data or rows from two or more tables based on a common field between them.

Which is this common field? That is typically the foreign key. Even if you have not defined a foreign key, you can still perform join operation if you have a common field.

SQL join works on the set theory.

So let’s use it to understand different type of joins.

We will first learn INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN. We will learn each of them with an example.

We will use these 2 tables which have a common field PIN as our example tables:

The syntax for the JOIN command is as shown:

If there are 2 tables, INNER join selects all rows from both the tables as long as the condition is satisfied.

So if we give a query to select couple of columns from table 1 with inner join with table 2 then it gives the rows where the pin is matched.

Now let’s learn LEFT join. This join returns all the rows of the table on the left side of the join and only the matching rows for the table on the right side of join. The rows for which there is no matching row on right side, the result-set will contain null.

So if we repeat the previous query with left join this returns whole of left table and matching rows with right table. As you can see, where match is not found, the value is null.

Similarly RIGHT join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join. The rows for which there is no matching row on left side, the result-set will contain null.

FULL JOIN creates the result-set by combining result of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both the tables. The rows for which there is no matching, the result-set will contain null.

The CARTESIAN JOIN is also known as CROSS JOIN. In a CARTESIAN JOIN there is a join for each row of one table to every row of another table. This usually happens when the matching column or WHERE condition is not specified.