Faster alternative to "NOT IN" queries

Employee Table: SSN, Name, Address, Department, HireDate
Dependent Table: EmpSSN, Name, DOB

Query A shows a typical "NOT IN" query to find all the employees without dependents:

SELECT Name 
FROM Employee 
WHERE SSN NOT IN (SELECT EmpSSN 
                  FROM Dependent); 

Query B shows the alternative using outer join notation. Query B will run much faster then Query A, particularly on large tables.

SELECT Name 
FROM Employee e, Dependent d 
WHERE e.SSN = d.EmpSSN(+) and 
            d.EmpSSN is null; 

But what if your primary key is a composite key? You can still use this type of query, with a little more setup. For example, say you have two different databases containing dependent information. You want to consolidate them, but don't want to introduce duplicates. One way is to identify which entries in db1's Dependent table are not in db2's dependent table - these are the values to keep. We will use the Dependents table for our example. For this example, EmpSSN+Name is the Primary key.

Step 1: Create views on both tables, generating fields that are concatenations of the composite fields.

Create view db1Depdnts as 
(Select EmpSSN, Name, DOB, EmpSSN||' '||Name as keyField1 
FROM db1.Dependents); 

Create view db2Depdnts as
(Select EmpSSN, Name, DOB, 
EmpSSN||' '||Name as keyField2 
FROM db2.Dependents); 

Step 2: Create indexes on the concatenations of the composite fields.

Create index Indx_db1Depdnts on 
db1.Dependents(EmpSSN||' '||Name); 

Create index Indx_db1Depdnts on 
db2.Dependents(EmpSSN||' '||Name); 

Step 3: Generate and run Query C. This will give the names of the Dependents in db1 that are not in db2's Dependents Table.

SELECT d1.Name 
FROM db1Depdnts db1, db2Depdnts db2 
WHERE db1.keyField1= db2.keyField2(+) and db2.keyField2is null; 

For More Information

• What do you think about this tip? E-mail us at [email protected] with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, and DB2 gurus are waiting to answer your toughest questions.