Quote of the day (from Oracle’s Ask Tom)
“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.”
Trackbacks
Use this link to trackback from your own site.
It is a good quote, although I think it is ironic that Tom then doesn’t actually say why a subquery is better than a join when no columns from the table are needed in the second :)
Hi James, thanks for commenting. Although perhaps not directly related to the quote, he does point out that the two approaches can result in different answers. He also makes a good point about premature optimization - Strive to the clearest, most intention revealing code and let the optimizer (or compiler) figure out how to make it fast.