Need help with a query

Question

Hi have a 2 tables organized in this way:

table_users
|user_id|username|

table_products
|product_id|user_id|

Now, I have some orphan entries in 'table_products'. With this query I check how many products I have:

SELECT count(product_id) FROM table_products

This query show me the number of the products associated to the users:

SELECT p.product_id, u.username
FROM table_products AS p
JOIN table_users AS u ON u.user_id = p.user_id

I need a query that allow me to select and remove all the orphans products. Anyone can help me ?

Answer 1

Warning: I am not taking any responsibility for data deleted with this query. Always test on test database before running on production data.

DELETE p 
FROM table_products AS p 
LEFT JOIN table_users AS u 
USING (user_id) 
WHERE u.user_id IS NULL

Answer 2

Using NOT IN:

DELETE FROM TABLE_PRODUCTS 
 WHERE user_id NOT IN (SELECT u.user_id
                         FROM TABLE_USERS u)

Using NOT EXISTS:

DELETE FROM TABLE_PRODUCTS 
 WHERE NOT EXISTS (SELECT NULL 
                     FROM TABLE_USERS u
                    WHERE u.user_id = TABLE_PRODUCTS.user_id)

Using LEFT JOIN/IS NULL:

   DELETE TABLE_PRODUCTS 
     FROM TABLE_PRODUCTS p
LEFT JOIN TABLE_USERS u ON u.user_id = p.user_id
    WHERE u.user_id IS NULL

Efficiency (MySQL only)

If the TABLE_PRODUCTS.user_id is nullable, the NOT IN and NOT EXISTS are more efficient choices. Otherwise, the LEFT JOIN is the most efficient choice in MySQL when the columns compared can not be null.

Addendum

Check and double check that you are selecting the correct rows for deletion before performing the action by swapping out "DELETE FROM" for "SELECT * FROM". Additionally, if using InnoDB tables - encapsulate the DELETE in a transaction so you can use ROLLBACK if necessary.