WHERE Clause in Enterprise Search SQL Syntax
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 Enterprise 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 an Enterprise Search query are assigned rank values according to how well they match the search conditions.
Specifying Search Scopes
You can indicate whether the search results should include content from a particular search scope in a search condition specified in the WHERE clause. Following is the syntax:
…FROM scope() WHERE "scope"=<search scope name>…
For example:
SELECT title, author, rank FROM scope() WHERE "scope"='All Sites'
Search Predicates
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.
In complex queries, you might 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, SQL supports weighting the search conditions. For more information about column weighting, see CONTAINS Predicate in Enterprise Search SQL Syntax and FREETEXT Predicate in Enterprise Search SQL Syntax.
There are two groups of search predicates in Enterprise 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.
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 wildcard characters. |
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 wildcard characters. |
| Non–Full-Text Predicate | Description |
|---|---|
LIKE |
Column values are compared by 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. |
Multivalued (ARRAY) Comparisons |
Multivalued columns are compared against a multivalued 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 Enterprise Search SQL Syntax
Full-Text Predicates in Enterprise Search SQL Syntax
Non-Full-Text Predicates in Enterprise Search SQL Syntax
See Also
Reference
SELECT Statement in Enterprise Search SQL Syntax
FROM Clause in Enterprise Search SQL Syntax