I got this wrong, but it then took me some time to work out what the right answer meant. In fact, I still cannot understand it and would like someone to provide a complete explanation.
Alternatively, is this behaviour described in BOL anywhere?
As far as I can figure out, the CROSS JOIN implied by the comma generates a virtual table with all columns from both tables. The subquery is then applied to this table and selects all rows WHERE PRICINGPLAN='X'. Since its a CROSS JOIN, this will then return all values of CUSTOMERID.
At this point, I would expect the outer query to just return all rows from the CROSS JOIN. That is, to produce the same result as
select CategoryID, Quantity, Price
from Customer c, SalesOrderDetail sod
where sod.clientid in (select customerid)
But it doesn't as it only returns 2 rows instead of 4.
As far as I can work out from using SELECT *, it limits the results to the rows where PRICINGPLAN='X'. I.e. the subquery acts as a filter on the CROSS JOIN.
As I said, can someone provide an explanation of what's going on as I admit to being confused! 🙁