Specify a boolean value.

 

Syntax

 

<boolean value expression> ::=

 

<boolean term> ::=

 

<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:



 



<boolean primary> IS NOT <truth value>




 


is equivalent to:



 



NOT <boolean primary> IS <truth value>




 

 

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"