1.3.2 - SQL selection, joins and nested queries

Selecting columns and filtering records

A SQL query describes the result required from the database; SELECT retrieves data without changing the stored records.

Use this complete dataset throughout the worked examples. MemberID, ItemID and LoanID are their respective primary keys. Loan.MemberID and Loan.ItemID reference their parent tables. All fields shown are non-null; Fee is a non-negative integer charge in pounds, Returned is 0 for active and 1 for returned. Names and titles are text. Do not assume displayed row order is a guaranteed query order.

The schema notation is Member(MemberID TEXT, Name TEXT, City TEXT), Item(ItemID TEXT, Title TEXT, Category TEXT, Fee INTEGER), Loan(LoanID TEXT, MemberID TEXT, ItemID TEXT, Returned INTEGER), with the keys and constraints stated above. Table headings are their field names.

SELECT Title, Fee
FROM Item
WHERE Fee < 6;

FROM identifies the table, WHERE keeps records satisfying the condition, and the list after SELECT identifies the output columns. This is a reasoning model for the result; the DBMS may optimise the physical execution. I2 and I5 pass the test, giving (Tripod,3) and (Cable,3). I3 has Fee exactly 6 and is excluded by < 6; <= 6 would include it. Numeric values are not written with a currency sign because Fee is an integer field. Text literals use single quotes, for example City = 'Leeds'.

SELECT * FROM Item; returns all Item columns and all five rows. In this position, * means every column; it is not a text-pattern wildcard. Without WHERE, no rows are filtered out. A query may legitimately return zero rows. No ORDER BY means there is no guaranteed ordering, so these lessons compare result sets; if an exercise adds unfamiliar SQL, it must explain it.

Combining conditions with AND and OR

AND requires both conditions to be true, while OR requires at least one to be true.

SELECT Title
FROM Item
WHERE Category = 'Audio' AND Fee < 10;

The category test keeps I3, I4 and I5, but the fee test removes I4. The result is Microphone and Cable. Replacing AND with OR includes all five items: the Image items pass Fee < 10, and Recorder passes the Audio condition despite its fee being 10. Changing the connector therefore changes the question asked, not just its wording.

Parentheses express which conditions belong together:

SELECT Title
FROM Item
WHERE (Category = 'Audio' OR Category = 'Image') AND Fee < 6;

This means “in either category, and cheap enough”, giving Tripod and Cable. SQL evaluates AND before OR if parentheses are absent. Writing Category = 'Audio' OR Category = 'Image' AND Fee < 6 instead gives every Audio item plus the cheap Image item: Microphone, Recorder, Cable and Tripod. Explicit grouping prevents this mistake. These comparisons concern the supplied non-null values; null uses SQL's unknown truth value and needs separately introduced handling.

Matching text with LIKE and percent

LIKE compares text with a pattern, and % in that pattern matches zero or more characters.

SELECT ItemID, Title
FROM Item
WHERE Title LIKE 'Ca%';

This returns I1 Camera and I5 Cable: each title begins Ca. LIKE '%phone%' would return Microphone because the sequence phone can occur anywhere. LIKE '%a' asks for text ending in a, while LIKE 'Ca' requires a complete match to Ca under the active collation rather than adding an implied wildcard. A title exactly Ca would also match 'Ca%', because % may match zero characters.