Avoid SQL WHERE NOT IN Clause

sql where-in
pengemizt picture pengemizt · Nov 6, 2011 · Viewed 8.7k times · Source

I have 3 tables listed below:

Blog    BlogArticle  Article
----    -----------  -------
        id
id------blog_id      -id
title   article_id__/ title

This SQL describe what I want:

SELECT * 
FROM  `article` 
WHERE  `article`.`id` NOT IN (
        SELECT CONCAT(`blog_article`.`article_id`) 
        FROM  `blog_article` 
        WHERE  `blog_article`.`blog_id` = 1 -- example value
       )

The problem is, I have a big NOT IN values in that case, and as far as i know it will impact to server performance (I'm not sure since I've never try to benchmark or Google it). Any suggestion?

Answer

GregM picture GregM · Nov 6, 2011

Try this : 

SELECT * 
FROM  `article` 
LEFT JOIN `blog_article` ON CONCAT(`blog_article`.`article_id`) = `article`.`id`
                            AND  `blog_article`.`blog_id` = 1 -- example value
WHERE `blog_article`.`article_id` is null --replace the not in

Related questions

SQL use CASE statement in WHERE IN clause

Is it posible to use case in where in clause? Something like this: DECLARE @Status VARCHAR(50); SET @Status='published'; SELECT * FROM Product P WHERE P.Status IN (CASE WHEN @Status='published' THEN (1,3) WHEN @Status='standby' THEN (2,5,9,6) WHEN @Status='deleted' THEN (4,5,8,10) …

Read More
MySQL WHERE IN Query - ORDER BY Match

I'm trying to rephrase my question, cause my last one wasn't clear to everyone. This is my Test Table +----------+---------+-------+ | rel_id | genre | movie | +----------+---------+-------+ | 1 | 1 | 3 | | 2 | 8 | 3 | | 3 | 3 | 3 | | 4 | 2 | 5 | | 5 | 8 | 5 | | 6 | 3 | 5 | | 7 | 1 | 8 | | 8 | 8 | 8 | | 9 | 3 | 8 | | 10 | 5 | 9 | | 11 | 7 | 9 | | 12 | 9 | 9 | | 13 | 4 | 9 | | 14 | 12 | 9 | | 15 | 1 | 10 | | 16 | 8 | 10 | | 17 | 3 | 10 | | 18 | 5 | 10 | | 19 | 1 | 11 | | 20 | 2 | 11 | | 21 | 8 | 11 | | 22 | 5 | 11 | | 23 | 3 | 11 | +----------+---------+-------+ Result should be in the following order if …

Read More
MySQL multiple columns in IN clause

I have a database with four columns corresponding to the geographical coordinates x,y for the start and end position. The columns are: x0 y0 x1 y1 I have an index for these four columns with the sequence x0, y0, …

Read More