MySQL Select statement Where table1.id! = Table2.id

Question

I have a data table that has messages in it, then I have a separate data table that deletes messages. What happens when a message is deleted is that its identifier is added to the remote table, rather than deleting the record.

Which is a clean, efficient way to select all messages from the message table without selecting those that have their ID in the remote table.

Answer 1

If you cannot change your tables, you can do a Left Join (which, if possible, will join your deleted_table ), and then check that id Null (meaning no rows were found).

 Select everything_you_need From post_table p Left Join delete_table d On ( d.id = p.id ) Where d.id Is Null; 

Make sure there are indexes for id -columns for best performance.

Answer 2

Wouldn't it be easier to add an additional column to the message table and save the logical value of the deleted one? Or use the whole field (to represent the user ID), if you want to save who deleted it, 0 = did not delete.

Answer 3

If you have a good reason not to use the boolean flag, as bigstylee suggests, you can use the EXISTS subquery:

 SELECT * FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.id=table2.id);