Checking for two fields in a record

Question

I am trying to eliminate only records where both fields match the given values from another table.

SELECT CODE, NUM 
FROM TABLE_A
WHERE  (CODE not in (SELECT CODE FROM TABLE_B) AND NUM not in (SELECT NUM FROM TABLE_B))

However this seems to eliminate ALL records that have either of these values. I need to eliminate only the records that have both matching a given record from TABLE2. An OR seems counter-intuitive here?

It's not working because your subqueries boil down to `code NOT IN (all code values from B) AND num NOT IN (all num values from B)`. There's no knowledge of WHICH records those code/num values came from, so you're not checking if the SAME record contains both values. just **ANY** records. — Marc B, Nov 12 '14 at 14:55

score 0 · Answer 1 · answered Nov 12 '14 at 14:52

0

SELECT CODE, NUM 
FROM TABLE_A TA
WHERE  
not exists (select 1 from TABLE_B TB where TB.CODE=TA.CODE)
and 
not exists (select 1 from TABLE_B TB where TB.NUM=TA.NUM)

answered Nov 12 '14 at 14:52

hazimdikenli

5,709
8
37
67

This does the same thing the OP is already doing, with better performance: but the question isn't how to speed it up, the question is how to change it to consider both values at the same time. – AHiggins Nov 12 '14 at 15:02
Yes. Same result unfortunately. – user1327418 Nov 12 '14 at 15:07
@user1327418 If that is not what you need then Multisync's answer should do what you asked for. Otherwise please provide sample data and the desired output. – hazimdikenli Nov 12 '14 at 15:15

Multisync · Answer 2 · 2014-11-12T15:21:27.910

0

SELECT CODE, NUM 
FROM TABLE_A a
WHERE NOT EXISTS (SELECT 1 FROM TABLE_B b where b.CODE = a.CODE and b.NUM = a.NUM);

The EXISTS keyword tests for the existence of any rows in a subquery. If no rows are found, the answer is FALSE. Otherwise, the subquery returns TRUE. NOT EXISTS reverses the results.

edited Nov 12 '14 at 15:21

answered Nov 12 '14 at 14:53

Multisync

8,657
1
16
20

My subquery will indeed be returning a lot of rows. I need to compare row to row. – user1327418 Nov 12 '14 at 15:06
@user1327418 It's a correlated subquery which is executed for each row of TABLE_A. And it's generally faster then NOT IN. Are you sure that the query "SELECT * FROM TABLE_B b where b.CODE = x and b.NUM = y" returns a lot of rows? – Multisync Nov 12 '14 at 15:10
@user1327418 Anyway in your case you have to full scan both tables so using NOT EXISTs is appropriate here. As I know Oracle stops searching if it finds something with NOT EXISTS. – Multisync Nov 12 '14 at 15:16
@user1327418 I gave you Oracle behaviour of EXISTS. You may also check this question if you're interested in performance: http://stackoverflow.com/questions/6777910/sql-performance-on-left-outer-join-vs-not-exists – Multisync Nov 12 '14 at 15:24

score 0 · Answer 3 · answered Nov 12 '14 at 15:16

If you're worried about the performance of NOT EXISTS, you could also try to check the same condition with a LEFT OUTER JOIN and a WHERE clause:

SELECT 
    a.CODE, 
    a.NUM 
FROM 
    TABLE_A a
     LEFT OUTER JOIN -- return all from table A, and matches from table B
    TABLE_B b ON 
        a.CODE = b.CODE AND 
        a.NUM = b.NUM 
WHERE b.CODE IS NULL -- filter out the ones that matched; think of this as a reverse INNER

Checking for two fields in a record

3 Answers3