Converting From NOT EXISTS to NOT IN

Question

I have three tables:

sailor (sname, rating);
boat (bname, color, rating);
reservation (sname, bname, weekday, start, finish);

In order to get a list of sailors who have reserved every red boat, I have:

select s.sname from sailor s 
where not exists(  
    select * from boat b  
    where b.color = 'red'  
    and not exists (  
        select * from reservation r  
        where r.bname = b.bname  
        and r.sname = s.sname));

I now need to rewrite this query with NOT IN instead of NOT EXISTS. Here's what I have so far:

select s.sname from sailor s
where s.sname not in (select s2.sname from sailor s2 where
    s2.sname not in (select r.sname from reservation r where r.bname not in (
            select b.bname from boat b where b.color <> 'red' )));

This, however, returns a list of all sailors who have reserved a red boat (not necessarily all of them). I'm having great difficulty checking whether or not a name in the list has reserved EVERY boat (I also cannot use COUNT()).

Any help is appreciated

No SQL errors, but when I run the second query with NOT INs I get a list of every sailor who has rented a red boat, which is not the same as every sailor who has rented every boat. I'm having trouble wrapping my mind around the NOT IN logic for this problem. — shinobutime, Nov 02 '14 at 10:00
If you want to find the sailors who rented all the boats then what is the need of this condition **where b.color = 'red'** — Pரதீப், Nov 02 '14 at 10:13
Sorry about that; I've edited the original post. I am trying to get a list of sailors who have reserved every RED boat, which is what my first query does. — shinobutime, Nov 02 '14 at 10:16
What is wrong with the double `NOT EXISTS(...)` construct ? It is the standard way to deal with relational division problems like yours. And it is in most cases superior to `IN (...)`, since it handles NULLs elegantly and does not need to suppress duplicates. — wildplasser, Nov 02 '14 at 12:10

score 4 · Answer 1 · answered Nov 02 '14 at 18:51

4

This is funny; you can maintain the syntactic structure (correlated subqueries) while replacing NOT EXISTS ( ... ) by IN ( ...) :

SELECT s.sname from sailor s
WHERE 13 NOT IN (
    SELECT 13 FROM boat b
    WHERE b.color = 'red'
    AND 42 NOT IN (
        SELECT 42 from reservation r
        WHERE r.bname = b.bname
        AND r.sname = s.sname
       )
    );

answered Nov 02 '14 at 18:51

wildplasser

43,142
8
66
109

What do 13 and 42 do? Are they like temporary variables? – user2619824 Nov 03 '14 at 04:06
2

@user2619824: x NOT IN (SELECT x FROM ...) means the same thing as NOT EXISTS ( SELECT * FROM ...). If there are any rows in SELECT * FROM ... then the SELECT x returns one row with an x, otherwise it returns no rows. The 13s and 42s could all just be 1. The values don't matter because we only care whether there are values, ie whether there is a row in each SELECT *. – philipxy Nov 03 '14 at 06:12
They are just literals. The subquery either yields 13, or does not yield anything at all. And thus if we compare the result to 13 we either get TRUE or there is nothing to compare. This is almost the same as `not exixts()` which resolves to a boolean value by itself. (similar for the 42, of course) – wildplasser Nov 03 '14 at 08:50

score 0 · Accepted Answer · edited Nov 02 '14 at 17:25

0

Inorder to get a list of sailors who have reserved every boat. I'll use this script

Solution 1:

 ;WITH k AS 
    (
    SELECT b.sname,COUNT(distinct a.bname) coun FROM boat a
    INNER JOIN reservation b 
        on a.bname = b.bname
    GROUP BY b.sname
    )
    SELECT k.sname FROM k WHERE coun = (select COUNT(*) FROM boat AS b)

Solution 2:

SELECT s.sname
FROM   sailor AS s
WHERE  s.sname NOT IN (SELECT DISTINCT a.sname
                       FROM   (SELECT s.sname,
                                      b.bname
                               FROM   sailor AS s
                                      CROSS JOIN boat AS b
                               WHERE  b.color = "Red") a
                       WHERE  a.sname + a.bname 
                                            NOT IN (SELECT r.sname + r.bname
                                                    FROM   reservation AS r
                                                    WHERE  r.sname IS NOT NULL
                                                            AND r.bname IS NOT NULL));

edited Nov 02 '14 at 17:25

Martin Smith

438,706
87
741
845

answered Nov 02 '14 at 09:10

tungula

578
1
6
12

Thanks for the response. I have to use nested NOT INs, though (and no COUNT()) – shinobutime Nov 02 '14 at 09:14
Check my second solution – tungula Nov 02 '14 at 10:00
Not working, unfortunately. This returns a list of every sailor. The boat color red needs to be checked but when I add a check after the cross join, it is still not working – shinobutime Nov 02 '14 at 10:08
I've updated the original post. I am actually looking for a list of every sailor who has reserved every RED boat. Sorry about that. – shinobutime Nov 02 '14 at 10:17
Simply add Color in Cross JOIN – tungula Nov 02 '14 at 10:45
This returns a list of every sailor. – shinobutime Nov 02 '14 at 10:51
+1 Looks like it should work to me. If it is returning all rows for the OP maybe it needs a `null` check though. – Martin Smith Nov 02 '14 at 17:21
1

Also you don't need that ugly string concatenation. `WHERE a.sname NOT IN (SELECT r.sname FROM reservation AS r WHERE r.sname IS NOT NULL AND r.bname = a.bname )` would work. – Martin Smith Nov 02 '14 at 17:46
Also, you don't need the `WHERE r.sname IS NOT NULL AND r.bname IS NOT NULL)` : NULLS can not be part of any `IN ()` set, and they would not survive the string concatenation anyway. BTW: the string constant 'Red' should be quoted with single quotes. – wildplasser Nov 02 '14 at 19:03
@wildplasser I added that in. You seem to be getting confused with `in`. A single null in the result set used as input for a `not in` means the query will return an empty result set. Which could explain why the overall query was returning all sailors according to the OP. – Martin Smith Nov 02 '14 at 19:32
No, I am not confused. The double NOT NULL check is not needed here. You only need check it. – wildplasser Nov 02 '14 at 23:11
@wild concatenating null produces null so if either of the inputs are null the projected column in the innermost query will return null. Which means the inner most not in will blow up and return an empty set. Which means the outer most not in will return everything. These are not the desired semantics. – Martin Smith Nov 03 '14 at 08:24

Converting From NOT EXISTS to NOT IN

2 Answers2

Linked