Filtering & predicates

WHERE semantics: NULL-aware predicates, timestamp ranges, LIKE vs equality, implicit casts, AND/OR precedence.

• ★NOT IN meets NULL

  x NOT IN (list-with-a-NULL) can never be TRUE — one NULL empties the whole result, silently; NOT EXISTS is NULL-safe.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE customers(id INT, name VARCHAR); INSERT INTO customers VALUES (1,'Ada'),(2,'Bo'),(3,'Cy'); CREATE TABLE orders(customer_id INT); INSERT INTO orders VALUES (1),(NULL);

  The trap

  SELECT name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders) ORDER BY name;

  0 rows

  The fix

  SELECT name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id=c.id) ORDER BY name;

  Bo

  Cy

  0 rows, no error — the 'customers we've never converted' report says there are none.

• Predicate on a nullable column drops NULLs

  status <> 'refunded' silently excludes rows where status IS NULL — the predicate is UNKNOWN, not TRUE.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE rep(id INT, status VARCHAR); INSERT INTO rep VALUES (1,'done'),(2,'refunded'),(3,NULL);

  The trap

  SELECT id FROM rep WHERE status <> 'refunded' ORDER BY id;

  1

  The fix

  SELECT id FROM rep WHERE status IS DISTINCT FROM 'refunded' ORDER BY id;

  1

  3

  'Everything except refunds' also dropped the unstatused repair — status <> 'refunded' is UNKNOWN where status is NULL, and WHERE keeps only TRUE; IS DISTINCT FROM (or OR status IS NULL) keeps the unknowns.

• BETWEEN on timestamps — the midnight trap

  BETWEEN '2024-01-01' AND '2024-01-31' loses Jan 31's daytime; half-open >= / < is the bar.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE swipes(id INT, ts TIMESTAMP); INSERT INTO swipes VALUES (1, TIMESTAMP '2024-01-31 09:00'), (2, TIMESTAMP '2024-02-01 00:00'), (3, TIMESTAMP '2024-01-15 12:00');

  The trap

  SELECT id FROM swipes WHERE ts BETWEEN TIMESTAMP '2024-01-01' AND TIMESTAMP '2024-01-31' ORDER BY id;

  3

  The fix

  SELECT id FROM swipes WHERE ts >= TIMESTAMP '2024-01-01' AND ts < TIMESTAMP '2024-02-01' ORDER BY id;

  1

  3

  The January report drops every swipe after midnight on the 31st — BETWEEN's closed upper bound is really '2024-01-31 00:00'.

• LIKE wildcards vs equality

  LIKE matches patterns and its case-sensitivity is dialect-dependent; = is exact.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE fabrics(name VARCHAR); INSERT INTO fabrics VALUES ('linen 100%'),('cotton 200'),('linen blend'),('silk 100');

  The trap

  SELECT name FROM fabrics WHERE name LIKE '%100%' ORDER BY name;

  linen 100%

  silk 100

  The fix

  SELECT name FROM fabrics WHERE name LIKE '%100\%' ESCAPE '\' ORDER BY name;

  linen 100%

  The search for a literal '100%' with LIKE '%100%' matched 'silk 100' too — the trailing % is a wildcard, not a character, until ESCAPE de-wildcards it.

• Implicit casts in comparisons

  Comparing a string to a date/number forces an implicit cast — silently wrong ordering or filtering.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE slips(slip VARCHAR); INSERT INTO slips VALUES ('9'),('10'),('2'),('15'),('100');

  The trap

  SELECT slip FROM slips ORDER BY slip;

  10

  100

  15

  2

  9

  The fix

  SELECT slip FROM slips ORDER BY CAST(slip AS INT);

  2

  9

  10

  15

  100

  The slip numbers are text, so ORDER BY sorted them character-by-character — '10' and '100' came before '2', and MAX would crown '9' instead of the real highest slip.

• AND/OR precedence

  AND binds tighter than OR — missing parentheses change which rows match.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE tix(id INT, zone VARCHAR, day VARCHAR); INSERT INTO tix VALUES (1,'lawn','sat'),(2,'lawn','sun'),(3,'vip','sat'),(4,'vip','sun');

  The trap

  SELECT id FROM tix WHERE zone='lawn' OR zone='vip' AND day='sat' ORDER BY id;

  1

  2

  3

  The fix

  SELECT id FROM tix WHERE (zone='lawn' OR zone='vip') AND day='sat' ORDER BY id;

  1

  3

  The Saturday report included Sunday lawn tickets — AND binds tighter than OR, so 'lawn OR vip AND sat' parsed as 'lawn OR (vip AND sat)', leaking every lawn day in.