Abstract

It is sometimes necessary to identify rows in one table for which there is no associated row in another table. An example would be an order for which there are no order detail lines. The conventional approach is to use NOT EXISTS (sub-query), but this can be extremely slow for large data sets. This article demonstrates how an ANSI join can achieve the same result much more efficiently.

Content

Run-time of such SELECT statements can be significantly reduced by using an ANSI outer join and post-join condition for NULLs, as shown in the real example below used by the Oninit Archiver module on the Openbet application:

Copy to Clipboard


Informix, in theory, attempts to “flatten” sub-queries, but this does not seem very effective in the above real-world scenario. In the “before” version, for each row in the outer query, a separate fetch is made via the inner query. In the “after” version, the two tables are joined in a single pass in one query, and rows discarded if the resulting joined key value is NULL.

Conclusion

Avoiding sub-queries can be essential to reduce long run-times on large data sets, and the above technique manages this while producing identical results to “NOT EXISTS”.

Disclaimer

The code fix suggested above is provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.