9 Practical Examples of SQL LEFT JOIN

LEFT JOIN is one of SQL's most common JOINs. Make sure you know all its ins and outs by going through all nine of our real-life LEFT JOIN examples.

LEFT JOIN – alongside INNER JOIN – is one of the essential SQL tools you need to work with data from two or more tables. But how and when should you use it? These nine LEFT JOIN examples will point the way.

Is this the first time you’ve heard about JOINs? If so, we’ve got you covered with our comprehensive SQL JOINs course. You can learn from scratch about all the SQL JOIN types, different ways of joining two or more tables, and when to use each type of JOIN. You’ll even learn how to self-join a table and how to use non-equi joins. By the end of the course, you’ll have solved 99 interactive challenges.

LEFT JOIN Basics

LEFT JOIN is one of several types of SQL JOINs. The purpose of JOINs is to get the data from two or more tables. LEFT JOIN achieves that goal by returning all the data from the first (left) table and only the matching rows from the second (right) table. The non-matched values from the right table will be shown as NULL.

How is this different from other JOINs? Here’s a short overview, but for an even better understanding, have a look at these SQL JOIN examples.

• INNER JOIN – Returns only the matching rows from the joined tables. Here’s an article for learning more about INNER JOIN.
• RIGHT JOIN – Returns all the data from the right table and only the matching rows from the left table. The non-matching rows’ values will be NULL.
• FULL JOIN – Returns all rows from both joined tables. If there are unmatched rows between the tables, they’re shown as NULL. You can learn more about it in our article dedicated to FULL JOIN.
• CROSS JOIN – Returns all the combinations of all the rows from the joined tables, i.e., a Cartesian product. More information is available in this CROSS JOIN article.

LEFT JOIN is the same as LEFT OUTER JOIN. So, yes, LEFT JOIN is an outer type of join, along with RIGHT JOIN and FULL JOIN. As SQL users, we usually write just LEFT JOIN. The reason? It’s shorter, and we’re lazy.

You can learn more in the article explaining how LEFT JOIN works.

LEFT JOIN Syntax

The LEFT JOIN syntax is as follows.

SELECT …
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

The two key points are the keyword LEFT JOIN and the ON joining clause. The first joined table is referenced in the FROM clause, then the LEFT JOIN is added, followed by the second table you want to join.

The tables are joined on matching column values; you reference these columns in the ON clause and put an equals sign between them. This will join the tables where the column from one table is equal to the column from the second table. This is the most common type of LEFT JOIN. It’s called equi-join because of the equals sign. Other comparison operators can be used; these are the non-equi joins and are outside of this article's scope.

All this is also explained in our SQL JOIN Cheat Sheet. Keep it close during the following examples; it will help you understand them better.

LEFT JOIN Examples

Let me now show you several real-life examples of using LEFT JOIN. I’ll kick off with a basic, straightforward example to show you how LEFT JOIN works on real data.

Example 1: Basic LEFT JOIN

I’ll work with two tables. The first is company, which stores a list of electronics companies. Use this script to create the table.

id	company_name
1	Lenovo
2	Apple
3	Samsung
4	Huawei
5	Fairphone

The second table is the product table. The script for creating the table is here.

id	product_name	company_id
1	Fairphone 4	5
2	Galaxy S24 Ultra	3
3	Galaxy Z Flip5	3
4	iPhone 15 Pro	2
5	Fairbuds XL	5
6	MacBook Pro 16' M3 Pro	2
7	iPad Air 10.9' M1	2
8	Galaxy Tab S9 FE+	3

Let’s LEFT JOIN these two tables and see what happens:

SELECT company_name,
	 product_name
FROM company
LEFT JOIN product
ON company.id = product.company_id
ORDER BY company_name;

I select the company and product name. These are the columns from two tables. So, I need to join the tables to get those columns in the output.

The left table is company, and I reference it in FROM. Then I add the LEFT JOIN and the second table, which is product.

In the ON clause, I specify the columns on which the tables will be joined. In this case, it’s the column id from the first and the column company_id from the second table.

I used ORDER BY to make the output more readable. (You don’t need ORDER BY for the join to work.)

