SQL dialect differences: 5 engines in DBeaver

If you work across more than one database, you already know the feeling: a query that ran flawlessly against PostgreSQL throws a syntax error the moment you paste it into SQL Server. The SQL standard exists, but every engine bolts its own dialect on top of it. The differences are small enough to feel trivial and sharp enough to cost you an afternoon.

This article walks through the dialect differences that trip people up most often when they switch between PostgreSQL, MySQL/MariaDB, Oracle, SQL Server and SQLite — with copy-paste SQL for each. It is useful whether or not you ever touch a particular tool; the goal is to stop these quirks from surprising you.

How do you paginate rows: LIMIT vs TOP vs ROWNUM vs FETCH?

Pagination is the single most common thing people get wrong when moving between engines, because there are genuinely three different families of syntax.

PostgreSQL, MySQL/MariaDB and SQLite use LIMIT / OFFSET:

SELECT id, title
FROM articles
ORDER BY published_at DESC
LIMIT 10 OFFSET 20;

MySQL and MariaDB also accept a shorthand — LIMIT 20, 10 — where the offset comes first and the count second. That reversed order is a classic bug when porting queries, so prefer the explicit LIMIT ... OFFSET ... form.

SQL Server historically uses TOP, which has no offset of its own:

SELECT TOP (10) id, title
FROM articles
ORDER BY published_at DESC;

The SQL-standard OFFSET ... FETCH clause is the portable option, and it is now supported by SQL Server (since 2012), Oracle (since 12c) and PostgreSQL. It requires an ORDER BY:

SELECT id, title
FROM articles
ORDER BY published_at DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Oracle before 12c had neither LIMIT nor the FETCH clause, so you had to page with ROWNUM inside a nested subquery — because ROWNUM is assigned before the ORDER BY is applied:

SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (
    SELECT id, title FROM articles ORDER BY published_at DESC
  ) a WHERE ROWNUM <= 30
) WHERE rn > 20;

You still meet this pattern in legacy Oracle codebases, so it is worth recognising. From 12c onward, prefer OFFSET ... FETCH (Oracle: row limiting clause, 12c).

One rule applies everywhere: pagination without a deterministic ORDER BY is a bug. Without a total ordering the engine may return rows in any order, so page 2 can repeat or skip rows from page 1.

How do you quote identifiers across engines?

When a column or table name collides with a reserved word (user, order, group) or contains a space, you have to quote it — and the quoting character is not the same everywhere.

-- PostgreSQL, Oracle, SQLite, standard SQL: double quotes
SELECT "user" FROM "Orders";

-- MySQL / MariaDB (default mode): backticks
SELECT `user` FROM `Orders`;

-- SQL Server: square brackets
SELECT [user] FROM [Orders];

Two traps hide here.

Case folding of unquoted names. PostgreSQL folds unquoted identifiers to lowercase; Oracle folds them to UPPERCASE. So SELECT MyCol FROM t really queries mycol in PostgreSQL and MYCOL in Oracle. The moment you wrap a name in double quotes, the exact case is preserved — which means "MyCol" and unquoted MyCol can point at two different columns. This is why a table created with quoted mixed-case names is so painful to query later.

MySQL and double quotes. By default MySQL and MariaDB treat "user" as a string literal, not an identifier. Double quotes only quote identifiers if you enable ANSI_QUOTES in sql_mode. Backticks always work, which is why MySQL tooling leans on them.

SQLite is the pragmatic outlier: it accepts double quotes, square brackets and backticks as identifier quotes, specifically for compatibility with the other engines (SQLite: keywords & quoting).

How do you read a query execution plan in each engine?

When a query is slow, every engine can show you how it intends to run — but the command differs, and so does whether it executes the query.

-- PostgreSQL: estimate only, or actually run it and measure
EXPLAIN SELECT ...;
EXPLAIN ANALYZE SELECT ...;

-- MySQL 8.0.18+: EXPLAIN ANALYZE runs the query and reports actual timings
EXPLAIN ANALYZE SELECT ...;

-- MariaDB: ANALYZE runs it; EXPLAIN gives the estimate
ANALYZE SELECT ...;

-- SQLite: shows how tables/indexes are scanned
EXPLAIN QUERY PLAN SELECT ...;

Oracle splits it into two steps — populate the plan table, then format it:

EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SQL Server toggles plan output as a session setting (or you capture the graphical plan in SSMS with Ctrl+M):

SET STATISTICS PROFILE ON;   -- row counts + plan for each statement
-- or: SET SHOWPLAN_XML ON;  -- the plan as XML, without executing

The key distinction to keep straight: EXPLAIN-style estimates do not run the query, while the “analyze” variants (EXPLAIN ANALYZE in PostgreSQL / MySQL 8.0.18+, ANALYZE in MariaDB) actually execute it and report real row counts and timings. On a DELETE or UPDATE, that difference matters a lot. MySQL’s EXPLAIN ANALYZE landed in 8.0.18 (MySQL blog: EXPLAIN ANALYZE).

What else trips you up when switching engines?

A few smaller differences cause a surprising share of “why doesn’t this work” moments:

String concatenation. PostgreSQL, Oracle and SQLite use ||. SQL Server uses +. MySQL and MariaDB use the CONCAT() function — by default || means logical OR, not concatenation, unless you set PIPES_AS_CONCAT in sql_mode (MariaDB: PIPES_AS_CONCAT).
Current date/time. CURRENT_TIMESTAMP works almost everywhere, but engine-specific names differ: NOW() (PostgreSQL, MySQL), GETDATE() / SYSDATETIME() (SQL Server), SYSDATE (Oracle), datetime('now') (SQLite).
Auto-increment keys. GENERATED ... AS IDENTITY or SERIAL (PostgreSQL), AUTO_INCREMENT (MySQL/MariaDB), IDENTITY (SQL Server), identity columns or sequences (Oracle), AUTOINCREMENT / implicit rowid (SQLite).
Booleans. PostgreSQL has a real BOOLEAN type; MySQL’s BOOLEAN is an alias for TINYINT(1); SQL Server uses BIT; SQLite stores them as 0 / 1. Classic Oracle SQL had no boolean type at all.

None of these are hard once you know them — the problem is that you rarely know all of them for all five engines at once.

How do you stay dialect-aware without memorising every quirk?

The honest answer is: keep a reference like this one, and lean on tooling that knows the dialect you are connected to. Most generic “AI SQL” helpers write one flavour of SQL and leave you to translate — which reintroduces exactly the bugs above.

Disclosure: this blog is published by LumaSoft (LUMA sp. z o.o.), and I work on the tool mentioned below.

SQL Sage for DBeaver is a plugin that adds an AI copilot inside DBeaver and is dialect-aware across these same five engines — PostgreSQL, MySQL/MariaDB, Oracle, SQL Server and SQLite — so pagination, quoting and plan syntax match the connection you are on. A few facts worth stating plainly: it is Windows-only (Linux and macOS are not supported), it is keyless (you sign in with your own Claude or ChatGPT account, no API key), and it is read-only by default — on PostgreSQL and MySQL/MariaDB that read-only mode is enforced at the engine level, not just in the UI. It is not affiliated with DBeaver, Oracle or Microsoft. How one copilot covers five engines in one workflow is on the AI for DBeaver page. A short overview of which engines it covers, how sign-in works and what reaches the model is on the SQL Sage for DBeaver product page.

Whether you use a tool or your own memory, the takeaway is the same: the SQL standard gets you most of the way, and the last 10% — pagination, quoting, plans, concatenation — is where cross-engine work actually lives. Keep this page bookmarked for the next time a “working” query refuses to run.