MySQL LEFT JOIN
LEFT JOIN is used to return data from multiple tables. In particular, the "LEFT" part means that all rows from the left table will be returned, even if there's no matching row in the right table. This could result in
NULL values appearing in any columns returned from the right table.
Consider the following tables:
Let's return a list of all customers (from the
customer table). And if the customer shares the same last name with an actor (from the
actor table), let's display that actor's details too.
But the important thing is that we display all customers — regardless of whether they share their last name with an actor. Therefore, if a customer doesn't share the same last name as an actor, the customer is still listed.
We could acheive that with the following query:
The result would look like this:
You can see that all customers are listed, even if they don't share a last name with an actor. If they don't share the last name with an actor, the actor fields are
RIGHT JOIN and
The difference between the join types is easy.
- All rows from the left table will be returned, even if there's no matching row in the right table.
- All rows from the right table will be returned, even if there's no matching row in the left 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
RIGHT JOIN (and order it by
a.actor_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.