Deleting the garbage

Databases
1

Hey,

When I do SELECT COUNT(id) FROM customers it returns 1, 543. But When I JOIN the customers table with a 2nd table that references the IDs the number is actually higher.

Here is the query:


SELECT COUNT(id)
FROM customers
INNER JOIN favs
  ON favs.userID = customers.id

So what I’m trying to do is show the IDs that are no longer in the customers table so that I can delete them and the COUNT can be accurate.

2

If a user has 5 favs, there will be 5 rows for that user here. You are not counting distinct customers but how many customer-favorite pairs there are. That’s why the number is higher.

An INNER JOIN won’t ever return rows from one table that don’t have corresponding rows in the other.

3 

```sql


DELETE FROM C
FROM customers AS C
  LEFT JOIN favs AS F ON F.userID = C.id
WHERE F.userID IS NULL





That should remove all the records from the "customers" table that don't have a match in the "favs" table.  Change the "DELETE FROM C" line to "SELECT" to see the records.