INNER JOIN

So far you have been selecting data from a single table at a time. But real databases are relational -- data is spread across multiple tables that reference each other. JOINs are the mechanism that lets you combine rows from two or more tables based on a related column.

The INNER JOIN is the most common type of join. It returns only the rows where the join condition is met in both tables.

How an INNER JOIN Works

Think of an INNER JOIN as a Venn diagram -- you only get the overlap:

  Table A           Table B
 +--------+       +--------+
 |        |       |        |
 |   A    | A & B |   B    |
 |  only  |returned| only  |
 |        |       |        |
 +--------+       +--------+

 Rows only   Rows in    Rows only
 in A are    BOTH are   in B are
 excluded    returned   excluded

Basic Syntax

SELECT columns
FROM table_a
INNER JOIN table_b
  ON table_a.column = table_b.column;

The INNER keyword is optional -- JOIN by itself means INNER JOIN.

Joining employees to departments

Our employees table has a department column that contains the department name. Our departments table has a name column along with budget and location. Let's connect them:

SELECT
  e.name AS employee_name,
  e.salary,
  d.name AS department_name,
  d.budget,
  d.location
FROM employees e
INNER JOIN departments d
  ON e.department = d.name;

Notice the use of table aliases (e and d). These are short nicknames that make the query easier to read and write, especially when column names appear in both tables.

What just happened step by step

employees table              departments table
+----------+-------------+   +-------------+--------+----------+
| name     | department  |   | name        | budget | location |
+----------+-------------+   +-------------+--------+----------+
| Alice    | Engineering |   | Engineering | 500000 | Building A|
| Bob      | Marketing   |   | Marketing   | 200000 | Building B|
| Charlie  | Engineering |   | Sales       | 300000 | Building C|
| Diana    | Sales       |   +-------------+--------+----------+
| Eve      | Engineering |
| Frank    | Marketing   |
| Grace    | Engineering |
| Hank     | Sales       |
+----------+-------------+

For EACH employee row, PostgreSQL looks for a matching
department row where employees.department = departments.name.

Result:
+----------+--------+-------------+--------+----------+
| emp_name | salary | dept_name   | budget | location |
+----------+--------+-------------+--------+----------+
| Alice    | 95000  | Engineering | 500000 | Building A|
| Bob      | 65000  | Marketing   | 200000 | Building B|
| Charlie  | 80000  | Engineering | 500000 | Building A|
| Diana    | 72000  | Sales       | 300000 | Building C|
| ...      | ...    | ...         | ...    | ...      |
+----------+--------+-------------+--------+----------+

Every employee matched a department, so every row appears. But if an employee had a department value that did not exist in the departments table, that employee would be excluded from the results.

Filtering JOINed Data

You can add a WHERE clause after the join to filter the combined results:

SELECT
  e.name AS employee_name,
  e.salary,
  d.location
FROM employees e
INNER JOIN departments d
  ON e.department = d.name
WHERE e.salary > 70000;

This first joins the tables, then filters to keep only employees earning more than 70,000.

Joining with Additional Conditions

You can have multiple conditions in the ON clause using AND:

SELECT
  e.name AS employee_name,
  d.name AS department_name,
  d.budget
FROM employees e
INNER JOIN departments d
  ON e.department = d.name
  AND d.budget > 250000;

This only joins employees to departments where the department budget exceeds 250,000. Employees in lower-budget departments will not appear at all.

Selecting Specific Columns

When tables share column names, you must qualify them with the table name or alias:

-- This would cause an error because 'name' exists in both tables:
-- SELECT name FROM employees INNER JOIN departments ON ...

-- Instead, be specific:
SELECT
  e.name AS employee_name,
  d.name AS department_name
FROM employees e
INNER JOIN departments d
  ON e.department = d.name;

Try It Yourself

1. Write a query that joins employees to departments and shows only employees in the "Engineering" department along with the department's budget.

2. Join employees to departments and find which location has the highest total salary across its employees. Use SUM(e.salary) and GROUP BY d.location.

3. Join employees to departments and list employees whose salary is greater than 10% of their department's budget.