How to compare 2 columns of 2 different tables on Mysql-CodePudding

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. :/

CodePudding user response：

You can try this:

DELETE t1 
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.column1 = t2.column2;

CodePudding user response：

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

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

CodePudding user response：

Here is a solution

DELETE FROM table1 WHERE column1 IN (
SELECT t0.column1 FROM
(SELECT column1 FROM table1) AS t0
JOIN
(SELECT column2 FROM table2) AS t1
ON t0.column1 = t1.column2)

Thanks.