Avoid Correlated Subselects: A correlated subselect is a nested select that refers to a column from the outer select. Here is an example that uses product.id as a correlation column to find all products that have no sales orders:
select product.id
from product
where not exists (
select sales_order_items.id
from sales_order_items
where sales_order_items.prod_id = product.id )Correlated subselects can be very slow if the inner result set is re-selected for each and every candidate row in the outer result set. Alternative SQL can sometimes look rather bizarre but it's usually worth the effort. In Watcom SQL the following select runs almost 4 times faster by using an outer join instead of a correlated subselect:
select product.id
from product left outer join sales_order_items
on product.id = sales_order_items.prod_id
where IfNull ( sales_order_items.id, 1, 0 ) = 1