Single-Table Queries in Microsoft SQL Server 2012

7/15/2012

Contents

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.