Query, summarise, join and change data in a relational database with SQL, practised in your browser.

SQL Fundamentals

## Why it matters

So far every query has used one table. Real databases split data across many tables on purpose: customers in one, orders in another, products in a third. Keys are what hold that design together. Understanding them lets you read an unfamiliar schema, write correct joins, and avoid the duplicated, contradictory data that spreadsheets are famous for.

## Why split data into tables at all?

Imagine storing everything in one wide orders table:

| order_id | customer_name | customer_email | customer_city | total |
|---|---|---|---|---|
| 1 | Ana Silva | ana@example.com | Porto | 25.00 |
| 2 | Ana Silva | ana@example.com | Porto | 40.00 |
| 3 | Ana Silva | ana@exmple.com | Lisbon | 15.00 |

Ana's details are repeated in every order. When she moves, someone must update every row; miss one and the data contradicts itself (row 3 already has a typo and an old city). This is called an **update anomaly**.

The relational fix is to store each fact **once**:

`customers`

| id | name | email | city |
|---|---|---|---|
| 7 | Ana Silva | ana@example.com | Porto |

`orders`

| id | customer_id | total |
|---|---|---|
| 1 | 7 | 25.00 |
| 2 | 7 | 40.00 |
| 3 | 7 | 15.00 |

Each order points at Ana through `customer_id`. Change her city once and every order sees it. Organising tables this way is called **normalisation**.

## Primary keys

A **primary key** (PK) uniquely identifies each row of a table. Rules:

- **Unique**: no two rows share the value.
- **Not null**: every row has one.
- **Stable**: it should not change, because other tables refer to it.

Most tables use a **surrogate key**: a meaningless number generated by the database, such as `id INTEGER PRIMARY KEY`. The alternative is a **natural key**, a real-world value like an email or a product code. Natural keys are tempting but risky: emails change, product codes get reissued, and some "unique" values (names, phone numbers) are not unique at all. A common pattern is a surrogate `id` as the primary key plus a `UNIQUE` constraint on the natural value, so duplicates are still blocked.

A **composite key** uses two or more columns together. In an `enrollments` table, the pair `(student_id, course_id)` can be the key: a student enrolls in a course once, but appears many times overall.

## Foreign keys

A **foreign key** (FK) is a column whose values must match a primary key in another table. `orders.customer_id` is a foreign key referencing `customers.id`.

```sql
CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  total       REAL NOT NULL
);
```

With the constraint switched on, the database refuses an order for customer 999 if no such customer exists. This is **referential integrity**: references always point at something real. It also controls deletes: by default you cannot delete a customer who still has orders, and you can choose `ON DELETE CASCADE` (delete their orders too) or `ON DELETE SET NULL` (keep the orders, clear the link). Choose carefully; cascading deletes can remove far more than you expect.

One SQLite detail: foreign key enforcement is off by default for backward compatibility, and is turned on per connection with `PRAGMA foreign_keys = ON;`. PostgreSQL, SQL Server and MySQL (InnoDB) enforce foreign keys automatically.

## The three kinds of relationship

**One-to-many** is the most common. One customer has many orders; each order belongs to one customer. The foreign key goes on the "many" side (`orders.customer_id`).

**One-to-one** is rarer. One user has one profile. The foreign key column on one side is also `UNIQUE`, so each user can appear only once. It is used to split rarely used or sensitive columns into their own table.

**Many-to-many**: a student takes many courses and a course has many students. You cannot put a single foreign key on either side. Instead you add a **junction table** (also called a bridge or linking table):

`enrollments`

| student_id | course_id | enrolled_on |
|---|---|---|
| 1 | 10 | 2024-09-01 |
| 1 | 11 | 2024-09-01 |
| 2 | 10 | 2024-09-03 |

Each row links one student to one course, and the pair is the primary key. The junction table can also hold facts about the relationship itself, such as the enrolment date or a grade.

## Reading a schema

When you meet a new database, find the keys first:

1. For each table, find the primary key. That tells you what one row represents ("one row per order").
2. Find columns ending in `_id`. They are usually foreign keys, and tell you how the tables connect.
3. Sketch the tables as boxes and draw a line for each foreign key. This is an **entity-relationship diagram** (ERD). Tools such as dbdiagram.io or your database client can draw one for you.

That sketch tells you which tables to join, and on which columns, before you write a line of SQL.

## Common mistakes

- **Using names as keys.** Two customers can both be called Ana Silva.
- **Changing primary key values.** Every foreign key pointing at them breaks or must be updated too.
- **Storing lists in one column**, such as `course_ids = '10,11,12'`. It cannot be joined or constrained. Use a junction table.
- **Skipping foreign key constraints.** Without them, orphaned rows (orders for deleted customers) creep in.
- **Assuming SQLite enforces foreign keys.** Turn them on with the pragma.

## Quick recap

