Skip to main content
Joins
1 / 4
0%

INNER JOIN

0/4 solved4 to unlock next
Locked

Lesson 01

INNER JOIN

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.

Why you need joins

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.

Syntax

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.
  • The 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.
  • Once two tables are in play, prefix columns with their alias (e.first_name, d.name) so both you and PostgreSQL know where each column comes from.

Worked examples

1. 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;

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.

2. Join, then filter with WHERE

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.

3. Join, then aggregate

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.

4. Join on a condition that is not equality

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.

5. When one row matches many

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.

Under the hood

It helps to picture a join in two logical steps, even though PostgreSQL never actually executes it this way:

  1. Form every possible pair of rows from the two tables (the Cartesian product). For customers and orders that would be 22 × 50 = 1,100 pairs.
  2. Keep only the pairs where the 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.

Common mistakes

Ambiguous column names

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.

Joining on the wrong columns

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.

Forgetting the ON clause

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.

Trusting NATURAL JOIN

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.

Older syntax you might see

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.

Practice

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.

Key takeaways

  • 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.
  • Join a foreign key to the primary key it references, and prefix columns with table aliases to avoid ambiguity.
  • One-to-many joins multiply rows: 22 customers joined to 50 orders gives 50 rows, not 22.
  • The ON condition can be any boolean expression, including ranges such as salary bands.
  • In PostgreSQL, a JOIN without ON is a syntax error. Use CROSS JOIN when you genuinely want every combination.

INNER JOIN: in depth

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.

Worked examples

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.

Common mistakes

  • Joining on the wrong columns (e.id = d.id). The query runs but pairs unrelated rows; always join a foreign key to the key it references.
  • Not noticing dropped rows. Compare COUNT(*) before and after the join to see whether unmatched rows disappeared.
  • Ambiguous column names: both tables have id and name, so prefix every column with its table alias.

Other databases

  • JOIN and INNER JOIN mean the same thing in every database.
  • Old comma syntax (FROM a, b WHERE a.x = b.y) still works but makes it easy to forget the join condition and produce a cross join.
Check yourself: Suppose 20 employees exist and one has a NULL department_id. How many rows does the join above return?

19. The employee with no department has no match, so INNER JOIN leaves them out.

Concept check

Quick check: INNER JOIN

3 quick questions. One at a time. Instant score at the end.

Exercise 1

Employees with department name

Enterprise Workforce AnalyticsmediumPostgreSQL

Return first_name, last_name, and the department name (alias department) for every employee that has a department.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 2

Order items with product name

Commerce Operations IntelligencemediumPostgreSQL

Return order_id, product name (alias product), quantity, and unit_price for every order item.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 3

Appointment: patient and doctor names

Clinical Care PerformanceeasyPostgreSQL

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.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 4

Products with their category name

Commerce Operations IntelligenceeasyPostgreSQL

From the store sandbox, return product name (alias product), category name (alias category), and price, ordered by category, then product.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Joins
1 / 4
0%

INNER JOIN

0/4 solved4 to unlock next
Locked