CODEWITHSUNDEEP
sqlsql-servert-sqlcomparisonsql-like
Stephanie Wilson
Stephanie Wilson

Reputation: 55

Microsoft SQL 'not like' vs <>

Is there any real difference between using 'not like' without any % signs and using the not-equal operator <> in Microsoft SQL? Example:

if exists (select * from table_name where column_name not like @myvariable)

or

if exists (select * from table_name where column_name not like 'myvalue')

versus

if exists (select * from table_name where column_name <> @myvariable)

or

if exists (select * from table_name where column_name <> 'myvalue')

I've noticed I have a habit of using not like (it's faster to type and feels more intuitive when reading my own code) and I was wondering if there's any chance that it will ever cause behavior that is different from a not-equal. I read in other questions that 'like' is slower than 'equals' but I'm more concerned about the result of the comparison here. I am nearly always using the varchar data type when doing comparisons.

Upvotes: 2

Views: 16950

Answers (5)

dark
dark

Reputation: 1

You coud have different results when comparing nchar and nvarchar columns. Try this:

CREATE TABLE #Test (ColA nchar(10), ColB nvarchar(10))

INSERT INTO #Test SELECT N'test    ', N'test '

SELECT * FROM #Test WHERE ColA = ColB

SELECT * FROM #Test WHERE ColA LIKE ColB

DROP TABLE #Test

This probably happens because when you use " = " sql server has to cast both sides of comparison into same data type and then nchar column is casted into nvarchar and trailing spaces are removed.

Upvotes: 0

Joe Pineda
Joe Pineda

Reputation: 5691

Besides the other problems mentioned in the other answers (potential problems should the variable contain the literals "%" or "_") I'd say it's also bad for performance: SQL Server will nonetheless scan the string looking for a pattern, only to find nothing.

Ergo, your query's semantically equivalent to using different-from operator in the first place, except the "<>" wouldn't force SQL S. to search the string looking for patterns to look for.

Admittedly, you wouldn't lose more than a few milliseconds yet those could sum up to a lot of extra time should this query be called repeatedly by several users, almost simultaneously.

Upvotes: 0

paparazzo
paparazzo

Reputation: 45106

<> will not evaluate wild cards
<> '%' is a search on literal %

There are more "wild cards" than % _

These are not the same 

  SELECT TOP 1000 [ID],[word]
  FROM [FTSwordDef]
  where [word] like '[a-z]a'


  SELECT TOP 1000 [ID],[word]
  FROM [FTSwordDef]
  where [word] = '[a-z]a'

Use <> when you have a literal match

Use LIKE when you want to use "wild cards"

The expression are not evaluated the same way and it is sloppy to just use LIKE exclusively with the assumption that they are interchangeable.

Upvotes: 4

agt
agt

Reputation: 301

In the examples you give, there is not a difference in the end result of the query. However, I'd say this is probably a bad idea. You're opening yourself up to bugs related to reserved characters that could be tricky to track down (LIKE uses % and _ as reserved characters for pattern matching). If you're hard-coding the WHERE clause, that might not be a problem, but you've got variables in there too. Your application would need to check that the variable doesn't contain % or _ in order to avoid bugs and security holes.

Also, LIKE is "marked" syntax--you don't typically use it unless you have to do pattern matching. Someone else reading your code is going to spend time trying to figure out why you used LIKE when you actually meant <>. Considering that the semantic meaning of what you're trying to do is "does not equal," using the designated operator will result in maximum clarity.

Upvotes: 2

refeline
refeline

Reputation: 39

  1. There is another special symbol '_' (any single character). I think that is bad idea to use 'like' and possible way to get headache in future.

  2. All your expression seems strange: double negation in logic, why not to use

    if exists( select ... where column_name = 'value' )

  3. Using negation in where automatically disables using indices (as I know from Oracle).

  4. Yes, I think using like also affects execution plan of query

Upvotes: 0

Related Questions