Code Project

Link Unit

Tuesday, May 16, 2006

Union to make missing Operator INTERSECT,MINUS

SQL Server misses MINUS and INTERSECT , whereas in Oracle they are provided as part of the parcel.

But we can create the same effect of these operators using UNION and group by

Let us say we have two sets(tables) with same structure

StageShow(table)
RollNo int references Student
...
&
Sports(table)
RollNo int references Student
...

StageShow
1
2
5
....

Sports
1
3
5
.....

In Oracle Finding out MINUS and INTERSECT is easy by using the respective operator
i.e All students participating in both activities (although it can be a single activity table ,but just to show how we can implement I had done splitting )

Select Rollno from StageShow
Intersect
Select Rollno from Sports


Similarly we can say MINUS as
Select Rollno from StageShow
MINUS
Select Rollno from Sports

Now how to do it in SQL Server

How to implement INTERSECT ?

Select Rollno From (
Select 1 AS dummy,Rollno From StageShow
Union ALL
Select 2 AS dummy,Rollno From Sports
) X group By Rollno having count(*)=2

How to implement MINUS (A-B) Rows in A not in B?

Select Rollno From (
Select 1 AS dummy,Rollno From StageShow
Union ALL
Select 2 AS dummy,Rollno From Sports
) X group By Rollno having Max(Dummy)=1

No comments: