[SOLVED] Is there any way to run an except query on MSSQL that uses only part of the columns?

Is there any way to run an except query on MSSQL that uses only part of the columns?

What I need to do is the following:

I have in my database a table like this:

idx   |   name   |   age
------ ---------- -------
 1    |   John   |    18
 2    |   Marry  |    19
 3    |   Eric   |    17

Then I get a secondTable:

name  |  age
------ -----
Moses |   29
John  |   18
Eric  |   20

I would like to run an except query like:

select   * 
from     firstTable 
where    (name, age) not in (select * from secondTable)

and an intersect query like this:

select   * 
from     firstTable 
where    (name, age) in (select * from secondTable)

So the result for the first query will be:

2   | Marry  | 19
---- -------- ----
3   | Eric   | 17

and the result for the second query will be:

1   |  John  | 18

I've also found a solution that recommends on the following:

select  * 
from    firstTable 
where   EXISTS (select 1 
                from   secondTable 
                where  firstTable.name = secondTable.name 
                and    firstTable.age = secondTable.age))

but then if I have on both tables "john - null" it will treat them as unknown (neither equal nor un-equal). I know the reason for that, but I do need them to be equal.

The reason I need to do this is in order to preserve the current index values to the query's result.

Solution

You just need to include handling the NULL values into your query logic. It would be like so:

SELECT * 
FROM firstTable 
WHERE EXISTS (SELECT TOP(1) 1 
              FROM secondTable 
              WHERE firstTable.name = secondTable.name
                AND (
                      firstTable.age = secondTable.age
                      OR
                      (firstTable.age IS NULL AND secondTable.age IS NULL)
                    )
             );

Should work like a charm. =)