FirebirdSQL logo

BOOLEAN

Data Type Declaration Format
BOOLEAN

The SQL-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 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.

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.

BOOLEAN Examples

  1. Inserting and selecting

    CREATE TABLE TBOOL (ID INT, BVAL BOOLEAN);
    COMMIT;
    
    INSERT INTO TBOOL VALUES (1, TRUE);
    INSERT INTO TBOOL VALUES (2, 2 = 4);
    INSERT INTO TBOOL VALUES (3, NULL = 1);
    COMMIT;
    
    SELECT * FROM TBOOL;
              ID    BVAL
    ============ =======
               1 <true>
               2 <false>
               3 <null>
  2. Test for TRUE value

    SELECT * FROM TBOOL WHERE BVAL;
              ID    BVAL
    ============ =======
               1 <true>
  3. Test for FALSE value

    SELECT * FROM TBOOL WHERE BVAL IS FALSE;
              ID    BVAL
    ============ =======
               2 <false>
  4. Test for UNKNOWN value

    SELECT * FROM TBOOL WHERE BVAL IS UNKNOWN;
              ID    BVAL
    ============ =======
               3 <null>
  5. Boolean values in SELECT list

    SELECT ID, BVAL, BVAL AND ID < 2
      FROM TBOOL;
              ID    BVAL
    ============ ======= =======
               1 <true>  <true>
               2 <false> <false>
               3 <null>  <false>
  6. PSQL declaration with start value

    DECLARE VARIABLE VAR1 BOOLEAN = TRUE;
  7. Valid syntax, but as with a comparison with NULL, will never return any record

SELECT * FROM TBOOL WHERE BVAL = UNKNOWN;
SELECT * FROM TBOOL WHERE BVAL <> UNKNOWN;