UNION and new feature of SQL Server 2005 (INTERSECTION, EXCEPT) Difference of INTERSECT and INNER JOIN:

UNION and new feature of SQL Server 2005 (INTERSECTION, EXCEPT):

You might have read my previous articles on JOIN as per those articles; JOIN is multiplication of data whereas UNION is addition.

UNION does nothing other than stack the data of multiple result sets in one result sets. While using the UNION you have to consider few things like: column name, number and aliases should be mentioned in first part of the SELECT. ORDER BY clause will sort the complete result sets, it will use the column name of first SELECT statement and should be write down after the last SELECT statement.

Let us create two demo tables to use UNION.

--create first table for demonstration

CREATE TABLE CAR

(

ID int IDENTITY(1,1),

CarName VARCHAR(16)

)

--create second table for demo

CREATE TABLE BIKE

(

ID int IDENTITY(1,1),

BikeName VARCHAR(16)

)

--Insert records in CAR table

INSERT INTO CAR

SELECT 'HONDA crv' UNION ALL

SELECT 'Mahi. Raunalt' UNION ALL

SELECT 'Test'

--Isert records in BIKE table.

INSERT INTO BIKE

SELECT 'HONDA Karishma' UNION ALL

SELECT 'Bajaj DTSI' UNION ALL

SELECT 'Test'

Now, both the select statement of above two tables will be merged and will return single result sets.

--use of union all

SELECT ID,CarName FROM CAR

UNION ALL

SELECT ID,BikeName FROM BIKE

Order by CarName

Above query returns all the records of both tables. Though we gave ORDER BY for CarName but it will sort complete second column which include some data of bikeName as well. You cannot give ORDER BY on BikeName column.

Above query was just an example, you can use UNION for less than or equal to 256 tables. This is not at all small amount.

Intersection Union: Intersection Union find common row in both data sets. As soon as you read this statement, you will say, ohh, this could be done by INNER JOIN as well. So, the answer is INNER JOIN matches two table horizontally and INTERSECTION matches two datasets vertically. There is one more difference in INTERSECTION and INNER JOIN is that, Intersection query will see NULL as common and includes the row in the intersection and INNER JOIN will not even includes two different row with NULL value in result sets.

Now, have a look at INTERSET usage in below query.

--use of intersect union

SELECT ID,CarName FROM CAR

INTERSECT

SELECT ID,BikeName FROM BIKE

Order by CarName

You will get only one row which is same in both the table.

EXCEPT: Except (a.k.a. DIFFERENCE UNION) finds records exists in one data sets and not available in another datasets. In above case we have three records in CAR table but third record is exist in BIKE table as well so EXCEPT will display only first two records. Have a look at below query.

--use of EXCEPT

SELECT ID,CarName FROM CAR

EXCEPT

SELECT ID,BikeName FROM BIKE

Order by CarName

Reference: Ritesh Shah