Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.
UNION and UNION ALL combine query results, but mismatched columns fail silently, not loudly. Here's how to align, parenthesize, and order them for readable SQL.
Quick answer
Format a UNION or UNION ALL query by giving every SELECT branch the same number of columns in the same order, aliasing them once in the first branch, indenting every branch identically, and placing exactly one ORDER BY after the final SELECT. Default to UNION ALL unless you specifically need duplicates removed, and parenthesize each branch whenever you mix UNION and UNION ALL in one statement.
FAQs
Do the column names have to match between SELECT statements in a UNION?
No. UNION matches columns by position and requires compatible data types, not matching names — the final result set uses the column names (or aliases) from the first SELECT. If a later branch names a column differently, that name is simply ignored in the output.
Can you mix UNION and UNION ALL in the same query?
Yes. Wrap each branch in parentheses when you do, both to remove ambiguity about which branches get deduplicated and because some engines otherwise parse mixed operators left to right. (SELECT ...) UNION (SELECT ...) UNION ALL (SELECT ...) is unambiguous to both the engine and the next reader.
Why does my UNION query fail with a column count or type mismatch error?
Every SELECT branch in a UNION must return the same number of columns, and each corresponding pair of columns needs a compatible type. The most common cause is a branch missing a column the others have, or a literal like a bare NULL whose type the engine can't reconcile with the matching column elsewhere — cast it explicitly, for example CAST(NULL AS TEXT).
Does UNION ALL guarantee the order rows come back in?
No. Neither UNION nor UNION ALL guarantees row order without an explicit ORDER BY on the combined result. UNION ALL often happens to return rows in something close to branch order in practice, but that's an implementation detail, not a guarantee — add ORDER BY if order matters to the result.
Can you UNION more than two SELECT statements?
Yes — chain as many SELECT statements as you need, with UNION or UNION ALL between each pair; none of the five dialects sqlfmt supports impose a fixed limit. Keep the same column-count and type rules for every branch, and apply the same UNION vs UNION ALL choice consistently unless you have a specific reason to mix them.
Try it now
Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.
Format a UNION or UNION ALL query the same way every time: give each SELECT branch the same number of columns in the same order, alias those columns once in the first branch, indent every branch identically, and use exactly one ORDER BY, placed after the final SELECT, to sort the combined result.
SQL doesn't check that formatting for you. A UNION matches columns by position, not by name, so a branch with one column too many — or a type that doesn't quite agree with its counterpart — doesn't throw an error. It just shifts every column after it one position to the left, silently, and the query keeps returning rows.
That's exactly the failure mode reporting and ETL queries hit most: stitching together active and archived users, this year's and last year's sales, or one region's schema and another's. Someone adds a column to one branch and forgets the other. Consistent formatting — aligned columns, parenthesized branches, one ORDER BY at the end — is what makes that kind of mismatch visible before it ships, and this post walks through exactly how to apply it, including where the rules change across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.
What's the difference between UNION and UNION ALL?
UNION ALL concatenates the rows from every branch as-is, duplicates included. UNION does the same, then runs a de-duplication pass — equivalent to a DISTINCT — over the combined set. That extra pass means UNION does real work: sorting or hashing every row to find and drop duplicates, where UNION ALL just appends result sets. On a few hundred rows the difference is invisible; on millions of rows, the de-dup step is a real cost you may be paying for nothing.
UNION removes duplicates; UNION ALL keeps them
-- Both tables contain user 42SELECT id, email FROM active_usersUNIONSELECT id, email FROM vip_users;-- user 42's row appears onceSELECT id, email FROM active_usersUNION ALLSELECT id, email FROM vip_users;-- user 42's row appears twice
Default to UNION ALL unless you know the branches can overlap and you specifically need those duplicates removed. When you do reach for UNION, treat that as a decision worth documenting — a one-line comment saying why duplicates matter saves the next reader from having to work out whether the DISTINCT-like behavior was intentional or copy-pasted.
A legitimate use for UNION shows up when you're combining overlapping date ranges or multiple event sources that might report the same event twice — there, the de-dup pass is doing real work, not cleaning up an accident. If you're not sure whether your branches can actually produce duplicates, run the query as UNION ALL once and compare its row count to the same query with UNION. If the counts match, you never needed the DISTINCT pass, and switching to UNION ALL is a safe, free performance win.
How do you align columns across SELECT branches in a UNION?
Every branch has to return the same number of columns, matched by position — the combined result takes its column names from the first branch, whatever the later branches call theirs. Line up each branch's column list the same way, one column per line if the list is long, so you can visually diff branch against branch. The same instinct that keeps a multi-table JOIN readable — one clause per line, consistent indentation — applies here; see our guide to formatting SQL JOINs for the same principle applied to ON conditions.
Notice the second branch doesn't repeat AS status — the output already takes that column's name from the first branch, so re-aliasing it there would just be noise. What matters is that both branches line up column for column: three columns, same order, same types, every time.
Avoid SELECT * in a UNION branch, even when the source tables share an identical schema today. UNION matches columns by position, so if one table later gains a column and the other doesn't, SELECT * silently shifts every column after it, and the query goes from correct to structurally wrong without a single line of the query itself changing. Naming columns explicitly is what makes a UNION resistant to schema drift in either branch.
Should you parenthesize each SELECT in a UNION?
Parentheses around each branch aren't required for a plain chain of UNIONs, but use them anyway in two cases: when you mix UNION and UNION ALL in the same statement, and when an individual branch needs its own ORDER BY or LIMIT before the sets combine. Without parentheses, a mix of UNION and UNION ALL reads left to right, and it's genuinely easy for a reader to misjudge which branches get deduplicated — even in engines where the precedence is well defined. Parenthesizing costs nothing and removes that ambiguity for good.
How do you format ORDER BY and LIMIT on a UNION query?
A UNION produces exactly one combined result set, so it gets exactly one ORDER BY and one LIMIT, placed after the final SELECT — never repeated on the individual branches. Reference the column alias from the first branch, not a table-qualified name; by the time ORDER BY runs, there's no longer a table to qualify against. MySQL is the one dialect that enforces this at the grammar level, per its UNION syntax reference: to sort or limit a single branch before combining it with the others, you must wrap that branch in parentheses, because a bare SELECT ... ORDER BY ... UNION ... is a syntax error.
When the branches' column names differ enough that picking one alias feels arbitrary, ordering by position — ORDER BY 1, 2 — is more portable than ordering by alias. Every dialect covered here accepts positional ordering on a UNION's combined result, which sidesteps any argument over whose column name should win.
Does SQL UNION formatting differ across dialects?
Everything above works unchanged in PostgreSQL, SQLite, and SQL Server, and in MySQL once you account for the ORDER BY-per-branch restriction above. BigQuery is the outlier worth knowing before you port a query into it: per Google's set operators reference, BigQuery's Standard SQL rejects a bare UNION outright and requires you to write UNION ALL or UNION DISTINCT explicitly, or it fails with "Expected keyword ALL or keyword DISTINCT but got keyword SELECT." It's a small difference, but it's also a good argument for spelling out ALL or DISTINCT everywhere, even in dialects where it's optional, so a query reads the same regardless of where it runs — see our rundown of MySQL vs PostgreSQL syntax differences for the other gotchas that show up when queries move between engines.
SQL UNION formatting checklist
Every SELECT branch returns the same number of columns, in the same order.
Column types are compatible across matching positions — cast explicit literals like NULL instead of leaving the engine to guess.
Columns are aliased once, in the first branch, and later branches don't re-alias the same position.
Every branch is indented the same way, so a reader can diff them column by column.
UNION and UNION ALL sit on their own line between branches, not tacked onto the end of a SELECT.
Each branch is wrapped in parentheses whenever UNION and UNION ALL are mixed in the same statement.
UNION ALL is the default; UNION is used — and commented — only when duplicates genuinely need to be removed.
ORDER BY and LIMIT appear exactly once, after the final SELECT, referencing the first branch's column aliases.
In MySQL, any branch that needs its own ORDER BY or LIMIT before combining is parenthesized.
In BigQuery, UNION ALL or UNION DISTINCT is written explicitly — a bare UNION never appears.
Wrapping up
A UNION query is only as trustworthy as its formatting, because the database checks column count and type compatibility — not the author's intent. Aligned branches, parenthesized operators, and a single ORDER BY at the end are what make a mismatch visible before it reaches a dashboard or a report.
If you'd rather not check column counts and indentation by hand on every query, run it through sqlfmt — it formats and validates SQL across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery, catching exactly this kind of column mismatch before it ships; see pricing for what's free versus Pro.
select id,name,'active' as status from users where active=1 union all select id,name,'archived' from archived_users
After: aligned, consistently styled branches
SELECT id, name, 'active' AS statusFROM usersWHERE active = 1UNION ALLSELECT id, name, 'archived'FROM archived_users;
Parenthesize branches when mixing UNION and UNION ALL
(SELECT id, email FROM current_customers)UNION(SELECT id, email FROM trial_customers)UNION ALL(SELECT id, email FROM churned_customers);
One ORDER BY, after the last SELECT
SELECT id, name, hire_date AS event_date FROM new_hiresUNION ALLSELECT id, name, term_date AS event_date FROM departuresORDER BY event_date DESCLIMIT 20;
BigQuery requires ALL or DISTINCT explicitly
-- Fails in BigQuery: Expected keyword ALL or keyword DISTINCT but got keyword SELECTSELECT id, sku FROM `project.dataset.orders_2025`UNIONSELECT id, sku FROM `project.dataset.orders_2026`;-- Works: UNION DISTINCT is BigQuery's spelling of plain UNIONSELECT id, sku FROM `project.dataset.orders_2025`UNION DISTINCTSELECT id, sku FROM `project.dataset.orders_2026`;