SQL Puzzle 2 – What can we talk about?

Ever been with a group of strangers and struggled to find a topic of conversation that you are all interested in?

Well if you have then these SQL statements are for you. We begin with two tables INTERESTS and PEOPLE. A third table PERSON_INTEREST_MAP is used to describe the many-to-many relationship between the first two tables, i.e. what interests each person has. Let us begin by creating the three tables and populating them:

DROP TABLE sqlfun.person_interest_map;
DROP TABLE sqlfun.people;
DROP TABLE sqlfun.interests;

CREATE TABLE sqlfun.people
(person_name VARCHAR2(100), person_id INTEGER  PRIMARY KEY);

CREATE TABLE sqlfun.interests
(interest_name VARCHAR2(100), interest_id INTEGER PRIMARY KEY);

CREATE TABLE sqlfun.person_interest_map
(
  person_id INTEGER,
  interest_id INTEGER
);

ALTER TABLE sqlfun.person_interest_map ADD (
  CONSTRAINT person_interest_map_r01
 FOREIGN KEY (interest_id)
 REFERENCES sqlfun.interests (interest_id),
  CONSTRAINT person_interest_map_r02
 FOREIGN KEY (person_id)
 REFERENCES sqlfun.people (person_id));

INSERT INTO sqlfun.people (person_name, person_id) VALUES ('Adam', 1);
INSERT INTO sqlfun.people (person_name, person_id) VALUES ('Bob', 2);
INSERT INTO sqlfun.people (person_name, person_id) VALUES ('Charlie', 3);

INSERT INTO sqlfun.interests (interest_name, interest_id) VALUES ('TV', 1);
INSERT INTO sqlfun.interests (interest_name, interest_id) VALUES ('Sports', 2);
INSERT INTO sqlfun.interests (interest_name, interest_id) VALUES ('Politics', 3);
INSERT INTO sqlfun.interests (interest_name, interest_id) VALUES ('Tony''s blog', 4);

INSERT INTO sqlfun.person_interest_map (person_id, interest_id) VALUES (1, 1);
INSERT INTO sqlfun.person_interest_map (person_id, interest_id) VALUES (1, 2);
INSERT INTO sqlfun.person_interest_map (person_id, interest_id) VALUES (1, 4);
INSERT INTO sqlfun.person_interest_map (person_id, interest_id) VALUES (2, 1);
INSERT INTO sqlfun.person_interest_map (person_id, interest_id) VALUES (2, 3);
INSERT INTO sqlfun.person_interest_map (person_id, interest_id) VALUES (2, 4);
INSERT INTO sqlfun.person_interest_map (person_id, interest_id) VALUES (3, 2);
INSERT INTO sqlfun.person_interest_map (person_id, interest_id) VALUES (3, 3);
INSERT INTO sqlfun.person_interest_map (person_id, interest_id) VALUES (3, 4);

Ok, we can see that our three people have three interests each. Here are our two queries:

SELECT interest_name
  FROM interests
       NATURAL LEFT JOIN
       (SELECT i.interest_id, 1 dummy
          FROM people p CROSS JOIN interests i
               LEFT JOIN person_interest_map m
               ON m.person_id = p.person_id
             AND m.interest_id = i.interest_id
         WHERE m.person_id IS NULL) i
 WHERE dummy IS NULL;

SELECT DISTINCT interest_name
           FROM interests i
          WHERE NOT EXISTS (
                   SELECT NULL
                     FROM people p
                    WHERE NOT EXISTS (
                             SELECT NULL
                               FROM person_interest_map m
                              WHERE m.person_id = p.person_id
                                AND m.interest_id = i.interest_id));

First question: what is the difference between the two queries? Second question: what do they do?

To see the answers see the next page: