How the join engine matches rows in SQL - Try It Yourself

How the Join Engine Matches Rows

📖 Scenario: You are working with two simple tables in a store database: customers and orders. You want to understand how the database combines rows from these tables when you ask for matching customer orders.

🎯 Goal: Build a SQL query step-by-step that shows how the join engine matches rows from customers and orders using a common column customer_id.

📋 What You'll Learn

Create a customers table with exact columns and data

Create an orders table with exact columns and data

Write a SQL query that joins customers and orders on customer_id

Select specific columns to show matched rows

💡 Why This Matters

🌍 Real World

Joining tables is a common task in databases to combine related information, like customers and their orders.

💼 Career

Understanding how joins work is essential for data analysts, backend developers, and anyone working with relational databases.

1

Create the customers table with data

Write SQL code to create a table called customers with columns customer_id (integer) and customer_name (text). Insert these exact rows: (1, 'Alice'), (2, 'Bob'), (3, 'Charlie').

SQL

-- Create the customers table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define columns and INSERT INTO to add rows.

2

Create the orders table with data

Write SQL code to create a table called orders with columns order_id (integer), customer_id (integer), and order_total (integer). Insert these exact rows: (101, 1, 250), (102, 2, 150), (103, 1, 300).

SQL

CREATE TABLE customers (
    customer_id INTEGER,
    customer_name TEXT
);

INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

-- Create the orders table and insert data
-- Your code here

Need a hint?

Define the orders table with three columns and insert the given rows.

3

Write a JOIN query to match customers with their orders

Write a SQL SELECT query that joins customers and orders on the customer_id column. Select customer_name and order_total from the joined tables.

SQL

CREATE TABLE customers (
    customer_id INTEGER,
    customer_name TEXT
);

INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

CREATE TABLE orders (
    order_id INTEGER,
    customer_id INTEGER,
    order_total INTEGER
);

INSERT INTO orders (order_id, customer_id, order_total) VALUES
(101, 1, 250),
(102, 2, 150),
(103, 1, 300);

-- Write a SELECT query joining customers and orders on customer_id
-- Your code here

Need a hint?

Use JOIN with ON to match rows where customer_id is the same.

4

Complete the query with an ORDER BY clause

Add an ORDER BY clause to the previous query to sort the results by customer_name in ascending order.

SQL

CREATE TABLE customers (
    customer_id INTEGER,
    customer_name TEXT
);

INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

CREATE TABLE orders (
    order_id INTEGER,
    customer_id INTEGER,
    order_total INTEGER
);

INSERT INTO orders (order_id, customer_id, order_total) VALUES
(101, 1, 250),
(102, 2, 150),
(103, 1, 300);

SELECT customers.customer_name, orders.order_total
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
-- Add ORDER BY to sort by customer_name
-- Your code here

Need a hint?

Use ORDER BY customers.customer_name ASC to sort results alphabetically by customer name.