Difference between != and <>

Question

Difference between != and <>

Shadab Shah

SSCarpal Tunnel

Points: 4759
More actions
September 1, 2012 at 12:57 am

#261018

Hi,
I want to know what is the difference between != and<>. I don't know which one is prior and later one but when there was a prior one why was there a need of later one

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

No difference. They mean and do exactly the same thing.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Arjen Krap SSC-Addicted Points: 473 More actions · Answer 2

A trivial, but interesting question.

Those operators are semantically equivalent in Microsoft SQL Server.

<> is the standardized not equal operator. Most other SQL products implements this operator. IMHO it is best practice to use this one. However if use this operator inside an XML document you will have to escape it to <> or use enclose the query in <[CDATA[ ... ]]> tags.

!= is the alternative not equal operator.

Beside Microsoft SQL Server, other SQL implementations, like Sybase, Oracle, and MySQL, support both operators. But Microsoft Access only supports <>.

The <> operator is also used in popular programming languages like Pascal, Basic, etc. The != operator, however, is used by C like languages, like C, C++, Java, JavaScript, C#, etc. It's obvious that most developers will prefer to use the operator from their favorite programming language. A C developer will prefer to use !=. And a Basic developer will prefer to use <>. So supporting both operators is more likely to gain developers acceptance.

In other programming languages NE, JNE, NEQ (from Not EQual) or /= (Lisp) is used. != and /= are derived from the mathematical not equal sign: ?. An equal sign which is crossed out by a diagonal line. This is however not an common character found on keyboard so something else is used instead. Just like >= is used for =, and <= is used for =. Also ! is used as an negation operator in may programming languages, so combining ! and = makes sense.

I am not sure about the origin of the <> notation, but I suspect it is a combination of the < and > characters meaning 'less or greater than' which is similar to 'not equal to'.

The most known SQL standardization is ANSI SQL92. Historically the earliest versions of Microsoft SQL Server where not ANSI SQL92 compliant. For backwards compatibility Microsoft SQL Server has a lot of settings to enable the old behavior. While Microsoft is traditionally a Basic company, which would prefer the <> operator, however, most systems programming at Microsoft (and Sybase) where already done in C at that time. Which make the != operator the most obvious choice. So I suspect the != operator was first and <> came along when the ANSI SQL92 standardization was enforced.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

Along with what the others said and from an old dude's perspective, <> is easier for me to quickly recognize than != as an inequality which, at a quick glance, looks like just = .

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

As an ex-C++ and occasional C# programmer, I almost always use !=

! means NOT, so != literally means NOT Equals

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

GilaMonster (9/1/2012)
As an ex-C++ and occasional C# programmer, I almost always use !=
! means NOT, so != literally means NOT Equals

I've never been a C++ or C# programmer but I still knew that because it's in BOL. Knowing what it is doesn't make it easier for me to see it, though. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

It's so good to know that I fall into the category of bad SQL programmer as well as poor database designer (for using surrogate keys) 😀

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Arjen Krap SSC-Addicted Points: 473 More actions · Answer 7

@celko,

Let me get this clear, so I'll understand you correctly. You can predict which kind of errors and design flaws someone makes in code, by someones preference to != or <>. That's remarkable.

I find this very interesting. Please tell us more about this. Can you explain the different SQL development styles by examining the following 4 queries:

DECLARE @test-2 TABLE

(

a int

);

INSERT @test-2 VALUES (1),(2),(3),(4),(5),(NULL);

-- Development style A

SELECT *

FROM @test-2

WHERE a != 3;

-- Development style B

SELECT *

FROM @test-2