How to compare 2 columns of 2 different tables on Mysql

I did something like this with PHP and it works.

$table1 = $DB->query('SELECT column1 FROM table1');
$table2 = $DB->query('SELECT column2 FROM table2'); # excludes

$diff = "'" . implode("','", array_diff($table1, $table2)) . "'";

$DB->query("DELETE FROM table1 WHERE column1 IN ({$diff})");

My question is how to do the same with one sql statement, instead of writing 3 different and doing the array_diff/implode thing.

Unfortunately I have limited sql knowledge but I’m pretty sure it’s possible, I think with JOIN but after 2 hours still can’t find how. :/

Answer

You can use sub-query. The query will be like below.

Delete from Table1 
where column1 in (
   SELECT column2 FROM table2
)