Logical Operators

NOT Unary Operator

You can use the NOT unary logical operator to specify the logical inverse of a condition, as shown in the following examples:

SELECT Name,Age FROM Sample.Person
WHERE NOT Age>21
ORDER BY Age

SELECT Name,Age FROM Sample.Person
WHERE NOT Name %STARTSWITH('A')
ORDER BY Name

You can place the NOT operator before the condition (as shown above). Or you can place NOT immediately before a single-character operator; for example, NOT<, NOT[, and so forth. Note that there must be no space between NOT and the single-character operator it inverts.

AND and OR Operators

You can use the AND and OR logical operators between two operands in a series of two or more conditions. These logical operators can be specified by keyword or symbol:

Spaces are not required (though recommended for readability) between a symbol operator and its operand. Spaces are required before and after a keyword operator.

These logical operators can be used with the NOT unary logical operator, such as the following: WHERE Age<65 & NOT Age=21.

The following two examples use logical operators to schedule an assessment based on age. People between the ages of 20 and 40 are assessed every three years, people from 40 to 64 are assessed every two years, and those 65 and over are assessed every year. The examples give identical results; the first example uses keywords, the second uses symbols:

SELECT Name,Age FROM Sample.Person
WHERE Age>20
      AND Age<40 AND (Age # 3)=0 
      OR Age>=40 AND (Age # 2)=0 
      OR Age>=65
ORDER BY Age

SELECT Name,Age FROM Sample.Person
WHERE Age>20
      & Age<40 & (Age # 3)=0 
      ! Age>=40 & (Age # 2)=0 
      ! Age>=65
ORDER BY Age

Logical operators can be grouped using parentheses. This establishes a grouping level; evaluation proceeds from the lowest grouping level to the highest. In the first of the following examples, the AND condition is applied only to the second OR condition. It returns persons of any age from MA, and persons with age less than 25 from NY:

SELECT Name,Age,Home_State FROM Sample.Person
WHERE Home_State='MA' OR Home_State='NY' AND Age < 25
ORDER BY Age

Using parentheses to group conditions gives a different result. The following example returns persons from MA or NY whose age is less than 25:

SELECT Name,Age,Home_State FROM Sample.Person
WHERE (Home_State='MA' OR Home_State='NY') AND Age < 25
ORDER BY Age

• SQL execution uses short-circuit logic. If a condition fails, the remaining AND conditions will not be tested. If a condition succeeds, the remaining OR conditions will not be tested.

• However, because SQL optimizes WHERE clause execution, the order of execution of multiple conditions (at the same grouping level) cannot be predicted and should not be relied upon.