SQL Delete on inner join on MISSING data

Question

My question is almost identical to SQL DELETE with INNER JOIN ; but I want to delete on non equal!

MY PROBLEM IN A BRIEF: There are 2 tables, bus_stops, bus_routes ;

bus_routes {id, bus_route_id,..other columns..}
bus_stops {id, bus_route_id,..other columns..}

Some routes had been deleted, but bus stops remaining, I need to delete them too. Means, I need to delete only bus_stops, which have NO associated bus route!

It means something like:

DELETE bs.* FROM bus_stops AS bs 
INNER JOIN bus_routes AS br 
ON bs.bus_route_id <> br.bus_route_id

But the above code will definitely not work.

Answer 1

You should use LEFT JOIN, below query will work:

DELETE bs.*  
FROM bus_stops AS bs 
LEFT JOIN bus_routes AS br 
ON bs.bus_route_id = br.bus_route_id
WHERE br.bus_route_id IS NULL

Answer 2

A join in SQL is first of all the Cartesian product of both tables. Meaning every record of table A is combined with every record of table B. The join condition then reduces the records by eleminating records that do not match the condition.

If you use an INNER JOIN with not equal (<>) every record is going to be deleted if you have at least to distinct values. A small example:

Table A | B     Table C | D
=============   =============
        | 1             | 1
        | 2             | 2

The Cartesian product of A X B is:

  | B | D
==========
  | 1 | 1
  | 1 | 2
  | 2 | 1
  | 2 | 2

If you now use B <> C to select the values, the result will be:

  | B | D
==========
  | 1 | 2
  | 2 | 1

This would delete both records.

As a solution try an outer join or a subquery.

Example (subquery):

DELETE FROM C WHERE NOT EXISTS(SELECT * FROM A WHERE A.B = C.d)

Example (outer join):

DELETE FROM C LEFT JOIN A ON C.D = A.B WHERE A.B IS NULL