SQLite INNER JOIN

An INNER JOIN combines rows from two tables where a matching condition is true. Rows that have no match in the other table are excluded from the result. It is the most commonly used JOIN type and the foundation for querying related tables together.

The Matching Analogy

Table A: employees          Table B: departments
────────────────────        ──────────────────────
id │ name  │ dept_id        id │ dept_name
───┼───────┼────────        ───┼────────────
1  │ Alice │ 10             10 │ Engineering
2  │ Bob   │ 20             20 │ Marketing
3  │ Carol │ 10             30 │ HR
4  │ Dan   │ 99  ← no match

INNER JOIN on dept_id = departments.id:

     employees           departments
     ──────────          ──────────────
     Alice  │ 10  ─────► 10 │ Engineering  ✓ match
     Bob    │ 20  ─────► 20 │ Marketing    ✓ match
     Carol  │ 10  ─────► 10 │ Engineering  ✓ match
     Dan    │ 99  ─────► ??  no match       ✗ excluded

INNER JOIN Syntax

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

-- "INNER" is optional — plain "JOIN" means INNER JOIN
SELECT columns
FROM table_a
JOIN table_b ON table_a.key = table_b.key;

Full Working Example

-- Setup
CREATE TABLE departments (
  id   INTEGER PRIMARY KEY,
  name TEXT
);
INSERT INTO departments VALUES (10,'Engineering'),(20,'Marketing'),(30,'HR');

CREATE TABLE employees (
  id      INTEGER PRIMARY KEY,
  name    TEXT,
  dept_id INTEGER
);
INSERT INTO employees VALUES
  (1,'Alice',10),(2,'Bob',20),(3,'Carol',10),(4,'Dan',99);

-- INNER JOIN query
SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

name  │ department
──────┼────────────
Alice │ Engineering
Bob   │ Marketing
Carol │ Engineering

Dan is excluded because dept_id 99 has no match.

Table Aliases

Aliases shorten table names in queries. Use a short letter or abbreviation after the table name.

-- "e" is alias for employees, "d" for departments
SELECT e.name    AS employee,
       d.name    AS department
FROM   employees  e
JOIN   departments d ON e.dept_id = d.id;

Selecting Columns from Both Tables

SELECT e.id,
       e.name    AS employee_name,
       e.dept_id,
       d.name    AS dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id
ORDER BY d.name;

id │ employee_name │ dept_id │ dept_name
───┼───────────────┼─────────┼────────────
1  │ Alice         │ 10      │ Engineering
3  │ Carol         │ 10      │ Engineering
2  │ Bob           │ 20      │ Marketing

INNER JOIN with WHERE

-- Engineering employees only
SELECT e.name, d.name AS dept
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE d.name = 'Engineering';

name  │ dept
──────┼────────────
Alice │ Engineering
Carol │ Engineering

Three-Table JOIN

-- employees → dept → location
SELECT e.name, d.name AS dept, l.city
FROM employees e
JOIN departments d ON e.dept_id = d.id
JOIN locations  l ON d.location_id = l.id;

Each JOIN adds one more table to the chain.

JOIN with Aggregate

-- How many employees per department?
SELECT d.name AS dept, COUNT(e.id) AS headcount
FROM departments d
JOIN employees e ON e.dept_id = d.id
GROUP BY d.id
ORDER BY headcount DESC;

dept         │ headcount
─────────────┼──────────
Engineering  │ 2
Marketing    │ 1

INNER JOIN vs Comma Syntax (Old Style)

-- Old-style comma join (works but avoid it)
SELECT e.name, d.name
FROM employees e, departments d
WHERE e.dept_id = d.id;

-- Modern INNER JOIN (clearer, preferred)
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.dept_id = d.id;

Key Takeaways

INNER JOIN returns rows with matching values in both tables
Rows without a match in the other table are excluded from results
JOIN without a keyword means INNER JOIN
Use table aliases to write shorter, readable queries
Chain multiple JOINs to connect three or more tables in one query

Previous lesson

Back to course

Next lesson