📎 This article includes 2 downloadable practice files ↓

📘 SQL Beginner Course · Lesson 6 of 12

The orders table stores customer_id 4, not “Rao Distributors”. Storing each fact once and linking by id keeps data consistent; JOIN brings it back together when you query. Think of it as VLOOKUP for whole tables at once.

Keys

• Primary key: the unique id of each row (customers.customer_id).
• Foreign key: a column pointing to another table’s primary key (orders.customer_id).

INNER JOIN

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

o and c are aliases, short names for the tables. ON says how rows match. JOIN (same as INNER JOIN) keeps only rows that match in both tables.

Calculate across tables

SELECT o.order_id, p.name, o.qty, o.qty * p.price AS value
FROM orders o
JOIN products p ON p.product_id = o.product_id;

Three tables and a summary

SELECT c.region, SUM(o.qty * p.price) AS sales
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN products  p ON p.product_id  = o.product_id
WHERE o.status = 'Delivered'
GROUP BY c.region
ORDER BY sales DESC;

That’s a full sales-by-region report: joins, filter, group, sort.

LEFT JOIN: keep everything from the left

SELECT p.name
FROM products p
LEFT JOIN orders o ON o.product_id = p.product_id
WHERE o.order_id IS NULL;

LEFT JOIN keeps every product, with NULLs where no order matches. Filtering on IS NULL finds products never ordered. In shop.db that’s the UPS.

SELECT c.name, COUNT(o.order_id) AS orders
FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id;

Every customer appears, including any with zero orders. Note COUNT(o.order_id), not COUNT(*), which would count 1 for customers with no orders.

Other joins

RIGHT JOIN is LEFT JOIN with the tables swapped; FULL OUTER JOIN keeps unmatched rows from both sides (supported in recent SQLite, PostgreSQL, SQL Server). Avoid comma joins without ON; they produce every combination of rows.

Where beginners go wrong

Practice

Download shop.db and this lesson’s exercise file below. Open the database in the free DB Browser for SQLite (or sqlite3 shop.db), paste the exercises into the Execute SQL tab and write a query for each task. The expected result is printed under every task; answers are at the bottom of the file.