Speaking of output, here it is.

company_name	product_name
Apple	iPhone 15 Pro
Apple	iPad Air 10.9' M1
Apple	MacBook Pro 16' M3 Pro
Fairphone	Fairphone 4
Fairphone	Fairbuds XL
Huawei	NULL
Lenovo	NULL
Samsung	Galaxy Z Flip5
Samsung	Galaxy S24 Ultra
Samsung	Galaxy Tab S9 FE+

The output shows a list of all the companies, which is in line with the LEFT JOIN showing all the data from the left table.

When one company has multiple products, all these products are listed and the company's name is duplicated. When there are no products by the company (Huawei and Lenovo), the product_name column’s value is NULL.

Example 2: Departments and Employees

Let’s explore a common scenario. In this example, you want to list all the departments and their employees but also show the departments without employees, if there are such.

To achieve that, you need LEFT JOIN.

Here’s the table department and its script. It’s a list of departments.

id	department_name
1	Sales
2	Accounting
3	IT
4	HR
5	Operations

The second table is employee, which is a list of employees. Here’s its script.

id	first_name	last_name	department_id
1	Bob	Estevez	3
2	Francesca	Gotze	2
3	Frank	Gordon	2
4	Milicent	Johnson	3
5	Hans	Handke	NULL
6	Katie	Keaton	1
7	Luca	Di Francesco	NULL
8	Zoe	Jong	1
9	Patrick	Rose	2
10	Billie	Thompson	NULL

The NULL values here mean that this employee hasn’t been assigned a department yet.

To show departments and their employees – as well as departments with no employees – this is the code:

SELECT department.id AS department_id,
	 department_name,
	 employee.id AS employee_id,
       first_name,
       last_name	   
FROM department
LEFT JOIN employee
ON department.id = employee.department_id
ORDER BY department_id, employee_id;

I select the ID from the table department and rename it as department_id. The second selected column from the same table is department_name. The data selected from the employee table is the id (renamed employee_id) and the employees’ names.  All this renaming of the columns is just to make the output easier to read.

Now, I can reference the table department in FROM and LEFT JOIN it with the table employee. The tables are joined where the department IDs are equal.

Finally, I sort the output by the department and then by the employee ID to make it more readable. Here’s the result:

department_id	department_name	employee_id	first_name	last_name
1	Sales	6	Katie	Keaton
1	Sales	8	Zoe	Jong
2	Accounting	2	Francesca	Gotze
2	Accounting	3	Frank	Gordon
2	Accounting	9	Patrick	Rose
3	IT	1	Bob	Estevez
3	IT	4	Milicent	Johnson
4	HR	NULL	NULL	NULL
5	Operations	NULL	NULL	NULL

The output shows all the departments and their employees. It also shows two departments that don’t have employees: HR and Operations. It might be that the database hasn’t been updated yet and the new employees are not allocated to the department.

Example 3: Customers and Orders

Another typical LEFT JOIN example is when you want to find all the customers and their orders – but you also want to include the customers who haven’t placed any orders yet.

For that example, I will use the following dataset. The first table is customer, which is a simple list of customers. Here’s the script.

id	first_name	last_name
1	Florentinus	Glöckner
2	Eman	Adcock
3	Erik	Nyman
4	Leeba	Kubo
5	Lias	Vámos
6	Lavanya	Nikolaev
7	Rishi	Petit
8	Christie	Fodor
9	Andris	Lončar
10	Juliana	Harlan

The second table in the dataset is orders. You can create it yourself using this script.

This is how I write the code to achieve the desired result:

SELECT customer.first_name, 
       customer.last_name,
	 orders.id AS order_id,
	 orders.order_date
FROM customer
LEFT JOIN orders
ON customer.id = orders.customer_id;

I select the customers' names from the table customer. Logically, the information about the orders comes from the table orders.

The left table is customer, and I want all its rows. I LEFT JOIN it with the table orders on the customer ID.

The output looks like this:

