SET Operators: Syntax & Rules

The batters table and the bowlers table have the same shape: name, team. To get one list of all players, you don't need a key or a join — you just stack one result under the other. That's what SET operators do.

Definition

SET operators combine the results of two or more SELECT queries into a single result set by stacking their rows. The queries must return the same number of columns with compatible data types.

Unlike joins, SET operators compare whole rows (all columns together), not a single key.

Syntax

SELECT name, team FROM batters WHERE team = 'MI'     -- 1st SELECT statement
UNION                                                -- SET operator
SELECT name, team FROM bowlers WHERE team = 'MI'     -- 2nd SELECT statement
ORDER BY name;                                       -- only ONCE, at the very end

Each SELECT can have its own WHERE, JOIN, GROUP BY, HAVING. The single ORDER BY sorts the combined result.

The 5 SET rules

Rule 3 in action — different column counts

SELECT name, team FROM batters
UNION
SELECT name FROM bowlers;

SQL Server: "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists." The query fails.

Rule 4 — compatible types, and the hidden trap

SELECT runs FROM players UNION SELECT name FROM batters fails in SQL Server: it tries to convert 'Virat Kohli' to INT ("Conversion failed when converting the varchar value…").

But matching types do not guarantee matching meaning:

SELECT name FROM batters
UNION
SELECT team FROM bowlers;

This runs without error and mixes player names with team codes (CSK, GT, MI…) in one column — garbage. SQL checks position and type, never meaning. Always list columns in the same order in every query.

Rule 5 — names come from the first query

SELECT name AS player, team FROM batters
UNION
SELECT name, team FROM bowlers
ORDER BY player;

The result column is called player (from the 1st query), and ORDER BY must use that name.

Using a SET operator inside another clause

A set query can be a subquery, e.g. in WHERE:

SELECT name, team, runs
FROM players
WHERE name IN (SELECT name FROM batters
               UNION
               SELECT name FROM bowlers)
ORDER BY runs DESC;

Common mistakes

• ❌ ORDER BY after the first SELECT → SQL Server error. ✅ One ORDER BY at the very end.
• ❌ Columns in a different order (name, team vs team, name). ✅ Same order in every query.
• ❌ Using SELECT * on both sides — works only while both tables have identical columns. ✅ List columns explicitly.

SQL Server vs MySQL / PostgreSQL / Oracle

Interview tip

Q: Where does the column name of a UNION result come from? A: From the first SELECT. Aliases in later queries are ignored, and ORDER BY must use the first query's names.