Home > Articles > Data > SQL Server

  • Print
  • + Share This
This chapter is from the book

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.

Example: Examples of string expressions:

  • ename

  • 'Mary Smith'

  • first_name || ' ' || last_name

Examples of numeric expressions:

  • salary

  • 123.45

  • 22 + 33

  • (salary + 22) * 1.2

  • AVG(salary)

  • 1.5 * AVG(salary)

Example of Boolean (logical) expressions:

qty > 45 -- evaluates to TRUE, FALSE or NULL (unknown, as if qty is NULL)

Example of subquery expression:

A subquery is a SELECT statement enclosed in parenthesis used inside another DML statement. See page 554 for more detail on subqueries.

  • The bold text in the following statement is a subquery expression

    • SELECT * FROM titles

      • WHERE price >=

      • ( SELECT AVG(price) FROM titles WHERE type = 'business')

Example of numeric, string and date expressions in SELECT list

SQL

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

Result


-------            -----------       -----------       ------------------------------------
Hello              1                 3                 2001-05-11 16:28:27.140

Example of Boolean expression in WHERE clause <search condition>

SQL

SELECT ord_num, qty  FROM sales WHERE qty > 45
               
 

Result


ord_num               qty
---------------       ------
A2976                 50
QA7442.3              75

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

+

Addition

Subtraction and unary negation

*

Multiplication

/

Division

%

Module (Remainder)

( )

Grouping

Notes:

Both symbols + and are unary and binary operators.

The symbol / returns the data type with the higher precedence as usual. So if both dividend (top) and divisor are integer types, the result is truncated (not rounded) to an integer.

The symbol % is modulo or remainder and returns the integer remainder of the division. Both operands must be integer data types. So 1%3 is 1 and 4%3 is also 1 as is 7%3.

Examples:

1 + 2

4 / 2

( 2.5 + 3 ) * 2

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

=

equal to

<>

not equal to

<

less than

<=

less than or equal to

>

greater than

>=

greater than or equal to

2.3.1.3 Assignment Operator: =

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

Example:

SQL

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

Result

123

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

Bitwise AND

Bitwise OR

Bitwise EOR

&

|

^

Example:

SQL

PRINT 1 | 2
                  

Result

3

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

Equal To

Greater Than

Less Than

Greater or Equal To

Less Than or Equal To

Not Equal To

Not Less Than

Not Greater Than

=

>

<

>=

<=

<>

!<

!>

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.

Example:

SQL

SELECT price FROM titles WHERE price > 21
                  
 

Result


price
---------------------
22.95
21.59

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

AND

OR

NOT

ANY/ SOME

ALL

BETWEEN

IN

EXISTS

LIKE

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 Standard4 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.

Example: Consider the rows in Table 2-8 of the titles table in the pubs database.

Table 2-8. Rows from Titles Table

title_id

price

PC1035

22.95

MC3021

2.99

PC9999

NULL

...

...

Which of these rows will be returned by the following SELECT statement on that table?

SQL

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

Of these rows only the first row is returned since, for the other two rows, the predicate evaluates to FALSE and NULL (unknown) respectively.

Result


title_id         price
--------         ---------------------
PC1035           22.9500

IS NULL

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

SQL

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

This returns only PC9999 from the previous table.

Result


title_id         price
--------         ---------------------
PC9999           NULL

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.

SQL

SELECT  title_id, price
               FROM     pubs.dbo.titles
               WHERE  price IS NOT NULL   -- Or: WHERE  NOT price
                  graphics/ccc.gif  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

Example: Change the price to NULL for title_id MC3021.

SQL

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.

Example: Consider the SELECT statement in Figure 2-1.

02fig01.gifFigure 2-1. Selected Statement.

When executed, the WHERE predicate is evaluated on each row in the table and only if both A and B are known to be TRUE then the row is included in the result set. So any row where price or pub_id is NULL will not appear in the final result set.

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

 

B

     

TRUE

FALSE

NULL

   

A

TRUE

TRUE

FALSE

NULL

FALSE

FALSE

FALSE

FALSE

 

NULL

NULL

FALSE

NULL

 

Hint: Read NULL as UNKNOWN

Table 2-10. Truth Table for A OR B

 

B

     

TRUE

FALSE

NULL

   

A

TRUE

TRUE

TRUE

TRUE

FALSE

TRUE

FALSE

NULL

 

NULL

TRUE

NULL

NULL

 

Table 2-11. Truth Table for NOT A

A

TRUE

FALSE

NULL

NOT A

FALSE

TRUE

NULL

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.

Example: List all publishers' names that have a title in the titles table.

SQL

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

Result


pub_id          pub_name
------          -------------------------------
0736            New Moon Books
0877            Binnet & Hardley
1389            Algodata Infosystems