first_name	last_name	order_id	order_date
Lias	Vámos	1	2024-01-01
Eman	Adcock	2	2024-01-08
Christie	Fodor	3	2024-01-08
Andris	Lončar	4	2024-01-12
Lias	Vámos	5	2024-01-18
Lavanya	Nikolaev	6	2024-01-22
Juliana	Harlan	NULL	NULL
Leeba	Kubo	NULL	NULL
Florentinus	Glöckner	NULL	NULL
Erik	Nyman	NULL	NULL
Rishi	Petit	NULL	NULL

You can see it shows all the customers and their orders. Where the customer doesn’t have any orders, there are NULLs.

For more practice, take a look at this article demonstrating four more LEFT JOIN examples.

Example 4: LEFT JOIN with 3 Tables

This is a LEFT JOIN example where I’ll show you how to join three tables.

Let’s first have a look at the dataset.

The first table is writer, with the script here. It’s simply a list of writers.

id	first_name	last_name
1	Bernardine	Evaristo
2	Albert	Camus
3	George	Orwell
4	Émile	Zola
5	Milan	Kundera
6	Charles	Dickens
7	Bohumil	Hrabal
8	Witold	Gombrowicz

The second table is translator. It’s a list of book translators. The script for creating the table is here.

id	first_name	last_name
1	Jennifer	Croft
2	Peter	Constantine
3	Ewald	Osers

The final table is book, which shows info about the particular books. Here’s the script.

id	book_title	publication_year	writer_id	translator_id
1	The Plague	2008	2	3
2	Cosmos	2015	8	1
3	Manifesto: On Never Giving Up	2021	1	NULL
4	Girl, Woman, Other	2019	1	NULL
5	The Stranger	2022	2	3
6	Germinal	2012	4	3
7	1984	2020	3	NULL

If the value in the translator_id column is NULL, this book is not a translation.

In this example, I want to show all the writers, no matter if they have a book or not. I also want to show the info about the book translator.

Here’s what the code should look like:

SELECT writer.first_name AS writer_first_name,
	 writer.last_name AS writer_last_name,
	 book_title,
	 translator.first_name AS translator_first_name,
	 translator.last_name AS translator_last_name
FROM writer
LEFT JOIN book
ON writer.id = book.writer_id
LEFT JOIN translator
ON book.translator_id = translator.id;

I select the writers’ names, the titles of their books, and the translators’ names. To get all this data, I need to join all three tables.

Joining three (or more) tables is done in the form of a chain. After you join the first two tables, you then add another join, reference the third table, and state the joining condition in the second ON clause.

First, I reference the table writer, and LEFT JOIN it with the table book on the writer ID.

Then, I add the second LEFT JOIN. I use it to join the second table (book) with the table translator on the translator ID.

Why are these LEFT JOINs the result of the relationship between the tables? The first LEFT JOIN is there because there might be writers without a book. However, this is also true for the relationship between the tables book and translator: a book might or might not be a translation, so it might or might not have a corresponding translator. So, you need to use the LEFT JOIN between them, too, because you want to show the books whether or not they are translations.

Here’s the code output:

writer_first_name	writer_last_name	book_title	translator_first_name	translator_last_name
Albert	Camus	The Plague	Ewald	Osers
Witold	Gombrowicz	Cosmos	Jennifer	Croft
Bernardine	Evaristo	Manifesto: On Never Giving Up	NULL	NULL
Bernardine	Evaristo	Girl, Woman, Other	NULL	NULL
Albert	Camus	The Stranger	Ewald	Osers
Émile	Zola	Germinal	Ewald	Osers
George	Orwell	1984	NULL	NULL
Milan	Kundera	NULL	NULL	NULL
Charles	Dickens	NULL	NULL	NULL
Bohumil	Hrabal	NULL	NULL	NULL

As you can see, Bernardine Evaristo’s books are shown despite them not being translations. This is because I used LEFT JOIN as a second join.

Also, Milan Kundera, Charles Dickens, and Bohumil Hrabal are shown despite not having any books and, therefore, no translators.

Example 5: ‘Forced’ LEFT JOIN with Three Tables

Usually the choice of LEFT JOIN comes from the nature of the table relationships. However, sometimes we’re “forced” to use the LEFT JOIN. You’ll soon see what I mean.

The first table in the dataset is a list of directors named director. Here’s the script.

