SQL in a Nutshell, 2nd Edition

Name

WHERE Clause

Synopsis

The WHERE clause sets the search criteria for an operation such as SELECT, UPDATE, or DELETE. Any records in the target table(s) that do not meet the search criteria are excluded from the operation. The search conditions may include many variations such as calculations, Boolean operators, and SQL predicates (for example, LIKE or BETWEEN).

Platform	Command
DB2	Supported
MySQL	Supported
Oracle	Supported
PostgreSQL	Supported
SQL Server	Supported

SQL2003 Syntax

 { WHERE search_criteria | WHERE CURRENT OF cursor_name } 

Keywords

WHERE search_criteria

Defines search criteria for the statement to ensure that only the target rows are affected.

WHERE CURRENT OF cursor_name

Restricts the operation of the statement to the current row of a defined and opened cursor called cursor_name.

Rules at a Glance

WHERE clauses are found in SELECT statements, DELETE statements, INSERT...SELECT statements, UPDATE statements, and any statement that might have a query or subquery such as DECLARE, CREATE TABLE, CREATE VIEW, and so forth. The search conditions, all of which are described in their own entries elsewhere in the book, can include:

All records (=ALL, >ALL, <= ALL, SOME/ANY)

For example, to see publishers who live in the same city as their authors:

SELECT pub_name
FROM   publishers
WHERE city = SOME (SELECT city FROM authors);

Combinations (AND, OR, and NOT) and evaluation hierarchy

For example, to see all authors with sales in quantities greater than or equal to 75 ...