Adding a not exist to a subquery with several joins

I'm trying to add a not exist subquery in my where statement and have several other joins ahead of it. I'm having difficulty determining where to put the NOT EXIST as the subquery is named as a column -- ie blue.color in (select * from .....). Where would I put the NOT EXIST In this case?

    Requires Free Membership to View

    Login

Aliases defined in the select list, whether aliases of simple columns, results of expressions or subqueries, should not appear in the where clause. To use a subquery in the where clause, you should redefine it.

Using NOT EXISTS is one way to find rows which appear in one table but do not appear in another: 

SELECT
  t1.*
FROM
  t1
WHERE
  NOT EXISTS (SELECT * FROM t2 WHERE t1.id = t2.id)

Such correlated subqueries can often be rewritten as joins: 

SELECT
  t1.*
FROM
  t1
  LEFT JOIN t2 ON (t1.id = t2.id)
WHERE
  t2.id IS NULL;

Test both approaches on your data. A different index or table size can make one approach faster than the other in different situations. Of course, if you're on a hosted server that still hasn't upgrade from 3.23 or 4.0, subqueries aren't available anyway, so you'll have to use the join form.

This was first published in June 2007

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top