Predicates

Predicates are conditions you write in the WHERE, ON, and HAVING clauses of an 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 1-13 lists the available comparison operators. Some operators, such as IN and EXISTS, are more fully described in later subsections. Regular-expression operators are described under Regular Expressions. Operators for dealing with nulls are described under Nulls.

Table 1-13. 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 and NOT EXISTS to test for the existence of rows matching a set of conditions that you specify. For ...

Get SQL Pocket Guide, 3rd 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.