Pages

Sunday, January 20, 2008

Minus operator in SQL Server

Minus operator in Oracle: Consider two tables T1 and T2. Hence T1-T2 or T1[minus]T2 means return all rows in T1 that are NOT in T2.

Consider the tables as,
T1
Col1.........Col2
1...............A
2...............B
3...............C

T2
Col1..........Col2
1................A
2................B
3................X

As per Oracle,
Query: Select * from T1 minus Select * from T2
Result: 3.........C
Query:Select * from T2 minus Select * from T1
Result: 3.........X

Minus operator in SQL Server: But unfortunately SQL Server does not support this operator. But here is work around to make it simulate in SQL Server.
Query:
Select * from T1 where
IsNull(cast(Col1 as varchar, '')) +
IsNull(cast(Col2 as varchar, ''))
Not in
(Select IsNull(cast(Col1 as varchar, '')) +
IsNull(cast(Col2 as varchar, '')) from T2)

Result: 3.......C

Explanation: The "In" operator works as per syntax. But it could be applied only to single column. Hence the basic idea is to concatenate all the columns to a single column. Similarly with the other table columns are also concatenated to a single column. Now using the "In" operator they are filtered out. The "cast" is for converting column values to varchar, the "IsNull" to remove NULL values. This is one such idea of doing it.

1 comment:

Anonymous said...

Can u post some more like this..