| Neg. logic in SQL [message #374941] |
Thu, 12 July 2001 10:33  |
JBolin
Messages: 1
Registered: July 2001
|
Junior Member |
|
|
I'm running the following SQL to determin what records are not matched in the contacts table.
select count(*) from tpm.contacts
where location_id not in (select location_id from tpm.location_id);
the contacts table has 326000 records and the location table has 9013.
This is running for hours. Is there better way to determine what records on contacts table do NOT have a match on the location table?
|
|
|
|
| Re: Neg. logic in SQL [message #374946 is a reply to message #374941] |
Thu, 12 July 2001 21:34  |
Sudhakar Atmakuru
Messages: 58
Registered: May 2001
|
Member |
|
|
There is only one (I guess) better idea is to achieve this by using MINUS operator. This should increase the performance of your query also, I bet.
SELECT COUNT(*) FROM (
SELECT LOCATION_ID FROM TPM.CONTACTS
MINUS
SELECT LOCATION_ID FROM TPM.LOCATION_ID)
Explanation: First, the instant virtual table (query) extracts the rows from TPM.CONTACTS table those which do not have any match in TPM.LOCATION_ID table, and finally the outer COUNT(*) counts the extracted rows of TPM.CONTACTS rows. Here the problem is the minus eliminates duplicates. So any duplicate record in the TPM.CONTACTS table does not count.
If you concern about it to be counted the duplicates,
try this out
SELECT COUNT(*) FROM TMP.CONTACTS WHERE
LOCATION_ID IN (
SELECT LOCATION_ID FROM TMP.CONTACTS
MINUS
SELECT LOCATION_ID FROM TMP.LOCATION_ID);
Explanation: First the subquery extracts the unmatched rows from TMP.CONTACTS and then the main/outer IN operator checks and counts the number of records those match any of the extracted unmatched records. Since the subquery is not a corelated query (which each time checks with the parent record), first the query extracts the unmatched records only once and the outer IN operator checks every record's match with the already extracted unmatched list. This should improve the performance of your query while achieving the desired result.
Hope this helps. Please let me know your concerns.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
