Background information

Before SQL-92 there was no possibility to use outer joins since they simply were not in the standard. To do an inner join you basically did:

SELECT * 
FROM t1,t2 
WHERE t1.id = t2.id

So there was not even the keyword JOIN that you can use today. Therefore people used subqueries quite a lot since it was the only possibility to do things an outer join can provide.

With outer joins available today there is the recommendation to rather use joins than subqueries since the Query Optimizer of major DBMS systems can handle joins better than subqueries. If you take SQL Server for example you will realize that the query optimizer tries to rewrite any subquery as a join. Since I treated that subject in my master thesis i know that Oracle DB does the same.

So you can help the query optimizer by using joins ....

continue reading