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>
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 using Uniform Resource Names (URNs). For more information about group aliases, see WITH…AS Group Alias Predicate.
The search condition portion of the WHERE clause specifies matching criteria for the search. Documents are returned if they meet the specified comparisons and logical combinations of the query. The result of a search condition is a Boolean value, either TRUE or FALSE. If the result is TRUE, the document is included. If the result is FALSE, it is not.
Documents returned in a Microsoft SharePoint Portal Server Search (SharePointPSSearch) 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.
Search predicates are expressions that assert some fact about some value. Documents that "match" the predicate requirements have an appropriate value for the property specified in the predicate.
A search condition consists of one or more predicates or search conditions, combined by 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 (B AND C) AND (A AND D) 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 within the WHERE clause. For example, WHERE NOT CONTAINS ('computer') is not accepted; however, WHERE CONTAINS ('software') AND NOT CONTAINS ('computer') is accepted.
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 Office SharePoint Portal Server query language supports weighting the search conditions. For more information about column weighting, see CONTAINS Predicate and FREETEXT Predicate.
There are two groups of search predicates in SharePointPSSearch. 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.
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 and non-full-text 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. |
Examples
For examples of the WHERE clause, see the individual predicate topics.
This section includes the following topics: