LEFT JOIN / RIGHT JOIN

-- LEFT JOIN: retains all rows from the left table (the FROM side).
SELECT column FROM table1
LEFT JOIN table2 ON table1.join_column = table2.join_column;

-- LEFT OUTER JOIN is identical to LEFT JOIN (OUTER is optional).
SELECT column FROM table1
LEFT OUTER JOIN table2 ON table1.join_column = table2.join_column;

-- RIGHT JOIN: retains all rows from the right table (the JOIN side).
SELECT column FROM table1
RIGHT JOIN table2 ON table1.join_column = table2.join_column;

-- Retrieves all employees, including those with no department (department columns will be NULL).
SELECT e.name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.id;

-- Retrieves customers who have never placed an order (filters rows where the orders side is NULL).
SELECT c.name AS customer_name, o.order_date
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customer_id
WHERE o.id IS NULL;

-- Retrieves all departments, including those with no employees.
SELECT d.department_name, e.name
FROM departments AS d
LEFT JOIN employees AS e ON d.id = e.department_id;

-- Retrieves all departments using RIGHT JOIN (equivalent to the LEFT JOIN above with table order swapped).
SELECT d.department_name, e.name
FROM employees AS e
RIGHT JOIN departments AS d ON e.department_id = d.id;

-- Result of: SELECT e.name, d.department_name FROM employees AS e
-- LEFT JOIN departments AS d ON e.department_id = d.id;
-- +----------+------------------+
-- | name     | department_name  |
-- +----------+------------------+
-- | Tanaka   | Development      |
-- | Suzuki   | Sales            |
-- | Takahashi| NULL             | ← employee with no department assigned
-- +----------+------------------+

The LEFT JOIN and RIGHT JOIN syntax is supported across all major databases.

-- Common to MySQL, PostgreSQL, SQL Server, Oracle, and SQLite
SELECT e.name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.id;

SQLite supports LEFT JOIN, but RIGHT JOIN was not available until version 3.39.0. In older versions of SQLite, you can achieve the same result by swapping the table order and using LEFT JOIN instead.

LEFT JOIN is one of the most commonly used joins in practice. It fits the requirement "retrieve all X and include related Y if it exists."

LEFT JOIN is also used to find unmatched rows — rows that exist in the left table but not the right. After joining, add WHERE right_table.id IS NULL to filter for rows that exist only in the left table (orphaned records).

RIGHT JOIN produces the same result as LEFT JOIN with the table order reversed. For this reason, many teams standardize on LEFT JOIN in production code for readability. If you need to retain all rows from both tables, see FULL OUTER JOIN.