I had to work out a SQL problem for a contractor. In a nutshell we need to join two tables based on data not being in one of the tables. Here is the background:
Table ORD has order records and table EXPORTED has export records. ORD_ID is the primary key in the ORD table and is a field in the EXPORTED table. When you export an ORD record you create an entry in the EXPORTED table with the ORD_ID and the date in an EXPORTED_DATE field.
We want to be able to select all the ORD records that need to be exported. I am weak on SQL so I wrote it this way first, using a Where Not In:
Select *
From ORD
Where ORD_ID Not In
(Select ORD_ID From EXPORTED)
This works, but I do not know if this is the most efficient way to get this information. I looked up this way of joining tables using a Left Outer Join:
Select *
From ORD
Left Outer Join EXPORTED On
EXPORTED.ORD_ID = ORD.ORD_ID
Where EXPORTED.EXPORTED_DATE Is Null
Finally, I found this way to do the same thing using a Not Exists:
Select *
From ORD
Where Not Exists
(Select 1 from EXPORTED
Where EXPORTED.ORD_ID = ORD.ORD_ID)
In this last example we are selecting 1, a static value. Basically the sub query will result in 1 or nothing if the record exists.
Does anyone know which of these methods is the most efficient? The ORD_ID field is the primary key in the ORD table. The ORD_ID field is both the primary key in the EXPORTED table and a foreign key back to the ORD table.
I have been programming (fat) client/server applications for over 12 years and now I am going to have to learn SQL the right way to move ahead in this thin client world.
