Elegant way to remove orphan rows?

Question

I have a table with a lot of history entries that contain customer IDs.

There is a separate customer table. Occasionally some of the customer entries are removed.

Is there an easy way, without looping through each history entry, to drop all rows in the history table where the customer ID no longer exists because the customer row was deleted?

Answer 1

delete from history_table where customer_id not in (select customer_id from customers)

did you mean something like this?

Answer 2

DELETE h.* FROM history h
LEFT JOIN customer c ON h.customer_id = c.id
WHERE c.id IS NULL

I'm typing this from the top of my head, but you get the idea hopefully.

Delete syntax documentation

Answer 3

How about:

DELETE FROM history_table 
WHERE customer_id NOT IN (SELECT customer_id FROM customer);