A Tour of the Dialects

SQL dialects are accents, not separate languages. Once you can read the shared shape—tables, joins, filters, groups, windows, writes—you can learn a new engine by checking the handful of places where the accent gets loud.

This tour is the map you use before opening an unfamiliar codebase. You are not memorizing every vendor manual. You are learning where to look first.

PostgreSQL: the feature-rich default

This course is PostgreSQL-first because PostgreSQL gives you a broad, strict, modern default: real types, rich SQL features, good error messages, JSON, windows, recursive CTEs, RETURNING, MERGE, EXPLAIN, and serious production tooling. That does not make PostgreSQL the only right engine. It makes it a strong baseline.

Here is the kind of Postgres feature you have already been using without ceremony: FILTER, which lets each aggregate carry its own row condition.

SELECT category,
       count(*) AS items,
       count(*) FILTER (WHERE in_stock) AS in_stock_items
FROM menu_items
GROUP BY category
ORDER BY category;

 category | items | in_stock_items
----------+-------+----------------
 coffee   |     4 |              3
 drinks   |     1 |              1
 pastry   |     3 |              3
 tea      |     2 |              1
(4 rows)

This is the PostgreSQL-first discipline from the course: write the clearest Postgres query here, then label the dialect edge when you leave it. You do not dilute every lesson to the smallest common denominator. You learn the strong default, then translate deliberately.

MySQL and MariaDB: the web workhorse

The MySQL/MariaDB family is the dialect you will often meet in web applications. Its core SELECT, JOIN, GROUP BY, and INSERT skills transfer cleanly, but you should expect portability edges.

Two MySQL 8 gaps matter more than most: it still lacks FULL OUTER JOIN and standard MERGE. That matters because those are not obscure features in this course. You used FULL OUTER JOIN for reconciliation and MERGE for matched/not-matched write logic. In MySQL-family code, expect alternative patterns instead of assuming the same spelling exists.

The bigger habit is to read MySQL-family SQL with a portability highlighter on: identifier quoting, boolean treatment, date functions, upsert syntax, and permissive configuration around grouped columns are all places you slow down.

SQLite: embedded everywhere

SQLite is the database hiding inside phones, browsers, desktop apps, test fixtures, and local tools. It is embedded: no separate server process, just your app and a database file.

Do not mistake that deployment shape for toy SQL. Modern SQLite supports window functions, RETURNING, and STRICT tables — features added across releases over the years — and, newest of the group, RIGHT/FULL OUTER JOIN (3.39+). At the same time, SQLite's historical personality is looser than PostgreSQL's. Ordinary tables use type affinity, where the declared type guides storage and comparison, while STRICT tables tighten the contract.

That split explains the right mental model. SQLite is excellent when the database lives with the app or file. PostgreSQL sits on the server side of the bargain, where many clients, roles, backups, monitoring, and operational controls become central. Same language, different deployment bargain.

SQL Server and Oracle: the enterprise pair

SQL Server and Oracle are the big enterprise pair in this map. You will see the same relational core—tables, constraints, joins, transactions, indexes—but also long-lived vendor vocabulary.

SQL Server's dialect is T-SQL, short for Transact-SQL. Oracle's procedural language is PL/SQL, a SQL-adjacent language used for stored procedures, packages, and database-side program structure. For this tour, that is enough. Do not infer exact behavior from the names. When you inherit SQL Server or Oracle code, read the vendor manual for procedural blocks, transaction defaults, date functions, pagination, and upsert/merge behavior before translating.

One everyday contrast is row limiting. The same "show the newest twenty orders" intent may be written with different clause vocabulary:

-- PostgreSQL, MySQL, SQLite, and DuckDB style you may see
SELECT order_id, ordered_at, total_amount
FROM orders
ORDER BY ordered_at DESC
LIMIT 20;
 
-- SQL Server T-SQL style you may see
SELECT TOP (20) order_id, ordered_at, total_amount
FROM orders
ORDER BY ordered_at DESC;
 
-- Oracle and standard-style SQL you may see
SELECT order_id, ordered_at, total_amount
FROM orders
ORDER BY ordered_at DESC
FETCH FIRST 20 ROWS ONLY;

The point is not that one clause is morally superior. The point is that pagination syntax is a translation checkpoint. You find it early, because it appears in application code, dashboards, and API endpoints everywhere.

DuckDB: analytics in-process

DuckDB is the analytics cousin of SQLite's deployment shape: in-process, easy to run locally, pointed at OLAP-style queries over tables and files. You met it in the modern data stack lesson as a laptop-scale analytical workbench.

DuckDB also has friendly SQL. Treat GROUP BY ALL, QUALIFY, and SELECT * EXCLUDE as non-portable sugar. They are pleasant inside DuckDB. They are not the house style for PostgreSQL lessons.

-- DuckDB-friendly SQL: useful there, not portable PostgreSQL
SELECT * EXCLUDE (raw_payload)
FROM app_events;
 
SELECT branch,
       event_type,
       count(*) AS events
FROM app_events
GROUP BY ALL;
 
SELECT user_id,
       event_ts,
       event_type
FROM app_events
QUALIFY row_number() OVER (
  PARTITION BY user_id
  ORDER BY event_ts DESC
) = 1;

The portable PostgreSQL translation is familiar: name the columns, write the GROUP BY columns, and compute window values in one step before filtering them in the next step.

How to read an unfamiliar dialect

When you open unfamiliar SQL, do not start with the weirdest line. Start with the shared shape, then inspect the accent points.

Use this checklist:

• Data types—integer, decimal, boolean, timestamp, JSON, array, identity, UUID. Ask what is strict, what is affinity, and what is just a synonym.
• Identifier quotes—double quotes, backticks, brackets, or mostly unquoted names. Quoting changes case-sensitivity and portability.
• String and date functions—concatenation, substring, regex, date truncation, interval arithmetic, and time-zone conversion move around a lot.
• Row limiting—LIMIT, OFFSET, FETCH FIRST, and TOP are the first pagination accents to spot.
• Upsert and merge—look for ON CONFLICT, ON DUPLICATE KEY, MERGE, or vendor-specific alternatives.
• Boolean, NULL, and case behavior—check truth values, unknown values, grouped NULLs, and text comparison rules before trusting a migrated filter.
• Procedural escape hatches—functions, stored procedures, triggers, packages, and scripts may be a different language wrapped around SQL.

Then translate with humility. If the query uses only the portable core, you can usually move it quickly. If it touches pagination, upsert, JSON, date math, procedural code, or analytics sugar, stop and verify against the target engine.

That is your dialect-reading loop: identify the shared relational shape, circle the accent points, and verify the risky ones in the engine that will actually run the query. The next lesson turns that habit into portable SQL rules, including the breakpoints that pass tests in one dialect and fail in production in another.