SQL left join
SQL

SQL LEFT JOIN — How to Include All Rows from the Left Table

PKComment(0)
CHAPTER 22 of 30 SQL Tutorial — Free Course on Neotech Navigators
SQL LEFT JOIN (also called LEFT OUTER JOIN) returns all rows from the left table and the matching rows from the right table. If there is no match, the result contains NULL values for the right table’s columns. In this chapter, you will learn the SQL LEFT JOIN syntax, see examples with NULL handling, and understand when to use LEFT JOIN vs INNER JOIN.

What Is SQL LEFT JOIN?

The SQL LEFT JOIN keyword returns all records from the left table (the table listed first in the FROM clause) and the matched records from the right table. When a row in the left table has no corresponding match in the right table, the columns from the right table are filled with NULL.

Think of it like taking attendance — you list every student in the class (left table), and if they submitted an assignment (right table), you note their score. Students who did not submit anything still appear on the list but with a blank score. If you have completed the previous chapter on INNER JOIN, you know that INNER JOIN would exclude students without submissions entirely.

SQL LEFT JOIN Syntax

Here is the basic SQL LEFT JOIN syntax:

SQL — LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

The ON clause specifies the matching condition between the two tables, just like with INNER JOIN. The difference is in what gets returned when there is no match.

💡 LEFT JOIN vs LEFT OUTER JOIN

LEFT JOIN and LEFT OUTER JOIN are identical in SQL. Most developers use the shorter form LEFT JOIN for readability.

Sample Tables

We will continue using the Customers and Orders tables from our SQL Tutorial course.

TABLE: Customers
+----+----------------+-----------+---------+
| ID | CustomerName   | City      | Country |
+----+----------------+-----------+---------+
| 1  | Rahul Sharma   | Mumbai    | India   |
| 2  | Sarah Johnson  | New York  | USA     |
| 3  | Amit Patel     | Delhi     | India   |
| 4  | Lisa Chen      | Singapore | SG      |
| 5  | David Brown    | London    | UK      |
+----+----------------+-----------+---------+
TABLE: Orders
+----+------------+------------+--------+---------+
| ID | CustomerID | OrderDate  | Amount | Status  |
+----+------------+------------+--------+---------+
| 1  | 1          | 2026-01-15 | 250.00 | Shipped |
| 2  | 2          | 2026-01-18 | 430.00 | Pending |
| 3  | 1          | 2026-02-01 | 180.00 | Shipped |
| 4  | 3          | 2026-02-10 | 520.00 | Shipped |
| 5  | 4          | 2026-03-05 | 310.00 | Pending |
+----+------------+------------+--------+---------+

Remember, Customer ID 5 (David Brown) has no orders. This is the perfect scenario to see how LEFT JOIN differs from INNER JOIN.

SQL LEFT JOIN Example

Let us write a SQL LEFT JOIN example to list all customers and their order amounts, including customers with no orders:

SQL
SELECT c.CustomerName, o.Amount, o.OrderDate
FROM Customers AS c
LEFT JOIN Orders AS o
ON c.ID = o.CustomerID;
✓ OUTPUT
+----------------+--------+------------+
| CustomerName   | Amount | OrderDate  |
+----------------+--------+------------+
| Rahul Sharma   | 250.00 | 2026-01-15 |
| Rahul Sharma   | 180.00 | 2026-02-01 |
| Sarah Johnson  | 430.00 | 2026-01-18 |
| Amit Patel     | 520.00 | 2026-02-10 |
| Lisa Chen      | 310.00 | 2026-03-05 |
| David Brown    | NULL   | NULL       |
+----------------+--------+------------+

Notice the last row — David Brown appears with NULL values for Amount and OrderDate because he has no matching order. With INNER JOIN, this row would have been excluded completely.

SQL LEFT JOIN NULL — Finding Unmatched Records

One of the most powerful uses of LEFT JOIN is finding records in the left table that have no match in the right table. You can do this by adding a WHERE clause that checks for NULL values:

SQL — Find Customers with No Orders
SELECT c.CustomerName, c.City
FROM Customers AS c
LEFT JOIN Orders AS o
ON c.ID = o.CustomerID
WHERE o.ID IS NULL;
✓ OUTPUT
+----------------+-----------+
| CustomerName   | City      |
+----------------+-----------+
| David Brown    | London    |
+----------------+-----------+

This pattern is extremely useful for data quality checks — finding orphaned records, inactive users, products without sales, and more. You learned about IS NULL in Chapter 9: SQL NULL Values.

LEFT JOIN vs INNER JOIN

Understanding when to use LEFT JOIN vs INNER JOIN is a critical skill. Here is a comparison:

Feature INNER JOIN LEFT JOIN
Left table rows Only matched rows All rows (matched + unmatched)
Right table rows Only matched rows Matched rows only; NULL for unmatched
NULL in result No NULLs from join NULLs appear for unmatched right rows
Use when You need only records that exist in both tables You need all records from one table, even without matches
Common use case Customers who placed orders All customers, including those without orders

💡 Quick Decision Guide

Ask yourself: “Do I need ALL rows from the left table, even if they have no match?” If yes, use LEFT JOIN. If no, use INNER JOIN.

SQL LEFT JOIN with Aggregation

You can combine LEFT JOIN with aggregate functions like COUNT and SUM to get summary data for all records:

