Win a copy of Node.js Design Patterns: Design and implement production-grade Node.js applications using proven patterns and techniques this week in the Server-Side JavaScript and NodeJS forum!
  • 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Rob Spoor
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Henry Wong
  • Liutauras Vilda
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Tim Holloway
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Mikalai Zaikin
  • Piet Souris

Correlated subquery

 
Ranch Hand
Posts: 65
7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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."
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic