Is there a difference between inequality symbols <> and != in SQL Server?

Question

When writing a query, does it matter if I use <> or != when I mean "not equal to"?

As per the example below, nulls don't seem to be affected whether I use <> or !=. Is this an aesthetic thing?

declare @fruit table
(
FruitID int,
FruitName varchar(50)
)

insert into @fruit (FruitID, FruitName) Values (1, 'Apple')
insert into @fruit (FruitID, FruitName) Values (2, 'Orange')
insert into @fruit (FruitID, FruitName) Values (3, 'Pineapple')
insert into @fruit (FruitID, FruitName) Values (null, 'Not a Fruit')

select *
from @fruit
where fruitid<>2

select *
from @fruit
where fruitid!=2

One is standard, one is not. – ta.speot.is Nov 08 '13 at 21:18 — ta.speot.is, Nov 08 '13 at 21:18
@ta.speot.is which one is standard? – FistOfFury Nov 08 '13 at 21:19 — FistOfFury, Nov 08 '13 at 21:19
<> is ANSI compliant. – Stuart Ainsworth Nov 08 '13 at 21:23 — Stuart Ainsworth, Nov 08 '13 at 21:23

Joel Coehoorn · Answer 1 · 2017-03-06T23:53:41.880

The <> operator is the operator defined by the formal sql standard. The != operator is an alias that is more familiar to many developers. They do the same thing, in the same way, and there are no performance or behavior differences on any dbms system I've worked with where both are supported.

Most database systems support !=, but there are exceptions. Thus, in theory, <> should be more portable, as it's the standard. In practice, I'd guess there are about as many that only implement != as there are that only implement <>.

I come from a C++ background, and in my early days using SQL I didn't at first even know <> existed. I learned about <> not long after leaving school, when I had to spend some time working on a VB code base, and at the time thought it was just a quirk of the developers, that they would prefer the VB-style sql operator to go with their VB client code. For a long time after I still used !=. Later I learned <> was the standard, and had probably been used since '86, back when VB was still just "BASIC". I've since used <> almost exclusively.

Is there a difference between inequality symbols <> and != in SQL Server?

1 Answers1