How WHERE Filters Records

The WHERE clause applies a condition to each record in the table. Records for which the condition is true are included in the result; records for which it is false are excluded.

WHERE affects rows, while SELECT affects columns. A query may select two fields from records that meet one condition on a different field. The condition field does not have to be displayed.

For example, SELECT StudentName FROM Students WHERE AverageMark >= 70; displays names only, even though AverageMark is used to decide which rows qualify.

Numeric Conditions

Numeric values are written without quotation marks. Common comparison operators include =, <, >, <=, >= and <> when shown or required by the question.

The condition is evaluated for every product. A price of exactly 25 is excluded because the operator is >, not >=. Boundary words such as at least and more than must be translated carefully.

Integer and real fields can both be compared numerically.

SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice > 25;

Text And Character Conditions

Text or character literal values are enclosed in quotation marks according to the question style. The field name YearGroup is not quoted.

An exact equality condition returns only records whose stored value matches the literal. Spelling and representation must be consistent with the data shown.

If YearGroup were declared integer, the value would be written as 10 without quotation marks. The data type controls how the literal is represented.

SELECT StudentID, StudentName
FROM Students
WHERE YearGroup = "10";

Boolean Conditions

A Boolean field is tested using TRUE or FALSE. Do not enclose Boolean values in quotation marks when the question represents them as logical values.

The query returns only paid bookings and displays the booking reference and customer name.

SELECT BookingRef, CustomerName
FROM Bookings
WHERE PaymentReceived = TRUE;

Date And Time Conditions

A date/time condition compares a date or time field with a date/time literal. The exact delimiter and format can vary, so follow the notation provided in the examination question.

The important concepts are that the field uses date/time type and the comparison selects records before, after or equal to the stated date or time.

Do not invent a universal date literal format if the question has not specified one. Explain the condition in words when syntax details are provided by the paper.

Tracing A WHERE Query

Boundary Analysis

For WHERE UnitPrice >= 25, P02 and P04 match. P04 is included because the boundary value 25 is allowed.

For WHERE UnitPrice > 25, only P02 matches. For WHERE InStock = FALSE, only P03 matches.

When identifying output, evaluate each row in order and then display only the fields listed after SELECT.

Worked Examples

Examination Guidance

• Translate wording such as at least, more than and no more than precisely.
• Do not quote numeric or Boolean literals.
• Quote text or character literals according to the question style.
• Remember a condition field need not be selected.
• Trace every record independently.

Knowledge Check

1. What does WHERE do?

2. Which records are returned?

3. Does a WHERE field have to appear after SELECT?

4. How is at least 10 written?

5. How is a Boolean true value normally written?