WHERE Clause

• General
• BETWEEN Predicate
• Comparison Predicate
• EXISTS Predicate
• IN Predicate
• LIKE Predicate
• NULL Predicate
• Quantifier

General

Description

In a query, a column can be processed based on conditions. The WHERE clause specifies a search condition for data.

Syntax

WHERE search_condition

search_condition :
comparison_predicate
between_predicate
in_predicate
like_predicate
null_predicate
quantified predicate
exists_predicate
set_predicate

The WHERE clause specifies a condition that determines the data to be retrieved by search_condition or a query. Only data for which the condition is true is retrieved for the query results. (NULL value is not retrieved for the query results because it is evaluated as unknown value.)

The logical operator AND or OR can be used for multiple conditions. If AND is specified, all conditions must be true. If OR is specified, only one needs to be true. If the keyword NOT is preceded by a condition, the meaning of the condition is reserved. The following table shows the order in which logical operators are evaluated.

Priority	Operator	Function
1	()	Logical expressions in parentheses are evaluated first.
2	NOT	Negates the result of the logical expression.
3	AND	All conditions in the logical expression must be true.
4	OR	One of the conditions in the logical expression must be true.

• search_condition : It is described in detail in the following sections.

BETWEEN Predicate

Description

The BETWEEN predicate allows you to select instances of a table that fall within a range of values or outside a range of values. The column evaluated by BETWEEN must be default data types which are single values.

Syntax

expression [ NOT ] BETWEEN expression AND expression

• expression : expression can be a column name, path expression, constant value, an arithmetic expression or aggregate function. Character string expressions are evaluated alphabetically. For example, a predicate i BETWEEN g AND m is equivalent to the compound predicates i >= g AND i <= m.

Example

The following is an example of selecting the name, gender and number of players of sports whose number of players is between 5 and 10. In this query, 20 rows are selected in the event table. The players values of selected rows fall between the specified range.

SELECT name, gender, players FROM event WHERE players BETWEEN 5 AND 10;
=== <Result of SELECT Command in Line 2> ===
  name                  gender                    players
=========================================================
  'Synchronized Team'   'W'                             8
  'Indoor'              'M'                             6
  'Indoor'              'W'                             6
  'Water Polo'          'M'                             7
  'Water Polo'          'W'                             7
...
20 rows selected.

You can use NOT in the BETWEEN clause. If you use NOT BETWEEN, data outside the specified range are selected. The players value in the event table is exactly 5 or 10 is not included in the query results of the NOT BETWEEN clause.

SELECT name, gender, players FROM event WHERE players NOT BETWEEN 5 AND 10;
=== <Result of SELECT Command in Line 2> ===
  name                  gender                    players
=========================================================
  'Freestyle 48kg'      'W'                             1
  'Freestyle -48kg'     'M'                             1
  'Freestyle 52kg'      'M'                             1
  'Freestyle -52kg'     'M'                             1
  'Freestyle 54kg'      'M'                             1
 ...
402 rows selected.

Comparison Predicate

Description

A comparison_predicate evaluates an expression by comparing it against another or subquery. The first expression is often a column name or path expression that is compared against a second expression. The following table shows comparison operators that can be used in comparison_predicate.

Comparison Operator	Meaning
=	Equal to
<>	Not equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to

Comparison against floating point and double precision data types should be regarded as approximate. Because of the conversion that occurs internal to your system, the value you enter may be altered during query manipulation and argument passing. This loss of precision affects the actual value stored of float point or double precision number, even though the output value may look like the desired value.

Example

• Example 1
• The following is an example of selecting the years and host cities of Olympic Games held in the United States of America. The following query selects host_year, host_nation and host_city from the olympic table. The predicate in the WHERE clause selects data for which host_nation is 'United States of America.' Comparisons on string values are case-sensitive. Strings must be enclosed in single quotes (' '). For more information on character strings, see the "Data Types" section.

SELECT host_year, host_nation, host_city FROM olympic WHERE host_nation='United States of America';
=== <Result of SELECT Command in Line 2> ===
    host_year  host_nation           host_city
=========================================================
         1996  'United States of America'  'Atlanta '
         1904  'United States of America'  'St. Louis'
         1984  'United States of America'  'Los Angeles'
         1932  'United States of America'  'Los Angeles'   
4 rows selected.

• Example 2

• The following is an example of selecting the name, gender and number of players of sports for which the number of players is greater than 4 and less than 10. The following query selects the name, gender and players columns from the event table. The comparison predicates and logical operators in the WHERE clause specify the range of the players value to retrieve.

SELECT sports, name, gender, players FROM event WHERE players > 4 AND players < 10;
=== <Result of SELECT Command in Line 1> ===
  sports                name                  gender                    players
===============================================================================
'Swimming'            'Synchronized Team'          'W'                        8
  'Volleyball'        'Indoor'                     'M'                        6
  'Volleyball'        'Indoor'                     'W'                        6
  'Water Polo'        'Water Polo'                 'M'                        7
  'Water Polo'        'Water Polo'                 'W'                        7
...    
20 rows selected.

EXISTS Predicate

Description

The EXISTS predicate is used to determine whether the result of a subquery is an empty set.

Syntax

EXISTS expression

• expression : If a subquery is specified in expression, the result of the SELECT statement in the subquery is evaluated. If the subquery does not produce any results, then EXISTS is false.

Example

The following is an example of selecting the names of the countries that participated in the 1988 Olympic Games, and their continents. The EXISTS predicate is used to determine whether a certain value exists in the results of a subquery. If any countries participated in the Olympic Games, the results of the subquery exist; data for 156 countries and their continents is outputted.

SELECT n.name, n.continent FROM nation n
WHERE EXISTS (SELECT * FROM participant p WHERE p.host_year=1988 AND p.nation_code=n.code);
=== <Result of SELECT Command in Line 2> ===
  name                  continent
============================================
  'Somalia'             'Africa'
  'Sri Lanka'           'Asia'
  'Sudan'               'Africa'
  'Switzerland'         'Europe'
  'Surinam'             'Americas'
...
156 rows selected.

IN Predicate

Description

The IN predicate compares a single value against a set, multiset or sequence set of values.

Syntax

expression [ NOT ] IN expression

• expression (left) : A single-value column, path expression, constant value, or an arithmetic function that produces a single value.
• expression (right) : A list of values to compare. The expression can be a subquery. The list can be an explicit list of constant values entered within braces or parentheses. In the IN predicate, only constant values enclosed in parentheses (()) are purpose of ANSI compliance.

Example

• Example 1
• The following is an example of selecting the name, gender and number of players of sports for which the number of players is 5 or 6. The following query selects the data whose column value is 5 or 6 in the event table. The data from the event table that have one of the values specified in the query condition are retrieved as the result.

SELECT name, gender, players FROM event WHERE players IN (5, 6);
=== <Result of SELECT Command in Line 2> ===
  name                  gender                    players
=========================================================
  'Indoor'              'M'                             6
  'Indoor'              'W'                             6
  'Hockey'              'M'                             6
  'Hockey'              'W'                             6
  'Rhythmic Group Competition'  'W'                     6
  'Rhythmic Team'       'M'                             6
  'Rhythmic Team'       'W'                             6
  'Basketball'          'M'                             5
  'Basketball'          'W'                             5    
9 rows selected.

• Example 2
• The following is an example of selecting the names of the countries that won one or more gold medals, and their continents. In the following query, a subquery is specified in the IN predicate. The subquery selects the nation_code from the participant table and then selects the data that have the code value included in the result of the subquery from the nation table.

SELECT continent, name FROM nation WHERE code IN (SELECT nation_code FROM participant WHERE gold>0);
=== <Result of SELECT Command in Line 2> ===
  continent             name
============================================
  'Africa'              'Algeria'
  'Americas'            'Argentina'
  'Europe'              'Armenia'
  'Oceania'             'Australia'
  'Europe'              'Austria'...
81 rows selected.

LIKE Predicate

Description

The LIKE predicate retrieves the data that matches a specified text pattern from a table. Comparison using the LIKE predicate is supported only with columns that have STRING or CHAR domains. The LIKE predicate does not support NCHAR or BIT domains.

Syntax

expression [ NOT ] LIKE expression [ ESCAPE char]

• expression (left) : The first expression represents a column that has a domain of STRING or CHAR(n) type. Pattern matching starts from the first character of the column value.
• expression (right) : The second expression to be retrieved can be a string consisting of the following characters:
  
• • The symbol "_" (underscore) in the string pattern represents any single character.
  • The symbol "%" (percent) in the string pattern represents any sequence of characters, which may be an empty string.
  • All other characters represent themselves.
  • In the LIKE predicate, the default values of escape characters do not exist.
• ESCAPE char : If the string pattern to be retrieved includes an actual "_" or "%" character, ESCAPE must be specified. That is, an ESCAPE character must precede the "_" or "%" character. For example, if you retrieve '10%', then an ESCAPE character such as "\" (backlash) can be used, the string would be actually represented as '10\%'.

Example

The following is an example of selecting the nation codes and names of the countries that include 'K' in their nation codes. This query selects codes whose string pattern is '%K_' from the nation table. In this case, the first character can be any string, the second character must be 'K', and the last character must be any single characters. Here, the code consists of three characters. Therefore, as shown in the result, only codes that include 'K' as the second character are retrieved.

SELECT code, name FROM nation WHERE code LIKE '%K_';
=== <Result of SELECT Command in Line 1> ===
  code                  name
============================================
  'TKM'                 'Turmenistan'
  'UKR'                 'Ukraine'
  'SKN'                 'Saint Kitts & Nevis'
  'MKD'                 'Former Yugoslav Republic of Macedonia'
  'HKG'                 'Hong Kong'    
5 rows selected.

NULL Predicate

Description

The NULL predicate determines whether columns in a table have a value specified as NULL. You can also select those columns that have values by using the NOT NULL keyword in the predicate.

Syntax

expression IS [ NOT ] NULL

• expression : It refers to the columns in the table that you want to check for NULL or NOT NULL.

Example

The following is an example of selecting the year, host country, host city, mascot and slogan of all Olympic Games that had a mascot. The following query selects specified columns from the olympic table for instances in which the mascot column values are not NULL.

SELECT host_year, host_nation, host_city, mascot, slogan FROM olympic WHERE mascot is NOT NULL;
=== <Result of SELECT Command in Line 1> ===
    host_year  host_nation               host_city             mascot                slogan
=====================================================================================================
         1988  'Korea'                    'Seoul'               'HODORI'              'Harmony and progress'
         1992  'Spain'                    'Barcelona '          'Cobi'                'Friends Forever '
         1996  'United States of America' 'Atlanta '            'Izzy'                'The Celebration of the Century '
         2000  'Australia'                'Sydney '             'Olly Syd Millie '    'Share the Spirit'
         1976  'Canada'                   'Montreal'            'Amik'                NULL
         1972  'Germany'                  'Munich'              'Waldi'               NULL
         1980  'U.S.S.R.'                 'Moscow'              'Misha'               NULL
         1984  'United States of America' 'Los Angeles'         'Sam'                 'Play part in History'
         2004  'Greece'                   'Athens '             'Athena  Phevos'      'Welcome Home'
...    
25 rows selected.

Quantifier

Description

The ALL quantifier compares a single value against every value returned by another expression. The SOME quantifier compares a single value against each value returned by another expression. The single value must compare at least one of the values returned by the expression. The keyword ANY can be used instead of keyoword SOME which returns the same query results.

Syntax

expression comp_op ALL expression
expression comp_op SOME expression

• comp_op : A comparison operator >, <, =, >= or <= can be used.
• expression (right) : The second expression can be a column name, path expression, a list (set) of constant values, a subquery or an arithmetic operator. If a subquery is used as the second expression in a query, each result produced by the subquery is compared against a single value associated with the first expression.

Example

• Example 1
• The following is an example of an ALL quantifier. In this example, the gold column in the participant table is compared against each value of the silver column in the participant table. The query result shows that the gold column value of one instance of the participant table is greater than the silver column values of all instances of the participant table.

SELECT nation_code, gold, silver FROM participant
WHERE gold > ALL (SELECT silver FROM participant) AND host_year=1992;
=== <Result of SELECT Command in Line 1> ===
  nation_code                  gold       silver
================================================
  'EUN'                          45           38    
1 rows selected.

• Example 2
• The following is an example of a SOME quantifier. The gold column in the participant table is compared against each value of the silver column in the participant table. The query result shows that the gold column values for 37 instances of the participant table are greater than at least one of the silver column values in the participant table.

SELECT nation_code, gold, silver FROM participant
WHERE gold > SOME(SELECT silver FROM participant) AND host_year=1992;    
=== <Result of SELECT Command in Line 1> ===
  nation_code                  gold       silver
================================================
  'ITA'                           6            5
  'GBR'                           5            3
  'ROU'                           4            6
  'TCH'                           4            2
  'PRK'                           4            0
...
37 rows selected.