<> 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.
<> 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.
query - Not equal to operator is not returning NULL values in SQL Server - Database Administrators Stack Exchange
sql if value is null not equal does not work - Oracle Forums
Need some knowledge on NULL and NOT NULL
When does NULL evaluate to FALSE in SQL?
Videos
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).
Where and why exactly a null is used?
What is exactly null and not null? To my understanding Not null we use when its mandatory to insert some value in that field, also when we give check constraint so by default the column will be not null right?
By adding new column through alter method default values are null, so how would I be able to insert values in it and is it right to give not null constraint to that new column while adding through alter method, basically when null and when not null to be used?...
god this is so confusing please help me, ik im asking alot but im really confused