Search conditions (predicates)

Use predicates to place conditions on the result set. Predicates are added in the WHERE clause of a SELECT, UPDATE, or DELETE statement, or the HAVING clause of a SELECT statement. A search condition consists of one or more predicates. A search condition specifies a condition that is true, false, or unknown about a given row. The result of a search condition is derived by application of logical operators (AND, OR, or NOT) to the result of each specified predicate.

In a SELECT statement, the WHERE clause specifies the condition or conditions that a row must meet to be selected. If the WHERE clause is omitted, the database manager returns all rows in the table.

In an UPDATE statement, the WHERE clause specifies the rows to be updated. If the WHERE clause is omitted, the database manager updates each row in the table with the values that you supply.

In a DELETE statement, the WHERE clause specifies the rows to be deleted. If the WHERE clause is omitted, the database manager deletes all rows in the table.

In a SELECT statement, the HAVING clause specifies an intermediate result table that consists of the groups that you specified in the GROUP BY clause for which the search condition is true. Add a HAVING clause to define search conditions that will filter groups of rows in the result set.

If you do not specify logical operators, the result of the search condition is the result of the single specified predicate. SQL Assist supports several types of SQL predicates:

Simple comparison predicate
A simple comparison predicate uses logical operators (=, <, >, <>, ...) to compare a column value with another specific value.
LIKE predicate
The pattern match (LIKE) predicate searches for strings that have a certain pattern. The pattern is specified by a string in which wildcard characters (underscore ( _ ) and percent sign(%)) have special meanings. Trailing blanks in a pattern are part of the pattern.
BETWEEN predicate
The range comparison (BETWEEN) predicate compares a value with a range of values.
NULL predicate
The NULL predicate tests for null values. The result of a NULL predicate cannot be unknown. If the value of the expression is null, the result is true. If the value is not null, the result is false. If NOT is specified, the result is reversed.
IN predicate
The IN predicate compares a value or values with a collection of values.

Related tasks:

Add search conditions to an SQL statement (WHERE or HAVING clause)
Add expressions
Search for and select existing column values using the List Values window