This same result can be obtained by using the IN and EXISTS operators (explained on the following pages) or by doing an INNER JOIN of publishers and titles.

SQL

SELECT DISTINCT p.pub_id, p.pub_name -- Same
                     graphics/ccc.gif 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

scalar_expression

This is any valid SQL Server (scalar) expression.

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

This is a comparison operator.

Subquery

This is a subquery that returns a result set of one column. The data type of the returned column must be the same data type as the data type of scalar_expression. It may not contain an ORDER BY clause, COMPUTE clause or INTO keyword.

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.

Example: List all publishers' names that have NO title in the titles table.

SQL

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

Result


pub_id           pub_name
------           -------------------------------
1622             Five Lakes Publishing
1756             Ramona Publishers
9901             GGG&G
9952             Scootney Books
9999             Lucerne Publishing

2.3.1.10 BETWEEN

BETWEEN specifies inclusion in a specified range.

Syntax

test_expr [ NOT ] BETWEEN begin_expr AND end_expr

Arguments

test_expr

This is the expression to test for in the range defined by begin_expr and end_expr.

NOT

Not specifies that the result of the predicate be negated.

begin_expr

This is any valid expression.

end_expr

This too is any valid expression.

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 )

Example: Find all publishers with pub_id values between 500 and 900 — use both forms.

SQL

SELECT pub_id, pub_name FROM publishers
                  WHERE pub_id BETWEEN 500 AND 900
                  
                  SELECT pub_id, pub_name FROM publishers  --
                     graphics/ccc.gif equivalent result
                  WHERE pub_id >= 500 AND pub_id <= 900
                  
 

Result


pub_id           pub_name
------           --------------------------------------
0736             New Moon Books
0877             Binnet & Hardley

Example: Find all publishers with pub_id values outside of 500 and 900—use both forms.

SQL

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

Result


pub_id            pub_name
------            -------------------------------------
1389              Algodata Infosystems
1622              Five Lakes Publishing
. . .                 . . .

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

test_expression

This is any valid Microsoft SQL Server expression.

Subquery

A subquery has a result set of one column. This column must have the same data type as test_expression.

expression [,...n]

This is a list of expressions to test for a match. All expressions must be of the same type as test_expression.

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.

Example with subquery: List all publishers' names with a title in the titles table.

SQL

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

Result

Same as shown above using ANY.

Example: With expression list, list all publishers' names with a pub_id of 0736 or 0877.

SQL

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

Result


pub_id        pub_name
------        ----------------------
0736          New Moon Books
0877          Binnet & Hardley

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).

Example: List all publishers' names that have a title in the titles table.

SQL

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

Result

The result of this is the same as that shown above using ANY.

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

match_expression

This is any valid SQL Server expression of character string data type.

Pattern

This syntax is the pattern to search for in match_expression, and can include the valid SQL Server wildcard characters shown in Table 2-12.

Table 2-12. Valid Wildcard Characters

LIKE Wildcard

Matches

% (percent)

Any string of zero or more characters

_ (underscore)

Exactly one of any character

[ ]

Any single among those listed inside the [ ]. E.g., a, b, c or d will match [abcd] or its shortcut form [a-d].

[^ ]

Any single character NOT listed in the [ ].

E.g., any character other than a, b, c or d will match [^abcd] or [^a-d].

Note: The [ ] and [^ ] notations are not included in the ANSI SQL standard and are thus not guaranteed to be portable. The rest of the LIKE notation does comply with SQL-92 and 99.

escape_character

This is any valid SQL Server expression of any of the character string data types. The escape_character has no default and may consist of only one character.

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.

Example: List all authors whose last names begin with Gr.

SQL

SELECT au_lname FROM authors WHERE au_lname LIKE 'Gr%'
                  

Result


au_lname
-------------------
Green
Greene
Gringlesby

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.

Example:

SQL

SELECT lname, fname, lname + ', ' + fname FROM
                     graphics/ccc.gif employee
                  
 

Result


lname           fname
-------         -------         ----------------
Cruz            Aria            Cruz, Aria

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

Positive

Negative

Bitwise NOT

+

~

Notes:

Positive (+) means the numeric value is positive.

Negative (–) means the numeric value is negative.

Bitwise NOT returns the ones complement of the number.

Example

SQL

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

Result

-2

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

Operator

+ (Positive), – (Negative), ~ (Bitwise NOT)

* (Multiply), / (Division), % (Modulo)

+ (Add), (+ Concatenate), – (Subtract)

=, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)

^ (Bitwise Exclusive OR), & (Bitwise AND), | (Bitwise OR)

NOT

AND

ALL, ANY, BETWEEN, IN, LIKE, OR, SOME

= (Assignment)

  • + Share This
  • 🔖 Save To Your Account