Question on contained queries 2

Question

Hello,

While recaping the chapter on contained queries I decided to modify the quiery which I had already asked about eariler ...

SELECT warehouse, product, model, quantity  
FROM Inventory as I1  
WHERE quantity IN    
(select quantity  
From Inventory as I2  
where I1.warehouse=I2.warehouse)  
Order by warehouse, product  

...to further restrict the resulting data set from the inner quiry, for example:

SELECT warehouse, product, model, quantity  
FROM Inventory as I1  
WHERE quantity IN    
(select quantity  
From Inventory as I2  
where I1.warehouse=I2.warehouse **and i2.model = 'X'**)  
Order by warehouse, product  

I was expecting that in this case the number of quantity values in the WHERE quantity IN predicate would be less than without the extra "and i2.model = 'X'" condition.

The result was exactly that I was expecting:

Another condition "and i2.product = 'Samsung'" also returned the correct result (at least as I think, of course):

But when I changed the "and i2.product = 'Samsung'" to "and i2.product = 'iPhone'" I got the result that I can't perceive: as far as I understand the resulting 'quantity' set should contain ALL possible values because the iPhone model has all available values of quantity column, but nevertherless the two rows - 5th and 9th - are missing (in spite of the fact that corresponding values of their 'quantity' column fall into 10, 50 ..., 200 set).

Why?

Thank you in advance,
Michael

Answer 1

Why?

Because your queries don't make much sense.

In the sub query you join on warehouse and filter on iPhone.
In warehouse San Fransico there is no iPhone with quantity of 100 => Samsung Note 8 with quantity 100 isn't returned
In warehouse San Jose there is no iPhone with quantity of 200 => Samsung Galaxy with quantity 200 isn't returned.

Answer 2

OlafHelper-2800, thank you very much for the explanation!!!

Regards,
Michael