Check if combination of fields in Table1 exists in another Table2 (SQL)

Question

I need to check whether a combination of values in my table A exists in the specified corresponding set of columns in a different table, B.

So far, I'm doing this, which doesn't seem very elegant or efficient:

select * from table1 where 
colX_table_1 NOT IN (select colX_table_2 from table2) 
and 
colY_table_1 NOT IN (select colY_table_2 from table2) 

Is there a better/faster way to do this combination check (colX_table_1,colY_table_1) -> (colX_table_2,colY_table_2)?

Answer 1

The query you gave evaluates each field separately:

select * from table1 where 
colX_table_1 NOT IN (select colX_table_2 from table2) 
and 
colY_table_1 NOT IN (select colY_table_2 from table2) 

This is not merely unelegant, as you claim, it is wrong, as it does not enforce combinations. E.g., consider the following tables:

table1:
------
colX colY
1    1

table2:
------
colX colY
1    2
2    1

According to your post, you are looking for a query that would return the row in table1, since such a combination does not exist in table2. However, in the given query, each part of the where clause evaluates to false, and the row is not returned.

Instead, in order to check the combination and not each column individually, you could use an exists condition:

SELECT *
FROM   table1
WHERE  NOT EXISTS (SELECT *
                   FROM   table2
                   WHERE  table1.colx = table2.colx AND
                          table1.coly = table2.coly)

Answer 2

It depends a bit on your data but this worked in my case:

select *
from table1 
where colx||coly not in (select colx||coly from table2)

This notation is just a string concatenation: ||