اختلافات لهجات SQL التي تؤثر في التنسيق

The SQL standard covers most of what a typical query does, but PostgreSQL, MySQL and MariaDB, SQL Server, Oracle, IBM Db2 and SQLite still differ in the places that matter for layout: how identifiers are quoted, how rows are limited, how strings are joined, how procedural code is delimited and how scripts are split into batches. This guide compares them and explains what SQL Format does with each.

What SQL Format does with dialects

Before the comparison, the honest summary. SQL Format has no dialect switch. It formats the SQL that these databases share: SELECT, joins, subqueries, CTEs, CASE, INSERT, UPDATE, DELETE and comments. On top of that it supports a few PostgreSQL extras, described in formatting PostgreSQL queries and PL/pgSQL: :: casts, $1 parameters and dollar-quoted function bodies.

MySQL backtick names and SQL Server [bracket] names are recognised as quoted identifiers, so they are kept intact and never re-cased, as shown below. It does not give special treatment to the GO batch separator, the MySQL client's DELIMITER command, or procedural BEGIN … END blocks outside PostgreSQL dollar quotes. If your code uses those, the practical advice in each section below tells you how to work with the formatter rather than against it.

Comparison at a glance

The table summarises current mainstream versions. Older releases may lack some features, and several databases have configuration modes that change the defaults shown here.

Identifier quoting and case folding

Quoted identifiers let you use spaces, reserved words or mixed case in names. Each database has its preferred quote character:

-- MySQL / MariaDB (default sql_mode)
SELECT `order id`, `status` FROM `orders`;

-- SQL Server (brackets; double quotes also work with QUOTED_IDENTIFIER ON)
SELECT [order id], [status] FROM [dbo].[orders];

-- PostgreSQL, Oracle, Db2, SQLite (standard double quotes)
SELECT "order id", "status" FROM "orders";

The SQL standard uses double quotes. MySQL treats double quotes as string delimiters unless the ANSI_QUOTES SQL mode is enabled, which is why MySQL code is full of backticks. SQL Server accepts double quotes for identifiers when QUOTED_IDENTIFIER is on, which is the default for modern client libraries, but brackets remain the house style in most T-SQL code.

Case folding is the other half of the story. An unquoted name is folded to lowercase in PostgreSQL and to uppercase in Oracle and Db2. So CREATE TABLE Orders creates orders in PostgreSQL and ORDERS in Oracle, and in both cases SELECT * FROM orders works. A name created with quotes and mixed case, on the other hand, must be quoted the same way in every query. The portable habit is simple: lowercase snake_case names that are not reserved words, never quoted.

SQL Format recognises all three quoting styles. A quoted name is copied exactly as typed: it is never split, and keyword case never changes it, even when the word inside is a keyword such as [Status] or `group`. Here is a SQL Server query and a MySQL query formatted with lowercase keywords:

select o.[Order Id], o.[Status], c.[Name] from [dbo].[orders] o join [dbo].[customers] c on c.[Id] = o.[CustomerId] where o.[Status] = 'open';
select `order id`, `group`, `Status` from `orders` where `Status` = 'open'

select o.[Order Id],
    o.[Status],
    c.[Name]
from [dbo].[orders] o
    join [dbo].[customers] c
        on  c.[Id] = o.[CustomerId]
where o.[Status] = 'open';
select `order id`,
    `group`,
    `Status`
from `orders`
where `Status` = 'open'

Row limiting: LIMIT, TOP and FETCH FIRST

Limiting a result to the first rows is the most visible difference between dialects:

-- PostgreSQL, MySQL, MariaDB, SQLite
SELECT id, title FROM articles ORDER BY published_at DESC LIMIT 10 OFFSET 20;

-- SQL Server
SELECT TOP (10) id, title FROM articles ORDER BY published_at DESC;
SELECT id, title FROM articles ORDER BY published_at DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- Standard form: Oracle 12c and later, Db2, PostgreSQL
SELECT id, title FROM articles ORDER BY published_at DESC
OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;

The standard form is OFFSET n ROWS FETCH FIRST m ROWS ONLY. Oracle supports it from 12c onwards (before that, ROWNUM in a subquery was the usual workaround), Db2 has supported FETCH FIRST for a long time, and PostgreSQL accepts it alongside LIMIT. SQL Server supports the OFFSET … FETCH NEXT form only after an ORDER BY, and keeps TOP for the simple case. MySQL and SQLite use LIMIT; recent MariaDB versions accept the standard form too.

Whatever the syntax, always pair row limiting with an ORDER BY that is unique, for example by adding the primary key as a last sort column. Without it, "the first ten rows" is not defined and pages can repeat or skip rows.

String concatenation

Concatenation looks trivial and is a frequent source of porting bugs:

-- PostgreSQL, Oracle, Db2, SQLite
SELECT first_name || ' ' || last_name AS full_name FROM customers;

-- SQL Server
SELECT first_name + ' ' + last_name AS full_name FROM customers;

-- MySQL / MariaDB (|| means OR unless PIPES_AS_CONCAT is set)
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;

In MySQL, || means logical OR unless the PIPES_AS_CONCAT SQL mode is set, so 'a' || 'b' does not return ab. In SQL Server, + concatenates strings but adds numbers, so mixing types needs explicit casts. NULL handling differs as well: in PostgreSQL, 'a' || NULL is NULL, while Oracle treats NULL as an empty string in concatenation and returns 'a'. When the result matters, wrap nullable columns in COALESCE.

