Predicates

Predicates are conditions you write in the WHERE, ON, and HAVING clauses of a SQL statement that determine which rows are affected, or returned, by that statement. For example, use the predicate name = 'Wagner Falls' to return data for only that particular waterfall:

SELECT u.zone, u.northing, u.easting
FROM upfall u
WHERE name = 'Wagner Falls';

Table 14 lists the available comparison operators. Some operators, such as IN and EXISTS, are more fully described in upcoming subsections. Regular-expression operators are described later in the "Regular Expressions" section. Operators for dealing with nulls are described in "Nulls.”

Table 14. Comparison operators

Operator

Description

!=, <>

Tests for inequality

<

Tests for less than

<=

Tests for less than or equal to

<=>

Null-safe test for equality; supported only by MySQL

=

Tests for equality

>

Tests for greater than

>=

Tests for greater than or equal to

BETWEEN

Tests whether a value lies within a given range

EXISTS

Tests whether rows exist matching conditions that you specify

IN

Tests whether a value is contained in a set of values that you specify or that are returned by a subquery

IS [NOT] NULL

Tests for nullity

LIKE

Tests whether a value matches a pattern

REGEXP, RLIKE

Regular-expression comparison operator; supported only by MySQL

REGEXP_LIKE

Tests whether a value matches the pattern described by a regular expression; supported only by Oracle

EXISTS Predicates

Use EXISTS ...

Get SQL Pocket Guide, 2nd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.