LEFT JOIN using SQL (examples included)

You can use a LEFT JOIN to get all the records from the left table, and the records from the right table where there is a match (if there is no match, you’ll get NULL under the selected fields from the right table):

SELECT 
tb1.*,
tb2.*
FROM left_table_name tb1
LEFT JOIN right_table_name tb2 ON tb1.id = tb2.id

Let’s see how to apply a left join by reviewing few examples.

Applying a Left Join Between Two Tables using SQL

In the following example, you’ll see how to apply a left join between:

  • The left table called person_name; and
  • The right table called person_age

The records under the ‘person_name’ table are:

person_id first_name age_id city_id
111 Bill 1005 9993
222 Julia 1009 9998
333 Martin 1015 9996
444 Rob 1003 9993
555 Maria 1007 9991

While the records under the ‘person_age’ table are:

age_id age
1000 20
1001 25
1002 30
1003 35
1004 40
1005 45
1006 50
1007 55
1008 60
1009 65

The age_id field (which exists in both tables) will be used to link the tables together.

Here is the complete query to perform the left join:

SELECT
pr.*,
ag.*
FROM person_name pr
LEFT JOIN person_age ag ON pr.age_id = ag.age_id

In green, you’ll get all the records and fields from the person_name table (the “left” table), and in yellow you’ll get all the records and fields from the person_age table (the “right” table):

person_id first_name age_id city_id age_id age
111 Bill 1005 9993 1005 45
222 Julia 1009 9998 1009 65
333 Martin 1015 9996 NULL NULL
444 Rob 1003 9993 1003 35
555 Maria 1007 9991 1007 55

Notice that there is one record (for the person_id = 333) with NULL values under the person_age table. This happened because the age_id of 1015 was not present in the person_age table (the “right” table).

Selecting subset of fields

At times, you may need to select a subset of fields from the joined tables.
For example, let’s select:

  • The first_name from the person_name table; and
  • The age field from the person_age table

Here is the query:

SELECT
pr.first_name,
ag.age
FROM person_name pr
LEFT JOIN person_age ag ON pr.age_id = ag.age_id

You’ll now see only the selected fields from the tables:

first_name age
Bill 45
Julia 65
Martin NULL
Rob 35
Maria 55

Applying a Left Join Across Multiple Tables using SQL

What if you want to perform a left join across multiple tables?

For example, let’s add a third table called cities:

city_id city_name
9990 Madrid
9991 Boston
9992 Tokyo
9993 Amsterdam
9994 Paris
9995 Nairobi
9996 Sydney
9997 Berlin
9998 London
9999 Buenos Aires

So the complete query would look as follows:

SELECT
pr.first_name,
ag.age,
ct.city_name
FROM person_name pr
LEFT JOIN person_age ag ON pr.age_id = ag.age_id
LEFT JOIN cities ct ON pr.city_id = ct.city_id

As you can see, the first field was selected from the person_name table (in green), the second field was selected from the person_age table (in yellow), while the third field was selected from the cities table (in orange):

first_name age city_name
Bill 45 Amsterdam
Julia 65 London
Martin NULL Sydney
Rob 35 Amsterdam
Maria 55 Boston

Another way to write the above query:

SELECT 
pr.first_name,
ag.age,
ct.city_name
FROM

	(
	SELECT 
	* 
	FROM person_name
	) pr

LEFT JOIN

	((
	SELECT 
	* 
	FROM person_age
	)) ag ON pr.age_id = ag.age_id

LEFT JOIN

	(((
	SELECT 
	* 
	FROM cities
	))) ct ON pr.city_id = ct.city_id

You’ll get the same results as before:

first_name age city_name
Bill 45 Amsterdam
Julia 65 London
Martin NULL Sydney
Rob 35 Amsterdam
Maria 55 Boston

And yet another way to write the above query:

SELECT 
mix.first_name,
mix.age,
ct.city_name
FROM

	(
	SELECT 
	pr.first_name,
	pr.city_id,
	ag.age
	FROM person_name pr
	LEFT JOIN person_age ag ON pr.age_id = ag.age_id
	) mix

LEFT JOIN

	(((
	SELECT 
	* 
	FROM cities
	))) ct ON mix.city_id = ct.city_id

You’ll see the same results again:

first_name age city_name
Bill 45 Amsterdam
Julia 65 London
Martin NULL Sydney
Rob 35 Amsterdam
Maria 55 Boston

You may also want to check the following page for additional SQL tutorials.