O'Reilly logo

SQL Pocket Guide, 2nd Edition by Jonathan Gennick

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

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

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