Salesforce, Python, SQL, & other ways to put your data where you need it

Need event music? 🎸

Live and recorded jazz, pop, and meditative music for your virtual conference / Zoom wedding / yoga class / private party with quality sound and a smooth technical experience

SQL booleans in 3 languages

22 Jun 2022 🔖 databases sql
💬 EN

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
;
--- ---