SQL LEFT JOIN

Summary: in this tutorial, we will introduce you to another kind of join called SQL LEFT JOIN that allows you to retrieve data from multiple tables.

Introduction to SQL LEFT JOIN clause

In the previous tutorial, you learned about the inner join that returns rows if there is, at least, one row in both tables that matches the join condition. The inner join clause eliminates the rows that do not match with a row of the other table.

The left join, however, returns all rows from the left table whether or not there is a matching row in the right table.

Suppose we have two tables A and B. Table A has four rows 1, 2, 3, and 4. Table B also has four rows of 3, 4, 5, 6.

When we join table A with table B, all the rows in table A (the left table) are included in the result set whether there is a matching row in table B or not.

In SQL, we use the following syntax to join table A with table B.

SELECT
	A.n
FROM
	A
LEFT JOIN B ON B.n = A.n;

The LEFT JOIN clause appears after the FROM clause. The condition that follows the ON keyword is called the join condition B.n = A.n

SQL LEFT JOIN examples

SQL LEFT JOIN two tables examples

Let’s take a look at the countries and locations tables.

Each location belongs to one and only one country while each country can have zero or more locations. The relationship between the countries and locations tables is one-to-many.

The country_id column in the locations table is the foreign key that links to the country_id column in the countries table.

To query the country names of the US, UK, and China, you use the following statement.

SELECT
	country_id,
	country_name
FROM
	countries
WHERE
	country_id IN ('US', 'UK', 'CN');

The following query retrieves the locations located in the US, UK, and China:

SELECT
	country_id,
	street_address,
	city
FROM
	locations
WHERE
	country_id IN ('US', 'UK', 'CN');

Now, we use the LEFT JOIN clause to join the countries table with the locations table as the following query:

SELECT
	c.country_name,
	c.country_id,
	l.country_id,
	l.street_address,
	l.city
FROM
	countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
	c.country_id IN ('US', 'UK', 'CN')

The condition in the WHERE clause is applied so that the statement only retrieves the data from the US, UK, and China rows.

Because we use the LEFT JOIN clause, all rows that satisfy the condition in the WHERE clause of the countries table are included in the result set.

For each row in the countries table, the LEFT JOIN clause finds the matching rows in the locations table.

If at least one matching row is found, the database engine combines the data from columns of the matching rows in both tables.

In case there is no matching row found e.g., with the country_id CN, the row in the countries table is included in the result set and the row in the locations table is filled with NULL values.

Because non-matching rows in the right table are filled with the NULL values, you can apply the LEFT JOIN clause to miss-match rows between tables.

For example, to find a country that does not have any locations in the locations table, you use the following query:

SELECT
	country_name
FROM
	countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
	l.location_id IS NULL
ORDER BY
	country_name;

SQL LEFT JOIN 3 tables for example

See the following tables: regions, countries, and locations.

One region may have zero or many countries while each country is located in one region. The relationship between countries and regions tables is one-to-many. The region_id column in the countries table is the link between the countries and regions table.

The following statement demonstrates how to join 3 tables: regions, countries, and locations:

SELECT
	r.region_name,
	c.country_name,
	l.street_address,
	l.city
FROM
	regions r
LEFT JOIN countries c ON c.region_id = r.region_id
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
	c.country_id IN ('US', 'UK', 'CN');

Now you should have a good understanding of how the SQL LEFT JOIN clause works and know how to apply the LEFT JOIN clause to query data from multiple tables.