A join combines rows from two tables based on a related column — usually a foreign key matching a primary key. The join type controls which rows appear when there is no match: INNER keeps only matches, while OUTER joins keep unmatched rows from one or both sides, filling the gaps with NULLs.
INNER vs OUTER
INNER JOIN returns only rows that match in both tables. LEFT JOIN returns every row from the left table plus matches from the right (NULLs where none). RIGHT JOIN is the mirror; FULL JOIN keeps unmatched rows from both sides.
Each employee with their department name
SELECT e.name, d.dept_name FROM employees e JOIN departments d ON e.dept_id = d.dept_id; -- INNER by default
Keep employees even if the department is missing
SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id; -- employees with no matching department still appear, dept_name = NULL
⚡ The edge
- INNER drops non-matching rows; LEFT keeps every left row. A LEFT JOIN with WHERE right_col IS NULL is the classic way to find rows in one table with no match in the other.
- Put the matching condition in ON; put row filters in WHERE. Always alias tables (e, d) and prefix shared column names to avoid ambiguity.
Worked example
Find departments that have no employees.
- Start from departments and LEFT JOIN employees, so every department appears.
- Departments with no employees have NULL in the employee columns after the join.
- Filter for those: WHERE e.emp_id IS NULL.
Answer: SELECT d.dept_name FROM departments d LEFT JOIN employees e ON d.dept_id=e.dept_id WHERE e.emp_id IS NULL;
Worked example
List every employee alongside their department name and location.
- The data spans employees and departments, linked by dept_id.
- An INNER JOIN on e.dept_id = d.dept_id pairs each employee with their department.
- Select the employee name plus the department's name and location.
Answer: SELECT e.name, d.dept_name, d.location FROM employees e JOIN departments d ON e.dept_id=d.dept_id;
⚠ Watch out
- An INNER JOIN silently drops rows with no match — if you need them, use an OUTER join.
- Forgetting the ON condition produces a cross join (every combination).
- Ambiguous column names (present in both tables) must be table-qualified (e.dept_id).