Union vs. Union All

PROBLEM:
What happens when you combine the same retrieval commands UNION (performs distinct query) and UNION ALL ?

SOLUTION:
Lets create test data:

CREATE TABLE #T (N INT);
GO
INSERT INTO #T
SELECT 1;
INSERT INTO #T
SELECT 2;
INSERT INTO #T
SELECT 2;
INSERT INTO #T
SELECT 3;
INSERT INTO #T
SELECT 3;
INSERT INTO #T
SELECT 3;
GO
SELECT *
FROM #T;

Now, lets query it:

SELECT * FROM #T
UNION ALL
SELECT * FROM #T
UNION
SELECT * FROM #T

SELECT * FROM #T
UNION
SELECT * FROM #T
UNION ALL
SELECT * FROM #T;

In the first case, the UNION makes distinct query and contracted three output lines, and in the second case the UNION ALL received three lines added all six.

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!