SQL Server UNION and UNION ALL


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.

Leave a comment

Your email address will not be published. Required fields are marked *