Correlated/non-correlated subqueries


#1

Hi, Can someone walk me through what the bold portion of the below script is doing or what it’s purpose is?

SELECT carrier, id,
(SELECT COUNT(*)
FROM flights f
WHERE f.id < flights.id
AND f.carrier=flights.carrier) + 1
** AS flight_sequence_number**
FROM flights;

Actually, I’m finished the sql intensive programme, but I can’t the difference between correlated and non-correlated. I mean, I understand the theory, but looking at the code how do I know if it is correlated or non correlated?


#2

A correlated subquery is executed for each row of the outer query and uses values from this outer query.
Example of a correlated query:

SELECT a.Column1, a.Culumn2
FROM tableA as a
WHERE x = (
    SELECT COUNT(b.ColumnA)
    FROM tableB as b
    WHERE b.ColumnB >= a.Column2);

Note that the subquery uses “a.Column2” from the outer query.

On the other hand, a non-correlated subquery is only executed once and doesn’t use any values from the outer query.
Example of a non-correlated query:

SELECT a.Column1, a.Culumn2
FROM tableA as a
WHERE x = (
    SELECT COUNT(b.ColumnA)
    FROM tableB as b
    WHERE b.ColumnB IS NOT NULL);

#3

Thank you for help.

One more thing…

SELECT origin, id,
(SELECT COUNT(*)
FROM flights f
WHERE f.id < flights.id
AND f.origin=flights.origin) + 1
AS flight_sequence_number
FROM flights;

In this example, as you said, the subquery takes values from the outer query, but why it says “f.id<flights.id” - the columns are the same, aren’t they?
And the “+1” , it is added to the number?


#4

They are the same, yes, but I will try to explain it as best I can.

WHERE d.id < flights.id
AND d.origin = flights.origin

According to the query you posted, this looks to me like it selects all of the flights prior to the current one (the WHERE clause) that ALSO have the same origin or starting location (the AND).

This the +1 adds to the result of the count.

So if there are 5 flights with id’s smaller than the current rows id that have the same origin, the result would be 6.
With this query it seems like the same result could be achieved by changing WHERE f.id < flights.id to WHERE f.id <= flights.id and removing the +1, but I might be wrong.


#5

I think I got it :slight_smile: Great, Thank you.