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.
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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 Open source databases
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.