Double Not Exists SQL Logic Explanation

Question

There are 2 tables, one called drinkers with a column of names, another called frequents which has 2 columns, drinker and bars (that they frequent).

I have a query that answers this statement:

Drinkers who frequent all bars

or wordred differently:

Drinkers such that there aren’t any bars that they don’t frequent

Here is the resulting query:

SELECT d.name
FROM drinkers d
WHERE NOT EXISTS (
    SELECT b.name
    FROM bars b
    WHERE NOT EXISTS (
        SELECT *
        FROM frequents f
        WHERE f.drinker = d.name
        AND f.bar = b.name
        )
    )

I am having the hardest time following the logic when two NOT EXISTS are used. How do I understand these types of queries?

score 11 · Accepted Answer · answered Sep 23 '15 at 12:41

You could try to unfold these kind of queries from the inside out. So, start with the last sub-query:

SELECT *
FROM frequents f
WHERE f.drinker = d.name
AND f.bar = b.name

Here you are selecting the clients of a specific bar having a particular name: in other words, you are checking if this particular drinker goes to this bar. So now:

SELECT b.name
FROM bars b
WHERE NOT EXISTS (
    SELECT *
    FROM frequents f
    WHERE f.drinker = d.name
    AND f.bar = b.name
)

could be seen as something like

SELECT b.name
FROM bars b
WHERE NOT EXISTS (this particular client in it)

Here you are selecting all bars that don't have this person as a client. Therefore, you end up with something like

SELECT d.name
FROM drinkers d
WHERE NOT EXISTS (any bar without this guy as a client)

And I think at this point the query should seem clear: select all drinkers for which there is no bar without them.

score 2 · Answer 2 · answered Sep 23 '15 at 13:00

I don't know if you absolutely need to go through those NOT EXISTS loops, since you could very well do something like this

SELECT d.name
FROM drinkers d 
INNER JOIN frequents f ON f.drinkerName = d.name
GROUP BY d.name
HAVING COUNT(distinct barName) = 
    (SELECT COUNT(distinct barName) 
     from frequents
)

Basically, you count the total number of bars, then you compare each person's number of frequented bar to that number. Adding those distinct clauses in COUNT allows you to ignore duplicates.

Double Not Exists SQL Logic Explanation

2 Answers2