SELECT Statements and Query Basics

The SELECT part determines which columns appear in the result, while the ON condition defines which rows belong together.

Programming

Interactive lab

Try it: JOIN Step by Step

How a JOIN combines rows from two tables by testing the ON condition for every pair (a nested loop), how LEFT and RIGHT JOIN keep unmatched rows by padding with NULL, and how a junction table links a many-to-many relationship with two joins.

How it works

Take the first row of the outer (left) table.
Compare its key with the key of every row of the inner table using the ON condition; every TRUE comparison produces one combined row. NULL = anything is NULL, never a match.
INNER JOIN drops an outer row with no match; LEFT JOIN keeps it with the inner columns set to NULL. RIGHT JOIN is the mirrored LEFT JOIN; CROSS JOIN pairs every row with every row.
Repeat for every outer row. A second JOIN treats the rows produced so far as its outer table — this is how student → member → course crosses a junction table.

Default run (18 steps): SELECT artist.name, album.title FROM artist JOIN album ON artist.id = album.artist_id. A nested loop: for each outer row, test the ON condition against every inner row. … Result: 3 joined rows (15 ON comparisons).

Simplified: A toy relational engine in the browser, not SQLite itself: a small structured query model built from the controls (no SQL text is parsed or executed), typed INTEGER/TEXT/NULL values, and tables of at most a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). The join is shown as a plain nested loop; real SQLite may reorder tables or build a temporary index, but the rows produced are the same. Result order here is outer row, then inner row (SQL promises no order without ORDER BY).

Educational simulation

Loading the simulation…

From Separate Tables to One Result

A normalized database may store related information in separate tables. A JOIN reconstructs related information by matching rows from those tables. The SELECT part determines which columns appear in the result, while the ON condition defines which rows belong together.

Key point

A JOIN does not mean that every row from one table is paired with every row from the other. The ON condition provides the key-based relationship that determines the matching rows.

A Joined Result Before and After

What do you think happens?

Suppose an orders table contains order_id and customer_id, and a customers table contains customer_id and customer_name. If an order has customer_id 2 and the customer with customer_id 2 is Mira, what customer name should appear beside that order after the tables are joined?

The first customer name in the customers table
Mira
Every customer name
No customer name can be selected

Reveal answer

Answer:

Mira

The ON condition matches the order's customer_id with the customers table's customer_id. The matching customer row supplies the related customer name.

What columns and rows appear after related rows from two normalized tables are combined?

Before the JOIN, the order information and customer information are stored in separate tables. After the JOIN, the result can contain columns selected from both tables. The result is not necessarily a new permanent table; it is the data produced by the query.

Reading the ON Condition

Code walkthrough

SELECT orders.order_id, customers.customer_name
FROM orders
JOIN customers
  ON orders.customer_id = customers.customer_id;

1
The query requests order_id from orders and customer_name from customers.
2
The query starts with the orders table.
3
The JOIN adds the customers table as a related source.
4
The ON condition matches orders.customer_id with customers.customer_id.

SELECT chooses the columns to return.
FROM identifies the starting table.
JOIN identifies the related table.
ON defines the key comparison used to match rows.

Which customer row matches each order row when equal key values are compared?

Definition

The ON condition is the logical relationship between the tables. It typically matches a foreign key in one table to a primary key in another table.

In the example query, orders.customer_id is compared with customers.customer_id. When the values are equal, the rows are treated as related for the joined result. The key values are used for matching; the SELECT list controls which fields from those matching rows are displayed.

Reconstructing Normalized Data

Worked example

Predicting the Joined Rows

Use the following two source tables and the query to predict the result. The orders table has rows (501, 2) and (502, 1), where each pair is (order_id, customer_id). The customers table has rows (1, Ava) and (2, Mira), where each pair is (customer_id, customer_name). Query: SELECT orders.order_id, customers.customer_name FROM orders JOIN customers ON orders.customer_id = customers.customer_id;