- Store each fact once and link tables with keys.
- A primary key is unique, not null and stable; surrogate keys are the safe default.
- A foreign key references another table's primary key and enforces referential integrity.
- One-to-many puts the FK on the many side; many-to-many needs a junction table.
- Read a schema by finding the keys first.

## Try it

This lesson has no code exercise. Open the Do tab and take the quiz, then use these ideas in the next lesson's join.


Learn why data is split across tables, and how primary keys, foreign keys and junction tables connect them.

Primary and foreign keys

Primary & Foreign Keys

Database Keys Made Easy - Primary, Foreign, Candidate, Surrogate, & Many More

## Why it matters

Splitting data into tables keeps it correct, but questions usually span tables: "show each order with the customer's name", "list every enrolment with the course title". A **join** puts related rows back together for the length of one query. `INNER JOIN` is the one you will use most.

## The idea

Take these two tables:

`customers`

| id | name | city |
|---|---|---|
| 1 | Ana | Porto |
| 2 | Ben | Leeds |
| 3 | Chen | Austin |

`orders`

| id | customer_id | order_date | total |
|---|---|---|---|
| 101 | 1 | 2024-05-02 | 25.00 |
| 102 | 1 | 2024-05-09 | 80.00 |
| 103 | 2 | 2024-05-10 | 42.50 |

An inner join pairs each order with the customer whose `id` equals the order's `customer_id`:

```sql
SELECT orders.id, customers.name, orders.total
FROM orders
INNER JOIN customers ON customers.id = orders.customer_id;
```

| id | name | total |
|---|---|---|
| 101 | Ana | 25.00 |
| 102 | Ana | 80.00 |
| 103 | Ben | 42.50 |

Chen has no orders, so Chen does not appear. That is the defining rule: an **inner join keeps only rows that have a match on both sides**. (Finding the unmatched rows is the next lesson.)

## The ON clause

`ON` says how rows match. Almost always it is "foreign key equals primary key": `customers.id = orders.customer_id`. Get it wrong and the result is quietly wrong. For example, `ON customers.id = orders.id` compares a customer id with an order id and pairs unrelated rows.

If you leave out the condition entirely (for example with a comma between tables and no `WHERE`), every customer is paired with every order: 3 × 3 = 9 rows. That is a **cross join** or Cartesian product, and it is a classic accidental bug that can also make queries very slow on large tables.

`JOIN` on its own means `INNER JOIN`. Many people write `INNER JOIN` anyway because it is explicit.

## Table aliases and qualified columns

Both tables have an `id` column, so you must say which one you mean: `orders.id` or `customers.id`. A column used without a table name when it exists in both tables gives an "ambiguous column name" error.

Writing full table names gets long, so give each table a short **alias**:

```sql
SELECT o.id AS order_id, c.name, o.total
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id;
```

The `AS` before a table alias is optional (`FROM orders o` works too). Aliasing output columns, as with `order_id`, avoids two columns both called `id`.

## Filtering, sorting and grouping joined rows

After the join, the combined rows behave like one table. Everything you have learned still applies:

```sql
-- Orders over 40, biggest first
SELECT o.id AS order_id, c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.total > 40
ORDER BY o.total DESC;
```

```sql
-- Revenue per customer
SELECT c.name, COUNT(*) AS orders, SUM(o.total) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
GROUP BY c.id, c.name;
```

Grouping by `c.id` as well as `c.name` keeps two different customers who share a name apart.

## Joining more than two tables

Chain joins one at a time. With an `order_items` table (order_id, product_id, quantity) and a `products` table (id, name, price):

```sql
SELECT o.id AS order_id, p.name AS product, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id;
```

Each `JOIN` adds one table and needs its own `ON`. Follow the foreign keys you found when reading the schema.

## Watch the row count

A join can **multiply** rows. Joining customers to orders gives one row per order, not per customer, so Ana appears twice. If you then `SUM` a customer column (say, a credit limit), Ana's limit is counted twice. Before aggregating joined data, ask: "what does one row represent now?" A quick `SELECT COUNT(*)` before and after a join is a cheap sanity check.

## Common mistakes

- **Wrong `ON` columns**, such as joining `id` to `id`.
- **Missing `ON`**, which produces a Cartesian product.
- **Unqualified column names** that exist in both tables.
- **Forgetting that inner joins drop unmatched rows.** If customers "disappear", you probably need a `LEFT JOIN`.
- **Double counting** after a join multiplies rows.

## Quick recap

- `INNER JOIN ... ON` pairs rows whose key values match and drops rows with no match.
- The `ON` condition is usually foreign key = primary key.
- Use table aliases and qualify columns that appear in both tables.
- `WHERE`, `GROUP BY` and `ORDER BY` work on the joined rows.
- Check what one row represents before aggregating.

## Try it

Open the Do tab and join a shop's orders to its customers to list the larger orders.


Combine related rows from two tables with INNER JOIN and an ON condition, and filter the joined result.

