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 clean

2) the same CTE is reusable a number of times

3) it preserves scope.

E.g Return all the details of the last order a customer placed.

SELECT

C.CustomerID,

O,*

FROM

Customer C

JOIN

(/* Last Order For Each Customer*/

SELECT CustomerID,max(orderID) from Orders O Group by O.CustomerID

) LO on LO.CustomerID = C.CustomerID

JOIN

Orders O on O.OrderID = LO.OrderID

Compared to

WITH CTE_LastOrder as

(/* Last Order For Each Customer*/

SELECT CustomerID,max(orderID) from Orders O Group by O.CustomerID

)

SELECT

C.CustomerID,

O,*

FROM

Customer C

JOIN

CTE_LastOrder LO on LO.CustomerID = C.CustomerID

JOIN

Orders O on O.OrderID = LO.OrderID

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.