File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes SQL - correlated subqueries Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL - correlated subqueries" Watch "SQL - correlated subqueries" New topic
Author

SQL - correlated subqueries

Janeice DelVecchio
Saloon Keeper

Joined: Sep 14, 2009
Posts: 1732
    
  12

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.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31062
    
232

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Janeice DelVecchio
Saloon Keeper

Joined: Sep 14, 2009
Posts: 1732
    
  12

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

Joined: Mar 24, 2008
Posts: 3859

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....


SCJP 5 | SCWCD 5
[How to ask questions] [Twitter]
Janeice DelVecchio
Saloon Keeper

Joined: Sep 14, 2009
Posts: 1732
    
  12

Ahhhh.... I see.

Thanks!
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31062
    
232

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL - correlated subqueries