Table alias is unknown in nested subquery

Question

The following query works just fine. I am using a value from the outer select to filter inside the inner select.

SELECT
    bk.ID,
    (SELECT COUNT(*) FROM guests WHERE BookingID = bk.ID) as count
FROM
    bookings bk;

However, the following select will not work:

SELECT
    bk.ID,
    (SELECT SUM(count) FROM (SELECT COUNT(*) AS count FROM guests WHERE BookingID = bk.ID GROUP BY RoomID) sub) as sumcount
FROM
    bookings bk;

The error message is: Error Code: 1054. Unknown column 'bk.ID' in 'where clause'

Why is my alias bk known in the subselect, but not in the subselect of the subselect?

For the record, I am using MySQL 5.6.

Answer 1

Correlated Scalar Subqueries in the SELECT list can usually be rewritten to a LEFT JOIN on a Derived Table (and in many cases they might perform better then):

SELECT
    bk.ID,
    dt.sumcount
FROM
    bookings bk
LEFT JOIN 
 (SELECT BookingID,SUM(COUNT) AS sumcount
  FROM
   (
     SELECT BookingID, RoomId, COUNT(*) AS COUNT 
     FROM guests 
     GROUP BY BookingID, RoomID
    ) sub
  ) AS dt
ON bk.BookingID = dt.BookingID

Answer 2

This is called "scoping". I know that Oracle (for instance) only looks out one level for resolving table aliases. SQL Server is also consistent: it looks out more than one level.

Based on this example, MySQL clearly limits the scope of the identifier bk to the immediate subquery. There is a small hint in the documentation (emphasis mine):

A correlated subquery is a subquery that contains a reference to a table that also appears in the outer query.

However, I have not found any other specific reference to the scoping rules in the documentation. There are other answers (here and here) that specify that the scope of a table alias is limited to one level of subquery.

You already know how to fix the problem (your two queries should produce identical results). Re-arranging the query to have joins and aggregations can also resolve this problem.