Why can’t we use/refer to derived table in subquery

I wonder why can’t a subquery use a derived table as its derived table (Query2), even though it can access the attribute of the derived table (Query1)?

Query1

select B.col1, col2 from dummy B
where B.col1 = (select col1 from dummy A where B.col1 = 'aa' and A.col1 = B.col1 limit 1);

Here we can use B.col1.

Query2

select B.col1, col2 from dummy B
where B.col1 = (select col1 from B where B.col1 = 'aa' limit 1);

Here we can’t use B. The error says B doesn’t exist.

You can find the sqlfiddle here.

Answer

The problem is that in your second query you are trying to select from the table that does not exist and in your first query you are referring to the column that does.

In both query’s the B and the A letters are only aliases not tables. Having that in mind, lets remove the aliases in the second query:

select col1, col2 from dummy
where col1 = (select col1 from  where col1 = 'aa' limit 1);

Do you see something missing now in the query structure ?

To conclude, from your question it seems you do not understand which B is the problem. This one: FROM B and not this one where B.col1.

Hope this helps…