SQL Select values of a column only if they are associated with all values of another subset, without using multiple queries

Question

I am sorry if this is a very basic question, but I have not been able to find an answer anywhere :

Let's say I want to get the products that are sold at both stores a and b, with a single table that associates products and stores. In class, we have been taught this kind of solution :

select product_id
from product
where store = "a"
and product in (
  select product_id
  from product
  where store = "b"
)

This however seems very inefficient to me, especially in a case where a very large table would be used. Is there any way to make a single request that satisfies both conditions, without nesting ?

It feels like there has to be, but being very new to sql I actually have no idea, and I have found this type of thing extremely hard to look up. No matter what I search, I find answers to similar problems that don't answer my question.

Answer 1

I would approach this by first filtering for the required criteria followed by a corresponding distinct count:

select product_id
from product
where store in ('a','b')
group by product_id
having Count(distinct store)=2;

Answer 2

if I understand correctly you can try to use condition aggregate function in HAVING

SELECT product_id
FROM product
GROUP BY product_id
HAVING COUNT(CASE WHEN store = 'a' THEN 1 END) > 0
AND COUNT(CASE WHEN store = 'b' THEN 1 END) > 0