WHERE Clause in SharePoint Foundation Search SQL Syntax
[This documentation is preliminary and is subject to change.]
Used to specify the conditions that determine whether a document is included in the results returned by the query.
…WHERE [<group_aliases>] <search_condition>
Remarks
At the highest level, there are two parts to the WHERE clause syntax, the column or columns to search, and the search condition.
To simplify complex queries, you can assign an alias to a group of one or more columns. For more information about group aliases, see WITH -- AS Group Alias Predicate in SharePoint Foundation Search SQL Syntax.
The search condition part 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 Search query are assigned rank values according to how well they match the search conditions.
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.
Note
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.
There are two groups of search predicates in Windows SharePoint Services Search. 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. |
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. |
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. |
In this Section
WITH -- AS Group Alias Predicate in SharePoint Foundation Search SQL Syntax
Full-Text Predicates in SharePoint Foundation Search SQL Syntax
Non-Full-Text Predicates in SharePoint Foundation Search SQL Syntax