LEFT JOIN in MySQL

Introduction

LEFT JOIN helps you combine rows from two tables. It keeps all rows from the first table and adds matching rows from the second table. If no match, it shows NULL for 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, even if some products have never been sold.

You want to compare two lists and see which items in the first list have no match in the second.

Syntax

MySQL

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

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

If there is no matching row in the second (right) table, columns from the right table will be NULL.

Examples

List all customers and their order IDs. Customers without orders will show NULL for order ID.

MySQL

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

Show all employees and their department names. Employees without a department will have NULL for department name.

MySQL

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

Display all products and their sales amounts. Products with no sales show NULL for amount.

MySQL

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

Sample Program

This example creates two tables: customers and orders. It inserts some data, then uses LEFT JOIN to list all customers with their ordered products. Customers without orders show NULL.

MySQL

CREATE TABLE customers (id INT, name VARCHAR(20));
CREATE TABLE orders (id INT, customer_id INT, product VARCHAR(20));

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO orders VALUES (101, 1, 'Book'), (102, 1, 'Pen'), (103, 3, 'Notebook');

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

Important Notes

LEFT JOIN is useful to keep all data from the first table, even if the second table has no matching rows.

NULL values in the result mean no matching row was found in the right table.

Summary

LEFT JOIN combines two tables, keeping all rows from the first table.

Matching rows from the second table are added; if none, NULLs appear.

Great for showing all items from one list with optional related data from another.