SQL INNER JOIN — Combine Users and Orders

Table Hierarchy & Schema

Database → tables

CREATE TABLE (reference)json

1CREATE TABLE users (
2  user_id   INTEGER PRIMARY KEY,
3  email     VARCHAR(255) NOT NULL UNIQUE,
4  is_active BOOLEAN NOT NULL DEFAULT TRUE
5);
6
7CREATE TABLE orders (
8  order_id     INTEGER PRIMARY KEY,
9  customer_id  INTEGER NOT NULL,
10  status       VARCHAR(20) NOT NULL DEFAULT 'pending',
11  total_amount DECIMAL(10, 2) NOT NULL,
12  FOREIGN KEY (customer_id) REFERENCES users (user_id)
13);

Entity-Relationship Diagram

Users and orders (1:N)

Join key

orders.customer_id (FK) references users.user_id (PK).

PK/FK Join Flow

How user 1 links to two orders

Sample Data

usersjson

1| user_id | email               | is_active |
2|---------|---------------------|-----------|
3| 1       | [email protected]   | true      |
4| 2       | [email protected]     | false     |
5| 3       | [email protected]   | true      |

ordersjson

1| order_id | customer_id | status   | total_amount |
2|----------|-------------|----------|--------------|
3| 1001     | 1           | paid     | 49.99        |
4| 1002     | 1           | shipped  | 12.50        |
5| 1003     | 3           | pending  | 8.00         |

[email protected] has no orders — an INNER JOIN with orders will not return Bob.

Basic INNER JOIN

Email and order amount for each orderjson

1SELECT u.email, o.order_id, o.total_amount
2FROM users u
3INNER JOIN orders o ON u.user_id = o.customer_id;

Expected outputjson

1| email | order_id | total_amount |
2|---|---|---|
3| [email protected] | 1001 | 49.99 |
4| [email protected] | 1002 | 12.50 |
5| [email protected] | 1003 | 8.00 |

JOIN with WHERE

Large orders with customer emailjson

1SELECT u.email, o.total_amount
2FROM users u
3INNER JOIN orders o ON u.user_id = o.customer_id
4WHERE o.total_amount > 20;

INNER vs LEFT (Preview)

Join type	Keeps	Use when
INNER JOIN	Only matching rows in both tables	You need related data only
LEFT JOIN	All left rows + matches or NULL	Include users with zero orders

Constraints Reminder

Constraint	Role in joins
PRIMARY KEY (user_id)	Unique user row to match against
FOREIGN KEY (customer_id)	Ensures order points to valid user
NOT NULL on customer_id	Every order must reference a user

Common Mistakes

Missing ON clause → Cartesian product (every user × every order).
Joining on wrong columns (e.g. order_id = user_id).
Duplicate column names without aliases in SELECT.

Practice Prompts

List order_id and email for all shipped orders.
Which user has the most orders in our sample? (Hint: GROUP BY next lesson.)
Draw the ER diagram for a products table linked to orders.

Try These Tools

SQL Query Explainer

Explain JOIN logic step by step

Open tool

SQL Data Generator

Generate joinable sample tables

Open tool

Continue Learning

Relational Databases & Tables

PK/FK fundamentals

SQL CASE Expression

Label joined rows conditionally

Frequently Asked Questions

What does INNER JOIN return?

Only rows where the JOIN condition matches in both tables. Users with no orders are excluded; orders without a matching user are excluded.

What goes in the ON clause?

The link between tables, usually primary key = foreign key: ON users.user_id = orders.customer_id.

What is a Cartesian product?

Every row from table A paired with every row from table B — happens when JOIN is missing or ON is wrong. Always specify ON.

Why use table aliases?

Shorter queries and required when the same table appears twice (self-join). Example: FROM users u JOIN orders o.

INNER JOIN vs LEFT JOIN?

INNER keeps only matches. LEFT keeps all rows from the left table and NULLs for non-matching right rows (covered in a future lesson).