What SQL Is

SQL is a declarative language for relational data. A statement says what the result should contain — which columns, from which tables, matching which conditions, in which order — and the server's optimizer decides how to produce it: which index to read, which join order to use, whether it can stream rows or must sort them first. You never write the loop.

Why the relational model held

Every SQL operation takes tables and returns a table. That closure is what lets one query become the input to another, and it is the reason the language outlived the object databases, the query builders and the key-value stores that were each going to replace it. Data outlives the application that wrote it, and a declarative query keeps working when the indexes, the optimizer or the hardware underneath it change.

Which MySQL this is

This course documents MySQL 8.4 LTS, generally available since 30 April 2024. Oracle ships two tracks: Innovation releases, which add features and may change behaviour or remove deprecated functionality, and LTS releases, which get bug and security fixes only, with no removals inside the series. Pinning to 8.4 means the behaviour described here does not move under you.

A dialect, not the standard

MySQL both extends standard SQL and omits parts of it. HAVING may refer to columns in the select list, which the standard forbids; NULLS FIRST does not exist at all. Portable SQL is something you choose deliberately, not something you get by default.

-- One statement, no loops. The clauses have a fixed logical order, but the
-- optimizer may execute them any way that produces the same answer.
SELECT   c.name,
         COUNT(*) AS order_count        -- an aggregate, one value per group
FROM     customers AS c
JOIN     orders    AS o ON o.customer_id = c.id
WHERE    o.placed_at >= '2026-01-01'    -- filters rows, before grouping
GROUP BY c.id, c.name
HAVING   COUNT(*) > 3                   -- filters groups, after grouping
ORDER BY order_count DESC, c.name       -- second key breaks ties in the first
LIMIT    10;

-- Run these before trusting any feature list: syntax available to you depends
-- on the version, and behaviour depends on the SQL mode.
SELECT VERSION();      -- e.g. 8.4.4
SELECT @@sql_mode;     -- e.g. ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,...
SELECT @@default_storage_engine;  -- InnoDB on a stock 8.4 server

EXPLAIN
SELECT id, email FROM customers WHERE email = '[email protected]';

-- key: uq_customers_email, rows: 1     -> the optimizer found and used the index
-- key: NULL,               rows: 92000 -> full scan: no usable index for this filter

-- Customers who have at least one order over 500, as a join...
SELECT DISTINCT c.name
FROM   customers AS c
JOIN   orders    AS o ON o.customer_id = c.id
WHERE  o.total > 500;

-- ...and as an existence test. Same result set.
SELECT c.name
FROM   customers AS c
WHERE  EXISTS (SELECT 1 FROM orders AS o
               WHERE o.customer_id = c.id AND o.total > 500);