The SQL:2008 compliant BOOLEAN data type (8 bits) comprises the distinct truth values TRUE and FALSE. Unless prohibited by a NOT NULL constraint, the BOOLEAN data type also supports the truth value UNKNOWN as the null value. The specification does not make a distinction between the NULL value of this data type, and the truth value UNKNOWN that is the result of an SQL predicate, search condition, or Boolean value expression: they may be used interchangeably to mean exactly the same thing.

    As with many programming languages, the SQL BOOLEAN values can be tested with implicit truth values. For example, field1 OR field2 and NOT field1 are valid expressions.

    3.6.1.1 The IS Operator

    Predicates can use the operator Boolean IS [NOT] for matching. For example, field1 IS FALSE, or field1 IS NOT TRUE.

    Note

    • Equivalence operators (=, !=, <> and so on) are valid in all comparisons.

    3.6.1.2 BOOLEAN Examples

    1. Inserting and selecting

      1. CREATE TABLE TBOOL (ID INT, BVAL BOOLEAN);
      2. COMMIT;
      3.  
      4. INSERT INTO TBOOL VALUES (1, TRUE);
      5. INSERT INTO TBOOL VALUES (2, 2 = 4);
      7. COMMIT;
      8.  
      9.           ID    BVAL
      10. ============ =======
      11.            1 <true>
      12.            2 <false>
      13.            3 <null>
      1. SELECT * FROM TBOOL WHERE BVAL;
      2.           ID    BVAL
      3. ============ =======
      4.            1 <true>

    3. Test for FALSE value

    4. Test for UNKNOWN value

      1. SELECT * FROM TBOOL WHERE BVAL IS UNKNOWN;
      2.           ID    BVAL
      3.            3 <null>

    5. Boolean values in SELECT list

      1. SELECT ID, BVAL, BVAL AND ID < 2
      2.   FROM TBOOL;
      3.           ID    BVAL
      4. ============ ======= =======
      6.            2 <false> <false>
      7.            3 <null>  <false>

    6. PSQL declaration with start value

    7. Valid syntax, but as with a comparison with NULL, will never return any record

    1. SELECT * FROM TBOOL WHERE BVAL = UNKNOWN;
    2. SELECT * FROM TBOOL WHERE BVAL <> UNKNOWN;
    3.6.1.2.1 Use of Boolean against other data types
    1. if (true > 'false') then ...

    'false' is converted to BOOLEAN. Any attempt to use the Boolean operators AND, NOT, OR and IS will fail. NOT 'False', for example, is invalid.

    A BOOLEAN can be explicitly converted to and from string with CAST. UNKNOWN is not available for any form of casting.

    Other Notes

    • The value TRUE is greater than the value .