Portable expressions such as ||, COALESCE and CASE are formatted the same way regardless of where they will run. Function arguments are split one per line and CASE gets its own layout:

select c.id, c.first_name || ' ' || c.last_name as full_name, coalesce(c.phone, c.email) as contact, case when c.vip then 'VIP' else 'regular' end as tier from customers c where c.country = 'BR' order by c.last_name, c.first_name

SELECT c.id,
    c.first_name || ' ' || c.last_name AS full_name,
    COALESCE(
        c.phone,
        c.email
    ) AS contact,
    CASE WHEN c.vip THEN
        'VIP'
    ELSE
        'regular'
    END AS tier
FROM customers c
WHERE c.country = 'BR'
ORDER BY c.last_name,
    c.first_name

Getting rows back: RETURNING and OUTPUT

Reading generated keys or updated values in the same statement saves a round trip and avoids race conditions. The syntax varies more than in any other area:

-- PostgreSQL, SQLite (and MariaDB for some statements)
INSERT INTO tags (name) VALUES ('urgent') RETURNING id;

-- SQL Server
INSERT INTO tags (name) OUTPUT inserted.id VALUES ('urgent');

-- Oracle, inside PL/SQL
INSERT INTO tags (name) VALUES ('urgent') RETURNING id INTO v_id;

-- Db2
SELECT id FROM FINAL TABLE (INSERT INTO tags (name) VALUES ('urgent'));

PostgreSQL and SQLite use a trailing RETURNING clause. SQL Server uses OUTPUT, placed before VALUES in an INSERT, with the inserted and deleted pseudo-tables. Oracle's RETURNING … INTO writes into variables, so it appears in PL/SQL or with bind variables. Db2 wraps the data change in FINAL TABLE and selects from it. MySQL has no equivalent and relies on functions such as LAST_INSERT_ID().

Batch separators and terminators

Some of what looks like SQL in a script is really an instruction for the client tool:

-- SQL Server (sqlcmd / SSMS): GO ends a batch, it is not T-SQL
CREATE TABLE audit_log (id int IDENTITY PRIMARY KEY, msg nvarchar(400));
GO
CREATE PROCEDURE log_msg @msg nvarchar(400) AS
BEGIN
    INSERT INTO audit_log (msg) VALUES (@msg);
END
GO

-- MySQL client: DELIMITER changes the statement terminator
DELIMITER //
CREATE PROCEDURE log_msg(IN p_msg VARCHAR(400))
BEGIN
    INSERT INTO audit_log (msg) VALUES (p_msg);
END //
DELIMITER ;

GO is not a T-SQL statement. sqlcmd and SQL Server Management Studio split the script on it and send each batch separately, which is required because statements such as CREATE PROCEDURE must be the first in their batch. The MySQL client's DELIMITER command changes the statement terminator so that the semicolons inside a procedure body do not end the CREATE PROCEDURE early. Oracle's SQL*Plus and SQLcl run a PL/SQL block when they see a / on its own line, and the Db2 command line processor can be told to use another terminator, such as @, for the same reason.

SQL Format splits input on semicolons only. For scripts that use GO or DELIMITER, format the statements between the separators and keep the separators themselves on their own lines when you put the script back together.

Procedural blocks

Every major database except SQLite has a procedural language, and each one wraps it differently:

-- Oracle PL/SQL anonymous block (SQL*Plus runs it after the "/")
DECLARE
    v_total NUMBER;
BEGIN
    SELECT SUM(total) INTO v_total FROM orders;
    DBMS_OUTPUT.PUT_LINE(v_total);
END;
/

-- PostgreSQL PL/pgSQL: the body is a dollar-quoted string
DO $$
DECLARE
    v_total numeric;
BEGIN
    SELECT SUM(total) INTO v_total FROM orders;
    RAISE NOTICE '%', v_total;
END
$$;

Oracle's PL/SQL uses DECLARE/BEGIN/EXCEPTION/END blocks, and PostgreSQL's PL/pgSQL follows the same shape inside a dollar-quoted string. Db2's SQL PL and MySQL or MariaDB stored programs use BEGIN … END compound statements, with declarations at the top and DECLARE … HANDLER for errors. T-SQL mixes procedural statements directly with SQL and handles errors with BEGIN TRY/BEGIN CATCH. SQLite has no stored procedures; only trigger bodies use BEGIN … END.

Of these, SQL Format gives block layout only to PL/pgSQL inside $$. For T-SQL, PL/SQL and SQL PL, it is most useful on the individual queries inside the block: paste a long SELECT or UPDATE, format it, and indent the result to the block's level. The same layout rules from the SQL style guide apply inside a procedure as anywhere else.

Habits that make SQL portable and easy to format

• Use unquoted, lowercase snake_case names that are not reserved words.
• Prefer standard syntax when every target supports it: explicit JOIN, CASE, COALESCE, CTEs and FETCH FIRST where available.
• Keep client-tool commands (GO, DELIMITER, /) on their own lines so they are easy to spot in review.
• Format with one documented set of options, so diffs show logic changes and not style changes. The migration checklist covers this for schema changes.

Frequently asked questions