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

Composition

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. – Claribelclarice

Then shouldn't the above query return everything in the column of A thus no division is occurring? – Composition

... 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. – Serpentiform

It's a correlated subquery so the EXISTS clause gets evaluated for every row in the outer part of the query. – Astor

see example here tc.umn.edu/~hause011/code/SQLexample.txt – May

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). – Engender

Answer 1

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.

Skirt