Filtering, NULL Semantics, Ordering, Expressions, and Deterministic Query Results

Learning outcomes

ServiceHub has reached the point where SQL that “looked right” in a small dataset is becoming an operational risk. One endpoint asks for the newest five open work orders but omits a complete ordering key. Another filters nullable notes with = NULL and quietly returns no rows. A third uses OFFSET pagination and assumes page 2 will remain stable while new work orders are arriving. These are not parser errors; they are semantic errors caused by an incomplete model of how SQL evaluates truth, comparison and order.

This lesson builds a deterministic-query discipline. MariaDB can return a correct set of rows while still leaving their order unspecified. NULL introduces UNKNOWN into predicates. Collations can make strings compare equal even when their bytes differ. Expressions have types and coercion rules. LIMIT restricts whatever ordered or unordered stream reaches it. The practical goal is not memorizing clauses; it is learning to state every business requirement strongly enough that a later index, optimizer choice, server upgrade or concurrent insert cannot expose a hidden assumption.

1. Build a query lab you can reset

Advanced query lessons are easier to trust when every surprising result can be reproduced from a known dataset. Run the bootstrap below in a disposable local instance. The schema deliberately includes duplicate timestamps, NULL technician assignments and NULL customer notes because those edge cases are where weak query assumptions become visible.

CREATE DATABASE IF NOT EXISTS servicehub_query_lab;USE servicehub_query_lab;DROP TABLE IF EXISTS work_order_events;DROP TABLE IF EXISTS work_orders;DROP TABLE IF EXISTS technicians;DROP TABLE IF EXISTS teams;CREATE TABLE teams (  team_id INT PRIMARY KEY,  parent_team_id INT NULL,  team_name VARCHAR(80) NOT NULL,  CONSTRAINT fk_team_parent FOREIGN KEY (parent_team_id) REFERENCES teams(team_id)) ENGINE=InnoDB;CREATE TABLE technicians (  technician_id INT PRIMARY KEY,  team_id INT NOT NULL,  display_name VARCHAR(80) NOT NULL,  active BOOLEAN NOT NULL DEFAULT TRUE,  CONSTRAINT fk_tech_team FOREIGN KEY (team_id) REFERENCES teams(team_id)) ENGINE=InnoDB;CREATE TABLE work_orders (  work_order_id BIGINT PRIMARY KEY,  technician_id INT NULL,  region VARCHAR(30) NOT NULL,  priority INT NOT NULL,  status VARCHAR(20) NOT NULL,  opened_at DATETIME NOT NULL,  closed_at DATETIME NULL,  labor_cost DECIMAL(10,2) NOT NULL,  customer_note VARCHAR(200) NULL,  CONSTRAINT fk_wo_tech FOREIGN KEY (technician_id) REFERENCES technicians(technician_id)) ENGINE=InnoDB;CREATE TABLE work_order_events (  event_id BIGINT PRIMARY KEY,  work_order_id BIGINT NOT NULL,  event_at DATETIME NOT NULL,  event_type VARCHAR(30) NOT NULL,  hours_spent DECIMAL(6,2) NOT NULL DEFAULT 0,  CONSTRAINT fk_event_wo FOREIGN KEY (work_order_id) REFERENCES work_orders(work_order_id)) ENGINE=InnoDB;INSERT INTO teams VALUES (1,NULL,'Operations'),(2,1,'North'),(3,1,'South'),(4,2,'North-East'),(5,2,'North-West');INSERT INTO technicians VALUES (101,2,'Ava',1),(102,2,'Ben',1),(103,3,'Chen',1),(104,4,'Dina',1),(105,5,'Eli',0);INSERT INTO work_orders VALUES (1001,101,'north',1,'open','2026-08-01 08:00:00',NULL,120.00,NULL), (1002,102,'north',2,'closed','2026-08-01 09:00:00','2026-08-02 11:00:00',90.00,'filter replaced'), (1003,NULL,'south',2,'open','2026-08-01 09:00:00',NULL,75.00,'awaiting assignment'), (1004,103,'south',3,'closed','2026-08-02 10:00:00','2026-08-02 17:00:00',210.00,NULL), (1005,104,'north',1,'open','2026-08-02 10:00:00',NULL,150.00,'repeat visit'), (1006,101,'north',1,'closed','2026-08-02 10:00:00','2026-08-03 10:00:00',130.00,NULL), (1007,102,'north',3,'open','2026-08-03 08:00:00',NULL,65.00,NULL), (1008,103,'south',2,'closed','2026-08-03 08:00:00','2026-08-04 10:00:00',175.00,'customer called');INSERT INTO work_order_events VALUES (1,1001,'2026-08-01 08:10:00','created',0),(2,1001,'2026-08-01 11:00:00','visit',2.5), (3,1002,'2026-08-01 09:10:00','created',0),(4,1002,'2026-08-02 10:30:00','repair',3.0), (5,1004,'2026-08-02 10:05:00','created',0),(6,1004,'2026-08-02 15:00:00','repair',4.0), (7,1005,'2026-08-02 10:10:00','created',0),(8,1006,'2026-08-02 10:15:00','created',0), (9,1006,'2026-08-03 09:30:00','repair',2.0),(10,1008,'2026-08-03 08:05:00','created',0);

