• Home
• About

Subquery using NOT IN doesn’t seem to work in MySQL

For some reason today when attempting to do a an INSERT INTO statement using a SELECT from to populate the condition I used to retrieve data, that is a NOT IN statement to prevent existing records being duplicated simply wouldn’t work.  I’ve heard that on MSSQL server that NOT EXISTS is a more efficient option anyway so I gave that a go and it rectified the issue.  I found some references to it on the web but nothing really clarified the problem so just stick to NOT EXISTS if you have this problem.

For the record I’m using MySQL 5 so there should be no version issues.

Here is a before and after example just so it’s clearer what I mean

First the one that doesn’t work:

INSERT INTO table2(col1, col2, col3)
SELECT    col1,col2,col3
FROM
table1

WHERE
table1.col1 NOT IN (select col1 FROM table2)

Now the functional equivalent that does work

INSERT INTO table2(col1, col2, col3)
SELECT    col1,col2,col3
FROM
table1

WHERE
NOT EXISTS (SELECT * FROM table2 A where A.col1 = table1.col1 )

If anyone actually knows why this wouldn’t work please leave a comment it would be great to know.

Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically to your feed reader.

Copyright HonkBlog|TechNotes | Theme by DailyBlogTips