SQL Building Blocks and Server Settings

This chapter is from the book

Transact-SQL Desk Reference: For Microsoft SQL Server

Learn More Buy

This chapter is from the book

This chapter is from the book 

Transact-SQL Desk Reference: For Microsoft SQL Server

Learn More Buy

2.3 EXPRESSIONS IN AN SQL STATEMENT

An expression is a combination of operands and operators, which evaluates to a scalar:

• Scalar: a single data value such as number 13, date '1 Jan 2003' or string 'Jim Doe'

• Operand: a constant (literal), column name, variable, scalar function subquery whose result set is a scalar value

• Operator: any legal operator symbol allowed by the data type of the operand(s)

An expression generally assumes the data type of its component operands except that operands combined using comparison or logical operators result in a value of Boolean data type.

String, numeric and date expressions may usually be used anywhere a scalar value of their data type is required in INSERT, UPDATE, DELETE and SELECT statements. (See the SELECT example below.)

Boolean (logical) expressions may appear where a <search_condition> is specified as in a WHERE clause of UPDATE, DELETE or SELECT statements. They may evaluate to TRUE, FALSE or NULL (unknown). A Boolean expression may also be used in an IF or WHILE construct.

Syntax

expression ::= operand | ( expression ) | unary_operator expression
               | expression binary_operator expression
operand ::= constant | [table_alias.]column_name | variable | scalar_function
               | ( scalar_subquery )
unary_operator ::= operator that operates on one expression: operator expression
               E.g., unary minus, -, as with ( - 6 ).
binary_operator ::= operator that operates on two expressions: expr operator expr.
               E.g., binary minus, -, as with ( 12 - 6 ).

For complete syntax see Books Online, Index: expressions, overview.

SELECT   'Hello', 1 ,  (SELECT 1) + 2 ,   GETDATE()
                  

Example of Boolean expression in WHERE clause <search condition>

SELECT ord_num, qty  FROM sales WHERE qty > 45
               

2.3.1 Operators

An operator is a symbol specifying an action that is performed on one or more expressions. Microsoft SQL Server 2000 uses the following operator categories.

• Arithmetic operators: all numeric data types

• Assignment operators: all data types

• Bitwise operators: all integer data types plus binary and varbinary data types

• Comparison operators: all data types except text, ntext or image

• Logical operators: operand data type depends on operator

• String concatenation operators: character or binary string data types

• Unary operators : all numeric data types

When two expressions of different compatible data types are combined by a binary operator the expression of lower precedence is implicitly converted to the data type of higher precedence, the operation is performed and the resulting value is of the higher precedence data type. See Data Type Precedence List on page 74.

2.3.1.1 Arithmetic Operators

The arithmetic operators, as shown in Table 2-3, may be used with any of the numeric types. Addition and subtraction also work with datetime and smalldate-time for adding or subtracting a number of days.

Table 2-3. Arithmetic Operators

2.3.1.2 Relational Operators

These relational operators, shown in Table 2-4, may be used with string, numeric or date values.

Table 2-4. Relational Operators

2.3.1.3 Assignment Operator: =

There is one assignment operator, equal sign (=), used primarily with local variables.

DECLARE   @MyInt   INT
                  SET  @MyInt = 123  -- Assignment Operation
                  PRINT @MyInt
                  

2.3.1.4 Bitwise Operators

Bitwise operators work on all integer data types and bit data types. (See Transact-SQL Data Type Hierarchy, page 74.)

Binary and varbinary data types may be one operand if an integer type is the other operand. Table 2-5 shows the necessary symbols for this use.

Table 2-5. Bitwise Operators

PRINT 1 | 2
                  

2.3.1.5 Comparison Operators

Comparison operators, shown in Table 2-6, also called relational operators, work on all data types except text, ntext or image.

Table 2-6. Comparison Operators

The result of a comparison is of Boolean type with three possible values: TRUE, FALSE or NULL (unknown). Boolean type may not be used as column or variable data types.

Comparisons are most often used in a WHERE clause or an IF statement.

SELECT price FROM titles WHERE price > 21
                  

2.3.1.6 Logical Operators and Boolean Predicates

Logical operations test the truth of some condition and return a Boolean value: TRUE, FALSE or NULL (unknown). Acceptable operand data types of logical operations depend on the operator.

Books Online lists all of the entries in Table 2-7 as logical operators. They can all appear in a WHERE clause as a Boolean <search condition>.

Table 2-7. Logical Operators

2.3.1.7 AND, OR, NOT with NULL

The first three are the well-known logical operators from classical Boolean algebra. A significant difference of SQL Boolean logic from classical Boolean logic is the effect that NULL has in creating three-way logic (TRUE, FALSE, NULL).

Using NULL in SQL Boolean Logic

The ANSI SQL-99 Standard⁴ says the following about NULL.

Although the null value is neither equal to any other value nor not equal to any other value — it is unknown whether or not it is equal to any given value

