INNER JOIN

Definition

INNER JOIN combines rows from two tables, keeping only the rows that have a match on both sides — the most common join type, and the default meaning of a plain JOIN with no qualifier.

Running example — departments(id, name) and employees(id, name, department_id, manager_id, salary), where department_id/manager_id can be NULL:

departments: (1, Engineering), (2, Sales), (3, Marketing)

employees: (1, Asha, dept=1, mgr=NULL, 90000), (2, Rohan, dept=1, mgr=1, 70000), (3, Neha, dept=2, mgr=1, 65000), (4, Vikram, dept=NULL, mgr=1, 50000)

Note: Marketing (dept 3) has no employees; Vikram has no department; Asha has no manager.

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

This returns Asha, Rohan, and Neha — each paired with their real department. Vikram is EXCLUDED (his department_id is NULL, matching nothing), and Marketing never appears (no employee references it). This is exactly the Equi Join concept from relational algebra (7.9), spelled with SQL's JOIN/ON syntax.

Edge Cases and Pitfalls

• INNER JOIN and plain JOIN are exactly the same thing — INNER is optional, included only for clarity/explicitness.
• Rows on EITHER side with no match are silently dropped — if you need unmatched rows preserved, that's exactly what LEFT/RIGHT/FULL OUTER JOIN (16.2-16.4) are for.
• Joining on the WRONG column (a common typo, e.g. ON e.id = d.id instead of ON e.department_id = d.id) doesn't error — it silently produces a wrong, often much smaller or larger, result.

Key Takeaways

• INNER JOIN keeps only rows matching on both sides; unmatched rows from either table are dropped.
• Plain JOIN defaults to INNER JOIN — the keyword is optional.
• A wrong join column is a silent correctness bug, not an error — double-check join conditions carefully.