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

Advertisements

7 Responses to “UNION and new feature of SQL Server 2005 (INTERSECTION, EXCEPT) Difference of INTERSECT and INNER JOIN:”

  1. rajat Says:

    how can find common data from same table
    i am explaining : suppose only one table name is Netpack and having 2 fields netpackid and channel name . each netpackid having a channels name . Problem is that how can find common channels name from all netpackids.means 1 id having channel name like CNN, HALL,APL ,TLC,TNT etc.
    2 id id having channel name like CNN, AMC,TLC,TNT etc. 3 id having channel name like SC, ESPN,SPK,NICK,MTV,TLC,CNN,TNT etc.
    4 id having channel name like CNN, ESPN,FX,HIST,TLC,VH1,TNT etc.

    then my result show common channels CNN , TLC, TNT
    and i have ids in this table near about 12 or 15 and each netpackid having a channels name .

    • riteshshah Says:

      hope this would help.

      select distinct n1.channelname from netpack n1
      full outer join
      netpack n2 on n1.netpackid<n2.netpackid
      and n1.channelname=n2.channelname
      where n2.netpackid is not null and n1.netpackid is not null

  2. paresh patel Says:

    hi i want to more complex and advanced example…….

  3. riteshshah Says:

    what exactly you want?

  4. vinay Says:

    This is an useful post.

  5. Pankaj Says:

    select * from appmail123

    select * from app_mail123
    declare @str varchar(500)
    select @str = coalesce(@str+’,’,”)+ mailid from appmail123
    union
    select @str = coalesce(@str+’,’,”)+ mailid from app_mail123

    select @str

    but in o/p it is showing only one record randomly


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

%d bloggers like this: