SELECT Execution Basics, Projection, Predicates, NULL Semantics, and Ordering

Learning outcomes

A dispatch dashboard looks simple: “show the open work orders, put the urgent ones first, and return only the first page.” Yet that request hides several correctness questions. What does a predicate do when a column is NULL? Does the order in which SQL is written equal the order in which MySQL executes it? If two rows have the same priority and timestamp, which one appears first? A production query is reliable only when those semantics are deliberate.

This lesson does not repeat every SELECT clause from the prerequisite SQL course. Instead, it makes MySQL-specific behavior observable and turns query writing into an evidence-driven workflow: define the intended rows, write the expression, inspect the exact result, and then examine optimizer evidence without confusing an execution plan with SQL meaning.

Build the query laboratory once, then observe every result

The chapter reuses one compact dataset so row counts can be reasoned about manually. It contains four customers, five technicians, seven work orders, and a small tag table. Several values are intentionally NULL: work order 1003 has no assigned technician and no priority, while open work orders have a NULL closed_at. Those values are not mistakes; they are probes for SQL semantics.

DROP DATABASE IF EXISTS servicehub_query_lab;CREATE DATABASE servicehub_query_lab  CHARACTER SET utf8mb4  COLLATE utf8mb4_0900_ai_ci;USE servicehub_query_lab;CREATE TABLE customers (  customer_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,  customer_name VARCHAR(100) NOT NULL,  region VARCHAR(20) NOT NULL) ENGINE=InnoDB;CREATE TABLE technicians (  technician_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,  technician_name VARCHAR(100) NOT NULL,  supervisor_id BIGINT UNSIGNED NULL,  active BOOLEAN NOT NULL DEFAULT TRUE,  CONSTRAINT fk_tech_supervisor    FOREIGN KEY (supervisor_id) REFERENCES technicians(technician_id)) ENGINE=InnoDB;CREATE TABLE work_orders (  work_order_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,  customer_id BIGINT UNSIGNED NOT NULL,  technician_id BIGINT UNSIGNED NULL,  status VARCHAR(20) NOT NULL,  priority INT NULL,  opened_at DATETIME NOT NULL,  closed_at DATETIME NULL,  summary VARCHAR(160) NOT NULL,  CONSTRAINT fk_wo_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id),  CONSTRAINT fk_wo_technician FOREIGN KEY (technician_id) REFERENCES technicians(technician_id)) ENGINE=InnoDB;CREATE TABLE work_order_tags (  work_order_id BIGINT UNSIGNED NOT NULL,  tag VARCHAR(30) NOT NULL,  PRIMARY KEY (work_order_id, tag),  CONSTRAINT fk_wot_work_order FOREIGN KEY (work_order_id) REFERENCES work_orders(work_order_id)) ENGINE=InnoDB;INSERT INTO customers VALUES  (1,'Northwind Clinic','north'),  (2,'Contoso Foods','south'),  (3,'Alpine School','north'),  (4,'Fabrikam Lab','east');INSERT INTO technicians VALUES  (10,'Mina',NULL,TRUE),  (11,'Arman',10,TRUE),  (12,'Sara',10,TRUE),  (13,'Dariush',11,FALSE),  (14,'Laleh',11,TRUE);INSERT INTO work_orders VALUES  (1001,1,11,'open',1,'2026-08-01 08:00:00',NULL,'Sterilizer temperature alarm'),  (1002,1,12,'closed',2,'2026-08-02 09:30:00','2026-08-02 12:15:00','Pump seal replacement'),  (1003,2,NULL,'open',NULL,'2026-08-03 10:00:00',NULL,'Conveyor vibration inspection'),  (1004,3,11,'open',2,'2026-08-03 10:00:00',NULL,'Boiler pressure sensor check'),  (1005,3,14,'closed',1,'2026-08-04 07:45:00','2026-08-05 15:00:00','Ventilation controller fault'),  (1006,4,14,'open',3,'2026-08-05 11:10:00',NULL,'Microscope stage calibration'),  (1007,2,12,'open',2,'2026-08-05 11:10:00',NULL,'Packaging line photoeye alignment');INSERT INTO work_order_tags VALUES  (1001,'urgent'),(1001,'electrical'),  (1002,'mechanical'),  (1003,'inspection'),  (1004,'sensor'),(1004,'urgent'),  (1005,'controls'),  (1006,'calibration'),  (1007,'sensor');

SELECT VERSION() AS server_version,       DATABASE() AS current_schema,       @@SESSION.sql_mode AS session_sql_mode;SELECT COUNT(*) AS customers FROM customers;SELECT COUNT(*) AS technicians FROM technicians;SELECT COUNT(*) AS work_orders FROM work_orders;SELECT COUNT(*) AS tags FROM work_order_tags;

Expected row counts are 4 customers, 5 technicians, 7 work orders, and 9 tag rows. If your counts differ, stop before analyzing query results. A query lesson becomes unreliable when the seed state is uncertain.

Logical reasoning is not an optimizer execution trace

A useful mental model is to reason about a query in stages: start with the rows named by FROM, apply WHERE, form the selected expressions, order the qualifying rows, and finally apply a row limit. That model explains semantics. MySQL’s optimizer is free to transform and reorder physical work when the transformation preserves the statement’s meaning. It might use an index to avoid visiting rows that the logical model imagines filtering later, or choose a different join order than the query text.

USE servicehub_query_lab;-- 1. Source rowsSELECT work_order_id, status, priority, opened_atFROM work_orders;-- 2. FilterSELECT work_order_id, status, priority, opened_atFROM work_ordersWHERE status = 'open';-- 3. Presentation order with explicit tie breakerSELECT work_order_id, status, priority, opened_atFROM work_ordersWHERE status = 'open'ORDER BY priority IS NULL, priority, opened_at, work_order_id;-- 4. First pageSELECT work_order_id, status, priority, opened_atFROM work_ordersWHERE status = 'open'ORDER BY priority IS NULL, priority, opened_at, work_order_idLIMIT 3;

The expression priority IS NULL evaluates to 0 for non-NULL priorities and 1 for NULL, so the first ordering term pushes unknown priorities after known priorities. The final work_order_id is a deterministic tie breaker. The important point is not this one ordering recipe; it is that the query states the complete required ordering rather than relying on storage or plan accidents.

EXPLAIN FORMAT=TREESELECT work_order_id, status, priority, opened_atFROM work_ordersWHERE status = 'open'ORDER BY priority IS NULL, priority, opened_at, work_order_idLIMIT 3;

Your exact plan can differ with server patch level, statistics, indexes, and data distribution. EXPLAIN is evidence of the optimizer’s current estimated strategy; it is not a contract that rows are logically filtered in the textual order of clauses.

NULL means unknown, not zero, empty, or “does not exist”

SQL predicates use three truth states: true, false, and unknown. Comparisons such as =, <>, <, or > involving NULL normally produce NULL (unknown), not true or false. A WHERE clause keeps rows for which its condition is true; false and unknown are both rejected. That is why column = NULL is a classic bug.

SELECT  NULL = NULL AS ordinary_equality,  NULL <=> NULL AS null_safe_equality,  2 <=> 2 AS equal_values,  2 <=> NULL AS value_vs_null;-- Wrong: returns no open work orders merely because closed_at is NULL.SELECT work_order_idFROM work_ordersWHERE closed_at = NULL;-- Correct test for missing close timestamp.SELECT work_order_idFROM work_ordersWHERE closed_at IS NULLORDER BY work_order_id;

MySQL’s <=> operator is a null-safe equality operator: it returns 1 when both operands are NULL, 0 when only one is NULL, and otherwise behaves like equality. It is MySQL-specific syntax, so use it deliberately when portability matters.

Projection, aliases, and the boundary between data and presentation

Projection chooses the expressions returned by a query. A good production query returns only the fields needed by its caller and names derived expressions clearly. This improves readability and often reduces transferred data, but it does not by itself prove a faster plan. Measure if performance matters.

SELECT work_order_id,       summary,       COALESCE(priority, 99) AS display_priority,       CASE         WHEN closed_at IS NULL THEN 'not closed'         ELSE 'closed'       END AS lifecycle_labelFROM work_ordersORDER BY work_order_id;

COALESCE(priority,99) changes only the query result. It does not replace NULL in the table. Keep that distinction clear: a display default is not a schema default and not an update.

ORDER BY and LIMIT: make the page contract deterministic

Without ORDER BY, SQL does not promise a meaningful row order. A current plan may appear to return primary-key order because of InnoDB’s clustered index, then change after an index is added, statistics change, or the optimizer chooses a different access method. Even with ORDER BY priority, ties can remain. If pagination or “top N” logic requires repeatability, specify a complete ordering that makes ties deterministic for the business purpose.

-- Incomplete contract: any three open rows may be returned.SELECT work_order_id, priorityFROM work_ordersWHERE status='open'LIMIT 3;-- Still ambiguous when priorities tie.SELECT work_order_id, priorityFROM work_ordersWHERE status='open'ORDER BY priorityLIMIT 3;-- Reproducible ordering for this lab.SELECT work_order_id, priority, opened_atFROM work_ordersWHERE status='open'ORDER BY priority IS NULL, priority, opened_at, work_order_idLIMIT 3;

The server may return the same rows repeatedly for the first two queries on your tiny dataset. That observation does not turn an unspecified order into a guarantee. Correctness comes from the statement’s semantics, not from a lucky repeated experiment.

Hands-on lab: dispatch queue with verification

1. Rebuild the seed schema and verify the four row counts.
2. List all open work orders with their raw priority, including the NULL priority row.
3. Run priority = NULL and explain why it matches nothing; then repair it with IS NULL.
4. Build a queue that orders known priorities first, then opening time, then work-order ID.
5. Add LIMIT 3 only after the full ordering is stated.
6. Run EXPLAIN FORMAT=TREE and record the current plan as local evidence, not a permanent contract.

SELECT work_order_id, priorityFROM work_ordersWHERE status='open' AND priority IS NULL;-- Expected work_order_id: 1003SELECT work_order_idFROM work_ordersWHERE status='open'ORDER BY priority IS NULL, priority, opened_at, work_order_id;-- Expected order with the seed data: 1001, 1004, 1007, 1006, 1003

Production judgment and next bridge

In production, make query contracts explicit: required filters, NULL meaning, complete ordering for pagination, and selected columns should be code-reviewed alongside schema assumptions. Monitor slow-query evidence and plan regressions, but do not allow performance tuning to weaken semantics. An index can change how a query runs; it must not change which rows the SQL is supposed to mean.

The next lesson adds more than one table. Once joins enter the picture, the central skill becomes cardinality reasoning: predict how many row combinations should exist before trusting the output.

Authoritative references

• MySQL 8.4 Reference Manual — SELECT Statement
• MySQL 8.4 Reference Manual — Working with NULL Values
• MySQL 8.4 Reference Manual — IS NULL Optimization
• MySQL 8.4 Reference Manual — Optimizing SELECT Statements
• MySQL 8.4 Reference Manual — LIMIT Query Optimization
• MySQL 8.4 Release Notes