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.
Dig Deeper on Linux servers
Related Q&A from Scott Noyes
Get suggestions for choosing MySQL character sets and field collations from expert Scott Noyes. Continue Reading
A MySQL expert describes two ways that the multi-master support can be used. Continue Reading
An expert describes where to find information on detection deadlock algorithms in MySQL. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.