# Single-Table Queries in Microsoft SQL Server 2012

- 7/15/2012

## Predicates and Operators

T-SQL has language elements in which predicates can be
specified—for example, query filters such as *WHERE*
and *HAVING, CHECK* constraints, and others. Remember
that predicates are logical expressions that evaluate to *TRUE,
FALSE*, or *UNKNOWN*. You can combine
predicates by using logical operators such as *AND*
and *OR*. You can also involve other types of
operators, such as comparison operators, in your expressions.

Examples of predicates supported by T-SQL include *IN,
BETWEEN*, and *LIKE*. The
*IN* predicate allows you to check whether a value,
or scalar expression, is equal to at least one of the elements in a set.
For example, the following query returns orders in which the order ID is
equal to 10248, 10249, or 10250.

SELECT orderid, empid, orderdate FROM Sales.Orders WHERE orderid IN(10248, 10249, 10250);

The *BETWEEN* predicate allows you to check
whether a value is in a specified range, inclusive of the two specified
boundary values. For example, the following query returns all orders in
the inclusive range 10300 through 10310.

SELECT orderid, empid, orderdate FROM Sales.Orders WHERE orderid BETWEEN 10300 AND 10310;

The *LIKE* predicate allows you to check
whether a character string value meets a specified pattern. For
example, the following query returns employees whose last names start
with D.

SELECT empid, firstname, lastname FROM HR.Employees WHERE lastname LIKE N'D%';

Later in this chapter, I’ll elaborate on pattern matching and the
*LIKE* predicate.

Notice the use of the letter *N* to prefix the
string *‘D%’*; it stands for
*National* and is used to denote that a character
string is of a Unicode data type (*NCHAR* or *NVARCHAR*), as opposed to a regular
character data type (*CHAR* or
*VARCHAR*). Because the data type of the
*lastname* attribute is
*NVARCHAR(40)*, the letter *N* is
used to prefix the string. Later in this chapter, in the section “Working with Character Data,” I elaborate on the treatment
of character strings.

T-SQL supports the following comparison operators: =, >, <, >=, <=, <>, !=, !>, !<, of which the last three are not standard. Because the nonstandard operators have standard alternatives (such as <> instead of !=), I recommend that you avoid the use of the nonstandard operators. For example, the following query returns all orders placed on or after January 1, 2008.

SELECT orderid, empid, orderdate FROM Sales.Orders WHERE orderdate >= '20080101';

If you need to combine logical expressions, you can use the
logical operators *OR* and
*AND*. If you want to negate an expression, you can
use the *NOT* operator. For example, the following query returns orders
that were placed on or after January 1, 2008, and that were handled by
one of the employees whose ID is 1, 3, or 5.

SELECT orderid, empid, orderdate FROM Sales.Orders WHERE orderdate >= '20080101' AND empid IN(1, 3, 5);

T-SQL supports the four obvious arithmetic operators: +, –, *, and /, and also the %
operator (modulo), which returns the remainder of integer division. For
example, the following query calculates the net value as a result of
arithmetic manipulation of the *quantity, unitprice*,
and *discount* attributes.

SELECT orderid, productid, qty, unitprice, discount, qty * unitprice * (1 - discount) AS val FROM Sales.OrderDetails;

Note that the data type of a scalar expression involving two operands is determined in
T-SQL by the higher of the two in terms of data type precedence. If both
operands are of the same data type, the result of the expression is of
the same data type as well. For example, a division between two integers
(*INT*) yields an integer. The expression 5/2 returns
the integer 2 and not the numeric 2.5. This is not a problem when you
are dealing with constants, because you can always specify the values as
numeric ones with a decimal point. But when you are dealing with, say,
two integer columns, as in *col1/col2*, you need to
cast the operands to the appropriate type if you want the calculation to
be a numeric one: *CAST(col1 AS NUMERIC(12, 2))/CAST(col2 AS
NUMERIC(12, 2))*. The data type *NUMERIC(12,
2)* has a precision of 12 and a scale of 2, meaning that it
has 12 digits in total, 2 of which are after the decimal point.

If the two operands are of different types, the one with the lower
precedence is promoted to the one that is higher. For
example, in the expression 5/2.0, the first operand is
*INT* and the second is *NUMERIC*.
Because *NUMERIC* is considered higher than
*INT*, the *INT* operand 5 is
implicitly converted to the *NUMERIC* 5.0 before the
arithmetic operation, and you get the result 2.5.

You can find the precedence order among types in SQL Server Books Online under “Data Type Precedence.”

When multiple operators appear in the same expression, SQL Server evaluates them based on operator precedence rules. The following list describes the precedence among operators, from highest to lowest:

( ) (Parentheses)

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

+ (Positive), – (Negative), + (Addition), + (Concatenation), – (Subtraction)

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

*NOT**AND**BETWEEN, IN, LIKE, OR*= (Assignment)

For example, in the following query, *AND* has
precedence over *OR*.

SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE custid = 1 AND empid IN(1, 3, 5) OR custid = 85 AND empid IN(2, 4, 6);

The query returns orders that were either “placed by customer 1 and handled by employees 1, 3, or 5” or “placed by customer 85 and handled by employees 2, 4, or 6.”

Parentheses have the highest precedence, so they give you full control. For the sake of other people who need to review or maintain your code and for readability purposes, it’s a good practice to use parentheses even when they are not required. The same is true for indentation. For example, the following query is the logical equivalent of the previous query, only its logic is much clearer.

SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE (custid = 1 AND empid IN(1, 3, 5)) OR (custid = 85 AND empid IN(2, 4, 6));

Using parentheses to force precedence with logical operators is similar to using parentheses with arithmetic operators. For example, without parentheses in the following expression, multiplication precedes addition.

SELECT 10 + 2 * 3;

Therefore, this expression returns 16. You can use parentheses to force the addition to be calculated first.

SELECT (10 + 2) * 3;

This time, the expression returns 36.