MySQL RIGHT JOIN

The RIGHT JOIN is used to return data from multiple tables. In particular, the "RIGHT" part means that all rows from the right table will be returned, even if there's no matching row in the left table. This could result in NULL values appearing in any columns returned from the left table.

Consider the following tables:

Let's return a list of all actors (from the actor table). And if the actor shares the same last name with a customer (from the customer table), let's display that customer's details too.

But the important thing is that we display all actors — regardless of whether they share their last name with a customer. Therefore, if an actor doesn't share the same last name as a customer, the actor is still listed.

We could acheive that with the following query:

SELECT 

c.customer_id, 

   c.first_name, 

   c.last_name,

   a.actor_id,

   a.first_name,

   a.last_name

FROM customer c

RIGHT JOIN actor a 

ON c.last_name = a.last_name

ORDER BY a.last_name;

The result would look like this:

You can see that all actors are listed, even if they don't share a last name with a customer. If they don't share the last name with a customer, the customer fields are NULL,

Difference Between RIGHT JOIN, LEFT JOIN and INNER JOIN

The difference between the join types is easy.

RIGHT JOIN

All rows from the right table will be returned, even if there's no matching row in the left table.

LEFT JOIN

All rows from the left table will be returned, even if there's no matching row in the right table.

INNER JOIN

Only returns rows where there's a matching row in both tables.

Below are some examples to demonstrate.

LEFT JOIN

If we change the above example to a LEFT JOIN (and order it by c.customer_id), here's the result:

INNER JOIN

If we change the join to an INNER JOIN, here's the result:

Qualifying the Fields

You'll notice that in the above example, we've qualified some field names with their respective table names. Actually, we've qualified them with aliases. In particular, the bit that reads c.last_name = a.last_name uses aliases to qualify the column name with the alias of the table.

We do this because the field names are the same (last_name). If we didn't qualify it with the table names (i.e. if we typed last_name = last_name;) MySQL wouldn't know which column we were referring to — the one in the customer table or the one in the actor table.

If we didn't qualify the columns, MySQL would throw an ambiguous column error. It would say something like this: