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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You can try this:

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

Method 2

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

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

Method 3

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0