SQL JOIN Practice — Interactive Examples

Understanding SQL JOINs

SQL JOINs combine rows from two or more tables based on a related column. The most common types are INNER JOIN (returns only matching rows), LEFT JOIN (all rows from the left table plus matches), and CROSS JOIN (every combination of rows). SQLite supports INNER JOIN, LEFT JOIN, and CROSS JOIN but not RIGHT JOIN or FULL OUTER JOIN — you can achieve RIGHT JOIN by swapping table order in a LEFT JOIN.

INNER JOIN Example

INNER JOIN is the default and most common join type. It returns only rows where the join condition matches in both tables. If an employee has no matching department, that row is excluded from results.

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

LEFT JOIN for Optional Relationships

LEFT JOIN returns all rows from the left table, filling in NULLs where no match exists in the right table. This is essential for finding missing relationships — for example, customers who have never placed an order, or products with no reviews.

SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id
HAVING order_count = 0;

Self-Joins and Multi-Table JOINs

A self-join joins a table to itself — useful for hierarchical data like org charts (employee → manager) or finding duplicates. Multi-table JOINs chain multiple JOIN clauses: SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id JOIN products p ON o.product_id = p.id.