Keywords: SQL | NULL values | comparison operators | three-valued logic | database queries
Abstract: This article provides an in-depth exploration of the special properties of NULL values in SQL and their impact on comparison operators. By analyzing standard SQL specifications, it explains why using != and <> operators with NULL returns 0 results, while IS NOT NULL correctly identifies non-null values. The article combines concrete code examples to detail how three-valued logic (TRUE, FALSE, UNKNOWN) works in SQL queries and offers practical guidance for properly handling NULL values.
The Nature of NULL and Three-Valued Logic
In SQL databases, NULL represents missing or unknown values, rather than specific numerical or string values. This special semantics determines that NULL cannot be compared using regular equality or inequality operators. According to the SQL-92 standard, both != and <> are valid inequality operators, but they can only be used to compare concrete values.
Behavior Analysis of Comparison Operators
When using MyColumn != NULL or MyColumn <> NULL, the SQL engine returns UNKNOWN results instead of TRUE or FALSE. In WHERE clauses, only records with TRUE results are selected, and UNKNOWN is treated as FALSE, hence these queries return 0 records.
Consider the following example code:
CREATE TABLE Employee (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Department VARCHAR(50)
);
INSERT INTO Employee VALUES
(1, 'John', 'Engineering'),
(2, 'Jane', NULL),
(3, 'Bob', 'Marketing'),
(4, NULL, 'HR');
-- Incorrect usage: returns 0 records
SELECT * FROM Employee WHERE Department != NULL;
SELECT * FROM Employee WHERE Department <> NULL;
-- Correct usage: returns 3 records (IDs 1,3,4)
SELECT * FROM Employee WHERE Department IS NOT NULL;IS NULL and IS NOT NULL Operators
SQL specifically provides IS NULL and IS NOT NULL operators to handle NULL value checks. These operators directly examine whether a value is NULL, avoiding the three-valued logic issues of comparison operators.
In practical development, when needing to exclude specific values while including NULL values, combination conditions should be used:
-- Find all employees whose department is not 'Engineering' (including those with NULL departments)
SELECT * FROM Employee
WHERE Department != 'Engineering' OR Department IS NULL;Cross-Database Consistency
This NULL handling behavior is a common characteristic of all SQL-standard compliant database systems, including SQL Server, MySQL, PostgreSQL, and SQLite. In SQLite forum discussions, users reported similar issues: using != operators to compare NULL values returned unexpected results, while using IS NOT operators worked correctly.
Best Practice Recommendations
1. Always use IS NULL or IS NOT NULL when checking for NULL values
2. Explicitly handle NULL cases in complex conditions involving NULL values
3. Understand the impact of three-valued logic on query results, especially in join queries and subqueries
4. Perform appropriate null checks on potentially NULL fields in application code
By properly understanding and using NULL-related operators, common query errors can be avoided, ensuring the accuracy and reliability of database queries.