"Correct open mode" in the Not-in clause in an SQL statement

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