After the inserts, verify the row counts before doing anything else. A failed seed statement changes every later result, so a lab should prove its starting state rather than assuming it.

SELECT COUNT(*) AS teams FROM teams;SELECT COUNT(*) AS technicians FROM technicians;SELECT COUNT(*) AS work_orders FROM work_orders;SELECT COUNT(*) AS events FROM work_order_events;SELECT MIN(opened_at) AS first_opened, MAX(opened_at) AS last_opened FROM work_orders;

2. NULL is not an ordinary value

SQL predicates do not operate with only TRUE and FALSE. A comparison involving NULL normally produces UNKNOWN. WHERE keeps rows for which the predicate is TRUE; FALSE and UNKNOWN are both filtered out. That is why customer_note = NULL does not find the rows whose note is missing. The expression is not “false for non-NULL and true for NULL”; it is UNKNOWN for every row.

SELECT work_order_id, customer_note,       customer_note = NULL AS equals_null,       customer_note IS NULL AS is_nullFROM work_ordersORDER BY work_order_id;SELECT work_order_idFROM work_ordersWHERE customer_note IS NULLORDER BY work_order_id;

MariaDB also supports the null-safe equality operator <=>. Unlike ordinary =, it returns a definite truth value when NULL is involved: NULL <=> NULL is true, and a non-NULL value compared to NULL is false. This is useful in comparison logic where NULL should be treated as a comparable state, but it should not replace careful domain modeling.

SELECT NULL <=> NULL AS both_null,       7 <=> NULL AS value_vs_null,       7 <=> 7 AS equal_values;

3. NOT IN can become a NULL trap

Three-valued logic matters beyond simple comparisons. If a NOT IN list or subquery contains NULL, the database may be unable to prove that a candidate value is different from every item, so the predicate becomes UNKNOWN. This is one reason anti-joins are often safer to express with NOT EXISTS when the inner expression can be nullable.

SELECT 103 NOT IN (101,102,NULL) AS not_in_result;SELECT t.technician_id, t.display_nameFROM technicians AS tWHERE NOT EXISTS (  SELECT 1  FROM work_orders AS w  WHERE w.technician_id = t.technician_id)ORDER BY t.technician_id;

The second query states the real requirement directly: return technicians for whom no matching work order exists. Lesson 2 will connect this logical form to semijoin and antijoin reasoning. The important point here is that a syntactically compact predicate is not automatically the clearest statement of intent.

4. Collation changes equality and ordering

String comparison is governed by character set and collation. A case-insensitive collation can consider forms equal that a binary comparison would distinguish. That affects WHERE, DISTINCT, GROUP BY and ORDER BY. If a business key is supposed to be case-sensitive or byte-exact, relying on a default collation inherited from another environment can silently change results.

SELECT @@character_set_connection AS connection_charset,       @@collation_connection AS connection_collation;SHOW FULL COLUMNS FROM work_orders LIKE 'region';SELECT region, COUNT(*) AS nFROM work_ordersGROUP BY regionORDER BY region;

For application-visible identifiers, decide whether equality is linguistic, case-insensitive, accent-insensitive, or byte-oriented. The correct answer is a domain decision. The query optimizer may use an index to implement that comparison, but the collation defines what equality and sort order mean.

5. ORDER BY must fully specify the business order

