Quote of the day (from Oracle’s Ask Tom)

Posted by Andrew on September 28, 2006

“When someone tells me “X is better than Y”, I say “show me, tell me why, show me why, teach me how to measure that what you say is likely true”

Amen.

As an aside, I found this while trying to figure out which was better out of joins vs. subqueries. Here’s why:

“they (joins/subqueries) are semantically different.

Use a subquery when you need no columns from the tables referenced in the subquery. Use a join when you do need some of the columns.

select * from emp where deptno in ( select deptno from dept );

would be “better” than:

select emp.* from emp, dept where emp.deptno = dept.deptno;

And remember, a subquery cannot simply be replaced by a join (and vice versa), since they often result in DIFFERENT ANSWERS.

The optimizer knows what to do - use the proper construct based on the question being asked. subqueries and joins are NOT interchangeable in general.”