Did you know that you can re write all sub-selects as SQL Joins? This comes handy when you are using a DB which does not support sub-selects (for eg. MySQL 3.x)
Take the following Sub-Select for instance:
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
The above can be re written as:
SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id;
Now, consider this Sub Select:
SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);
this can be rewritten as:
SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL;
Sub Selects/Joins help to distribute work load to the database server rather than filter data at the front end.