1
Select the requested columns
The result asks for order_id from orders and customer_name from customers.
2
Match order 501
Order 501 has customer_id 2. The customers row with customer_id 2 has the name Mira.
3
Match order 502
Order 502 has customer_id 1. The customers row with customer_id 1 has the name Ava.
4
Assemble the result
Each matching pair contributes one result row containing the selected order identifier and customer name.

Result

The result has the columns order_id and customer_name, with rows (501, Mira) and (502, Ava).

How does a JOIN connect separate normalized tables to reconstruct related information?

Normalization keeps related information in separate tables, and the JOIN supplies a way to reconstruct the relationship when it is needed. The ON condition connects the tables through their key values, while SELECT determines the shape of the returned data.

Why Integer Keys Matter

Integer keys are more efficient for joins than string-based connections. Integer keys reduce the amount of data involved and reduce comparison time. This matters because joins depend on comparing key values between tables.

How do rows move from source tables through the ON condition into the SELECT result?

Mistakes in Joined Queries

Common mistake
Choosing columns without understanding which table supplies them
What it looks like
```
SELECT order_id, customer_name FROM orders JOIN customers ON orders.customer_id = customers.customer_id;
```
Why it fails
A learner may know the desired names but overlook that order_id belongs to orders and customer_name belongs to customers.
Do this instead
Track each selected column back to its source table. Writing the table name before the column, as in orders.order_id, makes the source explicit.
Common mistake
Treating JOIN as the relationship itself
What it looks like
```
SELECT orders.order_id, customers.customer_name FROM orders JOIN customers;
```
Why it fails
The tables have been named, but the key-based relationship has not been specified.
Do this instead
Use an ON condition that identifies the related key values, such as orders.customer_id = customers.customer_id.
Common mistake
Matching descriptive strings when integer keys are available
What it looks like
Connecting rows by a customer name instead of a customer identifier
Why it fails
String-based connections involve more data and more comparison time than integer keys.
Do this instead
Use the key values that define the table relationship, with integer keys being more efficient for joins than strings.
Common mistake
Expecting every source column to appear automatically
What it looks like
Assuming a JOIN returns all columns from both tables even when SELECT names only two columns
Why it fails
The SELECT list determines which columns appear in the query result.
Do this instead
Predict the result columns by reading the SELECT list, then predict the rows by applying the ON condition.

Practice the Matching Process

EASY

Consider these tables. orders contains (700, 3) and (701, 1), where each pair is (order_id, customer_id). customers contains (1, Noor), (2, Eli), and (3, Sam), where each pair is (customer_id, customer_name). For the query SELECT orders.order_id, customers.customer_name FROM orders JOIN customers ON orders.customer_id = customers.customer_id, list the result columns and result rows.

Hints

Read the SELECT list first to identify the result columns.
For each orders row, copy its customer_id to the customers table.
Keep only the customer name from the matching customers row.

Your attempt (not saved)

Worked example

Practice Check

Using the practice tables, determine the rows produced by the ON condition.

1
Match order 700
Its customer_id is 3, so it matches the customers row whose customer_id is 3. The customer name is Sam.
2
Match order 701
Its customer_id is 1, so it matches the customers row whose customer_id is 1. The customer name is Noor.
3
Read the selected fields
The query selects order_id and customer_name, so those are the result columns.

Result

The result columns are order_id and customer_name. The result rows are (700, Sam) and (701, Noor).

Key Takeaways

1
JOIN combines related rows from separate normalized tables.
2
ON defines the logical relationship, typically by matching a foreign key with a primary key.
3
SELECT determines which columns appear in the joined result.
4
To predict result rows, match equal key values and then combine the selected fields from those rows.
5
Integer keys are more efficient than string-based connections because they reduce data volume and comparison time.

At a glance

A JOIN reconstructs related information stored in multiple normalized tables.
The ON condition matches rows by comparing related key values.
The SELECT list determines the columns in the result.
Integer keys improve join efficiency compared with string-based connections.
You can predict a joined result by matching keys first and then reading the requested columns.

Related concepts

More concepts starting with S