EDIT: Both answers are great but I think I must clarify my question.
NOTE: I must indicate that in the following, by executed, I mean logically. I understand that the actual execution made by the particular DBMS may use a completely different approach. So what I will ask is not about the actual execution but rather about the evaluation logic of a correlated subquery.
What I particularly want to know is:
"Why a correlated subquery is executed in this way?" For example, for the example query I have given, why isn't it executed in the following way:
"Return the rows of the
cities_stores
relation whosestore_type
is equal to some row'sstore_type
instores
relation. If the resulting relation has at least 1 element, the value ofEXISTS
clause isTRUE
. Otherwise, its value isFALSE
."
but rather in this way:
"For each row processed in the outer query (let's call this row as "current row"), process the inner (correlated) subquery, using the current row's values for the attributes not present in the subquery's
FROM
clause."
and from where can I learn to think about correlated subqueries in the latter method, instead of the former one?
I hope this is not an unwise question to ask but exactly this is what I don't understand.
Correlated subqueries are highly confusing me.
Example query from here:
SELECT DISTINCT store_type FROM stores
WHERE EXISTS (SELECT * FROM cities_stores
WHERE cities_stores.store_type = stores.store_type);
Initially, I thought that EXISTS
clause would have a value that doesn't change for each row of the outer query and that value would be calculated in the following way:
"Return the rows of the cities_stores
relation whose store_type
is equal to some row's store_type
in stores
relation. If the resulting relation has at least 1 element, the value of EXISTS
clause is TRUE
. Otherwise, its value is FALSE
."
Had the EXISTS
statement evaluated in this way, it would have a value that does not change among various rows for the outer relation and hence, the query would be equivalent to the following:
SELECT DISTINCT store_type FROM stores
WHERE TRUE; -- Or FALSE, if the subquery did not return any rows.
And of course this made me think how is an EXISTS
statement is useful at all?
But apparently, that isn't the way a correlated subquery is executed. Apparently, a correlated subquery is executed in the following way:
"For each row processed in the outer query (let's call this row as "current row"), process the inner (correlated) subquery, using the current row's values for the attributes not present in the subquery's FROM
clause."
So finally, what I don't understand:
Why is this the way it happens? I mean, why not it doesn't happen in the way I initially thought? (That is: Why it doesn't return the rows of the
cities_stores
relation whosestore_type
is equal to some row'sstore_type
instores
relation?) From exactly where am I supposed to interpret that it is how SQL works?In various sources around the internet, I constantly see the following about the correlated subqueries:
In a correlated subquery, the subquery is evaluated once for each row processed by the outer query.
But most curiously, a statement similar to this does not exist in MySQL's page about correlated subqueries. So:
Is that an incompleteness in MySQL manual, or is such a statement simply not required? If it is not required, from where am I supposed to understand that it the way how a correlated subquery is evaluated? Or, as another possibility, is the quote I have given simply a vague statement that is not completely correct? If so, how am I supposed to interpret a correlated subquery?
=stores.store_type
which is a value from the external query. How can it be then result to a value that does not change among various rows for the outer relation?"For each row processed in the outer query (let's call this row as "current row"), process the inner (correlated) subquery, using the current row's values for the attributes not present in the subquery's FROM clause."
--You are still phrasing this in terms of implementation ("for each row processed..."). The point of SQL and therefore of set-oriented thinking is that you don't think in terms of the underlying implementation.