TSql Practical SQL Examples (Except/Intersect/Union)

This is the 6th post on practical T-SQL; this post focuses on joining tables this time to continue the first one on SELECT, where you would start with…

Except/Intersect/Union

Some mathematical background…

 

 

Union puts the contents of two or more tables together

 

Intersect looks at what the tables have in common

 

Except returns the differences between tables

Venn diagrams;

set A is the blue circle (left) and its interior,

set B is the red circle (right) and its interior.

 

N>When you append tables with the Union operator, there must be an equal number of columns from each table.

N>If the data types of the two columns are incompatible the union fails

 

— get distinct EnglishProductName

SELECT
COUNT
(DISTINCT EnglishProductName)

FROM dbo.DimProduct

————-

–504

N> We have 504 EnglishProductName product

 

— union

SELECT EnglishProductName

INTO #Products1

FROM dbo.DimProduct AS P

WHERE EnglishProductName <
‘B’

ORDER
BY EnglishProductName

SELECT EnglishProductName

INTO #Products2

FROM dbo.DimProduct AS P

WHERE EnglishProductName >=‘B’

ORDER
BY EnglishProductName

–(3 row(s) affected)

 

–(601 row(s) affected)

 

Using temp table to collect data from DimProduct , partitioning on EnglishProductName

SELECT
*

FROM #Products1

UNION
ALL

SELECT
*

FROM #Products2

–(606 row(s) affected)

 

SELECT
*

FROM #Products1

UNION

SELECT
*

FROM #Products2

–(504 row(s) affected)

 

You have different results because union all will remove duplication of value of EnglishProductName coming from rows with the same EnglishProductName value and different value for the other column in DimProduct

 

SELECT
*

FROM #Products1

INTERSECT

SELECT
*

FROM #Products2

–(0 row(s) affected)

 

They have noting in common by design

 

SELECT EnglishProductName

FROM dbo.DimProduct

EXCEPT

SELECT
*

FROM #Products2

 

–Adjustable Race

–All-Purpose Bike Stand

–AWC Logo Cap

 

–(3 row(s) affected)

N> try

SELECT EnglishProductName

FROM #Products1

EXCEPT

SELECT *

FROM #Products2

You’ll get the same value before as the data in the tables is not common

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s