Search code examples
mysqlsqlquerying

SQL Query Using Minus/Not Exists

I want to query this statement:

Drinkers who do not frequent the bar Henrys

Now, I know I cant use a simple query like this:

SELECT dinker
FROM frequents
WHERE bar <> 'Henrys';

because what if a person goes to Henrys bar and also frequents other bars as well? This person will still be included in the result. This query only filters out the people who only frequent Henrys bar and nothing else.

I tried to use the MINUS operator, but in MySQL it doesn't seem to support the query:

SELECT drinker 
FROM frequents
MINUS
SELECT drinker
FROM frequents
Where bar = 'Henrys';

I want to try and rewrite this using NOT EXISTS, but can't seem to get it to work or correctly think through the logic.

Solution 
  • select drinker
  from frequents f
 where not exists (select 1
          from frequents x
         where x.drinker = f.drinker
           and x.bar = 'Henrys');