Hope Ashcraft Hope Ashcraft - 7 days ago 4
SQL Question

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));

Answer

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.