SQLite Operators, Comparison Rules, LIKE/GLOB, and Expression Semantics

Learning outcomes

The prerequisite SQL course taught expressions as values computed from literals, columns, operators, and functions. In SQLite, the same idea is shaped by dynamic typing, affinity, three-valued logic, collations, and a few SQLite-specific operators. This lesson makes those rules observable.

Start with the runtime value, not just what the screen prints

Chapter 4 established SQLite's five storage classes. Two values can print similarly while carrying different storage classes, and comparison rules can apply affinity before deciding the result. When a comparison surprises you, inspect both the value and its representation.

SELECT  8       AS a, typeof(8)       AS a_type, quote(8)       AS a_sql,  '8'     AS b, typeof('8')     AS b_type, quote('8')     AS b_sql,  8.0     AS c, typeof(8.0)     AS c_type, quote(8.0)     AS c_sql,  NULL    AS d, typeof(NULL)    AS d_type, quote(NULL)    AS d_sql;

typeof() reports the runtime storage class; quote() renders a value as an SQL literal suitable for diagnostics. Do not confuse either function with the declared type of a column.

Operator precedence: write for humans even when SQLite already knows

SQLite publishes a precedence table. Arithmetic binds before comparisons, comparisons bind before AND, and AND binds before OR. COLLATE and ESCAPE are postfix constructs with their own binding rules. Parentheses are still the best way to communicate intent when several classes of operator meet.

SELECT  1 + 2 * 3 AS arithmetic,              -- 7  (1 + 2) * 3 AS parenthesized,         -- 9  1 = 1 OR 0 = 1 AND 0 = 0 AS mixed;   -- 1 because AND binds tighter

NULL-safe comparison: IS and IS NOT

Ordinary = and != participate in SQL three-valued logic: comparing NULL with another value usually yields NULL, not true or false. SQLite's compact IS and IS NOT forms always produce 0 or 1 for these comparisons.

SELECT  NULL = NULL                         AS eq_null,       -- NULL  NULL IS NULL                        AS is_null,       -- 1  NULL IS NOT NULL                    AS is_not_null,   -- 0  5 IS 5                              AS same_value,    -- 1  5 IS NOT 6                          AS different;     -- 1SELECT  5 IS NOT DISTINCT FROM 5            AS std_same,      -- 1  5 IS DISTINCT FROM 6                AS std_diff,      -- 1  NULL IS NOT DISTINCT FROM NULL      AS std_null_same; -- 1

In SQLite, IS NOT DISTINCT FROM is an alternative spelling for IS, and IS DISTINCT FROM is an alternative spelling for IS NOT. The longer forms are more portable to engines that do not support SQLite's compact notation.

IN and BETWEEN: compact syntax still follows expression rules

IN tests membership. BETWEEN is inclusive at both ends and evaluates its left expression once. NULL can propagate into either construct, so a missing value can produce “unknown” rather than false.

SELECT  5 IN (3,5,7)                AS in_set,       -- 1  5 NOT IN (3,7)              AS not_in_set,   -- 1  5 BETWEEN 5 AND 10          AS inclusive,    -- 1  NULL BETWEEN 1 AND 10       AS null_range,   -- NULL  9 IN (1,2,NULL)             AS null_member;  -- NULL, not 0

The last result matters in anti-joins and filters. A WHERE predicate keeps rows only when the expression is true; false and NULL are both filtered out. If a membership list or subquery can contain NULL, test the intended semantics rather than assuming NOT IN is interchangeable with NOT EXISTS.

LIKE and GLOB are different pattern languages

SQLite's default LIKE uses % for any sequence and _ for one character. For ASCII letters it is case-insensitive by default, but this built-in folding does not extend to general Unicode. GLOB uses Unix-style *, ?, and bracket classes and is case-sensitive.

SELECT  'Pump-007' LIKE 'pump-%'              AS ascii_like,      -- 1  'æther' LIKE 'Æ%'                    AS unicode_like,    -- 0 by default  'Pump-007' GLOB 'Pump-*'             AS glob_exact,      -- 1  'Pump-007' GLOB 'pump-*'             AS glob_case,       -- 0  '100% ready' LIKE '100!%%' ESCAPE '!' AS escaped_percent; -- 1

ESCAPE belongs to a preceding LIKE expression and lets one character neutralize % or _. The course does not use PRAGMA case_sensitive_like: current SQLite documentation marks that PRAGMA deprecated, and changing global LIKE behavior can invalidate assumptions made by schema expressions or indexes.

Affinity can influence comparison before the comparison happens

A column's affinity can coerce the other operand in a comparison. This is why the same text literal may compare differently against a TEXT-affinity column and a NUMERIC-affinity column. Keep the experiment small and inspect typeof().

DROP TABLE IF EXISTS compare_probe;CREATE TABLE compare_probe(  n NUMERIC,  t TEXT);INSERT INTO compare_probe VALUES ('8.00','8.00');SELECT n, typeof(n), t, typeof(t) FROM compare_probe;-- n is stored as integer 8; t remains text '8.00'.SELECT  n = '8.0' AS numeric_affinity_match,  t = 8.0   AS text_affinity_match,  quote(n) AS n_debug,  quote(t) AS t_debugFROM compare_probe;

The NUMERIC column can convert a well-formed numeric text value before storage/comparison. The TEXT column instead tends to compare textual representations after applying TEXT affinity to the other operand. Do not repair surprising results by scattering CASTs randomly; first identify the declared affinity, the runtime storage classes, and the business representation you intended.

Query-debugging lab: make every hidden assumption visible

Create intentionally mixed values, then answer each question from observed types rather than from the display alone.

DROP TABLE IF EXISTS expr_lab;CREATE TABLE expr_lab(  id INTEGER PRIMARY KEY,  raw,  text_value TEXT,  numeric_value NUMERIC);INSERT INTO expr_lab(raw,text_value,numeric_value) VALUES(NULL,NULL,NULL),('8','8','8'),('08','08','08'),(8.0,'8.0',8.0),('Pump-007','Pump-007','Pump-007'),('æther','æther','æther');SELECT id,       quote(raw), typeof(raw),       quote(text_value), typeof(text_value),       quote(numeric_value), typeof(numeric_value)FROM expr_lab ORDER BY id;SELECT id, text_value LIKE 'pump-%' AS like_hit,           text_value GLOB 'Pump-*' AS glob_hitFROM expr_lab ORDER BY id;SELECT id,       numeric_value = 8 AS equals_eight,       numeric_value IS NULL AS missingFROM expr_lab ORDER BY id;

Before changing a query, record: declared column type/affinity, runtime typeof(), quote() output, collation/pattern operator, and whether NULL is possible. That five-item checklist turns many “SQLite compared this wrong” reports into explainable representation issues.

Production judgment and bridge

Expression correctness begins with a data contract. Stable representations, explicit NULL semantics, deliberate collations, and tested pattern rules are safer than relying on display formatting or implicit conversion. Lesson 2 builds on this by using SQLite's core functions to clean, classify, format, and aggregate values while tracking which functions are portable and which are version-specific.

Authoritative references

• SQLite — SQL Language Expressions.
• SQLite — Datatypes and Comparison Expressions.
• SQLite — Core Functions.
• SQLite — PRAGMA case_sensitive_like (deprecated).