So NULL can be read as UNKNOWN in SQL Boolean logic expressions.

In evaluating a WHERE clause as in a SELECT, UPDATE or DELETE statement,

WHERE <search_condition>

the <search_condition> is a Boolean predicate which evaluates to TRUE, FALSE or NULL. Only those rows that are known to be true are selected to be in the result set. So any row for which the WHERE predicate evaluates to FALSE (known to be FALSE) or NULL (unknown) will not be retained. See WHERE clause syntax page 478. The same is true when applied to the HAVING clause of a SELECT statement.

SELECT  title_id, price
                  FROM   pubs.dbo.titles
                  WHERE price > 22
                  

IS NULL

The correct way to find all rows for which a column value is NULL is to use the IS NULL predicate.

SELECT  title_id, price
               FROM    pubs.dbo.titles
               WHERE  price IS NULL
               

This returns only PC9999 from the previous table.

IS NOT NULL or NOT IS NULL

The correct way to find all rows for which a column value is not NULL is to use the IS NOT NULL predicate or to negate the IS NULL predicate by preceding it with NOT.

SELECT  title_id, price
               FROM     pubs.dbo.titles
               WHERE  price IS NOT NULL   -- Or: WHERE  NOT price
                    IS NULL
               

Either form returns both of the first two rows from the previous table.

Caution

Never use "columnname = NULL" or "columnname <> NULL" to find rows with NULL or non-NULL values. These forms are syntactically correct but logically of no value since they always return zero rows. Always use IS NULL.

Despite this caution, the = as an assignment operator is used in UPDATE to change a column value to NULL.

Updating a Value to NULL

A value may be set to NULL using the form UPDATE tablename SET columnname = NULL

UPDATE   pubs.dbo.titles
                  SET price = NULL
                  WHERE title_id = 'MC3021'
                  

Compound Predicates using AND, OR and NOT with NULL

The WHERE or HAVING predicate of a SELECT statement may include compound conditions using AND and OR operators.

Tables 2-9, 2-10 and 2-11 are truth tables for AND, OR and NOT.

Table 2-9. Truth Table for A AND B

Hint: Read NULL as UNKNOWN

Table 2-10. Truth Table for A OR B

Table 2-11. Truth Table for NOT A

2.3.1.8 ANY/SOME

ANY and SOME are equivalent and may be used interchangeably.

Syntax

scalar_expression { = | < > | ! = | > | > = | ! > | < | < = | ! < }
               { ANY | SOME } ( subquery )

Arguments

scalar_expression

This is any valid Microsoft SQL Server expression.

{ = | <> | != | > | >= | !> | < | <= | !< }

This is any valid comparison operator.

subquery

This is a SELECT statement that has a result set of one column. The data type of the column returned must be the same data type as scalar_expression.

Return

Type: Boolean

ANY/SOME returns TRUE if the comparison specified is TRUE for ANY pair (scalar_expression, x) where x is a value in the single-column set. Otherwise, ANY/SOME returns FALSE.

USE  pubs      --  Move to the pubs database
                  go
                  
                  SELECT pub_id, pub_name FROM publishers
                  WHERE pub_id = ANY ( SELECT pub_id FROM titles )
                  

SELECT DISTINCT p.pub_id, p.pub_name -- Same
                      result as previous query
                  FROM publishers p , titles t
                  WHERE p.pub_id = t.pub_id
                  

2.3.1.9 ALL

ALL compares a scalar value with a single-column set of values.

Syntax

scalar_expression { = | <> | != | > | >= | !> | < | <= | !< } ALL ( subquery )

Arguments

{ = | <> | != | > | >= | !> | < | <= | !< }

Result

Type: Boolean

ALL returns TRUE if the specified comparison is TRUE for scalar_expression and every value in the single-column set returned from the subquery, otherwise ALL returns FALSE.

ALL can often be used to find the complement of an ANY/SOME query.

SELECT pub_id, pub_name FROM publishers
                  WHERE pub_id <>  ALL ( SELECT pub_id FROM titles )
                  

2.3.1.10 BETWEEN

BETWEEN specifies inclusion in a specified range.

Syntax

test_expr [ NOT ] BETWEEN begin_expr AND end_expr

Arguments

Result

Type: Boolean

BETWEEN returns TRUE if the value of test_expr is greater than or equal to the value of begin_expr and less than or equal to the value of end_expr.

NOT BETWEEN returns TRUE if the value of test_expr is less than the value of begin_expr or greater than the value of end_expr.

Remarks

test_expr, begin_expr and end_expr must be the same data type.

If any input to the BETWEEN or NOT BETWEEN predicate is NULL, the result is UNKNOWN.

The BETWEEN operator is a shorthand equivalent to

( test_expr >= begin_expr AND test_expr <= end_expr )

SELECT pub_id, pub_name FROM publishers
                  WHERE pub_id BETWEEN 500 AND 900
                  
                  SELECT pub_id, pub_name FROM publishers  --
                      equivalent result
                  WHERE pub_id >= 500 AND pub_id <= 900
                  