id	first_name	last_name
1	Stanley	Kubrick
2	Céline	Sciamma
3	Woody	Allen
4	Lynne	Ramsay
5	Krzysztof	Kieślowski

Next is the table streaming_platform, which is a list of available streaming platforms. You can create the table using this script.

id	platform_name
1	Netflix
2	HBO
3	Hulu
4	Mubi
5	Apple TV

The third table is streaming_catalogue. It holds information about movies and has relationships with the first two tables via director_id and streaming_platform_id. Here’s the script to create the table.

id	movie_title	release_year	director_id	streaming_platform_id	started_showing	ended_showing
1	Three Colours: Blue	1993	5	4	2023-02-28	2023-09-30
2	Three Colours: White	1994	5	4	2023-02-28	2023-09-30
3	Three Colours: Red	1994	5	4	2023-02-28	2023-09-30
4	Manhattan Murder Mystery	1993	3	1	2023-08-15	NULL
5	Portrait of a Lady on Fire	2019	2	1	2023-01-01	2023-09-28
6	Three Colours: Blue	1993	5	2	2024-01-15	NULL
7	Three Colours: White	1994	5	2	2024-01-15	NULL
8	Three Colours: Red	1994	5	2	2024-01-15	NULL
9	Tomboy	2011	2	1	2020-04-01	2021-04-01
10	Vicky Cristina Barcelona	2008	3	1	2023-10-01	NULL

The NULL values in the ended_showing column mean that the movie is still being shown on the platform.

I want to show all the directors, their movies, and the streaming platforms that are showing (or have shown) their movies. Also, I want to show the directors who don’t have any movies streaming.

The relationship between the tables is that every movie has to have a director, but not vice versa. Also, every movie in the catalog has to have a streaming platform, but not every streaming platform has to be in the catalog.

I start writing the code by selecting the directors’ names, movie titles, platform names, and the showing start and end dates.

From the previous examples, you know that it’s expected to join the table director with the table streaming_catalogue on the director ID column. This is what I do to ensure I also show the directors who don’t have any movies in the catalog.

Now, I add the second LEFT JOIN to join the streaming_catalogue table with the streaming_platform table on the platform ID.

SELECT first_name AS director_first_name,
	 last_name AS director_last_name,
	 movie_title,
	 platform_name,
	 started_showing, 
	 ended_showing
FROM director
LEFT JOIN streaming_catalogue
ON director.id = streaming_catalogue.director_id
LEFT JOIN streaming_platform
ON streaming_catalogue.streaming_platform_id = streaming_platform.id;

The query returns this output:

director_first_name	director_last_name	movie_title	platform_name	started_showing	ended_showing
Krzysztof	Kieślowski	Three Colours: Blue	Mubi	2023-02-28	2023-09-30
Krzysztof	Kieślowski	Three Colours: White	Mubi	2023-02-28	2023-09-30
Krzysztof	Kieślowski	Three Colours: Red	Mubi	2023-02-28	2023-09-30
Woody	Allen	Manhattan Murder Mystery	Netflix	2023-08-15	NULL
Céline	Sciamma	Portrait of a Lady on Fire	Netflix	2023-01-01	2023-09-28
Krzysztof	Kieślowski	Three Colours: Blue	HBO	2024-01-15	NULL
Krzysztof	Kieślowski	Three Colours: White	HBO	2024-01-15	NULL
Krzysztof	Kieślowski	Three Colours: Red	HBO	2024-01-15	NULL
Céline	Sciamma	Tomboy	Netflix	2020-04-01	2021-04-01
Woody	Allen	Vicky Cristina Barcelona	Netflix	2023-10-01	NULL
Lynne	Ramsay	NULL	NULL	NULL	NULL
Stanley	Kubrick	NULL	NULL	NULL	NULL

The output shows all the directors, their movies, and the platforms where they are or were showing.

The movies with all the data except a NULL in the ended_showing columns can still be seen on a particular platform.

Despite there being no movies in the catalog, Lynne Ramsay and Stanley Kubrick are also listed. This is recognized by having their names but no other data.

