SQL Predicates - DB2 Using SQL

A predicate is used in either the WHERE or HAVING clauses of a SQL statement. It specifies a condition that true, false, or unknown about a row or a group.

Predicate Precedence

As a rule, a query will return the same result regardless of the sequence in which the various predicates are specified. However, note the following:

  • Predicates separated by an OR may need parenthesis .
  • Checks specified in a CASE statement are done in the order written.

Basic Predicate

A basic predicate compares two values. If either value is null, the result is unknown. Otherwise the result is either true or false.

Basic Predcate syntax, 1 of 2

Basic Predcate syntax, 1 of 2

Basic Predicate eample

A variation of this predicate type can be used to compare sets of columns/values. Everything on both sides must equal in order for the expressions to match:

Basic Predicate syntax, 2 of 2

Basic Predicate eample

Below is the same query written the old fashioned way

Same query prior, using individual predicates

Quantified Predicate

A quantified predicate compares one or more values with a collection of values.

Quantified Predicate syntax

Quantified Predicate syntax

Quantified Predicate example, two single-value sub-queries

10 Mgr

Quantified Predicate example, multi-value sub-query

BETWEEN Predicate

The BETWEEN predicate compares a value within a range of values.

BETWEEN predicate syntax

BETWEEN predicate syntax

The between check always assumes that the first value in the expression is the low value and the second value is the high value. For example, BETWEEN 10 AND 12 may find data, but

BETWEEN Predicate examples

EXISTS Predicate

An EXISTS predicate tests for the existence of matching rows.

EXISTS Predicate syntax

EXISTS Predicate syntax

EXISTS Predicate example

IN Predicate

The IN predicate compares one or more values with a list of values.

IN Predicate syntax

IN Predicate syntax

The list of values being compared in the IN statement can either be a set of in-line expressions (e.g. ID in (10,20,30)), or a set rows returned from a sub-query. Either way, DB2 simply goes through the list until it finds a match.

IN Predicate example, single values

The IN statement can also be used to compare multiple fields against a set of rows returned from a sub-query. A match exists when all fields equal. This type of statement is especially useful when doing a search against a table with a multi-columns key.
WARNING: Be careful when using the NOT IN expression against a sub-query result. If any one row in the sub-query returns null, the result will be no match.

IN Predicate example, multi-value

LIKE Predicate

The LIKE predicate does partial checks on character strings.

LIKE Predicate syntax

LIKE Predicate syntax
The percent and underscore characters have special meanings. The first means skip a string of any length (including zero) and the second means skip one byte. For example:

  • LIKE 'AB_D%' Finds 'ABCD' and 'ABCDE', but not 'ABD', nor 'ABCCD'.
  • LIKE '_X' Finds 'XX' and 'DX', but not 'X', nor 'ABX', nor 'AXB'.
  • LIKE '%X' Finds 'AX', 'X', and 'AAX', but not 'XA'.

LIKE Predicate example

The ESCAPE Phrase

The escape character in a LIKE statement enables one to check for percent signs and/or underscores in the search string. When used, it precedes the '%' or '_' in the search string indicating that it is the actual value and not the special character which is to be checked for. When processing the LIKE pattern, DB2 works thus: Any pair of escape characters is treated as the literal value (e.g. "++" means the string "+"). Any single occurrence of an escape character followed by either a "%" or a "_" means the literal "%" or "_" (e.g. "+%" means the string "%"). Any other "%" or "_" is used as in a normal LIKE pattern.

LIKE and ESCAPE example

=========================== ======================
LIKE 'AB%' Finds AB, any string
LIKE 'AB%' ESCAPE '+' Finds AB, any string
LIKE 'AB+%' ESCAPE '+' Finds AB%
LIKE 'AB++' ESCAPE '+' Finds AB+
LIKE 'AB+%%' ESCAPE '+' Finds AB%, any string
LIKE 'AB++%' ESCAPE '+' Finds AB+, any string
LIKE 'AB+++%' ESCAPE '+' Finds AB+%
LIKE 'AB+++%%' ESCAPE '+' Finds AB+%, any string
LIKE 'AB+%+%%' ESCAPE '+' Finds AB%%, any string
LIKE 'AB++++' ESCAPE '+' Finds AB++
LIKE 'AB+++++%' ESCAPE '+' Finds AB++%
LIKE 'AB++++%' ESCAPE '+' Finds AB++, any string
LIKE 'AB+%++%' ESCAPE '+' Finds AB%+, any string

Now for sample SQL:

LIKE and ESCAPE example

NULL Predicate

The NULL predicate checks for null values. The result of this predicate cannot be unknown. If the value of the expression is null,the result is true. If the value of the expression is not null, the result is false.

NULL Predicate syntax

NULL Predicate example

Special Character Usage

To refer to a special character in a predicate, or anywhere else in a SQL statement, use the "X" notation to substitute with the ASCII hex value. For example, the following query will list all names in the STAFF table that have an "a" followed by a semi-colon:

Refer to semi-colon in SQL text

Precedence Rules

Expressions within parentheses are done first, then prefix operators (e.g. -1), then multiplication and division, then addition and subtraction. When two operations of equal precedence are together (e.g. 1 * 5 / 4) they are done from left to right.

Precedence rules example

Example: 555 + -22 / (12 - 3) * 66 ANSWER
^ ^ ^ ^ ^ 423
5th 2nd 3rd 1st 4th

Be aware that the result that you get depends very much on whether you are doing integer or decimal arithmetic. Below is the above done using integer numbers:

Precedence rules, integer eample

---- ---- ---- ----
9 -2 -132 423

DB2 truncates, not rounds, when doing integer arithmetic.

Here is the same done using decimal numbers:

Precedence rules, decimal example

------ ------ ------ ------
9.0 -2.4 -161.3 393.6

AND/OR Precedence

AND operations are done before OR operations. This means that one side of an OR is fully processed before the other side is begun. To illustrate:

Processing Sequence

The various parts of a SQL statement are always executed in a specific sequence in order to avoid semantic ambiguity:

FROM clause
JOIN ON clause
WHERE clause
GROUP BY and aggregate
HAVING clause

Query Processing Sequence

Observe that ON predicates (e.g. in an outer join) are always processed before any WHERE predicates (in the same join) are applied. Ignoring this processing sequence can cause what looks like an outer join to run as an inner join . Likewise, a function that is referenced in the SELECT section of a query (e.g. row-number) is applied after the set of matching rows has been identified, but before the data has been ordered.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

DB2 Using SQL Topics