Section 6.2. Parentheses in SQL Expressions

6.2. Parentheses in SQL Expressions

As queries get longer, they can become very ambiguous to humans without the appropriate use of parentheses. In programming languages like C, you can write a statement like this:

    x = y + z * w

How is this statement computed? The answer depends on precedence rules. Usually in programming languages (and in SQL), clauses in parentheses have the highest precedence. The authors of this book advocate fully parenthesized expressions for three reasons:

It makes the expression easier to debug.
It tells anyone else who looks at your expression that it is written as you intended, because you explicitly and unambiguously wrote the expression in a fully parenthesized way.
There is no guarantee that another SQL language will behave like the one you learned.

In SQL, the precedence problem occurs when AND and OR are used in the same query. For example, what does the following query request? Does AND or OR have precedence or is the rule "left to right"?

    SELECT  *
    FROM    Student
    WHERE   class = 3 OR class = 4 AND stno < 100

This query produces the following 12 rows of output:

    STNO   SNAME                MAJOR CLASS  BDATE
    ------ -------------------- ----- ------ -----------------------
    3      Mary                 COSC  4      1978-07-16 00:00:00
    13     Kelly                MATH  4      1980-08-12 00:00:00
    20     Donald               ACCT  4      1977-10-15 00:00:00
    24     Chris                ACCT  4      1978-02-12 00:00:00
    31     Jake                 COSC  4      1978-02-12 00:00:00
    49     Susan                ENGL  3      1980-03-11 00:00:00
    62     Monica               MATH  3      1980-10-14 00:00:00
    122    Phoebe               ENGL  3      1980-04-15 00:00:00
    131    Rachel               ENGL  3      1980-04-15 00:00:00
    143    Cramer               ENGL  3      1980-04-15 00:00:00
    151    Losmith              CHEM  3      1981-01-15 00:00:00
    160    Gus                  ART   3      1978-10-15 00:00:00

    (12 row(s) affected)

The point is that you do not have to know the precedence rules to write an unambiguous expression. If you use parentheses appropriately, you make the expression clear and unambiguous. Consider the following examples. If we type the following:

    SELECT  *
    FROM    Student
    WHERE   class = 3 OR (class = 4 AND stno < 100)

we get the following 12 rows of output:

    STNO   SNAME                MAJOR CLASS  BDATE
    ------ -------------------- ----- ------ -----------------------
    3      Mary                 COSC  4      1978-07-16 00:00:00
    13     Kelly                MATH  4      1980-08-12 00:00:00
    20     Donald               ACCT  4      1977-10-15 00:00:00
    24     Chris                ACCT  4      1978-02-12 00:00:00
    31     Jake                 COSC  4      1978-02-12 00:00:00
    49     Susan                ENGL  3      1980-03-11 00:00:00
    62     Monica               MATH  3      1980-10-14 00:00:00
    122    Phoebe               ENGL  3      1980-04-15 00:00:00
    131    Rachel               ENGL  3      1980-04-15 00:00:00
    143    Cramer               ENGL  3      1980-04-15 00:00:00
    151    Losmith              CHEM  3      1981-01-15 00:00:00
    160    Gus                  ART   3      1978-10-15 00:00:00

    (12 row(s) affected)

The preceding query has the parentheses around the AND clause, the result of which is that the AND is performed first. The following query has the parentheses around the OR clause, meaning that the OR is performed first:

    SELECT  *
    FROM    Student
    WHERE   (class = 3 OR class = 4) AND stno < 100

This query results in the following seven rows of output:

    STNO   SNAME                MAJOR CLASS  BDATE
    ------ -------------------- ----- ------ -----------------------
    3      Mary                 COSC  4      1978-07-16 00:00:00
    13     Kelly                MATH  4      1980-08-12 00:00:00
    20     Donald               ACCT  4      1977-10-15 00:00:00
    24     Chris                ACCT  4      1978-02-12 00:00:00
    31     Jake                 COSC  4      1978-02-12 00:00:00
    49     Susan                ENGL  3      1980-03-11 00:00:00
    62     Monica               MATH  3      1980-10-14 00:00:00

    (7 row(s) affected)

As the preceding two query statements demonstrate, appropriate placement of parentheses eliminates any ambiguity in queries that contain both AND and OR.

6.2.1. Operator Precedence

In SQL Server , when complex expressions use multiple operators, precedence rules determine the sequence in which the operations are performed. The order of execution can significantly affect the resulting value (as you saw in the example in the preceding section). Although we can usually control precedence with parentheses, it is important to learn, or have at least a reference, to the order of precedence.

Operators have the following precedence (the following list is shown from the highest level of precedence to the lowest level of precedence):

    * (multiply), / (divide), % (modulo)
    + (add), + (concatenate), - (subtract)
    =, >, <, >=, <=, != (not equal to), !>, !<
    NOT
    AND
    BETWEEN, IN, LIKE, OR
    = (assignment)

6.2.2. Data Type Precedence

When an operator combines two expressions of different data types, the data type precedence rules specify which data type is converted to the other. The data type with the lower precedence is converted to the data type with the higher precedence. Here we list the precedence order for SQL Server data types (again shown from the highest level of precedence to the lowest level of precedence):

SQL_VARIANT
DATETIME
SMALLDATETIME
FLOAT
REAL
DECIMAL
MONEY
SMALLMONEY
BIGINT
INT
SMALLINT
TINYINT
BIT
NTEXT
TEXT
IMAGE
UNIQUEIDENTIFIER
NVARCHAR
NCHAR
VARCHAR
CHAR
BINARY

This order means that if a number of an INT data type is multiplied to a number that is of a FLOAT data type, the result would be a FLOAT data type. To illustrate something like this, we will use the Employee table that we created in the last chapter. The design of the Employee table is shown in Figure 6-2.

Note that the data type of the hours column is SMALLINT. If we multiply this column (hours) by 0.75 (a FLOAT), we get a FLOAT data type in the result set, as shown here:

    SELECT  names, hours, 'Hours * .75' = hours *  .75
    FROM    Employee

Figure 6-2. Table definition of the Employee table

This query gives us:

    names                hours  Hours * .75
    -------------------- ------ --------------------------------------
    Sumon Bagui          40     30.00
    Sudip Bagui          30     22.50
    Priyashi Saha        NULL   NULL
    Ed Evans             10     7.50
    Genny George         40     30.00

    (5 row(s) affected)