Writing SQL Sub Selects as JOINS...

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.