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;

Join type	Behavior
INNER JOIN	Returns only rows that match in both tables. Rows that exist in only one table are excluded from the result.
LEFT JOIN	Always includes all rows from the left table (the FROM side). If no matching row exists in the right table, the right table's columns are NULL.
RIGHT JOIN	Always includes all rows from the right table (the JOIN side). If no matching row exists in the left table, the left table's columns are NULL.

The following employees and departments tables are used in the examples below.

Retrieves all employees, including those with no department (department columns will be NULL).

sample_left_right_join.sql

SELECT e.name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.id;

+----------+-----------------+
| name     | department_name |
+----------+-----------------+
| Son Goku | Turtle School   |
| Vegeta   | Capsule Corp    |
| Bulma    | NULL            |
+----------+-----------------+
3 rows in set

Retrieves employees with no department assigned (filters rows where the departments side is NULL).

sample_left_right_join.sql

SELECT e.name, e.department_id
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.id
WHERE d.id IS NULL;

+-------+---------------+
| name  | department_id |
+-------+---------------+
| Bulma | NULL          |
+-------+---------------+
1 row in set

Retrieves all departments, including those with no employees (Namek Colony's name column will be NULL).

sample_left_right_join.sql

SELECT d.department_name, e.name
FROM departments AS d
LEFT JOIN employees AS e ON d.id = e.department_id;

+-----------------+----------+
| department_name | name     |
+-----------------+----------+
| Turtle School   | Son Goku |
| Capsule Corp    | Vegeta   |
| Namek Colony    | NULL     |
+-----------------+----------+
3 rows in set

Retrieves all departments using RIGHT JOIN (equivalent to the LEFT JOIN above with table order swapped).

sample_left_right_join.sql

SELECT d.department_name, e.name
FROM employees AS e
RIGHT JOIN departments AS d ON e.department_id = d.id;

+-----------------+----------+
| department_name | name     |
+-----------------+----------+
| Turtle School   | Son Goku |
| Capsule Corp    | Vegeta   |
| Namek Colony    | NULL     |
+-----------------+----------+
3 rows in set

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.