What's the Difference Between SQL's `IS NULL` and `= NULL` Operators?

In-depth understanding of the IS NULL and = NULL operators in SQL

The IS NULL and = NULL operators in SQL behave differently when dealing with NULL values, which often confuses beginners. This article explains the key differences between them in detail.

= NULL

Contrary to the literal meaning, = NULL does not return true when the value being checked is NULL. It operates based on three-valued logic, where NULL represents an unknown value. In a WHERE clause, = NULL is interpreted as false, causing the corresponding row to be excluded from the result set.

IS NULL

In contrast, IS NULL explicitly tests for NULL values ​​and returns true if the value being checked is NULL and false otherwise. This behavior is consistent with the actual situation where NULL represents an unknown or missing value.

When to use which operator

Now that you understand these differences, here are the appropriate scenarios for using each operator:

• = NULL: Use when you need to exclude rows with unknown values ​​from the result set. This includes rows where the value is NULL or may be NULL.
• IS NULL: Use this operator to specifically check if a value is NULL. It is particularly useful when you want to retrieve rows that explicitly contain NULL values.

Example

Consider a table containing the following data:

ID	Name	Age
1	John	25
2	Mary	NULL
3	Bob	30

Query 1:

<code class="language-sql">SELECT * FROM Table WHERE Age = NULL;</code>

Result:

returns no rows because the WHERE clause treats the = NULL condition as false, thus eliminating rows with NULL values.

Query 2:

<code class="language-sql">SELECT * FROM Table WHERE Age IS NULL;</code>

Result:

returns row 2 (Mary) because the WHERE clause uses IS NULL to only check for NULL values.

Conclusion

When writing SQL queries, it is crucial to understand the difference between IS NULL and = NULL. Choosing the right operator based on your specific needs ensures accurate and meaningful results.

The above is the detailed content of What's the Difference Between SQL's `IS NULL` and `= NULL` Operators?. For more information, please follow other related articles on the PHP Chinese website!