SQL — Count Orders per Customer
SELECT c.CustomerName, COUNT(o.ID) AS TotalOrders
FROM Customers AS c
LEFT JOIN Orders AS o
ON c.ID = o.CustomerID
GROUP BY c.CustomerName;
✓ OUTPUT
+----------------+-------------+
| CustomerName   | TotalOrders |
+----------------+-------------+
| Rahul Sharma   | 2           |
| Sarah Johnson  | 1           |
| Amit Patel     | 1           |
| Lisa Chen      | 1           |
| David Brown    | 0           |
+----------------+-------------+

David Brown shows 0 orders because COUNT does not count NULL values. This is a common reporting pattern — showing all entities with their activity count, even if zero. You will learn more about grouping in Chapter 25: SQL GROUP BY.

⚠️ Watch Out: WHERE vs ON in LEFT JOIN

Placing a filter condition in the WHERE clause after a LEFT JOIN can convert it into an INNER JOIN if the filter excludes NULL values. To preserve all left rows while filtering the right table, place the condition in the ON clause instead.

If you are building reports with SQL-powered data, check out our Google Sheets Dashboard Templates on NextGenTemplates for ready-made business analytics.

📺 Visit our YouTube channel @NeoTechNavigators for step-by-step video tutorials and dashboard demos on this topic.

🧪 Try It Yourself

Write a query to display every customer’s name and their total order amount. Customers with no orders should show 0 as the total. Use LEFT JOIN with the COALESCE function.

ANSWER
SELECT c.CustomerName, COALESCE(SUM(o.Amount), 0) AS TotalSpent
FROM Customers AS c
LEFT JOIN Orders AS o
ON c.ID = o.CustomerID
GROUP BY c.CustomerName;

Expected Output:

+----------------+------------+
| CustomerName   | TotalSpent |
+----------------+------------+
| Rahul Sharma   | 430.00     |
| Sarah Johnson  | 430.00     |
| Amit Patel     | 520.00     |
| Lisa Chen      | 310.00     |
| David Brown    | 0          |
+----------------+------------+

Excellent! You combined LEFT JOIN with SUM and COALESCE to handle NULL values gracefully.

📝 What You Learned in This Chapter

  • LEFT JOIN — returns all rows from the left table plus matching rows from the right table
  • Unmatched rows in the right table produce NULL values in the result
  • LEFT JOIN and LEFT OUTER JOIN are identical
  • Use WHERE column IS NULL after LEFT JOIN to find unmatched records
  • LEFT JOIN with COUNT or SUM gives activity summaries including zero-activity records
  • Be careful placing filters in WHERE vs ON — it changes the result set

Frequently Asked Questions

What is SQL LEFT JOIN?
SQL LEFT JOIN returns all rows from the left table and the matching rows from the right table. If a row in the left table has no match in the right table, the result still includes that row with NULL values for all right-table columns.
What is the difference between LEFT JOIN and LEFT OUTER JOIN?
There is no difference. LEFT JOIN and LEFT OUTER JOIN produce exactly the same result. The keyword OUTER is optional and most SQL developers omit it for brevity.
What is the difference between LEFT JOIN and INNER JOIN?
INNER JOIN returns only rows that have a match in both tables, excluding unmatched records entirely. LEFT JOIN returns all rows from the left table, filling in NULL for any columns from the right table where no match exists. Use INNER JOIN when you need only matched data; use LEFT JOIN when you need all records from one table.
How do I find rows with no matching record using LEFT JOIN?
After writing your LEFT JOIN query, add a WHERE clause that checks if a column from the right table IS NULL. For example: WHERE orders.ID IS NULL returns only customers who have no orders. This is one of the most common uses of LEFT JOIN in real-world applications.
Can LEFT JOIN return duplicate rows?
Yes. If a row in the left table matches multiple rows in the right table, the left row will appear multiple times — once for each match. For example, if a customer has 3 orders, that customer appears 3 times in the LEFT JOIN result. Use GROUP BY or DISTINCT to consolidate duplicates when needed.
Does LEFT JOIN slow down queries?
LEFT JOIN can be slightly slower than INNER JOIN because the database must preserve all left-table rows regardless of matches. However, with proper indexing on the join columns, the performance difference is minimal. For more details on optimization, see the official MySQL JOIN documentation.
← Previous: SQL INNER JOIN
Next: SQL RIGHT JOIN →

📖 Chapter 22 of 30 — SQL Tutorial on Neotech Navigators

PK
Meet PK, the founder of NeotechNavigators.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your data analysis skills to the next level!
https://neotechnavigators.com

Related Articles

SQL INNER JOIN
SQL

SQL INNER JOIN — How to Match Rows from Two Tables

PK

CHAPTER 21 of 30 SQL Tutorial — Free Course on Neotech Navigators SQL INNER JOIN returns only the rows that have matching values in both tables. It is the most common type of JOIN used in SQL queries. In this chapter, you will learn the SQL INNER JOIN syntax, see practical examples with output, and […]

SQL Introduction
SQL

SQL Introduction — What Is SQL and Why Should You Learn It?

PK

SQL stands for Structured Query Language — the standard language for working with databases. This beginner-friendly chapter covers what SQL is, why it matters in 2026, and walks you through your very first SQL query with examples.