4.5 Search Conditions | ODBC and SQL Reference

Description

A search condition specifies a condition that is true or false about a given row or group of rows. Query expressions and UPDATE statements can specify a search condition. The search condition restricts the number of rows in the result table for the query expression or UPDATE statement.

Search conditions contain one or more predicates. The predicates that can be part of a search condition are described in the following subsections.

Syntax

search_condition ::
        [NOT] predicate
        [ { AND | OR } { predicate | ( search_condition ) } ]
predicate ::
         basic_predicate
|        quantified_predicate
|        between_predicate
|        null_predicate
|        like_predicate
|        contains_predicate
|        exists_predicate
|        in_predicate
|        outer_join_predicate

4.5.1 Logical Operators: OR, AND, NOT

Logical operators combine multiple search conditions. SQL evaluates multiple search conditions in this order:

Search conditions enclosed in parentheses. If there are nested search conditions in parentheses, SQL evaluates the innermost search condition first.
Search conditions preceded by NOT
Search conditions combined by AND
Search conditions combined by OR

Examples

SELECT *
    FROM customer
    WHERE name = 'LEVIEN' OR name = 'SMITH' ;
SELECT *
    FROM customer
    WHERE city = 'PRINCETON' AND state = 'NJ' ;
SELECT *
    FROM customer
    WHERE  NOT (name = 'LEVIEN' OR name = 'SMITH') ;

4.5.2 Relational Operators

Relational operators specify how SQL compares expressions in basic and quantified predicates.

Syntax

relop ::
      =
    | <> | != | ^=
    | <
    | <=
    | >
    | >=

Relational Operator	Predicate is:
`=`	True if the two expressions are equal.
`<> \| != \| ^=`	True if the two expressions are not equal. The operators `!=` and `^=` are equivalent to `<>`.
`<`	True if the first expression is less than the second expression..
`<=`	True if the first expression is less than or equal to the second expression.
`>`	True if the first expression is greater than the second expression.
`>=`	True if the first expression is greater than or equal to the second expression.

See "Basic Predicate" and "Quantified Predicate" for more information.

4.5.3 Basic Predicate

Description

A basic predicate compares two values using a relational operator (see "Relational Operators"). If a basic predicate specifies a query expression, then the query expression must return a single value. Basic predicates often specify an inner join. See "Inner Joins" for more detail.

If the value of any expression is null or the query_expression does not return any value, then the result of the predicate is set to false.

Basic predicates that compare two character expressions can include an optional COLLATE clause. The COLLATE clause specifies a collation sequence supported by the underlying storage system. (See "Specifying the Character Set for Character Data Types" on page 4-6 for notes on character sets and collations. See the documentation for your underlying storage system for details on any supported collations.)

Syntax

basic_predicate ::
    expr relop { expr | (query_expression) } [ COLLATE collation_name ]

4.5.4 Quantified Predicate

Description

The quantified predicate compares a value with a collection of values using a relational operator (see "Relational Operators"). A quantified predicate has the same form as a basic predicate with the query_expression being preceded by ALL, ANY or SOME keyword. The result table returned by query_expression can contain only a single column.

When ALL is specified the predicate evaluates to true if the query_expression returns no values or the specified relationship is true for all the values returned.

When SOME or ANY is specified the predicate evaluates to true if the specified relationship is true for at least one value returned by the query_expression. There is no difference between the SOME and ANY keywords. The predicate evaluates to false if the query_expression returns no values or the specified relationship is false for all the values returned.

Syntax

quantified_predicate ::
    expr relop { ALL | ANY | SOME } (query_expression)

Example

10 <  ANY ( SELECT COUNT(*)
                 FROM order_tbl
                 GROUP BY custid
               )

4.5.5 BETWEEN Predicate

Description

The BETWEEN predicate can be used to determine if a value is within a specified value range or not. The first expression specifies the lower bound of the range and the second expression specifies the upper bound of the range.

The predicate evaluates to true if the value is greater than or equal to the lower bound of the range, or less than or equal to the upper bound of the range.

Syntax

between_predicate ::
    expr [ NOT ] BETWEEN expr AND expr

Example

salary BETWEEN 2000.00 AND 10000.00

4.5.6 NULL Predicate

Description

The NULL predicate can be used for testing null values of database table columns.

Syntax

null_predicate ::
    column_name IS [ NOT ] NULL

Example

contact_name IS NOT NULL

4.5.7 CONTAINS Predicate

Description

The SQL CONTAINS predicate is an extension to the SQL standard that allows storage systems to provide search capabilities on character and binary data. See the documentation for the underlying storage system for details on support, if any, for the CONTAINS predicate.

Syntax

column_name [ NOT ] CONTAINS 'string'

Notes

column_name must be one of the following data types: CHARACTER, VARCHAR, LONG VARCHAR, BINARY, VARBINARY, or LONG VARBINARY.
There must be an index defined for column_name, and the CREATE INDEX statement for the column must include the TYPE clause, and specify an index type that indicates to the underlying storage system that this index supports CONTAINS predicates. See the documentation for your storage system for the correct TYPE clause for indexes that support CONTAINS predicates.
The format of the quoted string argument and the semantics of the CONTAINS predicate are defined by the underlying storage system.

4.5.8 LIKE Predicate

Description

The LIKE predicate searches for strings that have a certain pattern. The pattern is specified after the LIKE keyword in a string constant. The pattern can be specified by a string in which the underscore ( _ ) and percent sign ( % ) characters have special semantics.

The ESCAPE clause can be used to disable the special semantics given to characters ' _ ' and ' % '. The escape character specified must precede the special characters in order to disable their special semantics.

Syntax

like_predicate ::
    column_name [ NOT ] LIKE string_constant
    [ ESCAPE escape-character ]

Notes

The column name specified in the LIKE predicate must refer to a character string column.
A percent sign in the pattern matches zero or more characters of the column string.
A underscore sign in the pattern matches any single character of the column string.

Examples

cust_name LIKE '%Computer%'
cust_name LIKE '___'
item_name LIKE '%\_%' ESCAPE '\'

In the first example, for all strings with the substring Computer, the predicate will evaluate to true. In the second example, for all strings which are exactly three characters long, the predicate will evaluate to true. In the third example, the backslash character ' \ ' has been specified as the escape character, which means that the special interpretation given to the character ' _ ' is disabled. The pattern will evaluate to TRUE if the column item_name has embedded underscore characters.

4.5.9 EXISTS Predicate

Description

The EXISTS predicate can be used to check for the existence of specific rows. The query_expression returns rows rather than values. The predicate evaluates to true if the number of rows returned by the query_expression is non zero.

Syntax

exists_predicate ::
    EXISTS ( query_expression )

Example

EXISTS (SELECT * FROM order_tbl
               WHERE order_tbl.custid = :custid)

In this example, the predicate will evaluate to true if the specified customer has any orders.

4.5.10 IN Predicate

Description

The IN predicate can be used to compare a value with a set of values. If an IN predicate specifies a query expression, then the result table it returns can contain only a single column.

Syntax

in_predicate ::
    expr [ NOT ] IN { ( query_expression ) |
                            ( constant , constant [ , ... ] ) }

Example

address.state IN ('MA', 'NH')

4.5.11 Outer Join Predicate

Description

An outer join predicate specifies two tables and returns a result table that contains all of the rows from one of the tables, even if there is no matching row in the other table. See "Outer Joins" for more information.

Syntax

outer_join_predicate ::
    [ table_name. ] column = [ table_name. ] column (+)
    |  [table_name. ] column (+) = [ table_name. ] column