This site runs best with JavaScript enabled.Lesson 27: Joining tables using LEFT JOIN.

Lesson 27: Joining tables using LEFT JOIN.


What is the LEFT JOIN?

The LEFT JOIN (also known as LEFT OUTER JOIN) lets us join tables but insists on including all rows from the first table, even if there is no match in the second table.

When would we use the LEFT JOIN?

We might do this if we had a CUSTOMERS table on the left side and an EMAILS table on the right side. We may not have emails for all of our customers but we still want to see all of the customers in our output.

If we had used an INNER JOIN we would only get back those customers who DO have an email record in the EMAILS table.

How do we write a Left Join?

We write the LEFT JOIN like this:

SELECT *
FROM Customers
LEFT JOIN Emails
ON Customers.EmailAddress = Emails.EmailAddress;

This would return all rows from the Customers table regardless of whether they had a corresponding record in the Emails table. Any customer WITHOUT an email address would have a NULL value in their EmailAddress column (like Maria in our output below).

CustIDCustNameEmailAddress
1Alanalan@simpleanalytical.com
2Julienbaptiste@gendarme.fr
3MariaNULL

Quiz Time.

The Question.

In the previous exercise, we used an inner join to connect our movies with their lead actors.

This time we don't care if there is a match between the 'movies' table and the 'actors' table. We want all of the movies and their lead actor, even if the actor isn't present in the actors table.

The Data.

The Editor.



The Answer.

Correct SQL:

Correct Output:

Share lesson