INNER JOIN

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

INNER JOIN returns only rows where the ON condition matches in BOTH tables. Rows with no match in either table are excluded.

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

You can add WHERE, ORDER BY, and other clauses after the JOIN. Always alias table names (e, d) when joining to avoid ambiguity.

SELECT e.name AS employee,
       m.name AS manager
FROM employees e
INNER JOIN employees m
  ON e.manager_id = m.id;

This is a SELF JOIN — joining a table to itself. Used when hierarchical data (manager-employee) is in the same table. Very common interview question.

SELECT o.order_id,
       c.customer_name,
       p.product_name,
       o.quantity
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products p ON o.product_id = p.id;

You can chain multiple JOINs. Each JOIN adds another table. The order of JOINs matters for readability but not (usually) for correctness.

SELECT d.department_name,
       COUNT(e.id) AS emp_count
FROM departments d
INNER JOIN employees e
  ON d.id = e.department_id
GROUP BY d.department_name
ORDER BY emp_count DESC;

Combining JOIN with GROUP BY is extremely common in reporting queries. Notice departments with no employees won't appear — use LEFT JOIN if you need them.

-- These are identical — JOIN defaults to INNER JOIN:
SELECT * FROM a JOIN b ON a.id = b.id;
SELECT * FROM a INNER JOIN b ON a.id = b.id;

JOIN without a type keyword is always INNER JOIN. Writing INNER JOIN explicitly is better practice for readability.