SQLDailyDrills

JOIN (INNER and LEFT)

Combine rows from two tables where a key matches. An inner join keeps only the matches; a left join keeps every row of the left table.

Syntax

SELECT … FROM a JOIN b ON a.key = b.key;
SELECT … FROM a LEFT JOIN b ON a.key = b.key;

Example

SELECT c.last_name, o.order_date FROM customers AS c JOIN orders AS o ON o.customer_id = c.customer_id;

Each order with its customer's last name. Orders without a customer are left out.

Tables used: customers, orders. Try it in a lesson below.

Common mistakes

  • Forgetting the ON condition makes a cross join: every row paired with every other row.
  • Filtering the right table in WHERE turns a LEFT JOIN back into an inner join.

Practise it