SQL Joins Explained.

How Sunrise Supermarket connects its tables

Sunrise Supermarket's database isn't one giant table it's four: customers, products, orders, and order_items.

Each one holds its own piece of the story. Customers live in one table, products in another, and every order plus what was actually bought in it is split across orders and order_items. That's good design, but it means the information you actually want ("which customer bought what") is scattered across all four.
Joins are how you bring them back together.

What Are Joins?

A join combines rows from two or more tables based on a column they share, usually a key like customer_id or product_id.
Think of it as matching two lists side by side: the customers list and the orders list both have a customer_id column, so SQL lines them up and shows you both sides at once.

Types of Joins

JOIN Type	What It Returns	When to Use It
INNER JOIN	Only rows that MATCH in BOTH table	When you only want rows with data on both sides
LEFT JOIN	ALL rows from the LEFT table + matches from the right (NULL if no match)	When you want to keep all left rows, even if no match on the right
RIGHT JOIN	ALL rows from the RIGHT table + matches from the left (NULL if no match)	When you want to keep all right rows, even if no match on the left
SELF JOIN	A table joined to itself	When comparing rows within the same table

Practical Examples

INNER JOIN =>customers matched with their orders

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

full_name	order_id	status
Grace Wambui	1	Delivered
Kevin Mutiso	2	Delivered
Grace Wambui	3	Delivered

LEFT JOIN => every order, even ones without items yet

SELECT o.order_id, o.status, i.product_id
FROM orders o
LEFT JOIN order_items i
ON o.order_id = i.order_id;

order_id	status	product_id
1	Delivered	1
1	Delivered	3
2	Delivered	2
3	Delivered	NULL

RIGHT JOIN => every customer, even those with no orders yet

SELECT o.order_id, o.status, c.full_name
FROM orders o
RIGHT JOIN customers c
ON o.customer_id = c.customer_id;

order_id	status	full_name
1	Delivered	Grace Wambui
3	Delivered	Grace Wambui
2	Delivered	Kevin Mutiso
NULL	NULL	Faith Chebet
NULL	NULL	Ibrahim Noor

Notice this is the same result you'd get flipping it into a LEFT JOINwith customers listed first which is exactly why RIGHT JOIN is rarely necessary in practice.

SELF JOIN => products that share the same category

SELECT p1.product_name AS product_a, p2.product_name AS product_b, p1.category
FROM products p1
JOIN products p2
ON p1.category = p2.category AND p1.product_id < p2.product_id;

product_a	product_b	category
Maize Flour 2kg	Cooking Oil 1L	Groceries

The table is joined to itself p1 and p2 are just two aliases pointing at the same products table. The p1.product_id < p2.product_id condition stops each pair from being counted twice (and a product from pairing with itself).

# Key takeaways.

Joins turn separate, well-organized tables back into one useful picture without duplicating data everywhere just to keep it together.

Start with INNER JOIN for matches, reach for LEFT JOIN when you need everything from one side.