Tutorial Items
By: Ben Snaidero
Hi All; In my below query i need to display i need to display clients with output = Start on Scheme post but output should be equal to Pre- Start Assist or Business Assist ( any one or both output should not be there) Only display those records Any help on this much appreciated Thanks select C. Thanks all for your comments i did something as below.
Overview
In almost all cases when we use the <> operator (or any other operator in conjunction with the NOT operator, i.e.. NOT IN) index seeks will not be performed and instead a table/index scan is required.
Explanation
For this example let's make an update to one of our test tables to skew the data a little. We'll also add an index to the table on the column that will be used in our WHERE clause.
Now let's look at a simple query which would return all the records where IntDataColumn <> 60000. Here is what that would look like.
Looking at the explain plan for this query we see something really interesting. Since the optimizer has some statistics on the data in this column it has rewritten the query to use separate < and > clauses. We can see this in the details of the Index Seek under the Seek Predicate heading.
' src='/tutorialimages/3203_ExplainPlan_NotEqual.gif'>
Now let's see what happens if we have two <> clauses as follows.
Looking at the explain plan for this query we also see that the optimizer has done some manipulation to the WHERE clause. It is now using the new value we added in the Seek Predicate and the original value as the other Predicate. Both have been changed to use separate < and > clauses.
and 2 values in WHERE clause' src='/tutorialimages/3203_ExplainPlan_NotEqual_2Values.gif'>
Although the changes that the optimizer has made have certainly helped the query by avoiding an index scan it's always best to use an equality operator, like = or IN, in you query if you want the best performance possible. One thing you should consider before making a change like is you want to make sure you have a good understanding of your data as changes in your table data can then affect your query results. With that said and given that we know our table has very few records that satisfy the WHERE condition let's flip it to an equality operator and see the difference in performance. Here is the new query.
Looking at the explain plan for this query we can see that it's also doing an index seek but looking deeper into the Seek Predicate we can now see it's using the equality operator which should be much faster given the number of records that satisfy the WHERE condition.
Now let's take a look at the SQL Profiler results for these two queries. We can see below that the example using the equality operator runs faster and requires much less resources. Note: Both queries returned the same result set.
Clause | CPU | Reads | Writes | Duration |
---|---|---|---|---|
Inequality | 250 | 110901 | 0 | 255 |
Equality | 15 | 654 | 0 | 15 |
Additional Information
Last Update: 2/17/2014
I have seen
SQL
that uses both !=
and <>
for not equal. What is the preferred syntax and why?I like
!=
, because <>
reminds me of Visual Basic
.Bob The JanitorBob The Janitor
14 Answers
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...
DBAndrewDBAndrew
Most databases support
!=
(popular programming languages) and <>
(ANSI).Databases that support both
!=
and <>
:- MySQL 5.1:
!=
and<>
- PostgreSQL 8.3:
!=
and<>
- SQLite:
!=
and<>
- Oracle 10g:
!=
and<>
- Microsoft SQL Server 2000/2005/2008/2012/2016:
!=
and<>
- IBM Informix Dynamic Server 10:
!=
and<>
- InterBase/Firebird:
!=
and<>
- Apache Derby 10.6:
!=
and<>
- Sybase Adaptive Server Enterprise 11.0:
!=
and<>
Databases that support the ANSI standard operator, exclusively:
- IBM DB2 UDB 9.5:
<>
- Microsoft Access 2010:
<>
'<>'
is from the SQL-92 standard and '!='
is a proprietary T-SQL operator. It's available in other databases as well, but since it isn't standard you have to take it on a case-by-case basis.In most cases, you'll know what database you're connecting to so this isn't really an issue. At worst you might have to do a search and replace in your SQL.
Adam LassekAdam Lassek
The ANSI SQL Standard defines
<>
as the 'not equal to' operator,http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt (
5.2 <token> and <separator>
)There is no
!=
operator according to the ANSI/SQL 92 standard.Mehrdad AfshariMehrdad Afshari
<>
is the valid SQL according to the SQL-92 standard.Justin NiessnerJustin Niessner
They're both valid and the same with respect to SQL Server,
madcolormadcolor
It seems that Microsoft themselves prefer
<>
to !=
as evidenced in their table constraints. I personally prefer using !=
because I clearly read that as 'not equal', but if you enter [field1 != field2]
and save it as a constrait, the next time you query it, it will show up as [field1 <> field2]
. This says to me that the correct way to do it is <>
.KyleKyle
!=
, despite being non-ANSI, is more in the true spirit of SQL as a readable language. It screams not equal. <>
says it's to me (less than, greater than) which is just weird. I know the intention is that it's either less than or greater than hence not equal, but that's a really complicated way of saying something really simple. I've just had to take some long SQL queries and place them lovingly into an XML file for a whole bunch of stupid reasons I won't go into.
Suffice to say XML is not down with
<>
at all and I had to change them to !=
and check myself before I riggedy wrecked myself.Fat AlbertFat Albert
You can use whichever you like in T-SQL. The documentation says they both function the same way. I prefer
!=
, because it reads 'not equal' to my (C/C++/C# based) mind, but database gurus seem to prefer <>
.Steve HaighSteve Haigh
I understand that the C syntax
!=
is in SQL Server due to its Unix heritage (back in the Sybase SQL Server days, pre Microsoft SQL Server 6.5).KarlKarl
One alternative would be to use the NULLIF operator other than
<>
or !=
which returns NULL if the two arguments are equal NULLIF in Microsoft Docs. So I believe WHERE clause can be modified for <>
and !=
as follows:As I found that, using
<>
and !=
doesn't work for date in some cases. Hence using the above expression does the needful.jitendrapurohitjitendrapurohit
I preferred using
!=
instead of <>
because sometimes I use the <s></s>
syntax to write SQL commands. Using !=
is more handy to avoid syntax errors in this case.Andrea AntonangeliAndrea Antonangeli
![Sql not equal to multiple values Sql not equal to multiple values](/uploads/1/2/5/0/125063801/886022097.gif)
They are both accepted in T-SQL. However, it seems that using
<>
works a lot faster than !=
. I just ran a complex query that was using !=
, and it took about 16 seconds on average to run. I changed those to <>
and the query now takes about 4 seconds on average to run. That's a huge improvement!Hamlet JavierHamlet Javier
Although they function the same way,
!=
means exactly 'not equal to', while <>
means greater than and less than the value stored. Consider
>=
or <=
, and this will make sense when factoring in your indexes to queries... <>
will run faster in some cases (with the right index), but in some other cases (index free) they will run just the same. This also depends on how your databases system reads the values
!=
and <>
. The database provider may just shortcut it and make them function the same, so there isn't any benefit either way.PostgreSQL and SQL Server do not shortcut this; it is read as it appears above.Kevin KinchenKevin Kinchen
protected by Community♦Jan 22 '15 at 15:41
Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?
Would you like to answer one of these unanswered questions instead?