Thanks for the explanation guys, I learned something today.Actually 2 things. 1) scope and 2) yet another reason not to use subqueries

@kingston, It is a result but I would argue not the correct result. I can't imagine a scenario where you would write the query like that for the result described.

Personally I avoid subqueries whereever possible; I find they produce untidy code that is difficult to maintain and whereever possible I will use Common Table Expressions and then use an appropriate join in the main query. This has Three main benefits:

1) it keeps code clean2) the same CTE is reusable a number of times3) it preserves scope.

I find the second easier to read and maintain. When looking at the main code I don't have to rethink what the subquery is doing each time, I can get the gist from the suitably named CTE. I can fiddle with the CTE and test the code independently of the main query. When building very complex queries, you can build a series of CTEs that build upon one another and re-use them.

anayway I have gone WAAAAAY off topic. We should really answer the OPs question which is 100: All the customers in the customer list.