<> 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.
sql - Not equal <> != operator on NULL - Stack Overflow
query - Not equal to operator is not returning NULL values in SQL Server - Database Administrators Stack Exchange
SQL how to express "not equal to and not less than" - Stack Overflow
sql - Oracle Not Equals Operator - Stack Overflow
Videos
<> 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.
NULL has no value, and so cannot be compared using the scalar value operators.
In other words, no value can ever be equal to (or not equal to) NULL because NULL has no value.
Hence, SQL has special IS NULL and IS NOT NULL predicates for dealing with NULL.
Consider checking documentation:
NULL indicates that the value is unknown. A null value is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a null value and any other value, return unknown because the value of each NULL is unknown.
If you consider that the value of istrue is unknown in the NULL case then it might or might not equal 1.
The expression istrue != 1 then evaluates to unknown
SQL only returns rows where the WHERE clause evaluates to true.
If you are on SQL Server 2022+ you can use
WHERE istrue IS DISTINCT FROM 1
To give the inequality semantics that you want (Fiddle).
You can simply use:
WHERE amount > 0
With propositional logic, your condition is the same as a != 0:
a != 0 or a < 0
(a < 0 or a > 0) or a < 0
(a < 0) or (a > 0) or (a < 0)
two repeated conditions with or, you can just remove one
(a < 0) or (a > 0)
equivalent to a != 0
aka a <> 0
They are the same (as is the third form, ^=).
Note, though, that they are still considered different from the point of view of the parser, that is a stored outline defined for a != won't match <> or ^=.
This is unlike PostgreSQL where the parser treats != and <> yet on parsing stage, so you cannot overload != and <> to be different operators.
There is no functional or performance difference between the two. Use whichever syntax appeals to you.
It's just like the use of AS and IS when declaring a function or procedure. They are completely interchangeable.
Most databases support != (popular programming languages) and <> (ANSI).
Databases that support both != and <>:
- Apache Derby 10.16:
!=and<> - IBM Informix Dynamic Server 14.10:
!=and<> - InterBase/Firebird:
!=and<> - Microsoft SQL Server 2000/2005/2008/2012/2016:
!=and<> - Mimer SQL 11.0:
!=and<> - MySQL 8.0.33:
!=and<> - Oracle 23c:
!=and<> - PostgreSQL 15:
!=and<> - SQLite:
!=and<> - Sybase Adaptive Server Enterprise 16.0:
!=and<>
Databases that support the ANSI standard operator, exclusively:
- IBM DB2 UDB 9.5:
<> - Microsoft Access 2010:
<>
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