Re: sub-query optimization

On 14 Feb 2003, Brad Hilton wrote:

> I am hoping someone can help explain why modifying the following query
> can effect such a huge change in speed.  The query is:
>
>       select * from articles
>       where exists
>        ( select 1 from article_categories
>          where
>          article_categories.article_id = articles.id and
>          article_categories.category_id is null
>        )
>
> The original query was much more complex, but I have trimmed it down to
> highlight the problem.  The query above also manifests the problem.  OK,
> the above query (with 100,000 records in the articles table) takes 1292
> msec (see output below).  If I modify the query slightly:
>
> --------
> select 1 from article_categories
>   -->
> select 1 from articles, article_categories
> ---------

After putting the latter in the subselect do you actually have the same
query?  In one case articles is an outer reference for the particular
row.  In the other it's a reference to the copy of articles in the
subselect.  Wouldn't that give the wrong results when you have any matches
(since there'd exist a row from the subselect even if it wasn't the one
matching the outer query)?