LEFT JOIN execution behavior in SQL

Introduction

LEFT JOIN helps you combine two tables and keep all rows from the first table, even if there is no match in the second table.

You want to list all customers and their orders, even if some customers have no orders.

You need to find all employees and their departments, including those not assigned to any department yet.

You want to show all products and their sales data, even if some products have no sales.

You want to compare two lists and keep all items from the first list regardless of matches.

Syntax

SQL

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column;

The LEFT JOIN returns all rows from table1.

If there is no matching row in table2, the result shows NULL for table2 columns.

Examples

Shows all customers and their orders. Customers without orders will still appear with NULL in order columns.

SQL

SELECT customers.name, orders.id
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

Lists all employees and their departments. Employees without a department show NULL for department name.

SQL

SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments ON employees.dept_id = departments.id;

Displays all products and their sales amounts. Products without sales show NULL for sales amount.

SQL

SELECT products.name, sales.amount
FROM products
LEFT JOIN sales ON products.id = sales.product_id;

Sample Program

This query lists all authors and their books. Authors without books still appear with NULL for book title.

SQL

CREATE TABLE authors (id INT, name VARCHAR(20));
CREATE TABLE books (id INT, title VARCHAR(30), author_id INT);

INSERT INTO authors VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');
INSERT INTO books VALUES (1, 'Book A', 1), (2, 'Book B', 1), (3, 'Book C', 2);

SELECT authors.name, books.title
FROM authors
LEFT JOIN books ON authors.id = books.author_id
ORDER BY authors.id, books.id;

Important Notes

LEFT JOIN keeps all rows from the left table, even if no match is found on the right.

Columns from the right table show NULL when there is no matching row.

Use LEFT JOIN when you want to keep all data from the first table and add matching info from the second.

Summary

LEFT JOIN returns all rows from the first (left) table.

Rows without matches in the second (right) table show NULL for right table columns.

It is useful to keep all data from one table while adding related data from another.