Defining complex search conditions

 

In addition to the basic comparison predicates, such as = and >, a search condition can contain any of these predicates: BETWEEN, IN, EXISTS, IS NULL, and LIKE.

A search condition can include a scalar fullselect.

For character, or UCS-2 or UTF-16 graphic column predicates, the sort sequence is applied to the operands before evaluation of the predicates for BETWEEN, IN, EXISTS, and LIKE clauses.

You can also perform multiple search conditions.

• BETWEEN ... AND ... is used to specify a search condition that is satisfied by any value that falls on or between two other values. For example, to find all employees who were hired in 1987, you can use this:

  …   WHERE HIREDATE BETWEEN '1987-01-01' AND '1987-12-31'

  The BETWEEN keyword is inclusive. A more complex, but explicit, search condition that produces the same result is:

  … WHERE HIREDATE >= '1987-01-01' AND HIREDATE <= '1987-12-31'

• IN says you are interested in rows in which the value of the specified expression is among the values you listed. For example, to find the names of all employees in departments A00, C01, and E21, you can specify:

  … WHERE WORKDEPT IN ('A00', 'C01', 'E21')

• EXISTS says you are interested in testing for the existence of certain rows. For example, to find out if there are any employees that have a salary greater than 60000, you can specify:

  EXISTS (SELECT * FROM EMPLOYEE WHERE SALARY > 60000)

• IS NULL says that you are interested in testing for null values. For example, to find out if there are any employees without a phone listing, you can specify:

  … WHERE EMPLOYEE.PHONE IS NULL

• LIKE says you are interested in rows in which an expression is similar to the value you supply. When you use LIKE, SQL searches for a character string similar to the one you specify. The degree of similarity is determined by two special characters used in the string that you include in the search condition:

  _

  An underline character stands for any single character.

  %

  A percent sign stands for an unknown string of 0 or more characters. If the percent sign starts the search string, then SQL allows 0 or more character(s) to precede the matching value in the column. Otherwise, the search string must begin in the first position of the column.

  If you are operating on MIXED data, the following distinction applies: an SBCS underline character refers to one SBCS character. No such restriction applies to the percent sign; that is, a percent sign refers to any number of SBCS or DBCS characters. See SQL Reference for more information about the LIKE predicate and MIXED data.

  Use the underline character or percent sign either when you do not know or do not care about all the characters of the column's value. For example, to find out which employees live in Minneapolis, you can specify:

  … WHERE ADDRESS LIKE '%MINNEAPOLIS%'

  SQL returns any row with the string MINNEAPOLIS in the ADDRESS column, no matter where the string occurs.

  In another example, to list the towns whose names begin with 'SAN', you can specify:

  … WHERE TOWN LIKE 'SAN%'

  If you want to find any addresses where the street name isn't in your master street name list, you can use an expression in the LIKE expression. In this example, the STREET column in the table is assumed to be upper case.

  … WHERE UCASE (:address_variable) NOT LIKE '%'||STREET||'%'

  If you want to search for a character string that contains either the underscore or percent character, use the ESCAPE clause to specify an escape character. For example, to see all businesses that have a percent in their name, you can specify:

  … WHERE BUSINESS_NAME LIKE '%@%%' ESCAPE '@'

  The first and last percent characters in the LIKE string are interpreted as the normal LIKE percent characters. The combination '@%' is taken as the actual percent character.

• Special considerations for LIKE
  Here are some considerations for using the LIKE predicate.

• Multiple search conditions within a WHERE clause
  You can qualify your request further by coding a search condition that includes several predicates.

 

Parent topic:

Retrieving data using the SELECT statement

 

Related concepts

Using subqueries
Sort sequences and normalization in SQL

 

Related reference

Specifying a search condition using the WHERE clause
Expressions in the WHERE clause
Predicates