Avoid SQL WHERE NOT IN Clause

Question

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?

What database are you using? SQL server 2008, for example, transforms IN into EXISTS. I read somewhere in here that the latest version of MySQL does the same. You can do the same if your DB documentation does not mention anything of the sort — Icarus, Nov 06 '11 at 15:25
@Icarus: SQL server 2008 is not a "database". Suggestion: ask, "Which SQL product...?" — onedaywhen, Nov 07 '11 at 10:56
@onedaywhen: "Which database are you using?" and "Sql Server 2008..." are 2 different sentences. If you have reading comprehension issues, I can't do anything about that. — Icarus, Nov 07 '11 at 11:20
@Icarus: You can help me comprehend why you would want to know which database they are using. If I told you they were using their enterprise's 'Blogs' database, or tempdb/Master, etc how would this help? — onedaywhen, Nov 07 '11 at 11:42
@Icarus "What database are you using? SQL server 2008, for example..." Are those two sentences supposed to be independent clauses? The second sounds dependent on the first to me. — onedaywhen, Nov 07 '11 at 11:43

score 2 · Accepted Answer · answered Nov 06 '11 at 15:25

2

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

answered Nov 06 '11 at 15:25

GregM

2,634
3
22
37

Hey, thank you, it's working if i run directly, but i think i have some problem when i use Yii AR Class. The error say "SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in field list is ambiguous.". Thanks in advance. – pengemizt Nov 06 '11 at 15:39
Sorry don't know the Yii AR Class, at least your sql is working, you could write this answer as accepted and post a new question for your Class – GregM Nov 06 '11 at 15:43
@user1032315 Column is ambiguous errors mostly (always?) mean that there are two columns with the same name in the join. Try `SELECT 'article'.*` instead of just `SELECT *`. – potNPan Nov 06 '11 at 16:37

Avoid SQL WHERE NOT IN Clause

1 Answers1

Linked