Which parts of SQL are portable across engines, and which parts break when the same query moves to another database?
The middle of the language is portable and the edges are not. That is the rule that makes it easy to remember what to worry about.
So what is portable? The query part of the language, almost entirely. A select with a filter, a join, a group by, an order by and a limit behaves the same way on every engine. The types are portable as well, with a few small differences. The rules that you write in the schema are portable too, because a primary key, a foreign key, a unique rule and a check rule all mean the same thing everywhere.
And what breaks when you move the same query to another engine? Seven things, and they are the same seven every time.
-- Portable on every engine.
CREATE TABLE customers (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(120) NOT NULL,
city VARCHAR(60) NOT NULL
);
-- MySQL spells the identity column like this instead:
-- id INT AUTO_INCREMENT PRIMARY KEY
-- Portable on every engine.
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
HAVING COUNT(o.id) > 2
ORDER BY order_count DESC
FETCH FIRST 10 ROWS ONLY;
The seven things that differ are these. The auto-numbering column. The upsert. The way you limit the rows, which is LIMIT in MySQL and PostgreSQL and SQLite and FETCH FIRST in the standard and TOP in SQL Server. The way you join text together, which is || in the standard and CONCAT in MySQL. The date and time functions. The boolean type, which is a real type in PostgreSQL and a small number in MySQL. And the extra options on the create table statement, such as the engine and the character set.
So the practical rule is this. Write the query part in the portable way, and keep the seven differences in one place. If you can, put the engine specific SQL behind a small layer of your own, so that the rest of your code never sees it.
The next question in this topic shows the same write in two spellings.