I Built a Supermarket Database in PostgreSQL - Here's What INNER JOIN vs LEFT JOIN Actually Taught Me

Most SQL tutorials hand you a dataset that's already loaded and ask you to query it. That skips the part that actually teaches you something: designing the schema, living with your own constraints, and finding out the hard way when INNER JOIN and LEFT JOIN give you different answers to the same-looking question.

So I built Sunrise Supermarket - a small PostgreSQL project modeling customers, products, orders, and order items - to work through that whole arc end to end.

The schema

Four tables, in their own schema:

CREATE SCHEMA IF NOT EXISTS Sunrise_Supermarket;
SET search_path TO Sunrise_Supermarket;

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone_number VARCHAR(20) UNIQUE NOT NULL,
    city VARCHAR(50) NOT NULL,
    loyalty_points INT DEFAULT 0
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(150) NOT NULL,
    category VARCHAR(50) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price > 0),
    stock_quantity INT DEFAULT 0
);

Two small things doing real work here: UNIQUE on email/phone stops duplicate customers, and CHECK (unit_price > 0) means the database itself refuses a free or negative price - no application code required to catch that.

The real design decision: orders vs order_items

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'Pending',
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK(quantity > 0),
    CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products(product_id)
);

An order by itself can't say what was bought - that's what order_items is for, pairing each order with a product and quantity so one order can hold multiple products.

ON DELETE CASCADE earns its keep almost immediately. After seeding a cancelled order, I just delete it outright:

DELETE FROM orders WHERE order_id = 4;

No manual cleanup of order_items needed - the cascade handles it.

INNER JOIN vs LEFT JOIN, side by side

This is the part that actually clicked for me. First, an inner join between customers and orders:

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

This only returns customers who have placed an order. Zero orders = you don't show up in this result at all.

Now compare that to a left join checking order completeness:

SELECT orders.order_id, orders.order_date, order_items.product_id, order_items.quantity
FROM orders
LEFT JOIN order_items
ON orders.order_id = order_items.order_id;

This keeps every order, even one with no matching line items - filling in NULL where nothing exists. Same join shape, opposite intent. Pick the wrong one and you'll either silently drop rows you needed, or silently include rows you didn't expect.

Pushing it to four tables

Once that clicked, joining across the whole schema wasn't a big leap:

SELECT customers.full_name, orders.order_id, products.product_name, order_items.quantity
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
INNER JOIN order_items ON orders.order_id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.product_id;

And aggregating across that same join to get total quantity sold per product:

SELECT products.product_name, SUM(order_items.quantity) AS total_quantity
FROM products
INNER JOIN order_items ON products.product_id = order_items.product_id
GROUP BY products.product_name;

What I'd tell someone doing this next

Don't skip the insert/update/delete step. It's tempting to seed your tables once and go straight to writing SELECT queries, but running real UPDATE/DELETE statements against your own constraints (and watching ON DELETE CASCADE actually fire) is what proves your schema holds up - not just that it compiles.

And when you hit your first join, write the INNER and LEFT versions of the same query side by side before moving on. Seeing the row count actually differ is worth more than any explanation of the difference.

Repo's on [e-mail]:emilioochieng/sql-database-projects.git if you want to see the full thing, including the aggregation queries and the rest of the filtering (BETWEEN, IN, LIKE) .