Joining Tables (JOIN)

In real projects, data is split across tables. So we use JOIN to combine them.

For example, suppose we have users and orders.

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    item TEXT
);

The user_id is a value that connects to the id in the users table. A column that points to another table’s primary key is called a Foreign Key. We’ll dive deeper into this in the next course.

Now let’s join the two tables.

SELECT users.name, orders.item
FROM users
JOIN orders ON users.id = orders.user_id;

Read it like this:

Join users and orders,
only where users.id equals orders.user_id

LEFT JOIN

SELECT users.name, orders.item
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

LEFT JOIN keeps all users, and fills missing orders with NULL (empty values).

NULL means “no value.” It’s different from 0 or an empty string (""); it represents a state where data is literally missing.

Now let us aggregate data.