Learn SQL  /  Joins  /  LEFT / RIGHT / FULL JOIN

LEFT / RIGHT / FULL JOIN

Outer joins keep rows that do not have a match in the other table, filling the missing side with NULL — essential for finding gaps, like employees without a department.

LEFT JOIN — Keep Everything from the Left Table

R Code
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d
  ON e.dept_id = d.dept_id;
Output
name  | dept_name
Rafi  | Sales
Meera | Engineering
Zayn  | NULL

Every employee appears, even Zayn who has no department - the dept_name column is simply NULL for that row.

Finding Unmatched Rows

A very common pattern: combine LEFT JOIN with a NULL check to find rows on the left that have NO match on the right.

R Code
SELECT e.name
FROM employees e
LEFT JOIN departments d
  ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;
Output
name
Zayn

RIGHT JOIN — Keep Everything from the Right Table

R Code
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d
  ON e.dept_id = d.dept_id;
Output
name  | dept_name
Rafi  | Sales
Meera | Engineering
NULL  | HR
💡 Tip
RIGHT JOIN is just LEFT JOIN with the tables swapped - most people standardise on always using LEFT JOIN and rewriting the FROM/table order instead, since it reads more predictably. Note: SQLite does not support RIGHT JOIN at all.

FULL OUTER JOIN — Keep Everything from Both Sides

🚀 Advanced Example
FULL OUTER JOIN unions the LEFT JOIN and RIGHT JOIN results - every row from both tables appears, matched where possible and NULL-padded where not. Not supported in MySQL or SQLite; simulate it there with UNION of a LEFT and RIGHT join.
R Code
SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d
  ON e.dept_id = d.dept_id;
Output
name  | dept_name
Rafi  | Sales
Meera | Engineering
Zayn  | NULL
NULL  | HR

Key Points

  • LEFT JOIN keeps every row from the left table, filling unmatched right-side columns with NULL.
  • LEFT JOIN + WHERE right_column IS NULL is the standard pattern for finding unmatched rows.
  • RIGHT JOIN is the mirror image of LEFT JOIN; FULL OUTER JOIN keeps unmatched rows from both sides.
  • Not all databases support RIGHT JOIN or FULL OUTER JOIN (notably SQLite/MySQL) - check your dialect.