topic Re: Non-correlated and Correlated subqueries in Advanced Programming

Non-correlated and Correlated subqueries

spandya — Sat, 27 Feb 2021 21:32:42 GMT

The Advanced Programming prep guide distinguishes correlated and non-correlated subqueries with the following definitions.

A noncorrelated subquery is a self-contained subquery that executes independently of the outer query. The simplest type of subquery is a noncorrelated subquery that returns a single value.

correlated - A dependent subquery is one that requires one or more values to be passed to it by the outer query before the subquery can return a value to the outer query.

Later on, however, they mention the following SQL query being a correlated subquery but how can that be? How is the subquery in this case dependent on the outer query?

The following PROC SQL query accomplishes this task by using a correlated subquery and the NOT EXISTS operator.

proc sql; 
select lastname, firstname
from certadv.flightattendants 
where not exists 
(select * from certadv.flightschedule where
 flightattendants.empid= flightschedule.empid);
quit;

Re: Non-correlated and Correlated subqueries

PGStats — Sat, 27 Feb 2021 22:03:07 GMT

Because flightattendants.empid is defined outside of the subquery (i.e. in the main query).

Re: Non-correlated and Correlated subqueries

spandya — Sun, 28 Feb 2021 02:21:42 GMT

In other words its because the certadv.flightattendants is in the outer query? If it was replaced with another dataset instead (that also had the empID variable) and the subquery was kept the same, would it become non-correlated?

Re: Non-correlated and Correlated subqueries

PGStats — Sun, 28 Feb 2021 04:38:28 GMT

If the subquery was uncorrelated it could only refer to variables from datasets mentioned in its FROM clause. It's hard to imagine a case where an EXISTS operator would refer to an uncorrelated subquery.

Re: Non-correlated and Correlated subqueries

spandya — Sun, 28 Feb 2021 16:38:18 GMT

Interesting. I couldn't come up with an example for an uncorrelated subquery that uses the EXIST operator. Which made me question the definitions again hence the post.