Oracle sql. Compare two tables to find missing data

Question

I have 2 tables:

Operator (id, name, product_id)
Product (id)

Operator table contains this data:

| 1 | Op1 | Pr1 |  
| 2 | Op2 | Pr2 |

Product table:

|    Pr1   |  
|    Pr2   |  
|    Pr3   |

And what i need is to show the products that Op1 does NOT have, and products that Op2 does NOT have:

| Op1 | Pr2 |  
| Op1 | Pr3 |  
| Op2 | Pr1 |  
| Op2 | Pr3 |

Could you please advise an efficient way of solving this task. I have 1 solution with building cross join, but i think there should be a right and easier way.

Possible duplicate of [SQL - find records from one table which don't exist in another](http://stackoverflow.com/questions/367863/sql-find-records-from-one-table-which-dont-exist-in-another) — Stefano Zanini, Apr 04 '17 at 11:16

score 1 · Answer 1 · answered Apr 04 '17 at 11:16

I don't understand your data model. You seem to have a column called operator.name that is supposed to match product.id. Let me assume the data format is actually reasonable. You should really have three tables:

Operators -- with one row per "operator_id"
Products -- with one row per "product_id"
OperatorProducts -- a junction table between the two

You can do this in two steps. Start by creating all combinations of operator and product (cross join). Then weed out the ones that are present (left join):

select o.operator_id, p.product_id
from (select distinct id as operator_id from operator) o cross join
     product p left join
     operator op
     on op.id = o.operator_id and op.product_id = p.product_id
where p.product_id is null;

If you need the names, you can join them in.

Regarding the table structure i wanted to have as simple model in the question as possible, but i made a typo in product table, it contains an id. Just lets assume that id is the product name. And regarding your answer. Thank you for suggestion, i thought about something similar using cross join, but just was not sure that it's the best solution.. — ayscha, Apr 04 '17 at 11:33

score 1 · Accepted Answer · answered Apr 04 '17 at 11:38

1

I would use minus:

^demo

select o.name op_name, p.name pr_name from operator o cross join product p
minus 
select name, product_id from operator

answered Apr 04 '17 at 11:38

Ponder Stibbons

14,723
2
21
24

Thanks for an elegant and simple solution. And for demo link. – ayscha Apr 05 '17 at 11:02

score 0 · Answer 3 · answered Apr 04 '17 at 19:51

0

A cross join is what is needed here.

select o.name, p.id from product p
cross join operator o
where 
  o.product_id != p.id

answered Apr 04 '17 at 19:51

Robert Love

12,447
2
48
80

Oracle sql. Compare two tables to find missing data

3 Answers3