More JOIN examples: the employee database

The employee database

Three tables, small enough to hold in your head, but with every feature these join examples need: a shared column name to join on, two departments with nobody in them, and a table that points back at itself.

DEPARTMENT (6 rows), JOBS (6 rows), and EMPLOYEES (24 rows).

Table	Columns	Why it is useful here
DEPARTMENT	dept_id (PK), dept_name, location, budget	Marketing and Research exist but have zero employees, exactly the case an outer join is built for
JOBS	job_id (PK), job_title, min_salary, max_salary	Gives a second, unrelated table to CROSS JOIN against and to join on with a condition other than equals
EMPLOYEES	emp_id (PK), first_name, last_name, email, hire_date, salary, dept_id (FK), job_id (FK), manager_id (FK to its own emp_id)	dept_id and job_id share their names with the parent tables' primary keys, so JOIN USING works; manager_id makes a self join possible

More Section 4.8 examples

Joins, again, on a different schema

Same five join forms as the chapter review. What changes is which rows survive, and that is worth seeing twice.

JOIN ON and JOIN USING

EMPLOYEES.dept_id and DEPARTMENT.dept_id share the same name, so this is exactly the case JOIN USING was built for.

SELECT first_name, last_name, dept_name
FROM   EMPLOYEES JOIN DEPARTMENT USING (dept_id)
ORDER BY last_name;
-- 24 rows: every employee belongs to some department, so no rows are lost

JOIN ON is required the moment the condition is not a simple name match. Here it checks a business rule instead: is anyone being paid below their own job's minimum salary?

SELECT first_name, last_name, salary, job_title, min_salary
FROM   EMPLOYEES JOIN JOBS
       ON EMPLOYEES.job_id = JOBS.job_id
      AND salary < min_salary;
-- 0 rows: nobody in this data is paid under their job's floor

NATURAL JOIN

SELECT first_name, last_name, dept_name, location
FROM   EMPLOYEES NATURAL JOIN DEPARTMENT;
-- 24 rows, dept_id appears once instead of twice

It works here because dept_id is the only column EMPLOYEES and DEPARTMENT happen to share. That is exactly the fragility a natural join carries: if DEPARTMENT ever gained a column that happened to share a name with something in EMPLOYEES, for instance a location column added to both tables for unrelated reasons, NATURAL JOIN would silently start matching on that column too, with no change to the SQL itself.

CROSS JOIN

SELECT dept_name, job_title
FROM   DEPARTMENT CROSS JOIN JOBS;
-- 36 rows: 6 departments times 6 jobs, every combination, whether anyone actually holds that job in that department or not

None of those 36 pairings describe a real employee; this is the row count you get from PRODUCT before any join condition trims it down to something meaningful.

LEFT and RIGHT OUTER JOIN

Marketing and Research have no employees at all, so they are exactly the rows an inner join would quietly drop. Keep them with an outer join and filter for the ones that never matched:

SELECT dept_name, first_name, last_name
FROM   DEPARTMENT D LEFT JOIN EMPLOYEES E
       ON D.dept_id = E.dept_id
WHERE  E.emp_id IS NULL;
-- Marketing and Research: every EMPLOYEES column is NULL because neither department has a single matching row

The same question, written as a RIGHT JOIN with the tables swapped, returns the identical two rows:

SELECT dept_name, first_name, last_name
FROM   EMPLOYEES E RIGHT JOIN DEPARTMENT D
       ON D.dept_id = E.dept_id
WHERE  E.emp_id IS NULL;

Joining all three tables

Chaining a third table works the same way as chaining a second: each JOIN adds one more table to the combined result before the next JOIN runs.

SELECT E.first_name, E.last_name, D.dept_name, J.job_title, E.salary
FROM   EMPLOYEES E
       JOIN DEPARTMENT D ON E.dept_id = D.dept_id
       JOIN JOBS J       ON E.job_id  = J.job_id
ORDER BY D.dept_name, E.salary DESC;
-- 24 rows, every numeric id replaced with the readable name or title it stands for

A self join: who reports to whom

EMPLOYEES.manager_id points back at another row's emp_id in the very same table, so the table is listed twice, once as the employee and once as the manager, with two different aliases.

|| is the standard SQL string concatenation operator: it glues two or more character values together into one. E.first_name || ' ' || E.last_name joins the first name, a literal space, and the last name into a single value, "Carolyn Reyes" instead of two separate columns. It shows up here purely for readability, so the manager's full name reads as one column instead of forcing you to compare four separate name columns by eye. SQLite, PostgreSQL, and Oracle all use || this way; SQL Server uses + for the same job instead.

SELECT E.first_name || ' ' || E.last_name AS employee,
       M.first_name || ' ' || M.last_name AS manager
FROM   EMPLOYEES E LEFT JOIN EMPLOYEES M
       ON E.manager_id = M.emp_id
ORDER BY manager, employee;
-- 24 rows; Carolyn Reyes is the only NULL manager, since she is the president