CODEWITHSUNDEEP
mysqlsqlsql-server
BoBasket4
BoBasket4

Reputation: 69

Why is SQL not matching two strings?

As I said in the title, I have a query that doesn't return what's expected and it isn't logical... Here is the case :

Table A -> id INT, value VARCHAR

Table B -> id INT, value VARCHAR

When I run that query SELECT value FROM A WHERE value NOT IN (SELECT value FROM B);

I expect that query to return all values that are not in the table B, but it returns all the values.

Let's assume that A contains

  1. '425415'
  2. '698741'

With that query : SELECT value FROM A WHERE value NOT IN ('698741','425415'); it still returns all the values without triggering the NOT IN...

Upvotes: 3

Views: 1222

Answers (2)

KtX2SkD
KtX2SkD

Reputation: 752

Summarizing checklist from comment thread:

  • Testing with aliases.
  • Testing with LIKE.
  • Checking lengths.
  • Checking collations.

Upvotes: 1

Bartosz X
Bartosz X

Reputation: 2798

This is because you have a NULL in the "B" table, try this:

SELECT A.[value]
FROM A
LEFT JOIN B ON A.[value] = B.[value]
WHERE B.[value] IS NULL;

Or if you want to keep your code just add IS NOT NULL:

SELECT [value] 
FROM A 
WHERE [value] NOT IN (SELECT [value] FROM B WHERE [value] IS NOT NULL);

sample image

Upvotes: 2

Related Questions