I'm asking this because I want to know if this is something people actually use. I'm using the O'Hearn SQL Certified Expert exam guide and I think either the example is not good or the description is worse...
So here's the example O'Hearn gives for a correlated subquery (page 361)
Okay. So I understand (I think) what is going on here. For each row of A, the subquery calculates the average square feet of A.ROOM_STYLE, and selects if A.SQ_FT is greater. Even if there's only 4 types of rooms and hundreds (thousands?) of A.CABIN_IDs.
To me, and my untrained brain, this looks like a blatant and utter disregard for time/performance. Does anyone use this in real life? Is there a better example where this isn't so wasteful? Is this just something I need to know for certification/college and then I can forget all about it?
When you do things right, people won't be sure you've done anything at all.
You're not going to like this, but "it depends." Sometimes a correlated subquery is useful and sometimes it is wasteful. It depends on what you are trying to accomplish and how big the table is. Hundreds of rows is nothing to a database. Small numbers of thousands are nothing to. I don't use a correlated subquery often, but it does happen once in a while.
Real world note: if you find yourself using "complex SQL", you want to do two things:
1) Run an explain plan to see whether there are unneeded table scans. See how we run it for JavaRanch.
2) Performance test it to see how long it takes in the real world.