Tuesday, November 22, 2005

SQL Server: minus operator

Lets see some Database stuff.

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.

4 comments:

  1. Hi Vijesh,

    I think you can achieve that by using EXCEPT operator:

    Select * from T1 except Select * from T2

    The query will show all rows exist in T1, but not exist in T2.

    Regards,


    Dapid Candra

    ReplyDelete
  2. thanks Dapid your suggestion helped.

    ReplyDelete
  3. as far as i know, except does not exist in 2000; only after 2005 it came.

    ReplyDelete
  4. 'except' works similar to MINUS. Thanks.

    ReplyDelete