<< Click to Display Table of Contents >> Navigation:  Data Module > Import & export > Data mappings > SQL Basics > Keywords > WHERE

Specifies a search condition to restrict the rows returned.  Note the old outer join *= or =* syntax is not supported.

 

Syntax

[ WHERE < search_condition > ]

 

Arguments

< search_condition >

 

Restricts the rows returned in the result set through the use of predicates. There is no limit to the number of predicates that can be included in a search condition. Search condition is a combination of one or more predicates using the logical operators AND, OR, and NOT.

 

Syntax

< search_condition > ::=

   { [ NOT ] < predicate > | ( < search_condition > ) }

   [ { AND | OR } [ NOT ] { < predicate > | ( < search_condition > ) } ] } [ ,...n ]

 

< predicate > ::=

   { expression { = | < > | ! = | > | > = | < | < = } expression

   | string_expression [ NOT ] LIKE string_expression

   | expression [ NOT ] BETWEEN expression AND expression

   | expression IS [ NOT ] NULL

   | expression [ NOT ] IN ( subquery | expression [ ,...n ] )

   | EXISTS ( subquery ) }

 

Arguments

< search_condition >

 

Specifies the conditions for the rows returned in the result set for a SELECT statement, query expression, or subquery. There is no limit to the number of predicates that can be included in a SQL statement search condition.

 

NOT

Negates the Boolean expression specified by the predicate.

 

AND

Combines two conditions and evaluates to TRUE when both of the conditions are TRUE.

 

OR

Combines two conditions and evaluates to TRUE when either condition is TRUE.

 

< predicate >

 

Is an expression that returns TRUE, FALSE, or UNKNOWN.

 

expression

Is a column name, a constant, a function, a variable, a scalar subquery, or any combination of column names, constants, and functions connected by an operator(s) or a subquery. The expression can also contain the CASE function.

 

=

Is the operator used to test the equality between two expressions.

 

<>

Is the operator used to test the condition of two expressions not being equal to each other.

 

!=

Is the operator used to test the condition of two expressions not being equal to each other.

 

>

Is the operator used to test the condition of one expression being greater than the other.

 

>=

Is the operator used to test the condition of one expression being greater than or equal to the other expression.

 

<

Is the operator used to test the condition of one expression being less than the other.

 

<=

Is the operator used to test the condition of one expression being less than or equal to the other expression.

 

string_expression

Is a string of characters and wildcard characters.

 

[ NOT ] LIKE

Indicates that the subsequent character string is to be used with pattern matching.

 

[ NOT ] BETWEEN

Specifies an inclusive range of values. Use AND to separate the beginning and ending values.

 

IS [ NOT ] NULL

Specifies a search for null values, or for values that are not null, depending on the keywords used. An expression with a bitwise or arithmetic operator evaluates to NULL if any of the operands is NULL.

 

[ NOT ] IN

Specifies the search for an expression, based on the expression's inclusion in or exclusion from a list. The search expression can be a constant or a column name, and the list can be a set of constants or, more commonly, a subquery. Enclose the list of values in parentheses.

 

subquery

Can be considered a restricted SELECT statement and is similar to <query_expresssion> in the SELECT statement. The ORDER BY clause and the INTO keyword are not allowed.

 

EXISTS

Used with a subquery to test for the existence of rows returned by the subquery.