MySQL RIGHT JOIN
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:
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
LEFT JOIN and
The difference between the join types is easy.
- All rows from the right table will be returned, even if there's no matching row in the left table.
- All rows from the left table will be returned, even if there's no matching row in the right table.
- Only returns rows where there's a matching row in both tables.
Below are some examples to demonstrate.
If we change the above example to a
LEFT JOIN (and order it by
c.customer_id), here's the result:
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
If we didn't qualify the columns, MySQL would throw an ambiguous column error. It would say something like this:
The examples on this page use the Sakila sample database.