The holes in the SQL statement about NULL

Problem description

Today in the follow-up of the company's internal testing platform online problems, found a long-neglected problem.

To simplify the description of the problem, it is abstracted.

There is a data table Qms_branch, which contains a batch of data as shown below:

ID	name	Types
1	Dashboard_trunk	Dashboard
2	Monkey_trunk	Monkey
3	Dashboard_projects_10_9_9	Dashboard
4	Performance_trunk
5	Performance_projects_10_9_8	Performance

In one of the pages of the system, you need to show all branches other than the dashboard type, and then query (Rails) in the following way.

Branches = Qms::Branch. where("types!= ' dashboard '")

Is there a problem with this approach?

I didn't think it was a problem before. However, after the code is online, the actual use of the partial branch is not loaded, which includes the Performance_trunk branch.

Then there is the problem location, and the MySQL console uses SQL statements to query:

SELECT * from qms_branch WHERE Types! = ' dashboard '

The discovery does not include the Performance_trunk branch in the query results.

What is the reason for this? Why in the 4th data, the value of the types attribute is not dashboard, but the use of types!= ' dashboard ' will not be able to query the results?

Reason Traceability

Look at the structure of the data table Qms_branch and see the properties of the types field: DEFAULT NULL.

After inquiring the information, found the answer on the w3schools.

• NULL is used as a placeholder for unknown or inapplicable values, it's treated differently from the other values.

• It is not possible to test for NULL values with comparison operators, such as =, <, or <>. We would have the-is null and was not NULL operators instead.

That is, in SQL, NULL cannot be compared with the value in! =, and we can only take the is null or is not NULL for comparison.

So we changed the SQL statement to the following form:

SELECT * from qms_branch WHERE types is NULL or types! = ' Dashboard '

When you query again, the result set contains the Performance_trunk branch.

Problem extension

From the above example, we know that when NULL is judged, it can only take the is null or is not NULL, and cannot take =, <, <>,! = these operators.

Besides, is there any other hole that could exist?

Let's look at an example:

There is a data table Table_foo, which has a field value_field, and we want to filter out all records Value_field as ' value1 ', ' value2 ' or null from this table.

Then we use the in operator to query with the following SQL statement.

SELECT * from table_foo WHERE value_field in (' value1 ', ' value2 ', NULL)

Is there a problem here? We did not use =, <, <>,! = to compare null.

The answer is the same problem!

Because in SQL, the in statement is converted to multiple = statements. For example, the SQL in the example above will be converted to the following SQL statement when executed:

SELECT * from table_foo WHERE value_field = ' value1 ' OR Value_field = c27> ' value2 ' OR value_field = NULL

At this point, the problem occurs when you execute Value_field = null.

The correct approach should be to separate null-related judgments, and the following SQL is the correct wording.

SELECT * from table_foo WHERE value_field in (' value1 ', ' value2 ') OR Value_field is NULL

The holes in the SQL statement about NULL