The best way to Find entries missing after inner-join?-CodePudding

Lets suppose that we have the following 3 tables

Animal

id name
1 dog
2 cat
3 crow


Actions

id name
1 run
2 walk
3 jump
4 fly
5 puppy_eyes
6 swim

Animal_Actions
id Animal_id action_id
1 1 1
2 1 2
3 1 3
4 1 5
5 2 1
6 2 2
7 2 3
8 3 2
9 3 4

I would like to find all the missing animal actions for certain animals

For example if we input dog and cat( id 1 and 2) we should get the following (1,4),(1,6),(2,4),(2,5), (2,6)

and if we input crow (3) we get the following (3,1),(3,3),(3,5), (3,6) .

Currently I'm doing an inner join between Animal and Animal_Actions table based on animal ID and importing this into a SET in my code and checking if every possible permutation is present in this set and collecting the missing ones. I'm not sure if the process I follow is the most efficient one, is there a better way to do this when the data is at a large scale ? Thanks in advance.

CodePudding user response：

If you'll be filtering on a small number of Animal records, one approach is to do a CROSS JOIN with the Actions table. That will give you all action combinations for each Animal record. Then do an OUTER JOIN to Animal_Actions to identify which ones are missing.

For example, using cat = 2 and dog = 1

SELECT ani.id AS Animal_Id
       , ani.Name AS Animal_Name
       , act.id AS Action_Id
       , act.Name AS Action_Name
FROM   Animal ani
          CROSS JOIN Actions act 
          LEFT JOIN Animal_Actions aa ON ani.id = aa.Animal_id
             AND aa.Action_Id = act.id
WHERE  ani.id IN (1,2)
AND    aa.id IS NULL
ORDER BY ani.Name, act.Name
;

Results:

Animal_Id | Animal_Name | Action_Id | Action_Name
--------: | :---------- | --------: | :----------
        2 | cat         |         4 | fly        
        2 | cat         |         5 | puppy_eyes 
        2 | cat         |         6 | swim       
        1 | dog         |         4 | fly        
        1 | dog         |         6 | swim

db<>fiddle here