SQL not displaying null values on a not equals query?

by

In several languages NULL is handled differently: Most people know about two-valued logic where true and false are the only comparable values in boolean expressions (even is false is defined as 0 and true as anything else).

In Standard SQL you have to think about three-valued logic. NULL is not treated as a real value, you could rather call it “unknown”. So if the value is unknown it is not clear if in your case state is 0, 1, or anything else. So NULL != 1 results to NULL again.

This concludes that whereever you filter something that may be NULL, you have to treat NULL values by yourself. Note that the syntax is different as well: NULL values can only be compare with x IS NULL instead of x = NULL. See Wikipedia for a truth table showing the results of logic operations.

More Related Contents:

  1. Why does Oracle 9i treat an empty string as NULL?
  2. Determine Oracle null == null
  3. How to create id with AUTO_INCREMENT on Oracle?
  4. LISTAGG in Oracle to return distinct values
  5. Fetch the row which has the Max value for a column
  6. Oracle SQL: Update a table with data from another table
  7. Oracle SQL – How to Retrieve highest 5 values of a column [duplicate]
  8. Fetch the rows which have the Max value for a column for each distinct value of another column
  9. Concatenate and group multiple rows in Oracle [duplicate]
  10. Difference between BYTE and CHAR in column datatypes
  11. SQL to generate a list of numbers from 1 to 100
  12. How to use BOOLEAN type in SELECT statement
  13. How to show all privileges from a user in oracle?
  14. SQL – How to select a row having a column with max value [duplicate]
  15. Oracle: loading a large xml file?
  16. IF EXISTS condition not working with PLSQL
  17. Using pivot on multiple columns of an Oracle row
  18. Sleep function in ORACLE
  19. Solution to “cannot perform a DML operation inside a query”?
  20. Why can’t I seem to force Oracle 11g to consume more CPUs for a single SQL query
  21. How to query a CLOB column in Oracle
  22. Oracle (Old?) Joins – A tool/script for conversion?
  23. update columns values with column of another table based on condition [duplicate]
  24. how to select even records from a table in oracle?
  25. Custom Order in Oracle SQL
  26. Compare Strings ignoring accents in SQL (ORACLE)
  27. How to check if a column exists before adding it to an existing table in PL/SQL?
  28. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  29. Why doesn’t Oracle SQL allow us to use column aliases in GROUP BY clauses?
  30. Oracle ‘Partition By’ and ‘Row_Number’ keyword

Leave a Comment