Inner Join
SQL · Joining Multiple Tables
Theory
A join combines rows from two tables using a condition, usually matching a foreign key to a primary key, such as employees.dept_id = departments.id. INNER JOIN keeps only the pairs where the condition is TRUE: an employee appears once for every department it matches, and rows with no match on the other side disappear from the result.
Conceptually the engine pairs every left row with every right row and keeps the pairs passing the ON condition; in practice it uses hash, merge or nested-loop algorithms to do this fast. Plain JOIN means INNER JOIN. Because NULL never equals anything, a row with a NULL key is always dropped. If the right table has two matching rows, the left row is duplicated, so watch row counts after a join.
Why we use it
Data is split across tables to avoid repetition: employees store a dept_id, not the department name. INNER JOIN puts the pieces back together so you can see related facts from both tables in one row.
When to use it
Use INNER JOIN when you only want rows that have a match on both sides. If you must keep rows with no match (e.g. employees without a department), use LEFT JOIN. Always join on key columns to avoid accidental duplicates.
How it works
- Take a row from employees
- Look for departments rows where e.dept_id = d.id
- Is there at least one match? Yes: Output one combined row per match. No: Drop the employee row.
- Repeat for every employees row
- Return only matched pairs
Syntax
SELECT columns
FROM t1
INNER JOIN t2 ON t1.id = t2.t1_id;Example
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;Key points
- Only rows with a match on both sides are returned
- The most commonly used join type; JOIN alone means INNER JOIN
- Rows with NULL join keys never match
- One-to-many matches repeat the left row once per match
- Use table aliases (e, d) to keep column references short and clear