Search code examples
sqldatabaseselectrelational

SQL SELECT IN returning two attributes

I am trying to understand SELECT IN better. Would this be a valid query?

SELECT name 
FROM products
WHERE product_id IN
(SELECT product_id, SUM(unit_price)
FROM sales
GROUP BY product_id
HAVING (SUM(unit_price) > 200));
Solution

  • 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.