I was able to get them because I used two LEFT JOINs. The first LEFT JOIN is not questionable; I had to use it in case there are directors without movies. It turns out there are.

But what about the second LEFT JOIN? I was kind of forced to use it to retain all those directors without the movies and get the desired output. Why ‘forced’? Well, let’s use INNER JOIN instead of the second LEFT JOIN, and you’ll see.

SELECT first_name AS director_first_name,
	 last_name AS director_last_name,
	 movie_title,
	 platform_name,
	 started_showing, 
	 ended_showing
FROM director
LEFT JOIN streaming_catalogue
ON director.id = streaming_catalogue.director_id
JOIN streaming_platform
ON streaming_catalogue.streaming_platform_id = streaming_platform.id;

The output is now missing Lynne Ramsay and Stanley Kubrick!

director_first_name	director_last_name	movie_title	platform_name	started_showing	ended_showing
Krzysztof	Kieślowski	Three Colours: Blue	Mubi	2023-02-28	2023-09-30
Krzysztof	Kieślowski	Three Colours: White	Mubi	2023-02-28	2023-09-30
Krzysztof	Kieślowski	Three Colours: Red	Mubi	2023-02-28	2023-09-30
Woody	Allen	Manhattan Murder Mystery	Netflix	2023-08-15	NULL
Céline	Sciamma	Portrait of a Lady on Fire	Netflix	2023-01-01	2023-09-28
Krzysztof	Kieślowski	Three Colours: Blue	HBO	2024-01-15	NULL
Krzysztof	Kieślowski	Three Colours: White	HBO	2024-01-15	NULL
Krzysztof	Kieślowski	Three Colours: Red	HBO	2024-01-15	NULL
Céline	Sciamma	Tomboy	Netflix	2020-04-01	2021-04-01
Woody	Allen	Vicky Cristina Barcelona	Netflix	2023-10-01	NULL

Why is that? Because INNER JOIN returns only the matching rows from the joined tables. So, I was able to output the directors without movies with the first LEFT JOIN. Well done me!

But then I used INNER JOIN and messed everything up! INNER JOIN cancels out the first LEFT JOIN, as it will show only the matching rows between streaming_catalogue and streaming_platform.

Since Lynne Ramsay and Stanley Kubrick have no movies in the table streaming_catalogue, their non-existing movies can’t be matched in the streaming_platform table and they don’t appear in the final result.

Here’s an article that provides more tips and examples for LEFT JOINing multiple tables.

Example 6: LEFT JOIN with WHERE

Let’s continue the SQL LEFT JOIN examples using the same data as in the previous one.

This example will show you how LEFT JOIN can be used with the WHERE clause.

The code below does exactly that to find the directors, their movies, and the start and end dates of the showings. However, it doesn’t show all the movies – only those whose showing ended before 1 October 2023.

SELECT first_name,
	 last_name,
	 movie_title, 
	 started_showing, 
	 ended_showing
FROM director
LEFT JOIN streaming_catalogue
ON director.id = streaming_catalogue.director_id
WHERE ended_showing < '2023_10_01';

After selecting the necessary columns, I LEFT JOIN the table director with the table streaming_ catalogue. The tables are joined on the director ID.

I use the WHERE clause to output only the movies that ended showing before 1 October 2023. In WHERE I compare the ended_showing column with the required cut-off date using the comparison operator ‘less than’ (<).

Here’s the output. No movie ended showing after 1 October 2023.

first_name	last_name	movie_title	started_showing	ended_showing
Krzysztof	Kieślowski	Three Colours: Blue	2023-02-28	2023-09-30
Krzysztof	Kieślowski	Three Colours: White	2023-02-28	2023-09-30
Krzysztof	Kieślowski	Three Colours: Red	2023-02-28	2023-09-30
Céline	Sciamma	Portrait of a Lady on Fire	2023-01-01	2023-09-28
Céline	Sciamma	Tomboy	2020-04-01	2021-04-01

Example 7: WHERE vs. ON in LEFT JOIN

I will now show you how the effect of the LEFT JOIN can be canceled if WHERE is used on the right table. And, of course, I will show you a remedy for that.

