Lesson Description
Combine rows from two tables where a condition matches.
Lesson 01
Lesson Description
Combine rows from two tables where a condition matches.
Easy Project
Mini project: Build a people-and-departments report that lists employee names, department names, and validates row counts to prove your join is correct.
JOIN Visual
employees
departments
Match keys first, then verify row counts after the join.
Up to now every query you wrote read from a single table. Real databases rarely work that way. To avoid storing the same fact twice, a well-designed schema splits information across tables and links them with keys.
Look at the HR sandbox. The employees table has a department_id column, but it holds a number like 80, not the word "Sales". The department's name lives in departments, in the row whose id is 80. If your manager asks "give me every employee next to the name of their department", neither table can answer on its own. You need to pair each employee row with the matching department row. That pairing is a join.
INNER JOIN is the most common kind. It returns one combined row for every pair of rows that satisfies a condition you write, and it drops any row that has no partner.
SELECT a.column, b.column
FROM table_a AS a
INNER JOIN table_b AS b
ON b.some_key = a.other_key;
A few things to notice:
INNER JOIN and plain JOIN mean exactly the same thing. Most people write JOIN.ON clause is the matching rule. It is usually "foreign key equals primary key", for example d.id = e.department_id.a and b are table aliases. The AS keyword is optional for tables, so FROM employees e works too.e.first_name, d.name) so both you and PostgreSQL know where each column comes from.SELECT
e.first_name,
e.last_name,
d.name AS department
FROM employees e
JOIN departments d ON d.id = e.department_id;
This returns 40 rows, one per employee. Every employee in sandbox_hr has a department_id, and every one of those ids exists in departments, so nobody is dropped.
What about the other direction? The HR sandbox has 10 departments, but only 9 of them show up in this result. The Accounting department (id = 110) has no employees, so there is no pair to produce, and an inner join silently leaves it out. That is the defining behavior of INNER JOIN: unmatched rows on either side disappear. Keep Accounting in mind, because the next lesson shows how to keep it.
A join is just a richer FROM clause. Everything you learned about WHERE and ORDER BY still works, and you can filter on columns from either table. In the store sandbox, list the Electronics products from most to least expensive:
SELECT p.name, p.price, c.name AS category
FROM products p
JOIN categories c ON c.id = p.category_id
WHERE c.name = 'Electronics'
ORDER BY p.price DESC;
Five rows come back, starting with the 4K Monitor 27" at 349.00 and ending with the USB-C Hub at 49.95. Notice that the filter uses c.name, a column that only exists after the join. Without the join you would have to know that Electronics has a particular category_id and hard-code that number.
Joins combine naturally with GROUP BY from the aggregations course. In the hospital sandbox, which doctors have completed the most appointments?
SELECT
d.first_name,
d.last_name,
d.specialty,
COUNT(*) AS completed
FROM appointments a
JOIN doctors d ON d.id = a.doctor_id
WHERE a.status = 'completed'
GROUP BY d.id, d.first_name, d.last_name, d.specialty
ORDER BY completed DESC, d.last_name
LIMIT 5;
The top three (Olivia Bennett, Aisha Khan and Yuki Tanaka) are tied with 6 completed appointments each, followed by Sarah Johnson and Priya Patel with 5. Grouping by d.id as well as the name columns is a good habit: two doctors could share a name, but never an id.
The ON clause accepts any boolean expression, not only =. The jobs table stores a salary band (min_salary, max_salary) for each job. Which employees are paid outside the band for their job?
SELECT
e.first_name,
e.last_name,
e.salary,
j.title,
j.min_salary,
j.max_salary
FROM employees e
JOIN jobs j ON j.id = e.job_id
WHERE e.salary < j.min_salary
OR e.salary > j.max_salary;
Three employees come back: Neena Kochhar and Lex De Haan, both earning 17000.00 against a President band starting at 20000.00, and Kevin Mourgos at 5800.00, just under the Shipping Manager minimum of 6000.00. This kind of query is a real data quality check. The join brings the rule (the band) next to the fact (the salary) so you can compare them in one row.
So far each employee matched exactly one department. Now flip the relationship. Each customer can have many orders, so joining customers to orders produces one row per order, not per customer:
SELECT c.first_name, c.last_name, o.id AS order_id, o.order_date
FROM customers c
JOIN orders o ON o.customer_id = c.id;
The store has 22 customers and 50 orders, and this query returns 50 rows. A customer with three orders appears three times. Only 20 distinct customers appear, because two customers have never ordered and an inner join drops them.
This is the single most important thing to understand about join sizes: the result has one row per matching pair. One-to-one relationships keep the row count stable. One-to-many relationships multiply the rows on the "one" side.
It helps to picture a join in two logical steps, even though PostgreSQL never actually executes it this way:
= 1,100 pairs.ON condition is true.In the full logical processing order, FROM and its joins come first, then WHERE, then GROUP BY, HAVING, SELECT and finally ORDER BY. That is why a WHERE clause can reference columns from any joined table, and why the result of a join is what GROUP BY sees.
In reality the planner picks a much cheaper strategy: a nested loop (often with an index lookup), a hash join (build a hash table from one side, probe it with the other) or a merge join (walk two sorted inputs together). You can see which one it chose with EXPLAIN. The result is the same regardless of strategy.
One more detail: if the join column is NULL, the row can never match, because NULL = NULL is not true (you saw this in the NULL lesson). An inner join always drops rows whose key is NULL.
Both employees and departments have an id column. If you write it without a prefix, PostgreSQL refuses to guess:
SELECT id, first_name, last_name
FROM employees
JOIN departments ON departments.id = employees.department_id;
ERROR: column reference "id" is ambiguous
Fix it by qualifying the column: SELECT e.id, ... with aliases, or employees.id without them. Columns that exist in only one table (like first_name) work unqualified, but prefixing everything makes queries easier to read and safe against future schema changes.
This query runs without error, which makes it dangerous:
SELECT COUNT(*)
FROM employees e
JOIN departments d ON d.id = e.id;
It returns 2 instead of 40. It compares an employee's own id with a department's id, which only coincidentally matches for the two employees whose ids are 100 and 110. Always join a foreign key (e.department_id) to the primary key it references (d.id). A sudden, surprising row count is your best clue that the ON clause is wrong.
SELECT e.first_name
FROM employees e
JOIN departments d;
ERROR: syntax error at or near ";"
In PostgreSQL, JOIN requires an ON (or USING) clause. If you really want every combination of rows, say so explicitly with CROSS JOIN. MySQL is more permissive and treats JOIN without ON as a cross join, which can quietly produce huge results.
NATURAL JOIN matches on every column that has the same name in both tables. In sandbox_hr, the only shared name between employees and departments is id, so employees NATURAL JOIN departments returns the same 2 wrong rows as the mistake above. Write explicit ON conditions instead.
Before the JOIN keyword was standardized, joins were written with a comma and the condition in WHERE:
SELECT c.first_name, o.id
FROM customers c, orders o
WHERE o.customer_id = c.id;
This returns the same 50 rows and works in every major database. Prefer the explicit JOIN ... ON form: it keeps the matching rule next to the table it belongs to, and forgetting the condition is a syntax error instead of a silent 1,100-row cross product.
The exercises below this lesson ask you to pair employees with department names, attach product names to order items, show patient and doctor names side by side for each appointment, and list products with their categories in a specific order. For each one, first identify the foreign key column that links the two tables, then decide which columns need an alias. The appointment exercise needs two joins from the same starting table, which previews the multi-table lesson later in this course.
INNER JOIN (or just JOIN) returns one row for each pair of rows where the ON condition is true, and drops unmatched rows from both sides.ON condition can be any boolean expression, including ranges such as salary bands.JOIN without ON is a syntax error. Use CROSS JOIN when you genuinely want every combination.Data is split across tables to avoid repetition: employees store a department_id, not the department name. INNER JOIN puts it back together, keeping only rows that have a match on both sides. It is the most common join and the base for most reports.
Employees with their department name
SELECT e.first_name, e.last_name, d.name AS department
FROM employees e
JOIN departments d ON d.id = e.department_id;Each employee row is matched to its department. Employees without a department are dropped.
Order lines with product names
SELECT oi.order_id, p.name AS product, oi.quantity, oi.unit_price
FROM order_items oi
JOIN products p ON p.id = oi.product_id
ORDER BY oi.order_id;One row per line item. The product name comes from products; quantity and price come from the line.
19. The employee with no department has no match, so INNER JOIN leaves them out.
Concept check
3 quick questions. One at a time. Instant score at the end.
Return first_name, last_name, and the department name (alias department) for every employee that has a department.
No results yet
Run a query to see results here
Return order_id, product name (alias product), quantity, and unit_price for every order item.
No results yet
Run a query to see results here
From the hospital sandbox, return patient first_name (alias patient_first), patient last_name (alias patient_last), doctor first_name (alias doctor_first), and doctor last_name (alias doctor_last) for every appointment.
No results yet
Run a query to see results here
From the store sandbox, return product name (alias product), category name (alias category), and price, ordered by category, then product.
No results yet
Run a query to see results here