LEFT JOIN usage and examples

Overview

LEFT JOIN keeps every row from the left table and adds right-table data that satisfies ON; unmatched right-side columns become NULL. Filtering a right-table column in WHERE can remove those unmatched rows and make the result behave like an INNER JOIN. Place such conditions carefully when unmatched rows must remain.

Basic syntax

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

Examples

LEFT JOIN example

Keep every order and observe that the order with customer_id 104 returns NULL for the missing customer name.

Sample table

Table：orders

orders
id	customer_id
1	101
2	102
3	101
4	104

Table：customers

customers
id	name
101	Alice
102	Bob
103	Cindy

Run result

result
id	name
1	Alice
2	Bob
3	Alice
4	NULL

Common issues

What is the main difference between LEFT JOIN and INNER JOIN?

LEFT JOIN keeps all left-table rows, while INNER JOIN keeps only rows matched on both sides.

Why are right-table columns NULL?

The left-table row has no matching row in the right table.

What should I watch when using WHERE after LEFT JOIN?

Filtering right-table columns in WHERE can remove unmatched rows and make the result behave like INNER JOIN.

Keep reading

INNER JOIN SELF JOIN CROSS JOIN FULL OUTER JOIN

LEFT JOIN