| Finding Orphan Records [message #373108] |
Tue, 27 March 2001 16:56  |
pui
Messages: 2
Registered: March 2001
|
Junior Member |
|
|
|
What's the most efficient way to find orphan records in TABLE_B. Parent table = TABLE_A, child table = TABLE_B?
|
|
|
|
|
|
| Re: Finding Orphan Records [message #373142 is a reply to message #373108] |
Thu, 29 March 2001 07:58  |
Phenoracle
Messages: 35
Registered: March 2001
|
Member |
|
|
I often
find NOT IN slows down my queries.
ther are may options for doing this and if the speed of which you wish to
retreive your data is of importants then try one of these
DETERMINE HOW MANY RECORDS THE DRIVING TABLE WILL VALIDATE AGAINST
DO YOU NEED TO USE INDEXES OR FULL TABLE SCANS
AND MANY MORE.
Select a.Missing_Record from table_a a
where not exists (select 1 from table_b b where b.missing_record = a.missing_record);
If your Driving table has no restrictions and will query
against a large amount of data try this
(forget the cost)
Select a.Missing_Record
from table_a a
, (Select a.Missing_Record
from table_a c
MINUS
Select a.Missing_Record
from table_b d)e
where e.missing_record = e.missing_record;
Hope this is of help
Phenoracle
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
