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