Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Dialects and Concepts

SQL booleans across dialects

How SQLite, PostgreSQL, MySQL, SQL Server and Oracle store true and false: BOOLEAN, BIT, TINYINT, 0 and 1, TRUE and FALSE literals, and NULL as unknown.

The SQL standard has a BOOLEAN type with three values: TRUE, FALSE and UNKNOWN (which is represented by NULL). Support is uneven. PostgreSQL has a real BOOLEAN. MySQL accepts the name but stores a TINYINT(1). SQL Server has no boolean column type and uses BIT. SQLite stores truth values as the integers 1 and 0. Oracle SQL had no boolean column type until 23ai.

Every system still has boolean expressions, such as price > 10, but they differ in whether such an expression is a value you can select or store.

SQL
SELECT price > 100 AS is_expensive FROM products LIMIT 3;

Try it

SQL Comparisons are 1, 0 or NULL in SQLite ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL A flag column, and the NULL trap ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
-- SQLite
flag INTEGER NOT NULL DEFAULT 0 CHECK (flag IN (0, 1))
WHERE flag = 1             -- TRUE and FALSE are aliases for 1 and 0 (3.23+)

-- PostgreSQL
flag BOOLEAN NOT NULL DEFAULT FALSE
WHERE flag                 -- also: WHERE NOT flag, WHERE flag IS TRUE

-- MySQL
flag BOOLEAN NOT NULL DEFAULT FALSE      -- stored as TINYINT(1)

-- SQL Server
flag BIT NOT NULL DEFAULT 0
WHERE flag = 1

-- Oracle (before 23ai)
flag NUMBER(1) DEFAULT 0 NOT NULL CHECK (flag IN (0, 1))

Dialect differences

Topic SQLite PostgreSQL MySQL SQL Server Oracle
Column type INTEGER (0 or 1) BOOLEAN BOOLEAN = TINYINT(1) BIT NUMBER(1); BOOLEAN in 23ai
Literals TRUE, FALSE (3.23+), 1, 0 TRUE, FALSE, 't', 'yes', '1' TRUE, FALSE, 1, 0 1, 0, 'true' in conversions 1, 0; TRUE in 23ai
WHERE flag works works works (any non-zero is true) error: needs = 1 error: needs = 1
SELECT a > b allowed allowed allowed not allowed; use CASE not allowed; use CASE
Other values allowed any, unless CHECK none any integer (2 is stored) only 0, 1, NULL any number, unless CHECK
SQL
-- SQL Server and Oracle (before 23ai): a comparison is a condition, not a value
SELECT name,
       CASE WHEN price > 100 THEN 1 ELSE 0 END AS is_expensive
FROM products;

-- PostgreSQL: three-valued tests
SELECT name FROM users WHERE verified IS NOT TRUE;   -- false and NULL
SELECT name FROM users WHERE verified IS UNKNOWN;    -- only NULL

Usage notes

  • NULL is the third value. WHERE flag keeps only true rows, and WHERE NOT flag keeps only false rows, so rows with NULL appear in neither. Declare flags NOT NULL with a default whenever you can.
  • Constrain the values. In SQLite, MySQL and Oracle, add CHECK (flag IN (0, 1)) so a stray 2 or 'yes' cannot get in.
  • IS TRUE, IS FALSE, IS UNKNOWN are standard and work in PostgreSQL and MySQL; SQLite has IS 1 / IS NOT 1 instead (and IS TRUE since 3.23).
  • Use CASE to be portable. CASE WHEN condition THEN 1 ELSE 0 END produces a 0/1 value in every dialect, including when you must SUM the matches to count them.
  • Drivers convert differently. The same column may arrive in your application as true, 1 or "t". Check how your driver maps BIT and TINYINT(1).
  • Prefer a status column with named values ('pending', 'active') over several boolean flags when the states are mutually exclusive.