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 ...

Get SQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.