I’m again using the same dataset as in the previous example. Let’s say I want to query it and retrieve all the directors whether they have a movie in the database or not. For those directors that have a movie, I want to show only movies that were released in 1993.

I might try to achieve that by writing this query:

SELECT DISTINCT first_name,
	   	    last_name,
	   	    movie_title, 
	   	    release_year
FROM director
LEFT JOIN streaming_catalogue
ON director.id = streaming_catalogue.director_id
WHERE release_year = 1993;

First, I select the necessary columns. I’m using SELECT DISTINCT to avoid row duplication, as there are some movies that appear more than once in the table streaming_catalogue.

Now, I LEFT JOIN the table director with streaming_catalogue on the director ID.

The final step would be to use the WHERE clause and retrieve only movies released in 1993.

Let’s see the output:

first_name	last_name	movie_title	release_year
Krzysztof	Kieślowski	Three Colours: Blue	1993
Woody	Allen	Manhattan Murder Mystery	1993

Nope, this is not right! I got only two directors instead of five. Remember, I wanted a list of all the directors. Why did this happen, despite my using LEFT JOIN?

The reason is that when the filter in WHERE is applied to data from the right table, it cancels out the effect of LEFT JOIN. Remember, if the director doesn’t have any movies in the table, then the values in the column release_year will be NULL. It’s the result of LEFT JOIN. And the filter in WHERE will exclude NULLs, too, from the output.

So, how can you then list all the directors and use the filter on the release year at the same time? The answer is you should move the filtering condition from WHERE to ON, like this.

SELECT DISTINCT first_name,
	          last_name,
	          movie_title, 
	          release_year
FROM director
LEFT JOIN streaming_catalogue
ON director.id = streaming_catalogue.director_id AND release_year = 1993;

The release year condition now becomes the second joining condition in the ON clause. The second (third, fourth…) condition is added using the keyword AND.

Look, the output is now correct:

first_name	last_name	movie_title	release_year
Stanley	Kubrick	NULL	NULL
Lynne	Ramsay	NULL	NULL
Céline	Sciamma	NULL	NULL
Krzysztof	Kieślowski	Three Colours: Blue	1993
Woody	Allen	Manhattan Murder Mystery	1993

You can learn more in this article dedicated to the difference between WHERE and ON in SQL JOINs.

Example 8: LEFT JOIN with Alias

In all the previous examples, using aliases with the tables in LEFT JOIN was not necessary. It might’ve helped you to shorten the names of the tables and write code a little quicker. Helpful, yes, but not mandatory.

However, aliases become mandatory when you’re LEFT JOINING the table with itself – i.e. when you’re self-joining the table.

Let’s see how this works in an example where I want to retrieve the names of all the employees and the names of their managers. I want this list to contain employees who don’t have a manager above them.

I’ll demonstrate this in the table named employees_managers. Here’s the script:

id	first_name	last_name	manager_id
1	Lind	Kaiser	2
2	Ian	McKune	8
3	Deck	Trustrie	NULL
4	Ruperta	Nind	1
5	Garrot	Charsley	NULL
6	Athene	Fedoronko	8
7	Priscilla	Crocombe	NULL
8	Stafani	Sidebottom	8
9	Marve	Trustie	1
10	Antony	Marple	2

This is a list of the employees. The column manager_id contains the ID of the employee who is the manager of the particular employee. Some employees don’t have managers, so the value is NULL.

To complete the required task, I need to write this query:

SELECT e.first_name AS employee_first_name, 
	 e.last_name AS employee_last_name, 
	 m.first_name AS manager_first_name,
	 m.last_name AS manager_last_name
FROM employees_managers e
LEFT JOIN employees_managers m
ON e.manager_id = m.id;

I reference the table in the FROM clause and give it the alias e. This table will serve as the employee data.

Then, I reference the same table in LEFT JOIN and give it the alias m. It will be used for the managers' data.

That way, I was able to join the table with itself. It’s no different than joining two different tables. When self-joining, one table acts as two tables. You only need to give them aliases so that SQL knows which table you refer to.

The table is self-joined where the manager ID from the ‘employee’ table equals the employee ID from the ‘manager’ table. That way, I will get all the employees and their managers.

