Logical operators

<< Click to Display Table of Contents >>

Navigation:  Local SQL > Data Manipulation Language > Operators >

Logical operators

Previous pageReturn to chapter overviewNext page
hmtoggle_plus1See also

Connect multiple predicates.

[NOT] predicate OR [NOT] predicate

[NOT] predicate AND [NOT] predicate

Description

Use the logical operators OR and AND to connect two predicates in a single WHERE clause. This allows the table to be filtered based on multiple conditions. Logical operators compare the boolean result of two predicate comparisons, each producing a boolean result. If OR is used, either of the two predicate comparisons can result on a TRUE value for the whole expression to evaluate to TRUE. If AND is used, both predicate comparisons must evaluate to TRUE for the whole expression to be TRUE; if either is FALSE, the whole is FALSE. In the statement below, if only one of the two predicate comparisons is TRUE (reservdate < "1/31/1998" or paid = TRUE), the row will be included in the query result set.

SELECT *

FROM Reservations

WHERE ((ReserveDate < "1/31/1998") OR (Paid = TRUE))

Logical operator comparisons are performed in the order of precedence: OR and then AND. To perform a comparison out of the normal order of precedence, use parentheses around the comparison to be performed first. The SELECT statement below retrieves all rows where the SHAPE column is "round" and the COLOR "blue". It also returns those rows where the COLOR column is "red", regardless of the value in the SHAPE column (such as "triangle"). It would not return rows where the SHAPE is "round" and the COLOR anything but "blue" or where the COLOR is "blue" and the SHAPE anything but "round".

SELECT Shape, Color, Cost

FROM Objects

WHERE ((Shape = "round") AND (Color = "blue")) OR

 (Color = "red")

Without the parentheses, the default order of precedence is used and the logic changes. The statement below, a variation on the above statement, would return rows where the SHAPE is "square" and the COLOR is "blue". It would also return rows where the SHAPE is "square" and the COLOR is "red". But unlike the preceding statement, it would not return rows where the COLOR is "red" and the SHAPE "triangle".

SELECT Shape, Color, Cost

FROM Objects

WHERE Shape = "round" AND Color = "blue" OR

Color = "red"

Use the NOT operator to negate the boolean result of a comparison. In the statement below, only those rows where the PAID column contains a FALSE value are retrieved.

SELECT *

FROM Reservations

WHERE (NOT (Paid = "TRUE"))