WHERE Clause

  • Article

Note

Indexing Service is no longer supported as of Windows XP and is unavailable for use as of Windows 8. Instead, use Windows Search for client side search and Microsoft Search Server Express for server side search.

 

The WHERE clause specifies the search condition for selecting rows in the virtual table defined by the FROM clause. The matching rows constitute the resulting rowset. The WHERE clause is an optional part of the SELECT statement. However, if the WHERE clause is absent, the resulting rowset can be extremely large because all rows of the virtual table defined by the FROM clause are returned in the resulting rowset.

SELECT Select_List | *
       FROM_Clause
       [WHERE Search_Conditions]
       [ORDER_BY_Clause]

Parameters

Select_List

Specifies the list of column aliases (properties) making up the table (rowset) that is returned as a result of the query.

* (asterisk)

Specifies all columns. This option is valid only when the FROM_Clause parameter references a predefined view or a temporary view.

FROM_Clause

Specifies the files on which to perform the search. For details about this parameter, see FROM Clause.

Search_Conditions

One or more predicates combined with AND, OR, and NOT. It specifies the conditions that rows must satisfy to belong to the resulting rowset. The rowset only includes rows in which all the predicates evaluate to TRUE.

ORDER_BY_Clause

Specifies the ordering of the resulting rowset. This clause is optional. For details about this parameter, see ORDER BY Clause.

Remarks

The composition of search conditions has two parts: Boolean operators and predicates.

Boolean operators are AND, OR, and NOT. When using these operators together, precedence rules determine the order of evaluation of the conditions. When the search condition consists of statements enclosed in parentheses, expressions in parentheses are evaluated first. After the parenthetical expressions are evaluated, the following rules apply:

  • NOT is evaluated before AND. NOT can only occur after AND (as in AND NOT; the combination OR NOT is not allowed).
  • AND is evaluated before OR.
  • AND expressions are associative and can be applied in any order. For example, A AND B AND C, is the same as (A AND B) AND C, which is the same as A AND (B AND C).
  • OR expressions are associative and can be applied in any order.

Note

It is not valid to place NOT before content query predicates (CONTAINS and FREETEXT). The following statement is not valid.

 

SELECT FileName FROM SCOPE()
  WHERE NOT CONTAINS ('search')

The following statement is valid.

SELECT FileName FROM SCOPE()
  WHERE CONTAINS ('search') AND NOT CONTAINS ('light')

A predicate is an expression that makes a factual assertion about values.

  • If the expression evaluates to TRUE, the associated condition is satisfied.
  • If the expression evaluates to FALSE, the condition is not satisfied.

The following table lists the predicate types you can use with the WHERE clause and summarizes each.

Predicate Type Description
ARRAY Performs comparisons of two arrays using logical operators.
Comparison Uses arithmetic operators to compare column data to a literal value.
CONTAINS Specifies certain Boolean, proximity and stemming conditions for matching text.
FREETEXT Specifies searching for the best match for the words and phrases.
LIKE Performs queries using pattern matching with wildcard characters.
MATCHES Performs queries using pattern matching with regular expressions.
NULL Determines whether a column in a selected row is defined.

 

FROM Clause

ORDER BY Clause

SELECT Statement