SQL: Filtering data using a connection 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 animals without owners, so I make a request:

 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 method of filtering data using a connection acceptable and safe? With the same scheme, is there a better alternative to get the same result?

Answer 1

Use the exists clause parameter:

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

Also, set the owner_animals.animal_id index to make this filter as fast as possible

Answer 2

Assuming nothing happens postgres (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

Never do, FROM (SELECT * FROM table) , just FROM table , the same thing happens with LEFT JOIN . What you wrote is just too 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() parameter because it saves the owner_id IS NULL fragment.

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