Working with Multiple Tables: Joins and Relationships

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.

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.

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.

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.

for row in cursor: print(row)

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.

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.

• Assuming a SELECT statement loads every matching row into Python immediately.

  A cursor fetches rows on demand and acts as a pointer to the result set.

  Fix: Iterate through the cursor and process each row as it arrives.

• Reading tuple values in an order different from the SELECT list.

  Tuple elements follow the selected-column order, beginning at index 0.

  Fix: Check the SELECT list, then use matching indexes or tuple unpacking.

• Sorting a large result in Python when the database can order it.

  The source recommends using ORDER BY in the database, which can sort more efficiently for large results.

  Fix: Add ORDER BY to the SELECT statement when the database should determine result order.

• Using fetchall() automatically for every query.

  Loading all rows at once can increase memory use.

  Fix: Use normal cursor iteration unless the result is known to be small and all rows are needed simultaneously.

Check Your Understanding

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.