SQL joins

Inner, left, right, and full

INNER JOIN keeps matches only. LEFT JOIN keeps every row from the left table and fills the right side with NULL when nothing matches. RIGHT JOIN is the mirror. FULL JOIN keeps both sides. The visualizer shows this with people and tasks before you write it against a larger schema.

Where the filter sits

A condition in WHERE is applied after the join. Put a filter on the right table in the ON clause when you still want unmatched left rows. The warehouse exercise is built around that distinction.

Example

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

The key is department_id, not a shared name. Names can collide. Keys should not.

Practice

• EasyE-commerce
  Order line totals

  Attach each order line to its product and compute the line total.

• EasyE-commerce
  Revenue by category

  Sum recognized revenue for each product category.

• EasyHR
  Employees and departments

  Pair every employee with the department they sit in.

• HardHR
  Employees and their managers

  Show each employee and the manager they report to.

• MediumUniversity
  Enrollment counts by course

  Count students enrolled in each course.

• MediumLogistics
  Active shipments per warehouse

  Count shipments per warehouse, excluding cancelled freight.

• HardE-commerce
  Customer spend with a CTE

  Calculate spend per customer on paid and shipped orders.

Common mistakes

• Joining on name because it looks unique in the sample.
• Forgetting a predicate and producing a cross product.
• Using an inner join when the question asks to keep people with no manager.
• Filtering the right table in WHERE and accidentally dropping the unmatched left rows.

FAQ

When should I alias tables?

As soon as two tables share a column name, and earlier if it makes the ON clause readable. employees AS e and employees AS m is how a self join stays understandable.

Does SQLite have RIGHT JOIN?

Recent SQLite does. If you are writing for an older engine, swap the tables and use LEFT JOIN. The result is the same set of pairs.

Related practice

• SQL practice
• Beginner SQL practice
• GROUP BY
• Subqueries