Reputation: 821
I have a table with 200 records out of which 10 records has text containing the word 'TAX'.
When I'm executing
Select * from tbl1 WHERE [TextCol] LIKE '%TAX%'
then I get the result set with those 10 records correctly .
But when I am trying to exclude those records by
Select * from tbl1 WHERE [TextCol] NOT LIKE '%TAX%'
it's returning 100 records only, instead of 190.
Upvotes: 54
Views: 9004
Reputation: 395
I had the same problem with the IN
operator on simple int column with nulls.
I found that these where not each others inverse as I thought. (I could tell by the row count)
select * from Dest where id in(select id from Source)
select * from Dest where id NOT in(select id from Source)
To get each others invert I had too rewrite them as such:
select * from Dest where isnull(id,-2) in(select isnull(id,-1) from Source)
select * from Dest where isnull(id,-2) NOT in(select isnull(id,-1) from Source)
Upvotes: 0
Reputation: 272376
(A) SQL comparison operators result in three possible values: True, False and Unknown. If one or both operands are NULL
then the result is Unknown. Consider the following example where we compare some values (a person's age) with a constant (18):
21 >= 18 -- True
15 >= 18 -- False
NULL >= 18 -- Unknown
As you can see, the database can/will not decide if NULL
is greater than/equal to 18.
(B) The database will only return rows where the WHERE
clause evaluates to True. Inverting the expression (e.g. WHERE age >= 18
changed to WHERE age < 18
) does not affect Unknown results.
You can use the IS [NOT] NULL
to match NULL
values. The following query will select the rows where the column does not match the pattern OR the column is NULL:
WHERE [TextCol] NOT LIKE '%TAX%' OR [TextCol] IS NULL
Functions such as ISNULL
and COALESCE
can be used to transform NULL
into some value.
Upvotes: 19
Reputation: 5893
Select * from tbl1
WHERE ([TextCol] NOT LIKE '%TAX%') AND ([TextCol] NOT LIKE '%TAX%')
select * from tbl1
where [TextCol] NOT LIKE '%TAX%' OR [TextCol] IS NULL
Upvotes: 0
Reputation: 40491
Does this return the correct result ?
Select * from tbl1 WHERE COALESCE([TextCol],'-1') NOT LIKE '%TAX%'
I believe NULL
values are the issue here, if the column contains them, then NULL NOT LIKE '%TAX%'
will return UNKNOWN/NULL
and therefore won't be selected.
I advise you to read about handling with NULL
values , or here.
As @ughai suggested, if performance is an issue you can also use:
Select * from tbl1
WHERE [TextCol] NOT LIKE '%TAX%'
OR [TextCol] IS NULL
Upvotes: 76