How to delete unused rows from DB table by using SQL?

Question

I am trying to delete unused rows from a table. This is simplified example of my problem:

There are 2 table:

user table:

user_id    user_name
--------------------
1          Mike
3          Carol
8          Eric


address table:

user_id    address
-----------------------
1          [email protected]        
3          [email protected]
10         [email protected]
3          [email protected]

I want to delete unused addresses from the address table. If user_id of an address does exists in user table, then the address is unused. There is one unused address in the example table: [email protected].

I am new with SQL, and my solution was ugly:

DELETE FROM address 
  WHERE NOT EXISTS 
   ( SELECT * FROM user WHERE address.user_id = user.user_id );

There must be better way to do it. What is the best way to do it?

sqlite is used.

Answer 1

Do it like this:

DELETE FROM address
    WHERE user_id NOT IN (SELECT user_id FROM user);