Correlated sub queries how to check

Learn CP on Unacademy
, ,
1

SELECT f_name, f_type
FROM food
WHERE f_id IN(
SELECT f_id FROM ratings
where f_rating < 3
);// this is non correlated subqueries

SELECT f_name, f_cost, f_type
FROM food
WHERE f_id IN (
SELECT f_id
FROM ratings
GROUP BY f_id
HAVING AVG(f_rating) >= 4
);// this is correlated subqueries

since both of the above query
inner query runs independently then
why is 1st one non correlated but 2nd one
correlated subqueries

The GROUP BY and WHERE clauses are the only places where we can see the subquery referencing a column in the outer query.
This is because the GROUP BY clause is used to group rows together based on the values of one or more columns,
and the WHERE clause is used to filter rows based on a condition.

If the subquery is referencing a column in the outer query, then the subquery is correlated.
This is because the sub query must be evaluated for each row in the outer query.
Is this correct?

1 Like
2

A correlated subquery is a subquery that contains a reference to a table that also appears in the outer query
Notice that the subquery contains a reference to a column of t1, even though the subquery’s FROM clause does not mention a table t1. So, MySQL looks outside the subquery, and finds t1 in the outer query.

Suppose that table t1 contains a row where column1 = 5 and column2 = 6; meanwhile, table t2 contains a row where column1 = 5 and column2 = 7. The simple expression ... WHERE column1 = ANY (SELECT column1 FROM t2) would be TRUE, but in this example, the WHERE clause within the subquery is FALSE (because (5,6) is not equal to (5,7)), so the expression as a whole is FALSE.

hope it helps !!

1 Like