mysqlsqlquerying

Double Not Exists SQL Logic Explanation

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?

Solution

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