MySQL LEFT JOIN

The 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:

SELECT 

c.customer_id, 

   c.first_name, 

   c.last_name,

   a.actor_id,

   a.first_name,

   a.last_name

FROM customer c

LEFT JOIN actor a 

ON c.last_name = a.last_name

ORDER BY c.last_name;

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 NULL,

Difference Between LEFT JOIN, RIGHT JOIN and INNER JOIN

The difference between the join types is easy.

LEFT JOIN

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

RIGHT JOIN

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

INNER JOIN

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

Below are some examples to demonstrate.

RIGHT JOIN

If we change the above example to a RIGHT JOIN (and order it by a.actor_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: