INNER JOIN

Explanation

Real databases split data across multiple tables. In the employees dataset, the employees table stores department_id but not the department name — that lives in the departments table. To show an employee alongside their department name, you join the tables.

An INNER JOIN returns only the rows where the join condition matches in both tables. Think of it as the overlapping part of two circles.

The join condition usually compares a foreign key in one table to a primary key in the other: employees.department_id = departments.id.

Syntax

SELECT a.col, b.col
FROM table_a AS a
INNER JOIN table_b AS b
  ON a.key = b.key;

Interactive Example

List each employee's name together with their department name. Then compute the average salary per department.

Common Mistakes

• A missing ON condition (or a wrong one). Without a correct join key you get a Cartesian product — every row matched to every row.
• Forgetting the table prefix when both tables share a column name like id or name.
• Assuming unmatched rows appear. INNER JOIN drops them; use a LEFT JOIN if you need to keep the left side.

Best Practices

• Always give joined tables short aliases (e, d) and qualify columns.
• Join on indexed keys (foreign keys) for speed.
• Be explicit with INNER JOIN rather than the older comma syntax — it's far easier to read and reason about.

Practice Question

Write a query that returns each employee's full name (first_name || ' ' || last_name) and the location of their department, ordered by location.

Summary

INNER JOIN combines two tables and keeps only the matched rows. You specify the match with ON table_a.key = table_b.key. Qualify column names with table aliases to stay unambiguous.

FAQ