Executing NOT IN query after LOAD DATA INFILE

Question

I have successfully insert data with LOAD DATA INFILE. After that when I am using the query:

SELECT * FROM tempupload 
         WHERE columnName NOT IN (SELECT columnName FROM othertable)

Not giving me the desirable result. But when I convert columnName datatype to double (and after again to varchar) giving me desirable results.

Please guide me as I have to use these queries from my Java EE Application.

Use `LEFT JOIN ... IS NULL` instead of `NOT IN()`, it will almost certainly be faster. What are the datatypes of the two fields used in the join? What data is being stored in these two fields? The fact that you can CAST to DOUBLE and back to VARCHAR would suggest that you are using the wrong datatypes. — user1191247, Mar 31 '12 at 12:03
I have to save the 13 digit number in the column may be later it may include the alphabet so i am using VARCHAR — vinay, Apr 03 '12 at 04:54

score 0 · Answer 1 · edited May 23 '17 at 10:08

0

If you have any NULLs in your othertable you may have some issues with a NOT IN (see this). To improve your query you could try:

SELECT * FROM tempupload 
WHERE EXISTS (SELECT 1
    FROM othertable 
    WHERE othertable.columnName = tempupload.columnName)

edited May 23 '17 at 10:08

Community

answered Mar 31 '12 at 18:11

GavinCattell

1 Answers1