When writing SQL statements, if the Where condition is to determine that the user is not within a set, we are accustomed to using the where column name not in (SET) clause, however, when there is a null value in the collection, The returned result is always empty. As shown below:
Oracle or MySQL
Select 1 from where 1 not inch (2null)
Returns 0 results.
SQL Server
Select 1 where 1 not inch (2null)
Returns 0 results.
Obviously the result in the above example is not what we want to get. For database management Systems
Select 1 from where 1 not inch (2null)
will be converted into
Select 1 from where 1 2 and null
We look at the SQL statements after the conversion, 1 ! = null The result is not deterministic, so the result of the WHERE clause is not true, so 0 records are returned. When writing SQL code, sometimes where column name not In (collection)The collection in the clause may be a subquery, and if the result set of the subquery feedback contains a record with a value of NULL, no result is returned, which is an error that SQL beginners can easily commit and find difficult to discover. So try to replace the NOT in clause with a different form of SQL statement, or in a subqueryNULLFor example, Oracle can use theNVL,NVL2OrCoalesefunction processingNULL;SQL Server can useISNULLfunction MySQL can useIfnullOrCoalesefunction). There are also problems with the not-in clause that are prone to appearing anyAnd AllOperations, such as where column name = = All (collection)As in the above clause, this is not a repeat.
The following is a concrete experiment for a further demonstration of the SQL Server database management system.
Suppose the company ERP system has two tables, employee table employees and Department table departments (see below), the boss to find out 2015 years no recruit department number and department name. Please write out the SQL statement for the query.
Employee Table EMPLOYEES
employee_id |
Employee_Name |
Hire_date |
department_id |
170101 |
Bob |
2016-02-02 |
001 |
170102 |
Alice |
2015-02-05 |
003 |
170103 |
Tony |
2016-03-04 |
002 |
170105 |
Aaron |
2015-08-03 |
002 |
170107 |
Rex |
2015-10-11 |
Null |
Department Table Departments
department_id |
Department_name |
manager_id |
001 |
Administration |
170101 |
002 |
IT |
170103 |
003 |
Finance |
170102 |
Create a table SQL statement.
CREATE TABLEdepartments (department_idCHAR(3)PRIMARY KEY, Department_nameVARCHAR( -), manager_idCHAR(6));CREATE TABLEEMPLOYEES (employee_idCHAR(6)PRIMARY KEY, Employee_NameVARCHAR( -) not NULL, Hire_date DATE, department_idCHAR(3), FOREIGN KEY(department_id)REFERENCESdepartments (department_id));Insert intoDepartmentsValues('001','Administration','170101');Insert intoDepartmentsValues('002','IT','170103');Insert intoDepartmentsValues('003','Finance','170102');Insert intoEMPLOYEESValues('170101','Bob','2016-03-02','001');Insert intoEMPLOYEESValues('170102','Alice','2015-02-05','003');Insert intoEMPLOYEESValues('170103','Tony','2016-03-04','002');Insert intoEMPLOYEESValues('170105','Aaron','2016-08-03','002');Insert intoEMPLOYEESValues('170107','Rex','2016-10-11',NULL);
Unwind Code
The above topic, we are very easy to produce this idea, first in the employee table EMPLOYEES to find out the department number of employees hired in 2015, as a department of the chant Collection, and then in the Department table departments to find out in the sub-set department number and department ID, that is, the results to find. Therefore, the following SQL statement is queried:
Select from where inch (Selectfromwhere'%2015%')
However, because there is a null value in the subquery, the SQL Server database management system will return 0 results after executing the above statement. In fact, we can see from the above table, 2015 years no recruit department number and department name has {001, administration} Department, so the above query statement there is a bug.
To ensure that we expect the results to be queried, the SQL Server ISNULL function is used to process null values in the subquery, after which the SQL statement is:
Select from where != All (SelectISNULL(department_id,"fromwhere to Like ' %2015% ' )
The SQL Server DBMS returns the results we expect:
department_id Department_name001 Administration
"Correct open mode" in the Not-in clause in an SQL statement