Back to course home page

SQL WHERE Clause
Quick Reference

A SQL where predicate is a boolean expression formed from the following components:

Constants:

these can be numeric, such as 137, or character strings enclosed in apostrophes, as for example 'Smith'.

Table column names:

if the name occurs in only one table involved in the query, it can be written as-is, for example mgr_name. If the name occurs in more than one table, it must be qualified with the name of the table to which it belongs, for example works.name.

Arithmetic operators:

these are the binary operators + - * / % (the last one signifies modulo). These operators can be applied only to numeric data.

Comparison operators:

these are the binary operators < <= > >= = <> and the ternary operator between which specifies a range that includes the endpoints. These operators can be applied to numeric or character string data.

Examples:

where name = 'Smith'

where amount*0.15 between 1000 and 2000

The binary operators can be used to compare a value against a subquery that yields a single value, or with the keyword all or some to compare a value against a subquery that yields multiple values.

Examples:

where amount > (select max(amount) from loan where branch_name='Mianus')

where amount > all (select amount from loan where branch_name='Mianus')

Pattern matching:

the like operator compares a character string to a pattern. The pattern is a quoted string that can contain % to match any string of characters (including the empty string) and _ to match any single character. It can also contain a list of characters within brackets [] to match any character in the list.

Examples:

where street like '_th'

where city like 'New %'

Null matching:

the is null and is not null phrases are used to test whether a data value is null. Do not use $\mbox{value} = \mbox{null}$ or $\mbox{value} <> \mbox{null}$ which are always false.

Example:

where street is null

Negation

the unary boolean operator not can be used in front of a boolean expression to reverse its value, or it can be used in front of any of the comparison operators or the like operator to reverse the result of the comparison.

Examples:

where not (name = 'Smith')

where amount*0.15 not between 1000 and 2000

where street not like '_th'

where street is not null

Boolean operators

The binary boolean operators and and or can be used to join any two boolean expressions.

Examples:

where assets > 100000 and br_city = 'Brooklyn'

where street = '1st' or street = '2nd' or street like '[3-9]th'

where street is not null

Set membership

The in operator tests whether a given value is found in a subquery that follows within parentheses.

Example:

where city in (select city from branch where assets > 100000)

Existential quantifier

Use exists followed by a subquery to determine if the subquery yields a non-empty result.

Example:

where exists (select amount from loan where amount < 100)