Working with Multiple Tables: Joins and Relationships

SELECT retrieves specific columns and rows from a database table using WHERE to filter and ORDER BY to sort results.

Programming

Interactive lab

Try it: SELECT, WHERE, ORDER BY, LIMIT

How a SELECT query tests every row against the WHERE condition (keeping only rows where it is TRUE, not FALSE or NULL), then picks columns, removes duplicates with DISTINCT, sorts with ORDER BY and cuts with LIMIT.

How it works

FROM: start with every row of the table.
WHERE: evaluate the condition on each row. Comparisons with NULL give NULL (unknown); NOT, AND and OR follow three-valued logic, with AND binding tighter than OR. Only rows where the whole condition is TRUE are kept.
SELECT picks the listed columns; DISTINCT then drops result rows that repeat an earlier one.
ORDER BY sorts by each key in turn, ASC or DESC; SQLite puts NULL first when ascending and last when descending.
LIMIT n keeps only the first n rows of the sorted result.

Default run (13 steps): SELECT title, plays FROM track WHERE plays >= 95 AND rating >= 4 ORDER BY plays DESC LIMIT 2. FROM: start with all 8 rows of track. … Result: 2 rows.

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). Ties in ORDER BY are broken by id here (by the selected columns with DISTINCT), and DISTINCT without ORDER BY keeps first-occurrence order; SQLite itself promises no order for ties. LIKE is case-insensitive for ASCII letters, as in SQLite.

Educational simulation

Loading the simulation…

The Result-Set Journey

When working with more than one table, the useful result is not simply the stored data. It is the set of rows and columns your query retrieves for your program to process. The supplied material establishes the essential foundation for that work: SELECT chooses the data, WHERE narrows the rows, ORDER BY arranges them, and a cursor delivers the results to Python one row at a time.

How can data selected from database tables become one result set for a Python program?

Selecting and Shaping Results

A SELECT statement has three main parts: the columns to retrieve, the table to retrieve them from, and optionally the rows to include. The basic structure is SELECT columns FROM table. WHERE adds a condition that filters the rows. ORDER BY sorts the rows that remain, using ascending or descending order.

sql

SELECT title, plays
FROM Track
WHERE plays > 100
ORDER BY plays DESC

SELECT title, plays
FROM Track
WHERE plays > 100
ORDER BY plays DESC

In this generated example, SELECT chooses title and plays. FROM identifies Track as the table. WHERE keeps only rows whose plays value is greater than 100. ORDER BY then sorts the remaining rows by plays in descending order. The order of the selected columns matters later because each returned tuple follows that same order.

What happens as SELECT chooses columns, WHERE filters rows, and ORDER BY sorts the remaining results?

Worked example

Reading a SELECT Statement

Interpret SELECT title, plays FROM Track WHERE plays > 100 ORDER BY plays DESC.

1
Choose columns
The query requests title and plays rather than every column.
2
Choose the table
The rows come from Track.
3
Filter rows
Only rows with plays greater than 100 are included.
4
Sort rows
The matching rows are ordered by plays from highest to lowest because DESC requests descending order.

Result

The result set contains only the selected columns from matching Track rows, arranged by plays in descending order.

Cursors and On-Demand Retrieval

After a SELECT statement is executed in Python, the database provides a cursor. A cursor is a pointer to the result set. It does not immediately load every matching row into memory. Instead, it moves through the result set and fetches rows on demand.

On-demand retrieval is important when a query may return thousands or millions of rows. The program can begin processing rows immediately, while memory usage stays low because it does not need to hold the entire result set at once. The source material describes this as the default and almost always appropriate approach.

How does data move from the database to Python one row at a time instead of being loaded all at once?

How does the Python loop request and receive each database row from the cursor?

Code walkthrough

for row in cursor:
    print(row)

1
The cursor represents the query's result set.
2
The loop requests rows from the cursor one at a time.
3
Each row is available inside the loop for processing.

The for loop advances through the cursor.
Each iteration assigns the next returned row to row.
The row can then be processed.

Tuple Positions and Values

