A trap that SQL often appears in the case of a not-in subquery with null values

If the following: Table_a table and Table_b table, ask to query for records that are not in the Table_b table in the Table_a table.

 create TABLE [dbo].[ Table_a] ([ID] [nchar] ( 10  ) NULL, [Name] [ NCHAR] ( 10  001   Zhang San  002   John Doe  003   Harry CREATE TABLE [dbo]. [Table_b] ([ID] [nchar] ( 10   10   002   John Doe NULL Harry  

View Code

It is very easy for us to do the first time:

SELECT  *  from     as a WHERE    not inch SELECT     b.id                      from as       b)

However, the query did not reach the expected

ID Name
001 sheets of three
003 Harry

The reason is simple: because null cannot do how to "manipulate"

– If NULL participates in arithmetic operations, the value of the arithmetic expression is null. (Example: +,-,*,/subtraction)

– If NULL participates in the comparison operation, the result can be treated as false. (e.g.:>=,<=,<> greater than, less than, not equal to)

– If NULL participates in the aggregation operation, the aggregation function is set to NULL. Except for COUNT (*).

--if there is a null value in the not in subquery, the data is not returned. (For the simplest explanation, please refer to the comments below)

--correct wordingSELECT  * fromDbo. Table_a asaWHEREa.ID not inch(SELECTb.id fromDbo. Table_b asbWHEREb.ID is  not NULL)--exclude null values to participate in operator comparisons                      --suggested modifications to the associated query method--correct wording 1SELECT  * fromDbo. Table_a asaWHERE    not EXISTS(SELECT *                      fromDbo. Table_b asbWHEREa.ID=b.id)--correct wording 2SELECT  * fromDbo. Table_a asa Left OUTER JOINDbo. Table_b asB ona.ID=b.idWHEREb.ID is NULL

View Code

A trap that SQL often appears in the case of a not-in subquery with null values