SQL INNER JOIN

INNER JOIN is the join type from the overview lesson's basic example, and the one meant whenever JOIN is written alone. It returns only rows where the join condition actually matches on both sides, an employee with no department_id, or a department with no employees, simply doesn't appear in the result.

Learning Objectives

• Write an INNER JOIN with an explicit, readable ON condition.
• Combine INNER JOIN with WHERE, ORDER BY, and table aliases.
• Join three tables in a single query.

Basic INNER JOIN

Legal (department id 5) has no employees yet, so it never appears in this result, INNER JOIN only returns rows that match on both sides. Writing INNER JOIN instead of plain JOIN behaves identically, it's purely a readability choice.

SELECT employees.first_name, departments.name AS department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

Using Table Aliases

e and d are table aliases, short stand-in names assigned right after the table name (no AS needed for a table alias, though SELECT column aliases like department_name still use AS). Once a query joins multiple tables, aliases make every column reference shorter without losing clarity, and they become close to mandatory once self-joins or multi-table joins are involved, both covered later in this module.

SELECT e.first_name, d.name AS department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

Adding WHERE and ORDER BY

WHERE and ORDER BY work exactly as before, applied after the join has already combined rows from both tables. The query reads naturally top to bottom: pick the tables and how they relate (FROM / JOIN / ON), narrow the rows (WHERE), then order what's left (ORDER BY).

SELECT e.first_name, d.name AS department_name, e.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
WHERE e.salary > 90000
ORDER BY e.salary DESC;

Joining Three Tables

Each additional JOIN ... ON adds one more table to the chain. This returns one row per employee per project they're assigned to, through the project_assignments linking table, an employee on two projects appears twice, once per project.

SELECT e.first_name, d.name AS department_name, p.name AS project_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
INNER JOIN project_assignments pa ON pa.employee_id = e.id
INNER JOIN projects p ON pa.project_id = p.id;

Try It Yourself

Write a query that returns the first_name and department name of every employee earning more than 85,000, ordered by salary descending.

SELECT e.first_name, d.name AS department_name, e.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
WHERE e.salary > 85000
ORDER BY e.salary DESC;

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Use short, consistent table aliases (e, d, p) once a query joins more than one table.
• Write INNER JOIN explicitly rather than relying on bare JOIN, for a reader scanning the query.
• Filter with WHERE after the join, not by trying to filter inside the ON condition unless there's a specific reason to.

Interview Questions

Summary

INNER JOIN, the default join type, returns only rows with a match in both tables, combines cleanly with WHERE and ORDER BY, and can chain across three or more tables by adding another JOIN ... ON for each one.

What's Next?

LEFT JOIN next, which keeps every row from the left table even when there's no match on the right, exactly what INNER JOIN silently drops, useful for finding Beacon Robotics' Legal department sitting empty.