2

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.

McNets
  • 10,352
  • 3
  • 32
  • 61
Ma'or
  • 95
  • 1
  • 2
  • 11
  • add isnull(age,'') and check – Pream Mar 10 '17 at 08:06
  • Or maybe set the EXISTS like `EXISTS (select 1 from secondTable where firstTable.name = secondTable.name AND (firstTable.age = secondTable.age OR firstTable.age IS NULL AND secondTable.age IS NULL)))`? (I just expanded your last clause to include the NULL case) – MK_ Mar 10 '17 at 08:07
  • what do you mean " treat them as unknown"? You mean remove duplicate? – Vijunav Vastivch Mar 10 '17 at 08:11
  • I mean like in SQL's three value'd logic: where null=null is neither true nor false. – Ma'or Mar 10 '17 at 08:25
  • 1
    @MK_ your solution works lika a charm!!!! If you'll turn it in to an answer, I will be more then happy to upvote it! – Ma'or Mar 10 '17 at 09:28
  • @מאורווינר - sure thing, I will, just give me a few minutes now. :) – MK_ Mar 10 '17 at 09:40
  • 1
    Then mark him as answered.. to @MK_ – Vijunav Vastivch Mar 14 '17 at 09:32
  • @reds - do you mean I should upvote him? I have 0nly 11 reputation, so I can't do that. ): – Ma'or Mar 15 '17 at 07:14
  • Not upvote just make it an answered.. to do it click the disabled check below located in down vote arrow.. its just below down vote arrow. Otherwise, You can do to upvote even you have just 11 reputation. – Vijunav Vastivch Mar 15 '17 at 08:17
  • Done. Sorry about that, I'm new here.... – Ma'or Mar 22 '17 at 15:11

2 Answers2

0

Try this:

 select distinct a.idx,a.name,a.age,b.name,b.age from first_table as a
 inner join 
 second_table as b
 on a.name = b.name and a.age = b.age

This one display only the record with same value both first_table and second_table

And this query Display not in second_table and union both table if has:

select distinct a.idx,b.name,b.age from first_table as a
inner join 
second_table as b
on a.name = b.name and a.age = b.age

union all

select a.idx,a.name,a.age 
from first_table as a where a.name not in(select name from second_table)
Vijunav Vastivch
  • 4,153
  • 1
  • 16
  • 30
0

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. =)

MK_
  • 1,139
  • 7
  • 18