Now that I have tables in place, I only need to select the necessary columns. Again, to make a distinction, I use different table aliases to get employees’ and managers’ names.

Here’s the output:

employee_first_name	employee_last_name	manager_first_name	manager_last_name
Lind	Kaiser	Ian	McKune
Ian	McKune	Stafani	Sidebottom
Deck	Trustrie	NULL	NULL
Ruperta	Nind	Lind	Kaiser
Garrot	Charsley	NULL	NULL
Athene	Fedoronko	Stafani	Sidebottom
Priscilla	Crocombe	NULL	NULL
Stafani	Sidebottom	Stafani	Sidebottom
Marve	Trustie	Lind	Kaiser
Antony	Marple	Ian	McKune

As you can see, this is a full list of employees and their managers. Deck Trustrie, Garrot Charsley, and Priscilla Crocombe don’t have managers. They are at the top of the company’s hierarchical structure.

Example 9: LEFT JOIN with GROUP BY

Let’s now go back to Example 2, where we worked with a list of departments and employees.

A straightforward example of LEFT JOIN with GROUP BY would be to list all the departments and count the number of employees in each:

SELECT department_name,
	 COUNT(employee.id) AS number_of_employees
FROM department
LEFT JOIN employee
ON department.id = employee.department_id
GROUP BY department_name;

I select the department and use the aggregate function COUNT() on the employee ID column to find the number of employees.

The data comes from two tables. I need to LEFT JOIN the department table with the employee table since I also want departments with no employees. The tables are joined on the department ID.

Since I used an aggregate function, I also need to group data. I do that by using the GROUP BY clause. Grouping the output by the department name will show the number of employees by each department.

Have a look. Neat, right?

department_name	number_of_employees
Accounting	3
Operations	0
Sales	2
IT	2
HR	0

Now, let’s try another example and use COUNT(*) instead of applying COUNT() to a particular column.

This time, I’m using the data about the companies and their products from Example 1. In this example, I want to retrieve all the companies and show the number of products they have.

Let’s see what happens if I use COUNT(*):

SELECT company_name,
	 COUNT(*) AS number_of_products
FROM company
LEFT JOIN product
ON company.id = product.company_id
GROUP BY company_name;

The tables company and product are LEFT JOINed on the company ID. I’m using COUNT(*) and GROUP BY to find the number of products by company.

This is the output:

company_name	number_of_products
Huawei	1
Lenovo	1
Samsung	3
Apple	3
Fairphone	2

However, I can tell you this output is not right: Huawei and Lenovo should’ve had zero products. Why did this mistake occur?

The culprit is COUNT(*)! The asterisk in the COUNT() function means it counts all the rows, including NULLs.  Is it getting clearer now? Yes, that’s right: when the companies without products are LEFT JOINed, they will have NULL products. However, this is still a value, and COUNT(*) will see every NULL value as one product. In other words, even the companies without products will be shown as having one product.

To fix this, use COUNT(expression). In this case, it means COUNT(product.id). Using COUNT() with a column name ignores NULLs:

SELECT company_name,
	 COUNT(product.id) AS number_of_products
FROM company
LEFT JOIN product
ON company.id = product.company_id
GROUP BY company_name;

The output is now as expected:

company_name	number_of_products
Huawei	0
Lenovo	0
Samsung	3
Apple	3
Fairphone	2

You can go into more detail by reading this article about different variations of the COUNT() aggregate function.

More LEFT JOIN Examples and Resources

You can see from the above examples that LEFT JOIN has wide use in practical work with data. It can be used for simple data retrieval where you need all the data from one table and only the matching data from another. However, it can also be used when joining multiple tables, with WHERE, in self-joins, and with aggregate functions and GROUP BY.

SQL joins are vital in working with multiple tables, which is an everyday task even for junior data analysts. Knowing joins is an absolute must if you want to consider yourself fluent in SQL and improve in your job.

This also means knowing LEFT JOIN, as it’s one of the two most used join types. Due to its very specific characteristics, many tasks can’t be done other than by leveraging LEFT JOIN.  So, if you need an in-depth guide through these topics, we invite you to take a look at our SQL JOINs course.