O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required