<p>I have three tables. One table is a travel distance table</p> <pre class="brush:php;toolbar:false;">create table routes ( slat int, slng int, dlat int, dlng int, distance int );</pre> <p>A table is a site table</p> <pre class="brush:php;toolbar:false;">create table sites ( id int, name varchar(100), lat int, lng int );</pre> <p>There is also a table for technicians</p> <pre class="brush:php;toolbar:false;">create table fse ( id int, name varchar(100), lat int, lng int );</pre> <p>The travel distance table is dynamically populated via Google API requests. So if there are new stops or new technicians, all new travel distances are requested through Google and stored into the route table. </p> <p>Of course, it may also happen that a customer or technician leaves. In this case, the entries in the distance table remain in the database even if they are no longer used. </p> <p>I want to delete these unused rows in the table. So I'm looking for a query that can remove all "non-existent" latitude/longitude combinations in the sites/technicians table, like written in the comments of this fiddle</p> <p>https://www.db-fiddle.com/f/mwF1iyZ7nn8rnDcE8Wstch/0</p> <p>I could write this query myself, but my only solution is to use a very large number of subqueries. But I'm wondering if there's a more efficient way and hopefully the SQL experts here can help? </p>

delete from routes where concat (slat,'|', slng) not in (
  select concat(lat ,'|', lng) from sites 
  union  select concat(lat,'|',lng) from fse
) ;