SQL - Left/Right/Full Join

Overview

LEFT JOIN returns all rows from the left table and matching rows from the right; RIGHT JOIN is the opposite (not supported by SQLite); FULL OUTER JOIN includes unmatched rows from both sides (not supported by SQLite).

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

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

-- FULL OUTER JOIN (use UNION of left and right when needed)

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

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

-- FULL OUTER JOIN
SELECT e.first_name, d.name AS department
FROM employees e FULL OUTER JOIN departments d ON d.id=e.department_id;

-- SQLite: LEFT JOIN only
SELECT e.first_name, d.name AS department
FROM employees e LEFT JOIN departments d ON d.id=e.department_id;

-- Simulate FULL OUTER via UNION of LEFT and RIGHT (emulated)
SELECT e.first_name, d.name AS department
FROM employees e LEFT JOIN departments d ON d.id=e.department_id
UNION
SELECT e.first_name, d.name AS department
FROM departments d LEFT JOIN employees e ON d.id=e.department_id;

Prerequisite

Load the Standard Test Data.

Live LEFT JOIN Output

first_name	department
Ada	Engineering
Bob	Engineering
Chen	HR
Dee	Sales
Eli	Engineering

Sample Use Cases (2)

1) All departments with employee counts

department	employees
Engineering	3
HR	1
Sales	1

2) Simulated FULL OUTER of departments and employees

first_name	department
Ada	Engineering
Bob	Engineering
Eli	Engineering
Chen	HR
Dee	Sales

« Prev: SQL - Inner Join Next: SQL - Self & Cross Join »