SELECT pub_id, pub_name FROM publishers
                  WHERE pub_id NOT BETWEEN 500 AND 900
                  SELECT pub_id, pub_name FROM publishers --
                      equivalent result
                  WHERE NOT (pub_id >= 500 AND pub_id <= 900)
                  

2.3.1.11 IN

IN determines if a given value matches any value in a subquery or a list.

Syntax

test_expression [ NOT ] IN
    (      subquery   | expression [ ,...n ]     )

Arguments

Result

Type: Boolean

If the value of test_expression is equal to any value returned by subquery or is equal to any expression from the comma-separated list, the result value is TRUE. Otherwise, the result value is FALSE.

Using NOT IN negates the returned value.

SELECT pub_id, pub_name FROM publishers
                  WHERE pub_id IN ( SELECT pub_id FROM titles )
                  

SELECT pub_id, pub_name FROM publishers
                  WHERE pub_id IN ( 0736 , 0877 )
                  

2.3.1.12 EXISTS

EXISTS is used only with a subquery (a SELECT statement enclosed in parentheses used inside another DML statement). It is TRUE if the subquery result set is nonempty.

See page 702 for more detail on subqueries.

Syntax

EXISTS subquery

Result

Type: Boolean

EXISTS returns TRUE if the subquery returns any rows at all (nonempty result set).

SELECT pub_id, pub_name FROM publishers p
                  WHERE EXISTS
                  ( SELECT pub_id FROM titles WHERE pub_id = p.pub_id)
                  

2.3.1.13 LIKE

LIKE provides pattern matching searching of character strings. LIKE determines whether or not a given character string matches a specified pattern. A pattern can include regular characters and wildcard characters. During pattern matching, regular characters must exactly match the characters specified in the character string. Wildcard characters, however, can be matched with arbitrary fragments of the character string. If any of the arguments are not of character string data type, SQL Server converts them to character string data type, if possible.

Syntax

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

Arguments

Table 2-12. Valid Wildcard Characters

For example, if you want to search for a literal percent sign (%) embedded in text, you could declare an escape character that does not occur in the text, e.g., \, and use it to escape the %. A sample search might be as follows.

SELECT * FROM titles
       WHERE notes LIKE '% 30\% discount %'
       ESCAPE '\'

The first and last % are wildcards, the \% causes the search for a literal %.

Some people prefer to use % as the escape character so that %% becomes a literal percent sign and a single % is a wildcard.

Result

Type: Boolean

LIKE returns TRUE if the match_expression (usually a column name) matches the specified pattern.

SELECT au_lname FROM authors WHERE au_lname LIKE 'Gr%'
                  

Remarks

When you perform string comparisons with LIKE, all characters in the pattern string are significant, including leading or trailing spaces.

For example, using LIKE 'abc ' (abc followed by one space), a row with the value abc (without a space) is not returned. However, trailing blanks, in the expression to which the pattern is matched, are ignored. For example, using LIKE'abc'(abc without a space), all rows that start with abc followed by zero or more trailing blanks are returned.

2.3.1.14 Performance Note: Anywhere Search

A LIKE query which begins with a wildcard, either % or _, is called an Anywhere Search and is usually not recommended if it can be avoided.

WHERE  au_lname  LIKE '%st%'

, or

WHERE  au_lname  LIKE '%st'

, or

WHERE  au_lname  LIKE '_st'

The reason to avoid anywhere searches is the impact on performance, especially if the table being searched is large. This is because an index, if one exists on the au_lname column, can not be used in an anywhere search. Queries on large tables will take a very, very, very long time if no index is used.

To see why an anywhere search disables the use of an index, think of an index being similar to a telephone book and imagine the usefulness of the telephone book if looking for each of the queries above.

On the other hand, the following query does NOT disable the use of an index on au_lname if one exists since the match string does not BEGIN with a wildcard: WHERE au_lname LIKE 'st%'

2.3.1.15 String Concatenation Operator: +

There is one concatenation operator, plus sign (+), and it may be used with character or binary string data types.

SELECT lname, fname, lname + ', ' + fname FROM
                      employee
                  

Note: The ANSI SQL concatenation operator is "||" used in Oracle, DB2 and others. The + string concatenation operator in SQL Server is not portable to other RDBMSs.

2.3.1.16 Unary Operators

Unary operators work on a single numeric operand expression.

Bitwise NOT only works with integer data types, the other two take any numeric data type. See Table 2-13.

Table 2-13. Unary Operators

Example

print  1  +  -3    -- + is binary operator, - is unary
               

2.3.1.17 Operator Precedence

When a complex expression has multiple operators, operator precedence determines the sequence in which the operations are performed. Operators have the precedence levels shown in Table 2-14. An operator on higher levels is evaluated before an operator on a lower level. In case of two operators of equal precedence evaluation proceeds from left to right

Table 2-14. Operator Precedence