Home

	Expand Search

Transact-SQL User's Guide

Chapter 1 SQL Building Blocks

Expressions in Adaptive Server

Relational and logical expressions

Relational and logical expressions

A logical expression or relational expression returns TRUE, FALSE, or UNKNOWN. The general patterns are:

expression comparison_operator [any | all] expression

expression [not] in expression

[not] exists expression

expression [not] between expression and expression

expression [not] like "match_string" [escape "escape_character"]

not expression like "match_string" [escape "escape_character"]

expression is [not] null

not logical_expression

logical_expression {and | or} logical_expression

any is used with <, >, or = and a subquery. It returns results when any value retrieved in the subquery matches the value in the where or having clause of the outer statement. all is used with < or > and a subquery. It returns results when all values retrieved in the subquery are less than (<) or greater than (>) the value in the where or having clause of the outer statement. See Chapter 5, "Subqueries: Using Queries Within Other Queries," for more information.

in returns results when any value returned by the second expression matches the value in the first expression. The second expression must be a subquery or a list of values enclosed in parentheses. in is equivalent to = any.

and connects two expressions and returns results when both are true. or connects two or more conditions and returns results when either of the conditions is true.

When more than one logical operator is used in a statement, and is evaluated before or. Use parentheses to change the order of execution.

Table 1-6 shows the results of logical operations, including those that involve null values:

The result UNKNOWN indicates that one or more of the expressions evaluates to NULL, and that the result of the operation cannot be determined to be either TRUE or FALSE.