"not exists" query

User 9aa4619393

11-10-2011 15:08:59


I have a data tree with one parent table linked with relationships to three child  tables.

I would like to formulate a query where I can retrieve records from the parent table for which one of the child tables doesn't have any data, similarly to what I would do in SQL by using the NOT EXISTS condition.

How can I do this?



ChemAxon fa971619eb

12-10-2011 16:32:43

You are meaning to find parent entries that don't have any child rows? Assuming so then I don't think there is any direct way to do this currently. Your best bet might be to use a view for the parent instead of a table, and incorporate a column in the view that has the child row count. Then you would be able to query on that column (for a value of zero).

Would this work for you?


User fad2fd0d4b

17-10-2011 14:12:12

I tried such a solution, but the perfomance was not satisfactory.

ChemAxon fa971619eb

19-10-2011 11:03:04

How are going doing this? I tested two approaches, one using NOT EXISTS, and one querying on the count and they are both very fast in my case (though using NOT EXISTS is admittedly slightly faster).

SELECT count(*)
FROM parent p
    FROM child c
    WHERE c.parent_id = p.id)

SELECT count(*)
FROM parent p
  (SELECT count(*)
    FROM child c
    WHERE c.parent_id = p.id) = 0

This would suggest that incorporating the child row count into a view of the parent table shoudl give qood query performance, at least for medium sized tables.