Development essay--not in VS not EXISTS

Original: Development essay--not in VS not EXISTS

Source: http://blog.csdn.net/dba_huangzj/article/details/31374037 Reprint Please quote

Before in the forum to see a discussion on In/exists, the original post is too lazy to find, here to introduce the recent study summary:

Not and not eixts have a certain risk in querying for nullable columns. In particular, not in, if the subquery contains at least one null, unexpected results occur. Let's do a demo.

IF object_id (' Shipmentitems ', ' U ') is not NULL DROP TABLE dbo. Shipmentitems; GO CREATE TABLE dbo. Shipmentitems (shipmentbarcode varchar () NOT NULL, Description varchar (+) NULL, Barcode Varch AR (+) not NULL); GO INSERT into dbo.                 Shipmentitems (Shipmentbarcode, Barcode, Description) SELECT ' 123456 ',                 ' 1010203 ', ' Some cool widget ' UNION all SELECT ' 123654 ',                 ' 1010203 ', ' Some cool widget ' UNION all SELECT ' 123654 ', ' 1010204 ', ' Some cool stuff for Some gadget '; GO-Retrieve all the items from shipment 123654 – that is not shipped in shipment 123456 SELECT Barcode from Dbo.s Hipmentitems WHERE shipmentbarcode = ' 123654 ' and Barcode not in (SELECT Barcode F ROM dbo. Shipmentitems WHERE ShipmentbarcodE = ' 123456 '); /* Barcode------------------------------1010204*/

It can be seen that the expected results are obtained. Here's a look at modifying the table structure to allow column nulls:

ALTER TABLE dbo. Shipmentitems ALTER COLUMN Barcode VARCHAR (+) NULL; INSERT into  dbo. Shipmentitems         (Shipmentbarcode,           Barcode,           Description         )         SELECT  ' 123456 ',                 NULL,                 ' Users Manual for some gadget '; GO SELECT  Barcode from    dbo. Shipmentitems WHERE   shipmentbarcode = ' 123654 ' and Barcode not in         (the SELECT Barcode from   dbo. Shipmentitems                              WHERE  shipmentbarcode = ' 123456 ');/* Barcode------------------------------*/

Many people think this is a bug, sometimes they can find out the data, sometimes they can't. But it's not actually a bug, and when the NOT in clause returns at least one null, the query returns empty, and the following statement is a better way to explain the idea:

SELECT case is  1 not in (2, 3) Then ' True '              ELSE ' Unknown or False '         END, case when 1 not in         (2, 3, NU LL) Then ' true '              ELSE ' Unknown or false '         END;/*--------------------True Unknown or False */

In fact, because the essence of in is an OR operation, so:

SELECT case is  1 in (1, 2, NULL) Then ' True '              ELSE ' Unknown or False '         END;

, 1 in 1, which is true, so returns True, the logic of this statement is actually:

SELECT Case If  (1 = 1)                   or (1 = 2)                   or (1 = NULL) Then ' True '              ELSE ' Unknown OR False '         

When you use not in, such as the following statement:

SELECT case is  1 not in (1, 2, NULL) Then ' True '              ELSE ' Unknown or False '         END;

Will turn into:

SELECT case is not  ((1 = 1)                         or (1 = 2)                         or (1 = NULL)                       ) and then ' True '              ELSE ' Unknown OR False ' EN D

Based on the concept of discrete mathematics, it can be converted to:

SELECT Case  [(1 <> 1) and                     (1 <> 2) and (                     1 <> NULL)) then                   ' True '              ELSE ' U Nknown or False '         END;

Predicates have short-circuit characteristics, that is, in the and condition, as long as there is a condition of false, the entire condition is false, and 1<>1 is false, so the subsequent does not need to judge, directly return to the else part. Even 1<>null, according to the characteristics of set theory, the comparison between null and actual data always returns unknown, so it is also false. If you want to use not in, make sure that the subquery never returns NULL. or additional processing is required to remove NULL, such as:

SELECT  Barcode from    dbo. Shipmentitems WHERE   shipmentbarcode = ' 123654 ' and Barcode not in   (the SELECT Barcode from   dbo. Shipmentitems                        WHERE  shipmentbarcode = ' 123456 ' and Barcode is not                          NULL);

Another way is to rewrite the statement and replace it with the NOT EXISTS equivalent:

SELECT  I.barcode from    dbo. Shipmentitems as I WHERE   i.shipmentbarcode = ' 123654 ' and not         EXISTS (SELECT *                          from   dbo. Shipmentitems as I1                          WHERE  i1. Shipmentbarcode = ' 123456 ' and                                 i1. Barcode = I.barcode); /* Barcode------------------------------1010204*/

In addition, based on SARG requirements, it is generally not recommended to use the not in/not exists this reverse scanning, to avoid affecting performance. There is also a choice to use in/exists, is the problem of multi-column matching, in T-SQL, multi-column matching to use exists, and single-row matching can be used exists/in. You might be able to use a different notation to implement in multi-column matching, but generally I would prefer to use exists to match multiple columns.

Source: CSDN Blog: Huang's Blog

Development essay--not in VS not EXISTS