Table Joins (2) — OUTER JOIN, CROSS JOIN, Self-Join

OUTER JOIN — keep rows that exist on only one side

The INNER JOIN from last time returned only rows that matched in both tables, so employees with no department and departments with no employees disappeared from the result. When the requirement is "I want every employee; people with no department can have a blank department name," you use an OUTER JOIN that always keeps the rows on one side.

There are 3 kinds of OUTER JOIN depending on which side you keep: LEFT JOIN keeps every row of the left table, RIGHT JOIN keeps every row of the right table, and FULL OUTER JOIN keeps every row of both. The columns from the side with no match are filled with NULL. This course's console uses SQLite, and you can run all of LEFT / RIGHT / FULL as-is. The data is the same as last time: department (6 departments) and employee (30 employees).

LEFT JOIN — always keep the left table

When you write SELECT cols FROM left LEFT JOIN right ON condition, every row of the left table is kept, and if there's no matching row in the right table the right-side columns become NULL. LEFT JOIN is short for LEFT OUTER JOIN, and both behave the same. You use it when you want to list the leading side without dropping anyone, like "show every employee, and leave the department name blank for people with no department."

-- Keep all employees. If there's no department, dept_name is NULL
SELECT e.name, e.city, d.dept_name
FROM employee e
LEFT JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY e.emp_id;

Imagine the requirement: "I want to find employees who belong to no department, line up the dept_id from the employee side and the department side, and confirm why the department name can't be looked up." (Run it correctly and the explanation will appear.)

① LEFT JOIN employee (alias e) on the left and department (alias d) on the right, and narrow to only the rows where d.dept_id is NULL.

② In addition to e.name and e.city, pull `e.dept_id` (employee side) and `d.dept_id` / `d.dept_name` (department side). Visually confirm that because e.dept_id itself is NULL (no department assigned) there's no join partner, so d.dept_id and d.dept_name are NULL too. Sort by e.name ascending.

③ Then change the same join to an INNER JOIN and confirm that not a single row comes back (because rows whose join key is NULL disappear under INNER JOIN).

RIGHT JOIN and FULL OUTER JOIN

RIGHT JOIN is LEFT JOIN with the left and right swapped — it keeps every row of the right table. When you write employee e RIGHT JOIN department d ON ..., the Legal department with no employees at all stays in the result, and the employee-side columns become NULL. It's suited for "show every department, and display departments with no employees with blank fields."

FULL OUTER JOIN keeps every row of both tables. Matched rows are joined side by side, rows that exist only on the left (the 4 people with no department) get NULL on the right side, and rows that exist only on the right (the Legal department) get NULL on the left side — all lined up in one result.

-- RIGHT JOIN: keep all departments. Legal has NULL on the employee side
SELECT d.dept_name, e.name
FROM employee e
RIGHT JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY d.dept_id;

-- FULL OUTER JOIN: keep both employees with no department and departments with no employees
SELECT e.name, d.dept_name
FROM employee e
FULL OUTER JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY d.dept_id;

Imagine the requirement: "I want to confirm departments with not a single employee assigned, lining up the department-side columns and the employee-side columns."

① RIGHT JOIN employee (alias e) on the left and department (alias d) on the right, and narrow to only the rows where e.emp_id is NULL.

② Pull d.dept_id / d.dept_name / d.location (department side) and `e.emp_id` / `e.name` (employee side). Confirm that d.dept_id has a value (the department number) while e.emp_id and e.name are NULL (blank) — meaning the department really exists but has 0 employees assigned. Sort by d.dept_name ascending.

Imagine the requirement: "I want to match employees and departments into a single table, line up the dept_id on both sides, and confirm how rows that exist on only one side become NULL."

① FULL OUTER JOIN employee (alias e) and department (alias d). The join condition is a dept_id match.

② Pull e.emp_id / e.name / `e.dept_id` (employee side) and `d.dept_id` / `d.dept_name` (department side). Matched rows have values on both sides, but employees with no department assigned have both `e.dept_id` and `d.dept_id` NULL, and the department with no employees (Legal) has a value in `d.dept_id` and NULL on the `e` side. Visually confirm which pattern each "one-side-only row" is.

③ Sort by d.dept_id ascending (rows where d.dept_id is NULL cluster toward the top).

CROSS JOIN — build every combination

CROSS JOIN has no join condition and builds every combination of each row of the left table with each row of the right table. The number of result rows is left row count × right row count (the Cartesian product). For employee (30 rows) and department (6 rows), that's 30 × 6 = 180 rows.

In practice you use it to enumerate every possible combination like "all employees × all months" or "all stores × all products," then join actuals onto it and fill the zero-actual cells with 0 — as the foundation for an aggregation. Since it's a join with no condition, you don't add ON.

-- Count the number of all-employee x all-department combinations
SELECT count(*) AS combo_count
FROM employee e
CROSS JOIN department d;

-- Inspect part of the combinations (only pairs with the Sales department)
SELECT e.name, d.dept_name
FROM employee e
CROSS JOIN department d
WHERE d.dept_name = 'Sales'
ORDER BY e.emp_id;

Imagine the requirement: "I want to count how many possible combinations there are of all employees and all departments."

① CROSS JOIN employee (alias e) and department (alias d) (don't write a join condition).

② Count the total number of combinations with count(*), and alias the result column as combo_count.

Self-join — use the same table twice to look up the manager's name

The employee table has a manager_id column (the manager's employee number), and the manager is also an employee in the same employee table. When you want to "display each employee's name alongside their manager's name," you JOIN the same table twice. This is called a self-join.

You write it as FROM employee e JOIN employee m ON e.manager_id = m.emp_id. You make the same table appear twice with aliases that split the roles — e as "the employee side" and m as "the manager side." Aliases are required in a self-join; without them you can't tell which employee is which. With an INNER JOIN, employees with no manager disappear, so use a LEFT JOIN if you want to list every employee.

-- Show each employee's manager name alongside (only employees who have a manager: INNER)
SELECT e.name AS employee, m.name AS manager
FROM employee e
JOIN employee m
  ON e.manager_id = m.emp_id
ORDER BY e.emp_id;

Imagine the requirement: "I want to list every employee with their manager's name alongside. Employees with no manager can have a blank manager name."

① LEFT JOIN employee as alias e (employee side) with employee again as alias m (manager side). The join condition is e.manager_id = m.emp_id.

② Pull 2 columns, aliasing e.name as employee and m.name as manager.

③ Sort by e.emp_id ascending.

An OUTER JOIN's NULL changes meaning depending on where you put WHERE

If after a LEFT JOIN you write a condition on a right-side column like WHERE right_table.column = value, NULL always evaluates as false in a comparison, so rows with no partner drop and you effectively get the same result as an INNER JOIN. When you want to narrow down while keeping "rows with no partner," write that condition on the ON side, or use WHERE right_table.key IS NULL to explicitly pick up "rows with no partner." For NULL checks, always use IS NULL / IS NOT NULL, never = NULL.