Filter NULL value records in SQL cannot be used = NULL or != NULL, 1. IS NULL or IS NOT NULL must be used; 2. For example, users who find email column NULL should write SELECT * FROM users WHERE email IS NULL; 3. Multi-field simultaneous judgment can combine multiple IS NULL conditions, such as OR or AND connections; 4. COALESCE can replace NULL values ??for display or default processing, but are not applicable to filtering. Because NULL represents an unknown value and does not participate in the comparison operation of equal or non-equal, = NULL will not return the result and will not report an error. The WHERE clause only accepts TRUE lines, ignores FALSE and UNKNOWN, so the NULL value can only be correctly judged by IS NULL/IS NOT NULL.

In SQL query, if you need to filter out records with a column value of NULL , you cannot use = NULL or != NULL directly, because NULL represents "unknown" or "non-existent value" in SQL and does not participate in regular comparison operations. The correct way to do this is to use IS NULL or IS NOT NULL .

Use IS NULL to filter empty values

To find out a record whose column is NULL , the syntax is very simple:

 SELECT * FROM table_name WHERE column_name IS NULL;

For example, you have a user table users , where email column may be empty, you can query all users who have not filled in the email address like this:

 SELECT * FROM users WHERE email IS NULL;

This method is the most common and recommended method. Remember, only IS NULL and IS NOT NULL can correctly judge the NULL value.

Why can't you use = NULL ?

Many beginners mistakenly think that they can write like this:

 SELECT * FROM users WHERE email = NULL;

But in fact, this statement will not return any results and will not report an error, which can easily make people misunderstand whether the logic is correct. Because in SQL, NULL represents an unknown value, it is neither equal to any value nor equal to any value. That is to say:

• NULL = NULL returns UNKNOWN
• NULL != 'something' also returns UNKNOWN

The WHERE clause will only select lines with the expression TRUE , ignoring FALSE and UNKNOWN . So using = NULL is invalid.

How to deal with situations where multiple fields can be NULL?

If you need to check whether multiple fields are NULL at the same time in one query, you can use multiple IS NULL conditions in combination:

 SELECT * FROM users
WHERE first_name IS NULL OR last_name IS NULL;

Or find cases where both fields are empty:

 SELECT * FROM users
WHERE first_name IS NULL AND last_name IS NULL;

This writing method is clear and suitable for data cleaning or troubleshooting missing information.

Tips: Use COALESCE to make default judgments (not direct filtering)

Although COALESCE is not used to filter NULL , it is a common function for processing NULL values. It can replace NULL with a default value to facilitate other judgments or displays:

 SELECT name, COALESCE(email, 'No email provided') AS email_info
FROM users;

However, this method is more suitable for use in SELECT part, rather than using it instead of IS NULL to filter data.

Basically that's it. Remember one thing: only IS NULL can be used to filter NULL , there is no other way.

The above is the detailed content of How to filter for NULL values in a SQL WHERE clause?. For more information, please follow other related articles on the PHP Chinese website!