Without ORDER BY, SQL does not promise a presentation order. Even with ORDER BY opened_at DESC, rows sharing the same timestamp are peers whose relative order is not defined by that clause. The sample deliberately gives work orders 1004/1005 and 1007/1008 duplicate timestamps. If an API takes the first three rows, an index or plan change can expose this missing tie-breaker.

-- Incomplete ordering: peers can appear in either order.SELECT work_order_id, opened_at, priorityFROM work_ordersORDER BY opened_at DESCLIMIT 5;-- Deterministic business ordering: unique final tie-breaker.SELECT work_order_id, opened_at, priorityFROM work_ordersORDER BY opened_at DESC, work_order_id DESCLIMIT 5;

A deterministic ordering should end with enough columns to make each row position unique for the result set. A primary key is a common tie-breaker. Do not confuse “I ran it ten times and it stayed the same” with a guarantee: the current plan and physical state happened to produce a stable-looking order.

EXPLAIN FORMAT=JSONSELECT work_order_id, opened_at, priorityFROM work_ordersORDER BY opened_at DESC, work_order_id DESCLIMIT 5;ANALYZE FORMAT=JSONSELECT work_order_id, opened_at, priorityFROM work_ordersORDER BY opened_at DESC, work_order_id DESCLIMIT 5;

EXPLAIN describes the optimizer plan; ANALYZE executes the SELECT and adds runtime statistics. Either may reveal a filesort, index scan or row estimates. Neither permits the application to omit the required ORDER BY. Physical access order is an implementation choice, not a semantic ordering promise.

6. LIMIT/OFFSET is simple, but moving data can move pages

OFFSET pagination asks the server to skip a number of rows in the current ordered result. If rows are inserted, deleted or updated between page requests, a row can shift from one page to another. Large offsets may also require the server to traverse and discard many rows before producing the requested page. For small administrative screens this may be acceptable; for busy feeds or deep pagination, keyset pagination often produces a clearer continuation contract.

-- Page 1SELECT work_order_id, opened_at, priorityFROM work_ordersORDER BY opened_at DESC, work_order_id DESCLIMIT 3;-- Example continuation if page 1 ended at-- opened_at='2026-08-02 10:00:00', work_order_id=1005SELECT work_order_id, opened_at, priorityFROM work_ordersWHERE opened_at < '2026-08-02 10:00:00'   OR (opened_at = '2026-08-02 10:00:00' AND work_order_id < 1005)ORDER BY opened_at DESC, work_order_id DESCLIMIT 3;

The continuation predicate must mirror the ordering keys and directions. The application carries the last seen key rather than a page number. This does not create a global snapshot—concurrency semantics still depend on the transaction/isolation model—but it avoids many duplicate/skip behaviors caused by offset drift.

7. Failure drill, verification and production judgment

The intentionally wrong endpoint is: SELECT ... WHERE status='open' LIMIT 3. It returns three rows, so an automated smoke test that checks only row count passes. The failure is semantic: “newest three” was never encoded. Repair the query by filtering, ordering by the intended business timestamp and adding a unique tie-breaker. Then insert another row with a duplicate timestamp and prove the order remains fully specified.

1. Run the NULL examples and explain UNKNOWN in your own words.
2. Compare = NULL, IS NULL, and <=>.
3. Inspect the region collation and state whether comparisons are case-sensitive.
4. Run the ambiguous timestamp ordering and identify peer rows.
5. Repair it with work_order_id as a final tie-breaker.
6. Run EXPLAIN and ANALYZE on the deterministic query and record plan/runtime evidence without treating it as a semantic guarantee.
7. Compare OFFSET and keyset pagination after inserting a new recent work order.

8. Summary and bridge

A reliable SELECT states the complete logical requirement. NULL introduces UNKNOWN; collations define string comparison; expressions and conversions can change predicate meaning; ORDER BY is the only result-order contract; a unique tie-breaker makes LIMIT deterministic; and OFFSET is a positional technique whose behavior changes as the underlying ordered set changes. Plans help explain execution, but they do not replace SQL semantics.

The next lesson adds multiple relations. You will distinguish join predicates from post-join filters, reason about EXISTS and IN as existence questions, and connect logical semijoins to the materialization and other execution strategies that MariaDB can expose in its plan.