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:
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.