Avoiding sub-queries can be essential to reduce long run-times on large data sets.
However, 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 the 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, even on large data sets.
Run-time of such SELECT statements and sub-queries 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.
Avoiding sub-queries can be essential to reduce long run-times on large data sets, and the above technique demonstrates how to achieve this while at the same time producing identical results to “NOT EXISTS”.
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.
If you have any questions or would like to find out more about sub-queries, ANSI joins and Informix, simply contact us.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.