Should I Use != or ≪≫ for Not Equal in T-Sql

Should I use != or for not equal in T-SQL?

Technically they function the same if you’re using SQL Server AKA T-SQL. If you're using it in stored procedures there is no performance reason to use one over the other. It then comes down to personal preference. I prefer to use <> as it is ANSI compliant.

You can find links to the various ANSI standards at...

http://en.wikipedia.org/wiki/SQL

What is the difference between NOT and != operators in SQL?

NOT negates the following condition so it can be used with various operators. != is the non-standard alternative for the <> operator which means "not equal".

e.g.

NOT (a LIKE 'foo%')
NOT ( (a,b) OVERLAPS (x,y) )
NOT (a BETWEEN x AND y)
NOT (a IS NULL)

Except for the overlaps operator above could also be written as:

a NOT LIKE 'foo%'
a NOT BETWEEN x AND y
a IS NOT NULL

In some situations it might be easier to understand to negate a complete expression rather then rewriting it to mean the opposite.

---

NOT can however be used with <> - but that wouldn't make much sense though: NOT (a <> b) is the same as a = b. Similarly you could use NOT to negate the equality operator NOT (a = b) is the same as a <> b

Is it better to use equal or not equal when making a query?

Performance is one reason to use =. There are two components to this. First is indexing, where = comparisons provide for more powerful indexing capabilities. The second is partitioning. Although unlikely on a column that takes just a handful of values, = is better for resolving partitions.

Another reason is semantic. The presence of NULLs can be confusing. Consider the two comparisons:

where col = 'x'
where col <> 'x'

Both of these where clauses filter out values where col is NULL. This makes total sense with the =. However, even after you know the rules, it is a bit confusing with the <>. Intuitively, we think "NULL is not equal to "x", so it should be true". In fact, NULL means an unknown value, and an unknown value could be equal to 'x', so the statement could be true; in fact, it returns NULL which is filtered out.

Not equal != operator on NULL

<> is Standard SQL-92; != is its equivalent. Both evaluate for values, which NULL is not -- NULL is a placeholder to say there is the absence of a value.

Which is why you can only use IS NULL/IS NOT NULL as predicates for such situations.

This behavior is not specific to SQL Server. All standards-compliant SQL dialects work the same way.

Note: To compare if your value is not null, you use IS NOT NULL, while to compare with not null value, you use <> 'YOUR_VALUE'. I can't say if my value equals or not equals to NULL, but I can say if my value is NULL or NOT NULL. I can compare if my value is something other than NULL.

What is difference between != and in sql server

There is no difference. You can use both in MSSQL.

The MSSQL doc says:

!= functions the same as the <> (Not Equal To) comparison operator.

But <> is defined in the ANSI 99 SQL standard and != is not. So not all DB engines may support it and if you want to generate portable code I recommend using <>.

SQL Performance With Not Equals before Equals and vice versa

Query optimization has very little to do with the syntax of your query and a lot to do with the RDMS query optimizer.

All of the things you suggest will probably make no difference whatsoever as the optimizer will pull them apart and build what it feels is the best query. Specifically,

1. Doesn't matter
2. Doesn't matter
3. No performance impact but note that COUNT(id)<>COUNT(*) if there are NULLs in the id column - for a primary key there won't be any NULLs.
4. I can't see how you could build this query with an IN but in any event it will not impact performance
5. Indexes impact speed dramatically - for this query, indexes on recipientId, recipientView and sourceUserId will have dramatic impacts

What you should do is not take my word for it. Set up each of the queries and look at the execution plan from the RDMS. If they are the same there, then they are the same query.

Difference between and != operators in MSSQL Server

!= is not ANSI compliant.

That's all.

Use <>

UPD. Oh, here

Difference between and != in SQL

None whatsoever, syntactically.

Both are inequality operators, <> is the SQL-92 standard, however its interchangable on some platforms with != (E.g. SQL Server)

SQL uses of less than or equal to = vs. not greater than ! operators

<= and > are comparison operators, not logical operators. ! is a logical operator (means NOT). When you combine ! and >, you're simply inverting a comparison operator, so your end result is the same.

Having said that, <= is the common form, so I'd say it's preferred, for readability if nothing else. I don't know if there's a performance benefit to either, but I doubt it.

Edit:
Also, you didn't say which flavor of SQL you're dealing with. As @harryovers pointed out, that's a valid operator in MS-SQL, but it might not work everywhere.

Using the correct, or preferable, not equal operator in MySQL

<> should be preferred, all things being equal, since it accords with the sql standard and is technically more portable...

!= is non-standard, but most db's implement it.

sql:2008 grammar:

<not equals operator> ::=
  <>

---

Related Topics