Problem solve Get help with specific problems with your technologies, process and projects.

Faster alternative to "NOT IN" queries

There is a faster alternative to using "NOT IN" queries. Here's how.

There is a faster alternative to using "NOT IN" queries. Let's first define the tables:

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:

FROM Employee 
                  FROM Dependent); 

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

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.

Dig Deeper on Oracle and SQL