Specify a boolean value.

Syntax

<boolean value expression> ::=

 |

<boolean term> ::=

 | AND

<boolean factor> ::= [ NOT ] <boolean test>

<boolean test> ::= <boolean primary> [ IS [ NOT ] <truth value> ]

<truth value> ::=

 | | TRUE FALSE UNKNOWN

<boolean primary> ::=

 |

<boolean predicand> ::=

Notes

 ▪ The declared type of a boolean primary shall be boolean.

 ▪ The AND and OR boolean operators are used to combine two or more predicates.

 ▪ The NOT operator inverts the meaning of a boolean expression.

 ▪ The boolean test:

 IS NOT

 is equivalent to:

 NOT IS

Truth tables

Since SQL supports null values and predicates whose value can be UNKNOWN, a three-valued logic is used to evaluate boolean value expressions. The rules of this logic are summarized in the following tables:

Truth Table for the AND Boolean Operator

 AND True False Unknown

¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

 True False Unknown True False Unknown False False False Unknown False Unknown

_____________________________________________________________________________

Truth Table for the OR Boolean Operator

 OR True False Unknown

¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

 True False Unknown True True True True False Unknown True Unknown Unknown

_____________________________________________________________________________

Truth Table for the NOT Boolean Operator

 NOT

¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

 True False Unknown False True Unknown

_____________________________________________________________________________

Truth Table for the IS Boolean Operator

 IS True False Unknown

¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

 True False Unknown True False False False True False False False True

_____________________________________________________________________________

Examples

 1) The following example selects students from Newport, CA:

 SELECT studentID, studentName, city, state
 FROM students
 WHERE city = 'Newport' AND state = 'CA'

 2) The following example selects students from Newport, CA and RI:

 SELECT studentID, studentName, city, state
 FROM students
 WHERE city = 'Newport' AND ( state = 'CA' OR state = 'RI' )

 3) The following example selects students from Massachusetts and Newport, CA:

 SELECT studentID, studentName, city, state
 FROM students
 WHERE ( city = 'Newport' AND state = 'CA' ) OR state = 'MA'

 4) The following example selects all students except those from California and Boston, MA:

 SELECT studentID, studentName, city, state
 FROM students
 WHERE NOT ( ( city = 'Boston' AND state = 'MA' ) OR state = 'CA' )

Conformance

 SQL:2003 standard - - Feature T031 "BOOLEAN data type" Feature F571 "Truth value tests"