The SQL map

The five families of statements

Every statement belongs to a family, and the family tells you what it touches: structure, rows, permissions or the transaction.

DQL is one statement, SELECT (MySQL added INTERSECT and EXCEPT in 8.0.31). DCL is yours to decide (chapter 14), TCL marks the unit of work (chapter 9), and the admin statements are much of your day (chapter 16). Client commands like psql's \dt are not SQL (chapter 6).

DDL: shaping the schema

CREATE, ALTER and DROP are the core; TRUNCATE empties a table and keeps its definition. Migrations and index changes are your hands-on time (chapter 3).

DML: the upsert

INSERT, UPDATE and DELETE change rows. The one people forget is the upsert: insert a row, or update the existing one if its key is taken.

-- Reprice the mouse if it exists, add it if it does not.
INSERT INTO products (id, name, price)
VALUES (101, 'Mouse', 22.00) AS new
ON DUPLICATE KEY UPDATE price = new.price;

-- The same upsert. EXCLUDED is the row that failed to insert.
INSERT INTO products (id, name, price)
VALUES (101, 'Mouse', 22.00)
ON CONFLICT (id) DO UPDATE SET price = EXCLUDED.price
RETURNING id, price;

Anatomy of a SELECT

A SELECT is written in one order and evaluated in another, and most "why does this not work" questions come from that gap.

WHERE tests one row at a time, before grouping, which is why HAVING may say COUNT(*) > 1 and WHERE may not.

SELECT c.city, COUNT(*) AS orders, SUM(o.total) AS revenue
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status <> 'cancelled'
GROUP BY c.city
HAVING SUM(o.total) > 30
ORDER BY revenue DESC
LIMIT 5;

The alias rule

An alias from SELECT cannot be used in WHERE, in either engine. ORDER BY and GROUP BY can. To filter on a computed value, repeat the expression or use a subquery (chapter 13).

-- Fails in both engines: WHERE runs before SELECT names anything.
SELECT id, ROUND(total * 1.05, 2) AS with_tax
FROM orders
WHERE with_tax > 100;

-- Works: repeat the expression (or move the query into a subquery).
SELECT id, ROUND(total * 1.05, 2) AS with_tax
FROM orders
WHERE ROUND(total * 1.05, 2) > 100
ORDER BY with_tax;

How a query travels through the server

Every statement takes the same road, and each kind of problem belongs to one stop.

The parser rejects bad syntax and unknown columns before a row is read. The optimizer costs its plan from statistics, so stale statistics mean bad plans; EXPLAIN prints the plan it chose, and EXPLAIN ANALYZE adds real timings (chapter 10). Writes are logged before the server answers, which is what makes backups and replicas possible (chapter 15).

MySQL serves each connection with a thread on port 3306; PostgreSQL starts a process per connection on 5432, which is why poolers come up more often there.

Sessions and the settings that travel with them

A session is your connection's memory: who you are, which database, and settings that change how statements behave.

Autocommit

On in both engines: each statement commits the moment it succeeds. START TRANSACTION or BEGIN suspends that, so you can check the row count before a DELETE becomes permanent.

MySQL sql_mode

Flags that decide how strictly MySQL treats bad data. The default is strict, and ONLY_FULL_GROUP_BY is why old tutorials fail on a new server. SET PERSIST survives a restart.

PostgreSQL search_path

The schemas an unqualified name is looked up in, "$user", public by default. Two teams can each have their own orders.

-- Where am I, and what mood is the session in?
USE shop;
SELECT DATABASE(), @@autocommit, @@sql_mode;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

-- psql: \c is a client command, so no semicolon
\c shop
SELECT current_database(), current_user;
SHOW search_path;
SET search_path = reporting, public;   -- this session only

Dialect differences that trip a returning person

Both engines take the standard core. The differences hide in quoting names, gluing strings and numbering rows (chapter 17).

-- MySQL spelling
SELECT CONCAT(name, ' (', city, ')') AS label FROM customers;

-- PostgreSQL spelling
SELECT name || ' (' || city || ')' AS label FROM customers;

Your Windows server treats Orders and orders as one table, so a mixed-case script works for you and fails on a Linux replica. Keep names lowercase.

Asking the AI for the exact syntax

Let an assistant write the syntax. A prompt that works carries four things.

Engine and version

"MySQL 8.4" or "PostgreSQL 17". A feature may not exist in yours (MERGE before PostgreSQL 15).

The table definitions

Paste SHOW CREATE TABLE orders or \d orders.

The rows you expect

"One row per customer, including customers with no orders" tells it you need an outer join.

A request to explain

You will be the one asked about it later.

Then verify: run it in a transaction you roll back, compare the rows, and EXPLAIN it before production (chapter 10, chapter 18).

• MySQL 8.4: SELECT statement

  Every clause, and aliases.

• PostgreSQL: SELECT

  LIMIT, OFFSET, FETCH FIRST.

• MySQL 8.4: statements that cause an implicit commit

  What ends your transaction.

• MySQL 8.4: server SQL modes

  Each flag and the default.

• PostgreSQL: schemas and the search path

  How unqualified names resolve.