QAYTA YoKI kutilmagan natijalarni beradi

Quyidagi so'rovlarni MS SQL Serverda ishga tushiraman (versiya 2016).

SELECT DISTINCT Job_Number
FROM table_A
WHERE Job_Number IS NOT NULL

2376 qatorni qaytaradi

SELECT *
FROM table_A
WHERE Job_Number IN (SELECT DISTINCT [House Bill] FROM table_B)

137 qatorni qaytaradi

Biroq,

SELECT *
FROM table_A
WHERE Job_Number NOT IN (SELECT DISTINCT [House Bill] FROM table_B)`

0 qatorni qaytaradi. Men buni 2239 qatorga (ya'ni 2376 - 137) qaytarishini kutardim.

Bu erda men tushunmaganimning asosiy tushunchasi bormi?

0

5 javoblar

NOT IN will fail to return results if there are any NULL values returned by your following query: (SELECT DISTINCT [House Bill] FROM table_B)

Mening takliflarim so'rovingizni o'zgartirish uchun:

(TUG'ILGAN DISTINCT [House Bill] FROM table_B QANDAY [House Bill] IS NOT NULL)

Buning sababi shundaki, table_B table_B ichida table_A bilan NULL qiymati bilan bir qator o'rtasida taqqoslash qilinsa, mantiqiy natija UNKNOWN .

Bu haqda ko'proq ma'lumot bor:

1 - here and

2 - bu erda

1
qo'shib qo'ydi

IN doesn't take NULL into consideration... try this instead, which checks for both...

SELECT *
FROM table_A
WHERE Job_Number IS NULL
  OR Job_Number NOT IN (SELECT DISTINCT [House Bill] FROM table_B)

(Juda kichik izoh, lekin [House Bill]] o'rniga sizning nomingizdagi bo'sh joylar qilmang ni tavsiya qilaman < kodi> House_Bill yoki HouseBill )

0
qo'shib qo'ydi

IN() jumlasi NULL qiymatlarini e'tiborga olmaydi.

0
qo'shib qo'ydi

NOT IN returns no rows at all if the subquery returns NULL -- even once.

Shuning uchun NOT EXISTS dan foydalanishni tavsiya etaman. Bu siz kutganidek harakat qiladi:

SELECT a.*
FROM table_A a
WHERE NOT EXISTS (SELECT 1 NFROM table_B b WHERE a.Job_Number = b.[House Bill])
0
qo'shib qo'ydi

Buning mumkin bo'lgan sababi [House Bill] ustidagi tableb null qiymatlari bo'lishi mumkin:

NOT IN va IN so'zlari o'rniga NOT EXISTS yoki EXISTS ishlatishning to'g'ri usuli bo'lishi kerak:

SELECT a.*
FROM table_A a
WHERE NOT EXISTS (SELECT 1 FROM table_B b WHERE a.Job_Number = b.[House Bill]);
0
qo'shib qo'ydi