Sql inequality null

Short answer: NULL. Every time. A comparison to NULL is never true or false. Since NULL can never be equal to any value, it can never be unequal, either. Sometimes people have difficulty understanding why a comparison to NULL can never be either true or false. The result of a comparison involving NULL is not a boolean value —it is a non-value. Community Bot 1 1 1 silver badge. Graeme Job Graeme Job 7 7 silver badges 4 4 bronze badges. The shortest Inequality test I've seen is WileCau's.

This will have problem with indexes — Royi Namir. We want something that returns false if one or both values are null. Quassnoi Quassnoi k 88 88 gold badges silver badges bronze badges. Some of us are few years behind : — butterchicken. Field1 OR Field1 OR -- Target is null but source is not Target. Field1 -- Source xor target is null OR Target. Field1, Source. Field1, Target. WileCau WileCau 1, 21 21 silver badges 33 33 bronze badges. Yours is 3 characters shorter for Equality and 4 characters shorter for Inequality, but I think mine is slightly easier to understand although that's definitely relative in this case.

This is pretty unreadable so it's better to compare ISNULLed with impossible values if you can, but sometimes that's not a solution i.

Sure would be nice if there was a built-in solution, but I think there's some ideological motivation to keep that kind of thing out of T-SQL to prevent people from using NULLs in the "wrong" way. Wonderful answer, but to me it demonstrates how 3-value logic is a completely inexcusable abomination for real-world usage, at least without a bunch more operators than exist in any SQL dialect.

Eric Eric What if the value of the field is 'NULL'? Use an alternative value which cannot occur, such as 'x' for a number field, or a too long string for a string field. This could cause problems for Jennifer Null: gizmodo. Show 1 more comment. Shannon Severance Shannon Severance Jamie G Jamie G 81 1 1 silver badge 2 2 bronze badges. As verbose as this is, it's the best answer.

Remus Rusanu Remus Rusanu k 39 39 gold badges silver badges bronze badges. Or a unique constraint, since he's indicated that there's already a primary key — bdukes.

Except sometimes. But mostly not. Doesn't the following work for Informix? IDS does not recognize boolean expressions such as 'a! Yes, it pains me greatly to have to state this. Jonathan Leffler Jonathan Leffler k gold badges silver badges bronze badges. Sign up or log in Sign up using Google.

Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Podcast Making Agile work for data science. Stack Gives Back Featured on Meta. New post summary designs on greatest hits now, everywhere else eventually. Linked Related Hot Network Questions. Question feed. Stack Overflow works best with JavaScript enabled.