Removing entries with missing links

Question

I have two tables

Number 1:

adresse
  id    name

Number 2:

produkte
  id    anbieter    

Details:

id: int
name: string
anbieter: int

Each anbieter in produkte is to be an id in adresse. For example:

adresse
  id  name
  1   hello
  2   there

produkte
  id  anbieter    
  1   1
  2   1
  3   2
  4   1

Works, but

adresse
  id  name
  1   hello
  2   there

produkte
  id  anbieter    
  1   4
  2   1
  3   2
  4   1

doesn't because there is no adresse.id of produkte.anbieter = 4

My goal is to remove every entry of produkte whose anbieter does not have an adresse.id.

Answer 1

Use a LEFT JOIN to find rows that don't have a match and delete them.

DELETE produkte.*
FROM produkte
LEFT JOIN adresse
ON produkte.anbieter = adresse.id
WHERE adresse.id IS NULL

LEFT JOIN is like INNER JOIN, except when there's a row in the left table (produkte) that has no rows in the right table (adresse) that match the ON condition. INNER JOIN would leave the row out of the result, LEFT JOIN returns a row with NULL in all the right table's columns. You can then use WHERE <right-table>.<column> = NULL to find the rows that had no match.

For more explanation about the different types of joins, see:

A Visual Explanation of SQL Joins