Should I use or for not equal in T-SQL

Question

I have seen SQL that uses both    and  lt  gt  for not equal  What is the preferred syntax and why   I like     because  lt  gt  reminds me of Visual Basic

User · Answer

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  lt  gt

User · Answer

lt  gt   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

User · Answer

Most databases support     popular programming languages  and  lt  gt   ANSI    Databases that support both    and  lt  gt     MySQL 5 1     and  lt  gt  PostgreSQL 8 3     and  lt  gt  SQLite     and  lt  gt  Oracle 10g     and  lt  gt  Microsoft SQL Server 2000 2005 2008 2012 2016     and  lt  gt  IBM Informix Dynamic Server 10     and  lt  gt  InterBase Firebird     and  lt  gt  Apache Derby 10 6     and  lt  gt  Sybase Adaptive Server Enterprise 11 0     and  lt  gt    Databases that support the ANSI standard operator  exclusively    IBM DB2 UDB 9 5   lt  gt  Microsoft Access 2010   lt  gt

User · Answer

One alternative would be to use the NULLIF operator other than  lt  gt  or    which returns NULL if the two arguments are equal NULLIF in Microsoft Docs  So I believe WHERE clause can be modified for  lt  gt  and    as follows   NULLIF arg1  arg2  IS NOT NULL   As I found that  using  lt  gt  and    doesn t work for date in some cases  Hence using the above expression does the needful

User · Answer

They re both valid and the same with respect to SQL Server   https   docs microsoft com en-us sql t-sql language-elements not-equal-to-transact-sql-exclamation

User · Answer

Although they function the same way     means exactly  not equal to   while  lt  gt  means greater than and less than the value stored    Consider  gt   or  lt    and this will make sense when factoring in your indexes to queries     lt  gt  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  lt  gt   The database provider may just shortcut it and make them function the same  so there isn t any benefit either way PostgreSQL and SQL nbsp Server do not shortcut this  it is read as it appears above

User · Answer

lt  gt  is the valid SQL according to the SQL-92 standard   http   msdn microsoft com en-us library aa276846 SQL 80  aspx

User · Answer

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 nbsp Server 6 5

User · Answer

despite being non-ANSI  is more in the true spirit of SQL as a readable language  It screams not equal    lt  gt  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  lt  gt  at all and I had to change them to    and check myself before I riggedy wrecked myself

User · Answer

Technically they function the same if you   re using SQL nbsp 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  lt   as it is ANSI compliant   You can find links to the various ANSI standards at     http   en wikipedia org wiki SQL

User · Answer

They are both accepted in T-SQL  However  it seems that using  lt  gt  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  lt  gt  and the query now takes about 4 seconds on average to run  That s a huge improvement

User · Answer

I preferred using    instead of  lt  gt  because sometimes I use the  lt s gt  lt  s gt  syntax to write SQL commands  Using    is more handy to avoid syntax errors in this case

User · Answer

The ANSI SQL Standard defines  lt  gt  as the  not equal to  operator   http   www contrib andrew cmu edu  shadow sql sql1992 txt  5 2  lt token gt  and  lt separator gt    There is no    operator according to the ANSI SQL 92 standard

User · Answer

It seems that Microsoft themselves prefer  lt  gt  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  lt  gt  field2    This says to me that the correct way to do it is  lt  gt

[sql] Should I use != or <> for not equal in T-SQL?

Examples related to sql

Examples related to sql-server

Examples related to tsql