• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Correlated subquery

 
Ranch Hand
Posts: 65
7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So I'm struggling to understand the declarative nature of SQL (or SQL altogether). Everything was well until I came across the concept of correlated subquery. According to Wikipedia, "the [correlated] subquery is evaluated once for each row processed by the outer query." I thought declarative programming specifies the-what, not the-how. So is thinking in terms of "rows processed" declarative? This concept (of correlated subquery) made me question my whole understanding of SQL syntax. I googled alot, but what I found did not clear my confusion.

Btw, I have some knowledge of relational algebra, in case you think it would help in the discussion.
Thank you...
 
Bartender
Posts: 598
26
Oracle Notepad Linux
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Ramsin Khoshaba wrote:So I'm struggling to understand the declarative nature of SQL (or SQL altogether). Everything was well until I came across the concept of correlated subquery. According to Wikipedia, "the [correlated] subquery is evaluated once for each row processed by the outer query." I thought declarative programming specifies the-what, not the-how. So is thinking in terms of "rows processed" declarative? This concept (of correlated subquery) made me question my whole understanding of SQL syntax. I googled alot, but what I found did not clear my confusion.


SQL is easy once it clicks, until then though, it can seem daunting. Fear not, correlated subqueries are very easy to understand, though they are usually avoided due to more efficient alternatives.

Anyway, a correlated subquery is merely a type of subquery. Well, it isn't really a type inasmuch as it is an important factor of some subqueries. Here's an example of a query, and then the same query using a correlated subquery:

Note that to get Formed, the query had to connect the party of the candidate with the party table. This is a called a correlation simply because it correlates the data in the two tables.
 
Ramsin Khoshaba
Ranch Hand
Posts: 65
7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Now I have read some sections of the old standard,
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

So the search condition is applied to each record of a result. When the search condition includes correlated subqueries and the search condition is applied to a record, the correlated subquery references the current record.

I was trying to figure out this code,

And it says: Select all students, for each of which there is no CS course that he/she has not taken.
But, this took me ages to figure out; plus it was closer to an intuitive guess, than educated certainty.
 
Brian Tkatch
Bartender
Posts: 598
26
Oracle Notepad Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Ramsin Khoshaba wrote:And it says: Select all students, for each of which there is no CS course that he/she has not taken.
But, this took me ages to figure out; plus it was closer to an intuitive guess, than educated certainty.


Well, that's a confusingly written query. There should only be one NOT EXISTS() to rule them out.

In general, EXISTS() clauses have to correlate to an enclosing query to be of any use, as otherwise, why care if there is matching record? But, EXISTS() are not usually referred to as correlated subqueries, it instead is simply referred to as an EXISTS(). Correlated subqueries generally refer to queries like the example shown above.

 
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Brian Tkatch wrote:Correlated subqueries generally refer to queries like the example shown above.


Great explanation of correlated subqueries, even with a nice code example Have a cow, Brian!
 
Brian Tkatch
Bartender
Posts: 598
26
Oracle Notepad Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Roel De Nijs wrote:

Brian Tkatch wrote:Correlated subqueries generally refer to queries like the example shown above.


Great explanation of correlated subqueries, even with a nice code example Have a cow, Brian!


Thank you; i do love my cows.

I can't edit the post anymore. Upon re-reading it, i wanted to clarify the last sentence. "This is a called a correlation simply because it correlates the data in the two tables." That's technically incorrect. The correlation refers to the query, not the tables. So, perhaps it ought to read, "This is a called a correlation simply because it correlates the inner query (in the parenthesis) and the outer query (the enclosing, or outer, query) in the inner query's WHERE clause."
 
    Bookmark Topic Watch Topic
  • New Topic