CODEWITHSUNDEEP
sqlt-sql
user2974732
user2974732

Reputation: 103

where clause with Is null and not in

I have an issue with where clause. I want "is null" and "<>" functionality of a column together. Wrote something like this but it removes rows with t1.column1= test and is null . I want to remove only test. 

where (t1.column1 is null 
or t2.column1 is null
or t2.column2 is null)
and t1.column1 <> 'test'

Thanks in advance.

Upvotes: 0

Views: 72

Answers (4)

CElliott
CElliott

Reputation: 452

You could try:

WHERE  (   t1.column1 != 'test'
       AND (t2.column1 IS NULL OR t2.column2 IS NULL)
       )
    OR t1.column1 IS NULL

to get your desired results.

Upvotes: 1

sz1
sz1

Reputation: 72

Select t1.[Column1], t2.[Column2]
From MyTable t1
INNER JOIN MyTable t2
On t1.userid = t2.userid
where t1.[column1] is null Or t1.[column1] = ''
or t2.[column1] is null Or t2.[column1] = ''
or t2.[column2] is null Or t2.[column2] = ''
and t1.[column1] <> 'test'

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

You can never satisfy this condition
This will always be false 

t1.column1 is null and t1.column1 <> 'test'

If it is <> 'test' then it is not null

Any comparison to null return false
All of these will return false
null = null
null <> null
'value' = null
'value' <> null

Even (Not ('value' = null)) will return false

So if t1.column1 = null then you have
true and false = false

Upvotes: 0

durbnpoisn
durbnpoisn

Reputation: 4669

You may have to break each out:

where (t1.column1 is null and t1.column1 <> 'test') 
or (t2.column1 is null  and t2.column1 <> 'test')
or (t2.column2 is null  and t2.column2 <> 'test')

Or whatever criteria you need for each condition...

Upvotes: 0

Related Questions