Adding a not exist to a subquery with several joins

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

    When you register, my team of editors will also send you resources covering Linux administration and management; integration and interoperability between Linux, Windows and Unix; securing Linux and mixed-platform environments; and migrating to Linux.

    Margie Semilof, Editorial Director

    By submitting your registration information to SearchEnterpriseLinux.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchEnterpriseLinux.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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

Back to top