Getting Non-Matching Records

[bigbalbossa]

I don't get the chance to work w/ SQL as much as i'd like, so i've got a fairly simple question.

I'm trying to find all records from table A that DON'T match table B. This is an Oracle DB...Normally, i would handle this like so:

select a.*
from table1 a, table2 b
where a.LINK = b.LINK(+) 
and b.LINK is null

But now I have to match on two fields concat'd together and this is causing an error: "ORA-01417: a table may be outer joined to at most one other table". Here's the code:

select a.*
from table1 a, table2 b
where a.LINK||a.LINK2 = b.LINK||b.LINK2(+) 
and b.LINK||b.LINK2 is null

Can somebody explain this and recommend a new way?

Thanks!

 

[PHV]

SELECT a.*
FROM table1 a LEFT JOIN table2 b
ON a.LINK = b.LINK AND a.link2 = b.link2
WHERE b.link IS NULL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

Another way:
SELECT *
FROM table1
WHERE LINK||link2 NOT IN(SELECT LINK||link2 FROM table2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bigbalbossa]

should both of these methods return the same count even if I have dup records? Which do you think would run faster?

Thanks!

 

[dnoeth]

If you don't want to think about duplicate records or if you have to deal with possible NULL values within your data use a NOT EXISTS, this is also probably the most efficient way:

select *
from table1 a
where NOT EXISTS
(select * from table2 b
 where a.LINK = b.LINK and a.link2 = b.link2)

Dieter

 

[bigbalbossa]

Thanks Dieter,

I have to keep duplicate records, and the first two methods mentioned above are taking FOREVER to run. Thoughts?

 

[PHV]

Thoughts?
Obviously, you have created index on table1(LINK,link2) and table2(LINK,link2), did you ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gwinn7]

I am not sure how this example is better on performance...

"SELECT *
FROM table1
WHERE LINK||link2 NOT IN(SELECT LINK||link2 FROM table2)"

The LEFT JOIN solution has worked far more quickly for me than the NOT IN clause. The NOT IN clause has always cause performance slowdowns.

If you want to eliminate duplicate records, you could, if applicable, try the DISTINCT or DISTINCT ROW clause. Like this...

SELECT DISTINCT * FROM mytable LEFT JOIN mytable2 ON mytable.a = mytable2.a WHERE mytable2.a IS NULL.


Gary
gwinn7