Selecting all object for which not exists value

Question

I have two tables: object that has object_id column and avalues that have object_id (FK for object.object_id) and value_type (for simplicity I ommited other columns).

I want to select all objects that don't have values with specified type. My select looks like this:

SELECT object_id FROM object WHERE NOT EXISTS (SELECT true FROM avalues v WHERE v.value_type = 10 and v.object_id = object_id); 

is there more effective way to do this?

Answer 1

NOT IN and LEFT JOIN / IS NULL are slightly more efficient:

SELECT  object_id
FROM    object
WHERE   object_id NOT IN
        (
        SELECT object_id
        FROM   avalues v
        WHERE  v.value_type = 10
        )

or

SELECT  o.object_id
FROM    object o
LEFT JOIN
        avalues v
ON      v.object_id = o.object_id
        AND v.value_type = 10
WHERE   v.object_id IS NULL

Make sure that you have an index on avalues (object_id, value_type)