Chapter 9 – Boolean values¶

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;

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>) and CAST (Boolean_source_is_a_null_value AS <data type>) both result in a CAST result of NULL.
• You can CAST a Boolean source to these targets: fixed length character string, variable length character string, CLOB, NCLOB and Boolean. You can also CAST 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 the EXECUTE 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:

1. The source value is TRUE and the fixed length of the target is at least four characters. In this case, the result of the CAST is 'TRUE', padded on the right with spaces, if necessary, to make it the exact fixed length of the target.
2. The source value is FALSE and the fixed length of the target is at least five characters. In this case, the result of the CAST is 'FALSE', padded on the right with spaces, if necessary, to make it the exact fixed length of the target.
3. The source value is UNKNOWN. As already stated, the result of the CAST is NULL.
4. 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:

1. 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'.
2. The source value is FALSE and the fixed length of the target is at least five characters. In this case, the result of the CAST is 'FALSE'.
3. The source value is UNKNOWN. As already stated, the result of the CAST is NULL.
4. 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 the SQLSTATE 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 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} ]

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);

SELECT column_1
FROM   Boolean_Test
WHERE  column_1 = 5;

SELECT column_1
FROM   Boolean_Test
WHERE  column_1 <> 5;

SELECT column_1
FROM   Boolean_Test
WHERE  (column_1 = 5 IS UNKNOWN);

SELECT column_1
FROM   Boolean_Test
WHERE (column_1 = 5 IS FALSE);

SELECT column_1
FROM   Boolean_Test
WHERE (column_1 = 5 IS TRUE);

SELECT column_1
FROM   Boolean_Test
WHERE (column_1 = 5 IS NOT FALSE);

SELECT column_1
FROM   Boolean_Test
WHERE (column_1 > 0 AND column_1 < 10);

SELECT column_1
FROM   Boolean_Test
WHERE (column_1 > 0 OR column_1 < 10);