CODEWITHSUNDEEP
mysqlsqlselectnull
mhellmeier
mhellmeier

Reputation: 2281

SQL SELECT is not a number but can be NULL

I have got a table with 3 columns: (int) test1, (int) test2 and (int) test3. The content is an integer or NULL.

For example:

╔═══╦════════════╦═════════════╦═════════════╗
║   ║ test1      ║ test2       ║ test3       ║
╠═══╬════════════╬═════════════╬═════════════╣
║ 1 ║ 123        ║ 456         ║ NULL        ║
║ 2 ║ 456        ║ 456         ║ NULL        ║
║ 3 ║ NULL       ║ NULL        ║ NULL        ║
╚═══╩════════════╩═════════════╩═════════════╝

I want to get every entry, where test1 is not "123" and test2 is not "123" and test3 is not "123". I need every entry with another number than "123" or NULL.

I tried the following SQL syntax:

SELECT * FROM table WHERE test1 != "123" AND test2 != "123" AND test3 != "123";

The problem is, that I get no entry because there must be a number everywhere. In this example, I want to get entry 2 and entry 3 but not the first one because there is a "123" in column test1.

How can I make this?

Upvotes: 2

Views: 1813

Answers (5)

Burcin
Burcin

Reputation: 985

You can use isnull(column, 0). I assume the columns are integer: 

create table #x (test1 int null, test2 int null, test3 int null)
go
insert #x select 123, 456, null
insert #x select 456, 456, null
insert #x select null, null, null

SELECT * FROM #x WHERE isnull(test1,0) != 123 AND isnull(test2,0) != 123 AND isnull(test3,0) != 123


test1       test2       test3       
----------- ----------- ----------- 
        456         456        NULL 
       NULL        NULL        NULL

Upvotes: 1

Antoine Lefebvre
Antoine Lefebvre

Reputation: 376

Should work with this:

SELECT * FROM table 
WHERE (test1 != "123" OR test1 is null) 
 AND  (test2 != "123" OR test2 is null)
 AND  (test3 != "123" OR test3 is null);

Upvotes: 0

Bohemian
Bohemian

Reputation: 425033

Use ifnull() to convert nulls to an included number:

SELECT * FROM table
WHERE ifnull(test1, 0) != "123"
AND ifnull(test2, 0) != "123"
AND ifnull(test3, 0) != "123";

Upvotes: 2

Mureinik
Mureinik

Reputation: 311348

null is not a value - it's the lack thereof. It returns "unknown" when used in any context expecting a value, even the != operator. In order to handle nulls, you should do so explicitly with the is operator:

SELECT * 
FROM   mytable
WHERE  (test1 IS NULL OR test1 != 123) AND 
       (test2 IS NULL OR test2 != 123) AND
       (test3 IS NULL OR test3 != 123)

Upvotes: 3

durbnpoisn
durbnpoisn

Reputation: 4669

You need to use the NOT IN function and is NULL function:

SELECT * FROM table 
WHERE (test1 not in ("123","others") or test1 is NULL)
and  (test2 not in ("123","others") or test2 is NULL)

And so on... You can add whatever you want to exclude from the NOT IN list.

Upvotes: 0

Related Questions