FirebirdSQL logo
 SQL Language StructureCommon Language Elements 

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;