I am trying to understand SELECT IN better. Would this be a valid query?
WHERE product_id IN
(SELECT product_id, SUM(unit_price)
GROUP BY product_id
HAVING (SUM(unit_price) > 200));
No, for the same reason this would not be a valid predicate:
WHERE product_id = (1234, 16)
It makes no sense to compare that one scalar is equal to a tuple.
Note that SQL does allow you to compare a tuple to a tuple:
WHERE (product_id, 16) = (1234, 16)
But the number of elements in both tuples must be the same.
And likewise you can compare a tuple to a subquery that returns a tuple:
WHERE (product_id, 16) IN (SELECT product_id, SUM(unit_price) FROM ...
As long as both tuples have the same number of elements.