SQL Dialect Conversion: A Complete Guide to Converting Between MySQL, PostgreSQL, SQL Server, SQLite & Oracle
SQL is the universal language for working with relational databases, but "universal" is generous. Every major database engine extends the ANSI SQL standard with its own functions, syntax quirks, data types, and conventions. If you've ever tried to take a MySQL query and run it on PostgreSQL — or migrate a SQL Server stored procedure to Oracle — you've encountered these differences firsthand.
This guide covers the most important syntax differences between the five major SQL dialects — MySQL, PostgreSQL, SQL Server (T-SQL), SQLite, and Oracle — and explains how automated dialect conversion works.
Table of Contents
Why SQL Dialects Diverge
The ANSI SQL standard defines the core language: SELECT, FROM, WHERE, JOIN, GROUP BY, ORDER BY, INSERT, UPDATE, DELETE, and basic DDL. But real-world databases need features the standard doesn't cover, or covered too late. Pagination (LIMIT vs TOP vs FETCH), NULL-coalescing functions, string concatenation, date arithmetic, auto-incrementing columns, and boolean handling all evolved independently in each engine.
This means the same logical operation is expressed differently depending on where your database runs. The table below summarizes the most common differences you'll encounter during migration.
Key Syntax Differences at a Glance
| Feature | MySQL | PostgreSQL | SQL Server | SQLite | Oracle |
|---|---|---|---|---|---|
| Current timestamp | NOW() | CURRENT_TIMESTAMP | GETDATE() | CURRENT_TIMESTAMP | SYSTIMESTAMP |
| Row limit | LIMIT n | LIMIT n | TOP n or FETCH | LIMIT n | FETCH FIRST n ROWS ONLY |
| NULL coalesce | IFNULL(a,b) | COALESCE(a,b) | ISNULL(a,b) | IFNULL(a,b) | NVL(a,b) |
| String concat | CONCAT(a,b) | a || b | a + b | a || b | a || b |
| Identifier quoting | Backticks `col` | Double quotes "col" | Brackets [col] | Double quotes or backticks | Double quotes "col" |
| Auto-increment | AUTO_INCREMENT | GENERATED BY DEFAULT AS IDENTITY | IDENTITY(1,1) | AUTOINCREMENT | GENERATED BY DEFAULT AS IDENTITY |
| Boolean type | TRUE / FALSE | TRUE / FALSE | 1 / 0 (BIT) | 1 / 0 | 1 / 0 |
Pagination: LIMIT, TOP, and FETCH
Pagination is one of the most common conversion headaches. MySQL, PostgreSQL, and SQLite all use the LIMIT clause, optionally with OFFSET. SQL Server historically used TOP n (which only limits rows, with no offset support), and later adopted OFFSET ... ROWS FETCH NEXT ... ROWS ONLY syntax. Oracle 12c added FETCH FIRST n ROWS ONLY, while older Oracle code uses the proprietary ROWNUM pseudo-column.
When converting LIMIT 10 OFFSET 20 to SQL Server, the equivalent becomes OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY. Note that SQL Server requires an ORDER BY clause for OFFSET...FETCH to work — something the converter can warn you about but can't always enforce automatically.
Function Mapping
Timestamps
Getting the current date/time is expressed differently in every dialect. MySQL uses NOW(), PostgreSQL uses CURRENT_TIMESTAMP, SQL Server uses GETDATE(), and Oracle uses SYSDATE or SYSTIMESTAMP. These are functionally equivalent but require mapping for each target.
NULL Handling
The "return the first non-NULL value" operation has at least four different names: IFNULL (MySQL/SQLite), COALESCE (PostgreSQL and ANSI standard), ISNULL (SQL Server), and NVL (Oracle). COALESCE is the most portable since it's in the ANSI standard and works on most engines, but legacy code often uses the vendor-specific variant.
Date Arithmetic
Adding days to a date involves completely different syntax per engine. MySQL uses DATE_ADD(date, INTERVAL n DAY), PostgreSQL uses date + INTERVAL '30 days', SQL Server uses DATEADD(day, 30, date), and Oracle uses date + INTERVAL '30' DAY. Getting the difference between two dates is equally varied with DATEDIFF taking different argument orders across engines.
Identifier Quoting
MySQL uses backticks, SQL Server uses square brackets, and PostgreSQL, Oracle, and SQLite use double quotes. When converting, a tool needs to identify quoted identifiers and re-quote them for the target dialect. If the identifier is a reserved word in the target dialect, the quoting must be preserved — dropping it would cause a syntax error.
Data Types in DDL
When converting CREATE TABLE statements, data types often need mapping. MySQL's DATETIME maps to DATETIME2 in SQL Server and TIMESTAMP in PostgreSQL. TEXT maps to NVARCHAR(MAX) in SQL Server and CLOB in Oracle. BOOLEAN maps to BIT in SQL Server and NUMBER(1) in Oracle. A good converter handles these mappings while warning about edge cases.
String Concatenation
PostgreSQL, Oracle, and SQLite use the || operator for string concatenation. SQL Server uses +, which behaves differently with NULLs — if either operand is NULL, the entire result is NULL. MySQL's CONCAT function accepts multiple arguments and handles NULLs more gracefully. These semantic differences are important to flag during conversion, even when the syntax can be mechanically transformed.
Upsert Patterns
MySQL's INSERT ... ON DUPLICATE KEY UPDATE maps to PostgreSQL's INSERT ... ON CONFLICT ... DO UPDATE. SQL Server uses MERGE, which has significantly different syntax and semantics. A safe converter will handle the MySQL-to-PostgreSQL mapping but warn when targeting SQL Server, since MERGE conversion is too complex for automated tooling in most cases.
How Automated Conversion Works
A client-side SQL dialect converter typically operates in three stages. First, a tokenizer splits the input into tokens (keywords, identifiers, strings, comments, operators), being careful not to modify content inside string literals or comments. Second, transformation rules — defined as data rather than hard-coded logic — are applied to each token stream: renaming functions, restructuring clauses, re-quoting identifiers, and mapping data types. Third, the transformed tokens are reassembled into output SQL.
This token-based approach is more robust than regex find-and-replace because it respects SQL structure. A naive regex replacing NOW() might accidentally modify a string literal like 'Call NOW() for help', but a tokenizer knows that content inside quotes is not code.
Try the SQL Dialect Converter — paste your SQL, pick source and target dialects, and see a detailed change log with confidence scoring.
Open Tool →What to Watch For
Automated conversion handles the mechanical differences well, but some conversions have semantic implications. SQL Server's + operator returns NULL if either operand is NULL, while || in PostgreSQL propagates NULLs differently depending on the setting. Boolean handling varies: SQL Server uses BIT (0/1), and comparisons like WHERE active = TRUE need adjustment. These are the cases where a warning system is essential — letting you know the conversion succeeded syntactically but may behave differently at runtime.
When Manual Review Is Needed
Automated tools are excellent for the 80-90% of conversions that are mechanical: function names, pagination syntax, quoting, data types. But some patterns require human judgment: complex MERGE statements, stored procedures, vendor-specific optimizer hints, partition definitions, and advanced constraint syntax. A good converter detects these and warns rather than guessing.