SQL booleans in 3 languages
22 Jun 2022
Table of Contents
A SQLite programmer pointed out a nifty feature I was excited about, but sadly, it’s not available in all database engines such as Oracle and Microsoft SQL Server.
My friend works in the world of printers (as in ink on paper), so he was dealing with a data table called sheets
, each of which have a front and a back that can refer to an ID from a pages
table. Both allow nulls. Here’s some sample data:
sheet_id | front_page_id | back_page_id | status |
---|---|---|---|
1 | 84 | 84 | printed |
2 | 93 | 17 | printed |
3 | 17 | 84 | printed |
4 | 23 | printed | |
5 | 84 | 30 | printed |
6 | printed |
My friend said he’d needed to find out how many times a given page reference, such as page 84
, had been printed.
In SQLite, he could simply write this query, letting an expression like (front_page_id = 84)
translate to 0’s and 1’s implicitly:
SELECT
TOTAL(front_page_id = 84) + TOTAL(back_page_id = 84) AS the_count
FROM
sheets
;
However, in Microsoft SQL Server 2017 or in Oracle 11g R2 it has to be more verbose:
SELECT
SUM(
CASE WHEN (first_syrup_id = 84) THEN (1) ELSE (0) END
) + SUM(
CASE WHEN (second_syrup_id = 84) THEN (1) ELSE (0) END
) AS the_count
FROM
tastings
the_count |
---|
4 |
In those database engines, you have to wrap each conditional expression in a CASE WHEN
and a THEN (1) ELSE (0) END
. (Although in Oracle you could probably also use an IF
and THEN (1) ELSE (0)
.)
However, explicitly ensuring that a conditional expression like (first_syrup_id = 84)
always returns a number allows SUM
to be used.
In SQLite, TOTAL
is preferable because it’s null-safe, and SQLite’s unadorned (first_syrup_id = ...)
will return NULL
if passed NULL
instead of 84
.
Further reading
By the way, for more great tricks with 0’s and 1’s, check out https://adminhero.com/2015/08/31/power-of-one-the-most-brilliant-analytics-trick-of-all-time/
This is also a fantastic old math book: Ones and Zeros : Understanding Boolean Algebra, Digital Circuits, and the Logic of Sets
Notes to self
DML for SQLite
CREATE TABLE sheets
("sheet_id" INTEGER, "front_page_id" INTEGER, "back_page_id" INTEGER, "status" TEXT(7))
;
INSERT INTO sheets
("sheet_id", "front_page_id", "back_page_id", "status")
VALUES
(1, 84, 84, 'printed')
;
INSERT INTO sheets
("sheet_id", "front_page_id", "back_page_id", "status")
VALUES
(2, 93, 17, 'printed')
;
INSERT INTO sheets
("sheet_id", "front_page_id", "back_page_id", "status")
VALUES
(3, 17, 84, 'printed')
;
INSERT INTO sheets
("sheet_id", "front_page_id", "back_page_id", "status")
VALUES
(4, NULL, 23, 'printed')
;
INSERT INTO sheets
("sheet_id", "front_page_id", "back_page_id", "status")
VALUES
(5, 84, 30, 'printed')
;
INSERT INTO sheets
("sheet_id", "front_page_id", "back_page_id", "status")
VALUES
(6, NULL, NULL, 'printed')
;
DML for MS SQL Server
CREATE TABLE sheets
([sheet_id] int, [front_page_id] int, [back_page_id] int, [status] varchar(7))
;
INSERT INTO sheets
([sheet_id], [front_page_id], [back_page_id], [status])
VALUES
(1, 84, 84, 'printed'),
(2, 93, 17, 'printed'),
(3, 17, 84, 'printed'),
(4, NULL, 23, 'printed'),
(5, 84, 30, 'printed'),
(6, NULL, NULL, 'printed')
;
DML for Oracle
CREATE TABLE sheets
(sheet_id int, front_page_id int, back_page_id int, status varchar2(7))
;
INSERT ALL
INTO sheets (sheet_id, front_page_id, back_page_id, status)
VALUES (1, 84, 84, 'printed')
INTO sheets (sheet_id, front_page_id, back_page_id, status)
VALUES (2, 93, 17, 'printed')
INTO sheets (sheet_id, front_page_id, back_page_id, status)
VALUES (3, 17, 84, 'printed')
INTO sheets (sheet_id, front_page_id, back_page_id, status)
VALUES (4, NULL, 23, 'printed')
INTO sheets (sheet_id, front_page_id, back_page_id, status)
VALUES (5, 84, 30, 'printed')
INTO sheets (sheet_id, front_page_id, back_page_id, status)
VALUES (6, NULL, NULL, 'printed')
SELECT * FROM dual
;