Chapter 9 – Boolean values¶
Note
You are reading a digital copy of SQL-99 Complete, Really, a book that documents the SQL-99 standard.
For more information specific to CrateDB, check out the CrateDB Reference.
In SQL, a Boolean value – either TRUE
, FALSE
or UNKNOWN
– is a
truth value. A Boolean value may be a <literal>, the value of a parameter or a
host language variable or the result of any expression or argument (including a
possibly qualified <Column name> or the result of an SQL predicate or search
condition) that evaluates to a truth value. Boolean values are stored in the
Boolean <data type>: BOOLEAN
.
Table of Contents
<Boolean literal>s¶
A <Boolean literal> is one of these three words:
TRUE
FALSE
UNKNOWN
Its <data type> is BOOLEAN
. A <Boolean literal> of TRUE
represents the
truth value TRUE
, a <Boolean literal> of FALSE
represents the truth
value FALSE
and a <Boolean literal> of UNKNOWN
represents the truth
value UNKNOWN
.
If you want to restrict your code to Core SQL, don’t use <Boolean literal>s.
Boolean <data type>s¶
A Boolean <data type> is defined by a descriptor that contains one piece of
information – the <data type>’s name: BOOLEAN
.
BOOLEAN¶
The required syntax for a BOOLEAN
<data type> specification is as follows.
BOOLEAN <data type> ::=
BOOLEAN
BOOLEAN
defines a set of truth values: either TRUE
, FALSE
or
UNKNOWN
(as the null value).
The SQL Standard doesn’t differentiate between BOOLEAN
’s null value (that
is, UNKNOWN
) and the UNKNOWN
truth value that is returned by an SQL
predicate, search condition or by any argument or expression that returns a
Boolean value – it allows both to be used interchangeably to mean the same
thing. Warning: by saying that UNKNOWN
and NULL
are the same thing, one
is saying that the answers “I don’t know” and “I know that the data is missing”
are the same thing. The drafters of the SQL Standard apparently forgot the
distinction, and they have been justly criticized for this error.
If you want to restrict your code to Core SQL, don’t define any BOOLEAN
<data type>s.
Now that we’ve described SQL’s Boolean <data type>, let’s look at some example SQL statements that put it to use.
These SQL statements make a Table with three Boolean Columns, insert a row, then search for a pair of equal Column values.
CREATE TABLE Logicals (
boolean_1 BOOLEAN,
boolean_2 BOOLEAN,
boolean_3 BOOLEAN);
INSERT INTO Logicals (
boolean_1,
boolean_2,
boolean_3)
VALUES (TRUE,FALSE,UNKNOWN);
SELECT boolean_1
FROM Logicals
WHERE boolean_1 = boolean_3;
Boolean Operations¶
A Boolean value is compatible with, and comparable to, all other Boolean values
and all SQL truth values (for example, the truth values returned by an SQL
predicate) – that is, all truth values are mutually comparable and mutually
assignable. Truth values may not be directly compared with, or directly
assigned to, any other <data type> class, though implicit type conversions can
occur in expressions, SELECT
s, INSERT
s, DELETE
s and
UPDATE
s. Explicit truth value type conversions can be forced with the
CAST
operator.
CAST¶
In SQL, CAST
is a scalar operator that converts a given scalar value to a
given scalar <data type>. The required syntax for the CAST
operator is as
follows.
CAST (<cast operand> AS <cast target>)
<cast operand> ::= scalar_expression
<cast target> ::= <Domain name> | <data type>
The CAST
operator converts values of a source <data type> into values of a
target <data type>, where each <data type> is an SQL pre-defined <data type>
(data conversions between UDTs are done with a user-defined cast). The source
<data type>, or <cast operand>, can be any expression that evaluates to a
single value. The target <data type>, or <cast target>, is either an SQL
predefined <data type> specification or the name of a Domain whose defined
<data type> is the SQL predefined <data type> that you want to convert the
value of “scalar_expression” into. (If you use CAST (... AS <Domain name>)
,
your current <AuthorizationID> must have the USAGE
Privilege on that
Domain.)
It isn’t, of course, possible to convert the values of every <data type> into the values of every other <data type>. For Boolean values, the rules are:
CAST
(NULL AS
<data type>) andCAST
(Boolean_source_is_a_null_value AS
<data type>) both result in aCAST
result ofNULL
.You can
CAST
a Boolean source to these targets: fixed length character string, variable length character string,CLOB
,NCLOB
and Boolean. You can alsoCAST
a Boolean source to a UDT target or a <reference type> target if a user-defined cast exists for this purpose and your current <AuthorizationID> has theEXECUTE
Privilege on that user-defined cast.
When you CAST
a Boolean value to a Boolean target, the result of the
CAST
is the source value.
When you CAST
a Boolean value to a fixed length character string target,
there are four possibilities:
The source value is
TRUE
and the fixed length of the target is at least four characters. In this case, the result of theCAST
is'TRUE'
, padded on the right with spaces, if necessary, to make it the exact fixed length of the target.The source value is
FALSE
and the fixed length of the target is at least five characters. In this case, the result of theCAST
is'FALSE'
, padded on the right with spaces, if necessary, to make it the exact fixed length of the target.The source value is
UNKNOWN
. As already stated, the result of theCAST
isNULL
.The fixed length of the target is less than the length of the source value. In this case, the CAST will fail: your DBMS will return the
SQLSTATE error 22018 "data exception-invalid character value for cast"
.
When you CAST
a Boolean value to a variable length character string,
CLOB
or NCLOB
target, there are four possibilities:
The source value is TRUE and the maximum length of the target is at least four characters. In this case, the result of the
CAST
is'TRUE'
.The source value is
FALSE
and the fixed length of the target is at least five characters. In this case, the result of theCAST
is'FALSE'
.The source value is
UNKNOWN
. As already stated, the result of theCAST
isNULL
.The maximum length of the target is less than the length of the source value. In this case, the
CAST
will fail: your DBMS will return theSQLSTATE error 22018 "data exception-invalid character value for cast"
.
[Obscure Rule] The result of a CAST to a character string target has the COERCIBLE coercibility attribute; its Collation is the default Collation for the target’s Character set.
When you CAST a Boolean value to a UDT or a <reference type> target, your DBMS invokes the user defined cast routine, with the source value as the routine’s argument. The CAST result is the value returned by the user defined cast.
If you want to restrict your code to Core SQL, don’t use <Domain name> as a CAST target: CAST only to a <data type>.
Assignment¶
In SQL, the TRUE
and FALSE
truth values may be assigned to any Boolean
target and the UNKNOWN
truth value may be assigned to any Boolean target
that isn’t constrained by a NOT NULL
Constraint.
Comparison¶
SQL provides the usual scalar comparison operators – = and <> and < and <= and
> and >= – to perform operations on truth values. All of them will be
familiar; there are equivalent operators in other computer languages. In SQL,
TRUE
is greater than FALSE
. If any of the comparands are the
UNKNOWN
truth value or are NULL
, the result of the operation is
UNKNOWN
. For example:
TRUE = {result is FALSE}
returns FALSE
.
TRUE <> {result is NULL}
returns UNKNOWN
.
SQL also provides three quantifiers – ALL
, SOME
, ANY
– which you
can use along with a comparison operator to compare a truth value with the
collection of truth values returned by a <table subquery>. Place the quantifier
after the comparison operator, immediately before the <table subquery>. For
example:
SELECT occurrence_boolean
FROM Boolean_Example
WHERE occurrence_boolean = ALL (
SELECT char_column
FROM Table_1
WHERE char_column LIKE '%e');
ALL
returns TRUE
either (a) if the collection is an empty set (i.e.:
if it contains zero rows) or (b) if the comparison operator returns TRUE
for every value in the collection. ALL returns FALSE
if the comparison
operator returns FALSE
for at least one value in the collection.
SOME
and ANY
are synonyms. They return TRUE
if the comparison
operator returns TRUE
for at least one value in the collection. They return
FALSE
either (a) if the collection is an empty set or (b) if the
comparison operator returns FALSE
for every value in the collection. (The
search condition = ANY (collection)
is equivalent to IN (collection)
.)
Other Operations¶
With SQL, you have several other operations that you can perform on truth values, or on other values to get a truth value result.
Boolean operators¶
SQL provides the usual scalar Boolean operators – AND
and OR
and
NOT
and IS
- - to perform operations on Boolean operands. Each returns
a Boolean result, or truth value. All of them will be familiar; there are
equivalent operators in other computer languages. If any of the operands are
the UNKNOWN
truth value or are NULL
, the result of the operation is
UNKNOWN
. Here is the syntax allowed for Boolean expressions:
<boolean value expression> ::=
<boolean term> |
<boolean value expression> OR <boolean term>
<boolean term> ::=
[ NOT ] <boolean test> |
<boolean term> AND [ NOT ] <boolean test>
<boolean test> ::=
boolean_argument [ IS [ NOT ] {TRUE | FALSE | UNKNOWN} ]
A Boolean expression operates on one or more operands that evaluate to a truth
value – that is, the boolean_argument
shown in this syntax diagram is
either a <Boolean literal>, the value of a parameter or a host language
variable or the result of any expression or argument (including a possibly
qualified <Column name> or – most often – the result of an SQL predicate or
search condition) that evaluates to a truth value. The result is also a truth
value, derived by applying the given Boolean operator(s) to the
boolean_argument
result.
IS
is a monadic Boolean operator. It tests for a condition: is the result
of the expression TRUE
, is it FALSE
or is it UNKNOWN
? You use the
<Boolean test> to influence a search condition result, since its effect is to
change a Boolean value (which is TRUE
or FALSE
or UNKNOWN
) to
either TRUE
or FALSE
. For example, consider these SQL statements, which
create a Table that contains four rows:
CREATE TABLE Boolean_Test (
column_1 SMALLINT);
INSERT INTO Boolean_Test (column_1)
VALUES (5);
INSERT INTO Boolean_Test (column_1)
VALUES (NULL);
INSERT INTO Boolean_Test (column_1)
VALUES (0);
INSERT INTO Boolean_Test (column_1)
VALUES (10);
Row 1 of the Table BOOLEAN_TEST
contains 5, row 2 contains NULL
, row 3
contains 0 and row 4 contains 10. Normally, of course, a search for equality
doesn’t find NULL
s – so the result of this SQL statement:
SELECT column_1
FROM Boolean_Test
WHERE column_1 = 5;
is row 1 and the result of this SQL statement:
SELECT column_1
FROM Boolean_Test
WHERE column_1 <> 5;
is row 3 and row 4. If you add a <Boolean test>, though, you can override the comparison’s usual result. Thus, the result of this SQL statement:
SELECT column_1
FROM Boolean_Test
WHERE (column_1 = 5 IS UNKNOWN);
is row 2 – it returns the rows where the search condition is UNKNOWN
,
rather than the rows where it is TRUE
. The result of this SQL statement:
SELECT column_1
FROM Boolean_Test
WHERE (column_1 = 5 IS FALSE);
is row 3 and row 4 – it returns only the rows where the search condition is
FALSE
. The result of this SQL statement:
SELECT column_1
FROM Boolean_Test
WHERE (column_1 = 5 IS TRUE);
is row 1 – it returns the rows where the search condition is TRUE
. Since
this is the same result you’d get without the <Boolean test>, adding it is
redundant. Finally, the result of this SQL statement:
SELECT column_1
FROM Boolean_Test
WHERE (column_1 = 5 IS NOT FALSE);
is row 1 and row 2 – it returns the rows where the search condition is either
TRUE
or UNKNOWN
. Table 9-1 shows how the result of a Boolean IS
operation is determined.
Table 9.1 Truth values for the Boolean IS operator
If a Boolean value
is:
|
… and the operator is:
|
… then the result is:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
NOT
is a monadic Boolean operator. It negates the result of a Boolean
expression (except in the case of NULL
s) – that is:
NOT ( TRUE )
returnsFALSE
.NOT ( FALSE )
returnsTRUE
.NOT ( UNKNOWN )
returnsUNKNOWN
.
AND
is a dyadic Boolean operator. It increases the number of conditions
that must be met by a value to be included in a search: the result is TRUE
only if both conditions are TRUE
. For example, the result of this SQL
statement:
SELECT column_1
FROM Boolean_Test
WHERE (column_1 > 0 AND column_1 < 10);
is row 1. Table 9.2 shows how the result of a Boolean AND
operation is
determined.
Table 9.2 Truth for the Boolean AND
operator
If the first Boolean
value is:
|
… and the second
Boolean value is:
|
… then the result is:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
OR
is a dyadic Boolean operator. It decreases the number of conditions that
must be met by a value to be included in a search: the result is TRUE
if
either condition is TRUE
. For example, the result of this SQL statement:
SELECT column_1
FROM Boolean_Test
WHERE (column_1 > 0 OR column_1 < 10);
is row 1 and row 3. Table 9.3 shows how the result of a Boolean OR
operation is determined.
If the first Boolean
value is:
|
… and the second
Boolean value is:
|
… then the result is:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The precedence of the Boolean operators and their effect on Boolean values is as follows:
Precedence |
Operator |
Effect on Boolean value(s) |
|
overrides normal result |
|
|
negates result |
|
|
combines, with logical AND |
|
|
combines, with logical inclusive OR |
The precedence shown determines evaluation order, unless you use parentheses to
force a different order. Although SQL’s three-valued logic can complicate
things if we use contrived and unlikely examples, the normal situation is
straightforward for any speaker of a human tongue. When we hear the English
expression “Martians are vicious and dishonest but not stupid”, we know we
could search them with the SQL expression x = 'vicious' AND x = 'dishonest'
AND x <> 'stupid'
. The correct application of the Boolean operators turns out
to be an intuitive calculation for most people. The most common error is to
forget what the operator precedence is, and that can be corrected easily:
always use parentheses if the search condition contains two different Boolean
operators.
If you want to restrict your code to Core SQL, don’t use the optional truth
value Boolean test (i.e.: don’t use the constructs boolean_argument IS
TRUE
, boolean_argument IS FALSE
or boolean_argument IS UNKNOWN
) and
don’t use boolean_argument
unless it’s an SQL predicate or it’s enclosed in
parentheses.
Scalar Operations¶
SQL provides no scalar functions that return or operate on a Boolean value.
Set Functions¶
SQL provides eight set functions that operate on Booleans: EVERY
, ANY
,
SOME
, COUNT
, MAX
, MIN
and GROUPING
. We’ll discuss them all
in our chapter on set functions.
Predicates¶
Every SQL predicate returns a Boolean value. Since none of them operate strictly on truth values, we won’t discuss them here. Look for them in our chapters on search conditions and the various other <data type>s.