Remove 'not in' claus from subqueries

Question

I have following query

select * 
  from object 
 where parent_id = ? 
  and id not in ( select parent_id 
                    from object 
                   where discriminator='ABC')

I have tried to use Joins as follows

select * 
  from object parent 
 inner join object child 
    on (parent.id != child.parent_id) 
 where child.discriminator='ABC'

But I am getting incorrect result. Is there any way to improve the performance of query in postgres.

Sorry, I guess, I failed to explain my problem in first time,

Below is modified query

select * 
from object parent
where parent.parent_id = ?  
 and  parent.discriminator ='XYZ'
 and parent.id not in ( select child.parent_id 
                        from object child
                        where child.discriminator='ABC')

So besically i have been given an id and need to find all it's child, who don't have any child.

Answer 1

I'd use not exists for anti-join:

select * 
from object as o
where
    o.parent_id = ? and
    not exists (
        select * from object as t
        where t.parent_id = o.id and t.discriminator='ABC'
    )

note also using aliases for table, if you're not using aliases (or table names), you can get incorrect results - see SQL IN query produces strange result.

Answer 2

Try a left join

select * from object parent 
left join object child on parent.id = child.parent_id
where parent_id = ? 
and child.discriminator = 'ABC'
and child.parent_id is null

Answer 3

If I understand correctly you need something like this

select parent.* 
  from object parent 
 left join 
  (select * from object 
  where child.discriminator='ABC') child
    on parent.id = child.parent_id 
 where parent.parent_id = ? and child.parent_id is null

UPDATE:

Actually this can be achieved without a join statement.

select * 
from object  
where parent_id = ? and 
                 (discriminator!='ABC' OR (discriminator='ABC' and parent_id!=id))