SQL Joins and Relationships

Joins connect data stored across multiple tables. They are essential for backend validation because real applications rarely store everything in one table.

Keys

A primary key uniquely identifies a row. A foreign key points to another table's primary key.

customers.id        -- primary key
orders.customer_id  -- foreign key to customers.id

INNER JOIN

Returns rows where both tables match.

SELECT o.order_id, c.name, o.status
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;

LEFT JOIN

Returns all rows from the left table, even when the right table has no matching row. This is useful for finding missing related data.

SELECT c.id, c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.order_id IS NULL;

Common Mistakes

• Joining on names instead of stable ids.
• Forgetting that one-to-many joins multiply rows.
• Using WHERE filters that accidentally turn a left join into an inner join.