Q

Adding a not exist to a subquery with several joins

A MySQL expert describes how to rewrite NOT EXISTS as joins when trying to add 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?

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

Dig deeper on Open source databases

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataCenter

SearchServerVirtualization

SearchCloudComputing

SearchEnterpriseDesktop

Close