oracle SQL left join() or full out join () exclude records based on keys

Question

I want to exclude the records in one table if it appear in the other table (based on keys)

I want to delete the record in the first table: cust_recommendataion which has the same( cust_id and product_id) in the second table the distinct pair of ( cust_id and product_id) in the second table may only a subset of the first table' distinct pair of ( cust_id and product_id) also there are some '( cust_id and product_id)' pair in the second table may unique.

I have 2 tables 1. cust_recommendataion: for each cust_id has multiple product_id s

---

cust_id | product_id | rank

---

2. cust_last_buy ; for each cust_id has multiple product_id

---

cust_id | product_id | date

---

would love to know the suggestion how to do that. by using left join()or full out join()or any other suggestion? Thank you!

Answer 1

One possible solution using Exist:

Delete from cust_recommendataion c
WHERE
    EXISTS (
        SELECT
           *
        FROM
            cust_last_buy
        WHERE
            cust_id = c.cust_id
             and 
            product_id = c.product_id
    )