Check 2 tables by 2 fields 2

[Andrzejek]

If I have some ‘unrelated’ tables (no PK no FK relation between tables), I can check if I have some IDs in one table that are NOT in the other table by:

[pre]
SELECT B.ID
FROM SomeTable B
WHERE (NOT (B.ID IN
(SELECT A.ID FROM OtherTable A)))[/pre]

And that works fine.

Now, if I want to check if there are 2 fields that should match between those 2 tables, I tried:

[pre]
SELECT A.ID || '*' || A.PN
FROM SomeTable A
WHERE (NOT ((A.ID || '*' || A.PN)
IN (SELECT B.ID || '*' || B.PN FROM OtherTable B)))[/pre]

Which is pretty much: concatenate ID and PN (with * between) in both tables and find ‘missing’ records.

That also works, but it takes a long time to process.

Is there any other, better, faster, more elegant way of checking tables by 2 (or more?) fields?


Have fun.

---- Andy

 

[LKBrwnDBA]

Yes: Create a temporary index.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

. . . and use NOT EXISTS ( ) . . .

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Andrzejek]

Thank you LKBrwnDBA, but I cannot create temporary indexes on the tables. :-( I don’t really ‘own’ the tables, I just have to work with them (without making any modifications/changes/additions etc. even temporary). All what I want to do is to find those ‘orphan’ records. And since the tables are joint on 2 or more fields, I am trying to find the most efficient way to do that.
Right now my attempt takes about 15 sec to compare 2 tables, and I may have many pair of tables to compare.


Have fun.

---- Andy

 

[LKBrwnDBA]

How big the tables?


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

PS: You could create indexes on your own work schema...

And also/or then:

a) Use: NOT EXISTS...

b) If the tables are small, load the compare columns into pl/sql tables and compare simulating anti-join.

SELECT A.Id, A.Pn
  FROM Sometable A
 WHERE NOT EXISTS
          (SELECT '?'
             FROM Othertable B
            WHERE B.Id = A.Id
              AND B.Pn = A.Pn);

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Andrzejek]

Thank you LKBrwnDBA
This works a LOT faster

[pre]
SELECT A.Id, A.Pn
FROM Sometable A
WHERE NOT EXISTS
(SELECT '?'
FROM Othertable B
WHERE B.Id = A.Id
AND B.Pn = A.Pn); [/pre]

Have fun.

---- Andy

 

[carp]

You might also want to try

SELECT Id, Pn FROM tableA
MINUS
SELECT Id, Pn FROM tableB;

This will typically run faster than using a subquery.

As for checking to see if there are matching fields, you could try a plain old inner join:

SELECT a.id, a.pn
FROM tableA AS a
     INNER JOIN tableB AS b
     ON a.id = b.id AND a.pn = b.pn;

This is not only easier to read, but should also be faster than using a subquery.

 

[carp]

Ooops - just noticed you wanted the records in B that aren't in A. So the code above should be:

SELECT Id, Pn FROM tableB
MINUS
SELECT Id, Pn FROM tableA;

 

[Andrzejek]

Thank you carp,
I totally forgot about the MINUS option
That also gives me the easy way for checking on more than just 2 filelds.

Have fun.

---- Andy