SQL: Filtering data using a join is bad?

Question

For example, I have the following tables:

animal
-----------------------
animal_id | animal_name
-----------------------

owners
-----------------------
owner_id | owner_name
-----------------------

owners_animals
--------------------
owner_id | animal_id
--------------------

I want to find the animals with no owners so I do the query:

select animal_name 
from (select * from animals) as a 
    left join (select * from owners_animals) as o on (a.animal_id = o.animal_id) 
where owner_id is NULL

Is this way of filtering data using a join acceptable and safe? With the same schema, is there a better alternative to get the same result?

Answer 1

Use a Not Exists clause:

Select animal_name 
From animals as a 
Where Not Exists(Select 1
                 From owners_animals oa
                 Where oa.animal_id = a.animal_id)

Also, put an index of owners_animals.animal_id to make this filter as fast as possible

Answer 2

Assuming there's nothing postgres specific going on (I'm not familiar with postgres) then the following is easier to follow.

Select *
From animals a
    left outer join owners_animals oa On a.animal_id = oa.animal_id
Where oa.owner_id is NULL

Answer 3

Don't ever do, FROM (SELECT * FROM table), just do FROM table, same goes with the LEFT JOIN. What you wrote is just an overly verbose

SELECT animal_name
FROM animals
LEFT JOIN owners_animals
  USING ( animal_id )
WHERE owner_id IS NULL;

With that said, I often like the NOT EXISTS() option, because it keeps the owner_id IS NULL fragment out.

USING (foo) is the same as foo = foo, on the joined tables, except only one of them will be in the result set.