The Search Is On

While you know that you can SELECT records from a table without searching for a specific record, the SELECT statement would be more than limiting without the ability to search for single rows or a small collection that meets some criteria. The search for rows in the table, or in multiple tables, is provided by a chunk of code within the SELECT statement known as the search condition.

The search condition makes use of comparison operators and relationship predicates to look for matching records with which to satisfy the query. The syntax looks a little arcane but is really pretty simple to grasp. The T-SQL official search condition syntax looks like this:

 { [ NOT ] < predicate > | ( < search_condition > ) } [ { AND | OR } [ NOT ] { < predicate > | ( < search_condition > ) } ] } [ ,...n ] < predicate > ::= { expression { = | < > | ! = | > > = | ! > | < | < = | ! < } expression| string_expression [ NOT ] LIKE string_expression [ ESCAPE 'escape_character'] | expression [ NOT ] BETWEEN expression AND expression | expression IS [ NOT ] NULL | CONTAINS ( { column | * } , '< contains_search_condition >' ) | FREETEXT ( { column | * } , 'freetext_string' ) ) | expression [ NOT ] IN ( subquery | expression [ ,...n ] ) | expression {=|< >| !=| > | >=| ! > | < | <=| !<} { ALL | SOME | ANY } ( subquery ) | EXISTS ( subquery ) }

The search condition is used in several places in the SELECT statement. It is also used in the UPDATE and DELETE statements, where it specifies the rows to be updated and the rows to be deleted, respectively.

You can pretty much build a search condition that tests scores of possibilities using as many predicates and operators as you need. In addition, operators like NOT can be used to negate Boolean expressions specified by the predicate. And you can use AND to combine two conditions that must both test for TRUE to be included in the result set. OR is also here for the search posse, allowing you to combine two conditions, which will test for true if at least one of the conditions is true.

The order of precedence for the logical operators is NOT (highest), followed by AND, followed by OR. The order of evaluation at the same precedence level is from left to right. The Parentheses can be used to override this order in a search condition, as discussed earlier in the chapter.

The < predicate > placeholder represents an expression that returns TRUE, FALSE, or UNKNOWN. It can test on a column name, a constant, a function, a variable, a scalar subquery, or any combination of column names, constants, and functions connected by the operators or subqueries. You can also build an expression that contains an entire CASE function. The following operators can be used in the search condition:

• The = (equal) operator tests for equality between two expressions.

• The <> (not equal) operator tests the condition of two expressions for not being equal to each other.

• The != is the ANSI not-equal operator that works the same as the T-SQL operator.

• The > (greater than) operator tests the condition of one expression being greater than the other.

• The >= (greater than or equal to) operator tests the condition of one expression being greater than or equal to the other expression.

• The !> (not greater than) operator tests the condition of one expression not being greater than the other expression.

• The < (less than) operator tests the condition of one expression being less than the other.

• The <= (less than or equal to) operator tests the condition of one expression being less than or equal to the other expression.

• The !< (not less than) operator tests the condition of one expression not being less than the other expression.

The [NOT] LIKE expression indicates that the subsequent character string is to be used with the pattern matching.

The ESCAPE escape_character expression lets you search for the exact string that also includes a wildcard character (not functioning in the capacity of a wildcard character) to be searched for.

The escape_character you denote replaces the usual wildcard character. To denote this, you need to place the new wildcard character in front of the old wildcard character. For example, the statement

 select * from items where item like 'Tz_square' ESCAPE 'z'

lets you search for all rows that contain the column value “T_square” where “z” replaces the underscore as the wildcard. The key to remember here is that you are searching for the exact string, not a pattern match on several characters. So if you just search on “T_s” the search will fail.

The NOT BETWEEN lets you specify an inclusive range of values between one point and another. Use AND to separate the beginning and ending values.

IS NOT NULL lets you specify a search for nulls or for values that are not null, depending on the keywords used. An expression with a bitwise or arithmetic operator evaluates to NULL if any of the operands is NULL.

The CONTAINS keyword lets you search for columns containing character-based data for precise or “fuzzy” (which means less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, and weighted matches. CONTAINS can only be used with SELECT statements.

The FREETEXT keyword provides a simple form of natural language query by searching columns containing character-based data for values that match the meaning rather than the exact words in the predicate. FREETEXT can only be used with SELECT statements.

The NOT IN keywords let you specify the search for an expression, based on the expression’s inclusion in or exclusion from a list. The search expression can be a constant or a column name, and the list can be a set of constants or a subquery, which is the usual approach. The list of values must be enclosed in parentheses.

The subquery clause can be considered a restricted SELECT statement and is similar to the <query_expresssion> in the SELECT statement. You cannot use the ORDER BY clause, the COMPUTE clause, or the INTO keyword in these subqueries.

The ALL keyword is used with a comparison operator and a subquery. It will return TRUE for <predicate> if all values retrieved for the subquery satisfy the comparison operation, or FALSE if not all values satisfy the comparison or if the subquery returns no rows to the outer statement.

The SOME | ANY keywords are used with a comparison operator and a subquery They will return TRUE for the predicate if any value retrieved for the subquery satisfies the comparison operation, or FALSE if no values in the subquery satisfy the comparison or if the subquery returns no rows to the outer statement. Otherwise, the expression is unknown.

EXISTS is used with a subquery to test for the existence of rows returned by the subquery.