What does a double not exists clause mean?

Question

SELECT c.name
FROM   Customer c
WHERE  NOT EXISTS(SELECT w.WID
                  FROM   Woker w
                  WHERE  NOT EXISTS(SELECT la
                                    FROM   look_after la
                                    WHERE  la.CID = c.CID
                                           AND la.WID = w.WID));

I dont know what the code means... Could anyone tell me broadly what the code do? C is a Customer, who will looked after from a Worker.

In what sense is this off-topic? It's a "specific programming problem", and commonly encountered when analyzing a database set up by SQL experts. — Andomar, Jun 30 '13 at 16:01
It is perfectly on topic for SO; it is about SQL programming, but programming and hence on topic. — Jonathan Leffler, Jun 30 '13 at 16:51
@Andomar The 'Off-Topic' reason chosen is about demonstrating minimal understanding of the problem being solved; There are more off-topic reasons that aren't exactly like the prior ones. That said, I don't know if the specific close reason applies, and I'm reopening it. — Andrew Barber, Jun 30 '13 at 17:07

Andomar · Answer 1 · 2013-06-30T15:57:03.750

8

The query selects customers that are looked after by all workers.

The double not exists is a way to implement relational division.

edited Jun 30 '13 at 15:57

answered Jun 30 '13 at 15:49

Andomar

232,371
49
380
404

1

Should be without the "not": *"that are looked after by all workers"* – ypercubeᵀᴹ Jun 30 '13 at 15:51
1

@ypercube: Where no worker exists who is not looking after the customer. So I think the current version is OK. Very hard to read double negation though. – Andomar Jun 30 '13 at 15:55
The query shows customers that are looked after by all workers, without exception. – ypercubeᵀᴹ Jun 30 '13 at 15:56
@ypercube: Looks like you're right. A bit too hard to read to be used in production without comments :) – Andomar Jun 30 '13 at 15:57

score 2 · Answer 2 · answered Jun 30 '13 at 17:42

As an illustration to Andomar's excellent answer an example:

    -- Some test data
DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;

CREATE TABLE persons
        ( person_id  INTEGER NOT NULL PRIMARY KEY
        , pname   varchar
        );
INSERT INTO persons( person_id, pname ) VALUES
 (1 , 'Bob' ) ,(2 , 'Alice' ) ,(3 , 'Carol' )
        ;

CREATE TABLE movies
        ( movie_id  INTEGER NOT NULL PRIMARY KEY
        , mname   varchar
        );
INSERT INTO movies( movie_id, mname ) VALUES
 (1, 'The Blues brothers' ), (2, 'Modern Times' ), (3, 'The Sound of Music' )
,(4, 'Amadeus' ), (5, 'Never say Never' )
        ;

        -- people that have seen a particular movie
CREATE TABLE person_movie
        ( person_id  INTEGER NOT NULL
        , movie_id INTEGER NOT NULL
        , PRIMARY KEY ( person_id, movie_id)
        );
INSERT INTO person_movie( person_id, movie_id) VALUES
 (1 ,5 ) ,(1 ,1 )
,(2 ,5 ) ,(2 ,4 ) ,(2 ,1 ) ,(2 ,3 ) ,(2 ,2 )
,(3 ,1 ) ,(3 ,3 )
        ;

        -- Find the people that have seen ALL the movies
        -- This is equivalent to:
        -- Find persons for whom NO movie exists that (s)he has NOT seen
SELECT * FROM persons p
WHERE NOT EXISTS (
        SELECT * FROM movies m
        WHERE NOT EXISTS (
                SELECT * FROM person_movie pm
                WHERE pm.movie_id = m.movie_id
                AND pm.person_id = p.person_id
                )
        );

        -- similar: Find the movies that have been seen by ALL people
SELECT * FROM movies m
WHERE NOT EXISTS (
        SELECT * FROM persons p
        WHERE NOT EXISTS (
                SELECT * FROM person_movie pm
                WHERE pm.movie_id = m.movie_id
                AND pm.person_id = p.person_id
                )
        );

Results:

 person_id | pname 
-----------+-------
         2 | Alice
(1 row)

 movie_id |       mname        
----------+--------------------
        1 | The Blues brothers
(1 row)

What does a double not exists clause mean?

2 Answers2

Linked