MySQL Delete with Join and Count-CodePudding

I have two tables s_filter_values and s_filter_articles.

s_filter_values:       s_filter_articles:

| id | value |         | articleID | valueID |
|----|-------|         |-----------|---------|
| 1  | one   |         | 1         | 2       |
| 2  | two   |         | 1         | 3       |
| 3  | three |         | 2         | 2       |

With the following statement I count the the occurence of the values respectively I get the values that are not linked to an article:

SELECT v.*, IFNULL(COUNT(a.articleID), 0) AS counter 
FROM s_filter_values AS v
LEFT JOIN s_filter_articles AS a ON v.id = a.valueID
GROUP BY v.id
HAVING counter = 0

In this case, I got

| id | value | counter |
| 1  | one   | 0       |

My questions is: How can I use this statement to delete all rows from s_filter_values that are not linked to an article?

CodePudding user response：

I would use a subquery to get all IDs that are in the table. Then drop the rows from s_filter_values that are not present.

DELETE FROM s_filter_values WHERE id NOT IN (SELECT DISTINCT valueID FROM s_filter_articles);

CodePudding user response：

A simple sub select should do it:

DELETE FROM `s_filter_values` 
WHERE `id` NOT IN SELECT DISTINCT `valueID` FROM `s_filter_articles`

CodePudding user response：

I will suggest using NOT EXIST rather than NOT IN.

The NOT EXISTS should perform faster on a large dataset. There is one key difference between the two constructs: if the subquery returns a NULL in its results then the NOT IN condition will fail, because null is neither equal-to nor not-equal-to any other value.

   create table s_filter_values(
                 id int(10), 
                `value` varchar(10) );

   insert into s_filter_values  values ( 1,'one'),( 2,'two'),( 3,'three');

   create table s_filter_articles(
                 articleID int(10), 
                   valueID int(10) );

   insert into s_filter_articles values ( 1,2),( 1,3),( 2,2);


DELETE FROM s_filter_values   
WHERE  NOT EXISTS  (SELECT valueID  FROM s_filter_articles a where a.valueID= s_filter_values.id);

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/84