Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL - correlated subqueries

 
Janeice DelVecchio
Saloon Keeper
Posts: 1809
12
Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Janeice,
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.
 
Janeice DelVecchio
Saloon Keeper
Posts: 1809
12
Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're right.... I don't like it

Okay, so I was thinking that creating a table for the average square feet and doing a query off that would be better.... is that another way to skin this cat, or am I totally off base?
 
Vijitha Kumara
Bartender
Pie
Posts: 3918
10
Chrome Fedora Hibernate
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Janeice DelVecchio wrote:... so I was thinking that creating a table for the average square feet and doing a query off that would be better

Then you need to update it all the time (or in a given time interval) when new records are addded to the master table....
 
Janeice DelVecchio
Saloon Keeper
Posts: 1809
12
Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ahhhh.... I see.

Thanks!
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Or create a temp table/stored procedure that does it on demand.

If the performance of the query isn't as critical, there is an advantage to having it all in one statement.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic