MySQL JOIN Clauses (3/4) – The Outer JOINs

In this short article series I would like to describe the meaning of MySQL Join clauses. MySQL semantics is similar among different SQL database engines, so this knowledge could be proudly used in other SQL environments. Have a nice reading in part three of four.

Here, in the part two, you can read about the Inner JOIN Clause. Now, let’s focus on the Outer JOINs.

The basic application of the clause is to return all records matching from both tables. Database engine can automatically detect records having no match in joined table. It returns NULL values for records of joined table if no match is found. Let’s look into an example:

Left JOIN

Assume that now you want to get titles of all movies combined with names of members who have rented them. It is obvious that some movies have not being rented by any member. To do that, we can simply use LEFT JOIN.

The Left JOIN clause returns all the rows from the table on the left, even if no matching rows have been found, in the table on the right. Where no matches have been found in the table on the right, NULL is returned. Let’s see a simple example:

SELECT A.`title`, B.`first_name`, B.`last_name`
FROM `movies` as A
LEFT JOIN `members` as B
ON B.`movie_id` = A.`id`

Note: As you can see, NULL is returned for non-matching rows on right.

Right JOIN

The Right JOIN Clause is obviously the opposite of Left JOIN. This clause returns all the columns from the table on the right, even if no matching rows have been found in the table on the left. Where no matches have been found in the table on the left, NULL is returned.

In the following example, we assumed that we need to get members data with movies rented by them.

SELECT A.`first_name`, A.`last_name`, B.`title`
FROM `members` as A
RIGHT JOIN `movies` as B
ON B.`id` = A.`movie_id`

Note: Null is returned for non-matching rows on left.

Visit the fourth, and the last one as well, article where you can read about the ON and USING clauses.