INNER JOIN

How to Join Tables in SQL | Clearly Explained | Beginners Start Here!

SQL INNER JOIN - SQL Tutorial #22

## Why it matters

Some of the most valuable questions are about what is **missing**: customers who never ordered, products that never sold, students not enrolled in anything, servers with no recent backup. An inner join cannot answer them, because it throws away unmatched rows. A `LEFT JOIN` keeps them, and a simple `IS NULL` test then finds the gaps.

## LEFT JOIN keeps every row from the left table

Using the same tables as the previous lesson:

`customers`: Ana (1), Ben (2), Chen (3). `orders`: 101 and 102 for Ana, 103 for Ben.

```sql
SELECT c.name, o.id AS order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
```

| name | order_id | total |
|---|---|---|
| Ana | 101 | 25.00 |
| Ana | 102 | 80.00 |
| Ben | 103 | 42.50 |
| Chen | NULL | NULL |

Every customer appears. Where there is a match, you get the order columns. Where there is none, the order columns are filled with `NULL`. "Left" refers to the table written first (before `LEFT JOIN`); that is the table whose rows are all kept.

## Finding rows with no match

Because unmatched rows have `NULL` in the right table's columns, filter for that:

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

| name | city |
|---|---|
| Chen | Austin |

This pattern is sometimes called an **anti-join**. Test a column that can never be `NULL` in a real match, such as the right table's primary key (`o.id`). If you tested a nullable column, such as an optional `o.discount_code`, real orders without a code would be counted as "no order".

An equivalent form uses `NOT EXISTS`, which some people find easier to read:

```sql
SELECT c.name, c.city
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
```

Avoid `WHERE c.id NOT IN (SELECT customer_id FROM orders)` when `customer_id` can be `NULL`: a single `NULL` in the list makes `NOT IN` return no rows at all, the trap from the NULLs lesson.

## Counting including zeros

`LEFT JOIN` with `GROUP BY` gives totals that include customers with nothing:

```sql
SELECT c.name,
       COUNT(o.id) AS order_count,
       COALESCE(SUM(o.total), 0) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;
```

Chen gets `0` orders and `0` revenue. Two details matter:

- Use `COUNT(o.id)`, not `COUNT(*)`. `COUNT(*)` counts Chen's single row of `NULL`s as 1.
- `SUM` of only `NULL`s is `NULL`, so wrap it in `COALESCE(..., 0)`.

## WHERE vs ON in a LEFT JOIN

Where you put a filter on the right table changes the result:

```sql
-- Filter in WHERE: customers without a 2024 order vanish
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.order_date >= '2024-01-01';
```

The `WHERE` runs after the join, and Chen's `NULL` date is not `>= '2024-01-01'`, so Chen is dropped. The query has quietly become an inner join.

```sql
-- Filter in ON: every customer kept, only 2024 orders attached
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.order_date >= '2024-01-01';
```

Rule of thumb: conditions on the **right** table go in `ON` when you want to keep all left rows. Conditions on the **left** table go in `WHERE`.

## Other outer joins

- `RIGHT JOIN` keeps every row from the right table. It is a `LEFT JOIN` with the tables swapped, so most people just reorder and use `LEFT JOIN`.
- `FULL OUTER JOIN` keeps unmatched rows from both sides. It is useful when reconciling two lists, such as comparing an export with a database table.

SQLite added `RIGHT` and `FULL OUTER JOIN` in version 3.39 (2022); older SQLite versions and MySQL do not have `FULL OUTER JOIN`.

## Worked example: products that never sold

```sql
SELECT p.id, p.name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.product_id IS NULL;
```

A merchandising team might discount these, and a database administrator might check whether they are test data that should be cleaned up.

## Common mistakes

- **Testing a nullable column for `IS NULL`** in the anti-join. Test the right table's key.
- **Filtering the right table in `WHERE`**, which turns the left join back into an inner join.
- **`COUNT(*)` with a left join**, which counts unmatched rows as 1.
- **`NOT IN` with a list that may contain `NULL`.**
- **Putting the tables in the wrong order.** The table you want to keep in full comes first.

## Quick recap

- `LEFT JOIN` keeps every left row; unmatched right columns are `NULL`.
- `LEFT JOIN ... WHERE right.key IS NULL` finds rows with no match.
- Count with `COUNT(right.key)` and wrap sums in `COALESCE` to include zeros.
- Filters on the right table belong in `ON` if you want to keep all left rows.

## Try it

Open the Do tab and find the customers who have never placed an order.


Keep every row from one table with LEFT JOIN, and find rows with no match using IS NULL.

LEFT JOIN and finding gaps

SQL LEFT JOIN - SQL Tutorial #23

SQL Inner Join vs Outer Join: Explained

Understand primary and foreign keys, combine related tables with INNER JOIN, and find rows with no match using LEFT JOIN.