Each row fetched from the cursor arrives as a Python tuple. The tuple is an immutable sequence, and its values appear from left to right in the same order as the columns in SELECT. If the query selects title and plays, the first tuple element is title and the second is plays.

You can retrieve a value by index, starting at index 0, or unpack the tuple into separate variables. Unpacking is useful when the selected column order is clear. Index access is useful when you need a particular position directly.

How do selected column positions map to the values in each returned Python tuple?

python

for row in cursor:
    title, plays = row
    print(title)
    print(plays)

for row in cursor:
    title = row[0]
    plays = row[1]

for row in cursor:
    title, plays = row
    print(title)
    print(plays)

for row in cursor:
    title = row[0]
    plays = row[1]

Practical Retrieval Choices

Ask the database for only the columns you need when you do not require every column. The asterisk is convenient when all columns are needed, as in SELECT * FROM Track WHERE title = 'My Way'. When only certain values are needed, list the column names separated by commas.

Use WHERE to filter in the database and ORDER BY to sort there rather than retrieving a larger unsorted result and doing that work in Python. The source material notes that database sorting can be more efficient, especially for large result sets.

Common mistake
Assuming a SELECT statement loads every matching row into Python immediately.
What it looks like
Treating the cursor as if it were already a complete in-memory list.
Why it fails
A cursor fetches rows on demand and acts as a pointer to the result set.
Do this instead
Iterate through the cursor and process each row as it arrives.
Common mistake
Reading tuple values in an order different from the SELECT list.
What it looks like
Using row[0] as plays after selecting plays, title.
Why it fails
Tuple elements follow the selected-column order, beginning at index 0.
Do this instead
Check the SELECT list, then use matching indexes or tuple unpacking.
Common mistake
Sorting a large result in Python when the database can order it.
What it looks like
Leaving ORDER BY out and planning to sort every returned row in application code.
Why it fails
The source recommends using ORDER BY in the database, which can sort more efficiently for large results.
Do this instead
Add ORDER BY to the SELECT statement when the database should determine result order.
Common mistake
Using fetchall() automatically for every query.
What it looks like
Fetching all rows even when a result set may be very large.
Why it fails
Loading all rows at once can increase memory use.
Do this instead
Use normal cursor iteration unless the result is known to be small and all rows are needed simultaneously.

Choice	What it does	When it fits
Cursor iteration	Retrieves and processes rows on demand	Large or unknown result sets
fetchall()	Retrieves all rows at once	A known small result set when all rows are needed in memory
ORDER BY	Sorts results in the database	When returned rows need a defined order

Retrieval and sorting choices described in the source material.

Check Your Understanding

What do you think happens?

A query selects title, plays. If one returned row is assigned to row, which value does row[1] represent?

The title value
The plays value
The complete result set

Reveal answer

Answer:

The plays value

Tuple indexes begin at 0, and tuple elements follow the order of the SELECT statement. title is at index 0 and plays is at index 1.

EASY

Suppose a query retrieves title and plays from Track, keeps only rows where plays is greater than 50, and sorts by title in ascending order. Identify the selected columns, the filtering condition, the sorting column, and the tuple indexes for title and plays.

Hints

Read the SELECT list first.
The condition after WHERE determines which rows remain.
The column after ORDER BY determines the sorting field.
The first selected column uses index 0.

Your attempt (not saved)

1
Choose only the columns the program needs.
2
Use FROM to identify the table supplying the rows.
3
Add WHERE when only rows matching a condition should be returned.
4
Add ORDER BY when the result needs a defined ascending or descending order.
5
Execute the SELECT statement and iterate through its cursor with a Python for loop.
6
Treat each fetched row as a tuple whose positions match the SELECT column order.

Key Takeaways

SELECT chooses the columns and table for a result, while WHERE filters rows and ORDER BY sorts them.
A database cursor points to a result set and retrieves rows on demand rather than loading every row immediately.
A Python for loop can process the cursor one row at a time.
Each returned row is a Python tuple whose values follow the SELECT column order.
Use tuple unpacking or zero-based index access, and reserve fetchall() for known small results when all rows are needed together.

More concepts starting with W