question

The main difference is UNION ALL will include all rows from the multiple recordsets being UNION'd together... Including any duplicates. BOL has a full description at http://msdn.microsoft.com/en-us/library/ms180026.aspx

We use union and union all to combine result sets which return the same number of columns with same data types. And no, it is not same as full outer join.

Lets take an example with one column.

Table1 
Col1
1
2
3
4

Table2
Col2
3
4
5
6

Now, when I write

SELECT Col1 FROM Table1
UNION 
SELECT Col2 FROM Table2

That would give me 1,2,3,4,5,6 - So it has removed the duplicates.

If I do

    SELECT Col1 FROM Table1
    UNION ALL
    SELECT Col2 FROM Table2

It would return me 1,2,3,4,3,4,5,6 - It has got the duplicates with it.

Hope this help.

UNION is for joining 2 result sets, JOIN is for joining 2 tables, views, table-valued functions, table expressions and the like.

UNION ALL simply joins 2 result sets including all the rows. UNION joins 2 result sets and removes duplicate rows. UNION by itself incurs the overhead of a merge join so that it can remove these dupes. It also has a side affect of ordering the rows. If you do not care about dupes UNION ALL is more performant

Queue's answer covers the difference between UNION and UNION ALL well.

You should also be aware of EXCEPT and INTERSECT which are two further horizontal concatenation operators.

In terms of the difference between JOIN and UNION - think of one in terms of vertical concatenation, and think of the other in terms of horizontal concatenation. Each of them can return less rows than were present in the source (with the exception that join can repeat on the one side when there are multiples on the other), and each of them take the dimensions of your result set and extend them.

That's how I usually explain them, anyhow.

To illustrate the many plan possibilities for UNION and UNION ALL:

-- UNION implemented using a Concatenation  
SELECT data FROM (VALUES (1), (2)) A (data)  
UNION  
SELECT data FROM (VALUES (3), (4)) B (data);

CREATE TABLE #A (data INT NOT NULL);  
CREATE TABLE #B (data INT NOT NULL);

INSERT  #A VALUES (1),(1), (3), (2), (2);  
INSERT  #B VALUES (1),(2), (2), (1), (4);

-- Concatenate then Distinct Sort on the result
SELECT data FROM #A  
UNION  
SELECT data FROM #B OPTION (CONCAT UNION, ORDER GROUP);

-- Concatenate then Hash Distinct on the result  
SELECT data FROM #A  
UNION  
SELECT data FROM #B OPTION (CONCAT UNION, HASH GROUP);

-- Sort Distinct on the inputs, then Merge Union
SELECT data FROM #A 
UNION 
SELECT data FROM #B OPTION (MERGE UNION, ORDER GROUP);

-- Hash Distinct and Sort on the inputs to a Merge Union
SELECT data FROM #A 
UNION 
SELECT data FROM #B OPTION (MERGE UNION, HASH GROUP);

-- Sort Distinct on the probe input to a Hash Union
SELECT data FROM #A 
UNION 
SELECT data FROM #B OPTION (HASH UNION, ORDER GROUP);

-- Hash Distinct on the probe input to a Hash Union
SELECT data FROM #A 
UNION 
SELECT data FROM #B OPTION (HASH UNION, HASH GROUP);

-- UNION ALL performed by concatenation
SELECT data FROM #A 
UNION ALL 
SELECT data FROM #B;

-- Add indexes to provide sorted input (optimiser now has more options)
CREATE INDEX nc1 ON #A (data);
CREATE INDEX nc1 ON #B (data);

-- UNION ALL performed by Merge Concatenation
SELECT data FROM #A 
UNION ALL 
SELECT data FROM #B ORDER BY data;

-- Stream Aggregate Distinct on the inputs to a Merge Union
SELECT data FROM #A 
UNION 
SELECT data FROM #B;

-- Stream Aggregate Distinct on the inputs to a Merge Concatenation, then another Stream Aggregate Distinct
SELECT data FROM #A 
UNION 
SELECT data FROM #B OPTION (CONCAT UNION, ORDER GROUP);

DROP TABLE #A, #B;

SQL is not a truly relational language and one of its non-relational features is that SQL tables and queries can contain duplicate rows. The need to support duplicates complicates the language and its implementations very significantly. This is arguably SQL's most serious flaw (at least that's what Ted Codd thought).

SQL supports ALL and DISTINCT versions of some operations. The ALL keyword means duplicate rows are included while the DISTINCT keyword eliminates duplicates. To make matters worse, although both keywords are optional the behaviour when neither one is specified is not consistent. In the SELECT clause ALL is the default but with the UNION operator DISTINCT is the default.