• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Correlated subquery

 
Ramsin Khoshaba
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...
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • 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: 567
25
Linux Notepad Oracle
  • 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.

 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • 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: 567
25
Linux Notepad Oracle
  • 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."
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic