54

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.

TylerH
  • 20,799
  • 66
  • 75
  • 101
Shanka
  • 811
  • 1
  • 7
  • 16
  • 42
    Do you have `null` values for that column at all? – musefan Nov 02 '16 at 09:24
  • Yes, there are null values in that column – Shanka Nov 02 '16 at 09:25
  • 1
    Try avoiding to use `%` at the beginning of your search string when possible. It is quite heavy. – Moslem Ben Dhaou Nov 02 '16 at 16:54
  • @MoslemBenDhaou There are ways to optimize infix queries. I believe the primary method in SQL Server is the Full Text Search mechanism, but I may be unaware of better options. In PostgreSQL, you can use trigram indexes to make them efficient. For a table of 200 rows, though, it probably isn't even worth indexing. – jpmc26 Nov 02 '16 at 17:45
  • 2
    What? MySQL is "teen-vocabulary" compliant? – This company is turning evil. Nov 02 '16 at 22:54
  • The 90 'missing' records in the NOT LIKE result regards to null values into the tested column. From my 'personal logic' I also find weird that null value won't match NOT LIKE (because it does match!) but this is how MSSQL works. – Yan Kleber Apr 29 '21 at 17:26

4 Answers4

75

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
sagi
  • 40,026
  • 6
  • 59
  • 84
  • 16
    I would suggest using `[TextCol] NOT LIKE '%TAX%' OR [TextCol] IS NULL` as it would have better performance if the user wants to include `NULL` values – ughai Nov 02 '16 at 09:29
  • 3
    He said "190 records", so I doubt performace is an issue, but edited anyway as others might read this answer @ughai . – sagi Nov 02 '16 at 09:38
  • 1
    Thanks! NULL was the issue. – Shanka Nov 02 '16 at 09:49
  • 4
    "then `NULL NOT LIKE '%TAX%'` will return false" -- No, it won't. If it returned false, then it would be trivial to work around the problem by putting the `NOT` at a higher level: `NOT(NULL LIKE '%TAX%')` would return true if `NULL LIKE '%TAX%'` returned false. Instead, it returns unknown. Unknown also means the row won't be selected. –  Nov 02 '16 at 12:03
  • 3
    It's not "if performance is an issue", that's also the obviously clearer version of the code. Saves people puzzling out where "-1" comes from. – hobbs Nov 02 '16 at 20:07
18

(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.

Salman A
  • 262,204
  • 82
  • 430
  • 521
0
  1. Select * from tbl1 
WHERE ([TextCol] NOT LIKE '%TAX%') AND ([TextCol] NOT LIKE '%TAX%')

  2. select * from tbl1
where [TextCol] NOT LIKE '%TAX%' OR [TextCol] IS NULL
Toby Speight
  • 27,591
  • 48
  • 66
  • 103
Chanukya
  • 5,833
  • 1
  • 22
  • 36
  • 17
    What's the point of `WHERE ([TextCol] NOT LIKE '%TAX%') AND ([TextCol] NOT LIKE '%TAX%')` ? – moopet Nov 02 '16 at 11:39
  • 5
    While this code snippet may solve the question, including an explanation [really helps](//meta.stackexchange.com/q/114762) to improve the quality of your post. Remember that you are answering the question for readers in the future, not just the person asking now! Please [edit] your answer to add explanation, and give an indication of what limitations and assumptions apply. – Toby Speight Nov 02 '16 at 16:22
0

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)
Tomas Hesse
  • 385
  • 3
  • 10