Except/Intersect/Union

1. Union

Syntax:

-- union 10 records
select * from Table1
union
select * from Table2
--
select EnglishProductName from Table1
union
select EnglishProductName from Table2

Analysis:

You can union more than two tables --- simply add more Union operators and Select statements. A union is not the same as a join. A join puts the tables together horizontally, usually based on a primary-to-foreign-key relationship --- it’s for creating a single result from often dissimilar tables (denormalization). A union appends tables; is puts them together vertically --- it’s for creating a single result from similar tables.

2. Union All

Syntax:

-- union all 11 records
select * from Table1
union all
select * from Table2

Analysis:

The record that is common to both tables. The union operator suppresses duplicates, while union all does not.

3. Intersect

Syntax:

-- intersect 1 record
select * from Table1
intersect
select * from Table2

Analysis:

Intersect returns the common record from the two tables. It shows just one copy of the record, not both of them. This is a simple, yet powerful, set operation to see if you have the same data in more than one table.

4. Except

Syntax:

-- except 8 records
select * from Table1
except
select * from Table2

Analysis:

Except is showing all of the records from Table1 that do not also appear in Table2. Please not that Table1 is the first table in the query.