The UNION, EXCEPT and INTERSECT operators are relational operators that combine rows from the results sets of two queries. The UNION operator unifies the rows from the two inputs. The INTERSECT operator returns only rows that are common to both inputs. The EXCEPT operator returns the rows that appear in the first input but not the second. The input queries are not allowed to have an ORDER BY clause they are supposed to return a relational result, however an ORDER BY can be placed at the end of the queries, as seen in the general form below.

<query_1>
<operator>
<query_2>
[ORDER BY <order_by_list>]

The schemas of the input queries need to be compatible. The number of columns has to be the same and the types need to be implicitly convertible from the one with the lowest data type precedence to the highest. The names of the result columns are defined by the first query.

When multiple operators are used in a query without parentheses, INTERSECT precedes UNION and EXCEPT. The last two have the same precedence, and are therefore evaluated based on appearance order. Force your desired evaluation order using parentheses.

There is a difference between the SQL keyword UNION and UNION ALL. To show the difference we will look at some queries here. In this example, we don’t use a database. We don’t need to. Using some constants we will first use UNION ALL and we get the results we expected.

SELECT cast('H2345' as char(5))
UNION ALL
SELECT cast('T' as char(1))
UNION ALL
SELECT cast('H234' as char(4))
UNION ALL
SELECT cast('T' as char(1))

New we will use UNION.

SELECT cast('H2345' as char(5))
UNION
SELECT cast('T' as char(1))
UNION
SELECT cast('H234' as char(4))
UNION
SELECT cast('T' as char(1))

Here is the SQL Server text output screen shot. Notice that UNION only shows unique rows. We only see one ‘T’ row. Not only that, the rows are not in the same order as our SELECT statements.