Structured query language (SQL): WHERE

The idea

The supplied statement is only the single keyword WHERE, so this note teaches the role of that keyword within SQL as specified for this section and does not invent missing clauses. In a SELECT statement, WHERE filters the rows returned so that only records meeting a stated condition are included; rows failing the condition are excluded. The condition follows WHERE and typically compares a field with a value, for example selecting records where a field equals a given value. WHERE differs from simply listing fields after SELECT, which chooses columns rather than rows. Learners should be able to identify WHERE in a query, state that it applies a condition to rows, and predict which records a given condition would return from a small table.

What earns the marks

• Identify WHERE as the SQL keyword that introduces a condition filtering rows.
• State that only rows satisfying the condition after WHERE are returned.
• Distinguish filtering rows with WHERE from choosing columns in the SELECT list.
• Apply a simple condition to a small table and list the records that would be returned.

Common mistakes and how to put them right

• Confusing WHERE with SELECT.

  Put it right: SELECT chooses which columns appear, WHERE chooses which rows are returned.

• Believing WHERE sorts or orders results.

  Put it right: WHERE filters rows and does not order them.

• Assuming WHERE changes the stored data.

  Put it right: A SELECT with WHERE retrieves records and does not alter the table.

Exam tips

• Underline the condition after WHERE before deciding which rows qualify.
• Test each row of a small table against the condition in turn.
• Remember WHERE affects rows, not the columns listed after SELECT.