SQL equivalent of relational algebra DIVISION

Question

Here is an example T(A) = RENTED(A,C) / BOATS(C)

select distinct R1.A from RENTED R1  
where not exists                     
  (select * from SAILBOAT S     
   where not exists                  
     (select * from RENTED R2        
      where R1.A = R2.A              
        and R2.C = S.C)              
   );

My question is, if NOT EXISTS just returns TRUE or FALSE, how does SELECT distinct R1.A know which values to return?

For example this jsfiddle

This query returns EVERYTHING in the numbers column if there exists a number = 5

If the first NOT EXISTS returns true, then the query would be equivalent to `SELECT ... WHERE TRUE` which naturally should return all rows. If false, then none. — Matt, Oct 30 '13 at 22:47
Then shouldn't the above query return everything in the column of A thus no division is occurring? — Instinct, Oct 30 '13 at 23:21
... if `NOT EXISTS just returns TRUE or FALSE` it yields one boolean result _per row_: for every row in the outer query it is decided whether this row is wanted or not. — wildplasser, Oct 31 '13 at 00:05
It's a *correlated* subquery so the EXISTS clause gets evaluated for every row in the outer part of the query. — nvogel, Oct 31 '13 at 13:01
see example here http://www.tc.umn.edu/~hause011/code/SQLexample.txt — jbaliuka, Feb 13 '14 at 19:01
Wondering about whether this question, is it about division or just the return value of `exist` and `not exist`? Afaik the correct format for the equivalent SQL query to AR division is as follows: `SELECT attribute FROM individuals i WHERE NOT EXIST ( SELECT * FROM boats b WHERE NOT EXIST ( SELECT * FROM rented r WHERE r.id = i.id AND r.id = b.id) )`. Posting this in case anyone lands here looking for the answer to that question instead (which would seem more logical in my mind). — BasilBrush, Nov 27 '20 at 21:07

score 2 · Answer 1 · answered Mar 25 '14 at 14:57

As wildplasser and sqlvogel have mentioned, the subquery gets executed once for each row in the outer query.

The result of the subquery (TRUE / FALSE) determines whether the row in the outer query would be returned. Invariably, the parent key (identifier) columns of the outer query would be referenced within the subquery to check its existence in other tables. This reference makes the subquery a "correlated subquery".

Please see the updated fiddle.

SQL equivalent of relational algebra DIVISION

1 Answers1