WHERE Clause
The conditions that determine whether a document is included in the results returned by the query is specified by the WHERE clause. At the highest level, there are two parts to the WHERE clause syntax:
...WHERE [<group_aliases>] <search_condition>
The <search condition> portion of the WHERE clause is one or more search predicates that specify matching criteria for the search. Search predicates are expressions that assert some fact about some value.
The result of a search condition is a Boolean value, either TRUE if the document meets the specified search conditions or FALSE if it does not. If the result is TRUE, the document is returned. If the result is FALSE, it is not. Documents returned in a Microsoft Windows Search query are assigned rank values according to how well they match the search conditions. Each of the query search conditions can include a RANK BY clause that supports modifying the returned rank values.
To simplify complex queries, you can assign an alias to a group of one or more columns. This can improve the readability of complex queries that search for the same information across multiple columns specified by Uniform Resource Names (URNs). For more information about group aliases, see WITH -- AS Group Alias Predicate.
Search Predicates
A search condition consists of one or more predicates or search conditions that describe what the user is searching for (e.g., WHERE "System.DateCreated" >'2006-04-19'). Search predicates can be combined using the logical operators AND, OR, or AND NOT. The optional unary operator NOT can be used to negate the logical value of a predicate or search condition. You can use parentheses to group and nest logical terms.
The following table shows the logical operator precedence order.
| Order (precedence) | Logical operator |
|---|---|
| First (highest) | NOT |
| Second | AND |
| Third (lowest) | OR |
Logical operators of the same type are associative, and there is no specified calculation order. For example, (A AND B) AND (C AND D) can be calculated (A AND D) AND (B AND C) with no change in the logical result.
Important You cannot apply the unary logical operator NOT to the CONTAINS predicate or to the FREETEXT predicate if that predicate is the first one in the WHERE clause.
NOT OKAY: WHERE NOT CONTAINS ('computer')
OKAY: WHERE CONTAINS ('software') AND NOT CONTAINS ('computer')
In complex queries, you may want to place more emphasis on matches in some columns than in others. For example, when searching for documents that discuss "software design", finding the search term in the document title is more likely to be a "good" match than finding the individual words inside the text of the document. To influence the ranking of documents in this manner, the Microsoft Windows Search query language supports weighting the search conditions. For more information about column weighting, see CONTAINS Predicate and FREETEXT Predicate.
There are three groups of search predicates in Windows Search: full-text, non-full-text, and folder depth searches. Full-text search predicates typically match the meaning of the content, title, and other columns, and support linguistic matching (for example, alternative word forms, phrases, proximity searching). In contrast, non-full-text search predicates match the value of the specified columns and do not include any special linguistic processing, but in several cases offer character-based pattern matching. Folder depth predicates restrict the search scope to a specified path.
Note If the query returns a document because a non-full-text predicate evaluates to TRUE for that document, the rank value is calculated as 1000. Using the rank coercion function can modify the rank value.
The following tables describe the full-text, non-full-text, and folder depth search predicates.
| Full-text predicate | Description |
|---|---|
| CONTAINS | Supports complex searches for terms in document text columns (for example, title, contents). Can search for inflected forms of the search terms, test for proximity of the terms, and perform logical comparisons. Search terms can include wildcards. |
| FREETEXT | Searches for documents that match the meaning of the search phrase. Related words and similar phrases will match, with the rank column calculated based on how closely the document matches the search phrase. Search terms cannot include wildcards. |
| Non-full-text predicate | Description |
|---|---|
| LIKE | Column values are compared using simple pattern matching with wildcards. The pattern matching of the LIKE predicate is simpler but less powerful than that of the MATCHES predicate. |
| Literal Value Comparison | Column values are compared against string, date, time stamp, numeric, and other literal values. This predicate supports equality as well as inequalities such as greater than and less than. |
| MATCHES | Column values are compared using regular expression matching. The regular expression capability of the MATCHES predicate is more flexible and powerful than that of the LIKE predicate. |
| Multi-Valued (ARRAY) Comparisons | Multi-valued columns are compared against a multi-valued array of literals. |
| NULL | Column values that are undefined for the document can be detected by using the NULL predicate. |
| Folder Depth | Description |
|---|---|
| SCOPE | Performs a deep traversal of the specified path, including the specific folder and all subfolders. |
| DIRECTORY | Performs a shallow traversal of the specified path, searching only the specific folder. |
Examples
For examples of the WHERE clause, see the individual predicate topics.
This section includes the following topics:
- WITH -- AS Group Alias Predicate
- Full-Text Predicates
- Non-Full-Text Predicates
- SCOPE and DIRECTORY Predicates