(NOT) IN เมื่อใช้กับค่า NULL ทำให้คิวรีแสดงผลไม่ครบ

(ข้อเขียนบางส่วนแปลจากบทความของ Scott Stephens)

โดยปกติดูเหมือนคำว่า 'IN' และ 'EXISTS' ดูเหมือนว่าจะคล้ายกัน แต่อย่างไรก็ตามทั้งคู่มีความแตกต่างกันในการจัดการกับค่า NULL และอาจให้ผลที่ต่างกัน ซึ่งปัญหามาจากความจริงที่ว่าในระบบฐานข้อมูล Oracle คำว่า NULL หมายถึง "ไม่ทราบค่า" ดังนั้นหากมีการเปรียบเทียบค่า หรือมีการกระทำกับค่า NULL ใด ๆ ผลที่ได้ก็ควรจะเป็น NULL คือไม่ทราบค่า ดังเช่นตัวอย่าง

SQL> select 'true' from dual where 1 = null;
SQL> select 'true' from dual where 1 != null;

ทั้งสองคำสั่งจะแสดงผลเป็น 0 row ทั้งนี้เนื่องจากจะบอกว่า 1 เท่ากับ NULL ก็ไม่ใช่ (เป็นเท็จ) ในขณะเดียวกันถ้าจะบอกว่า 1 ไม่เท่ากับ NULL ก็ไม่ใช่อีก เพราะ NULL หมายถึงตัวที่ไม่ทราบค่า จะมีก็แต่ IS NULL จึงจะเปรียบเทียบค่าเท่ากับ NULL ได้ เช่น

SQL> select 'true' from dual where 1 is null;
(เป็นเท็จ คือแสดงผลเป็น 0 rows)
SQL> select 'true' from dual where null is null;
(เป็นจริง)

เมื่อคุณใช้ "IN" คุณกำลังบอก SQL ว่าให้เอาค่า ๆ หนึ่งจากข้างหนึ่งมาเปรียบเทียบกับทุก ๆ ค่าหรือกลุ่มของค่าที่อยู่อีกข้างหนึ่ง ซึ่งถ้ามีค่า NULL อยู่ในกลุ่มแม้เพียงค่าเดียว มันก็จะไม่แสดงข้อมูลใด ๆ ออกมาเลย แม้ว่าค่าทั้งสองข้างจะเป็น NULL เช่น

SQL> select 'true' from dual where null in (null);
SQL> select 'true' from dual where (null,null) in ((null,null));
SQL> select 'true' from dual where (1,null) in ((1,null));

คำสั่งทั้งสามจะไม่แสดงแถวของข้อมูลใด ๆ ซึ่ง "IN" มีความหมายเหมือนกับ "=ANY" กล่าวคือ

SQL> select 'true' from dual where null = ANY(null);
SQL> select 'true' from dual where (null,null) = ANY((null,null));
SQL> select 'true' from dual where (1,null) = ANY((1,null));

คำสั่งทั้งสามจะไม่แสดงแถวของข้อมูลใด ๆ เช่นกัน

แต่เมื่อคุณใช้ "EXISTS", SQL จะนับจำนวนแถวและไม่สนใจค่าใน Subquery แม้ว่าจะมีค่า NULL ใน Subquery นั้น

SQL> select 'true' from dual where exists (select null from dual);
SQL> select 'true' from dual where exists (select 0 from dual where null is null);

โดยตรรกะและ "IN" เหมือนกับ "EXISTS" กล่าวคือ "IN" จะใช้ค่าที่ได้จาก Subquery ในการกรองเอาแถวที่อยู่ในคิวรีตัวนอก (Outer Query) ออก ในขณะที่ "EXISTS" จะเปรียบเทียบค่าแล้วกรองเอาแถวที่อยู่ใน Subquery ออก ดังนั้นในกรณีของค่า NULL จำนวนของแถวที่ได้จากคิวรีก็จะยังคงเท่าเดิม (ตัวอย่างข้างล่างคำสั่ง select mgr from emp จะมี 1 แถวที่ค่า mgr เป็น NULL)

SQL> select ename from emp where empno in (select mgr from emp);
SQL> select ename from emp e where exists (select null from emp where mgr = e.empno);

แต่ปัญหาเกิดขึ้นเมื่อมีเปลี่ยนเป็น "NOT IN" และ "NOT EXISTS" ซึ่งจะให้เซ็ทของแถวที่ต่างกัน ("NOT IN" จะให้ผลเป็น 0แถวในขณะที่ "NOT EXISTS" จะให้ผลเป็นจำนวนแถวตามเงื่อนไขแม้ว่าใน Subquery จะมีค่า NULL อยู่)

SQL> select ename from emp where empno not in (select mgr from emp);
SQL> select ename from emp e where not exists (select 0 from emp where mgr = e.empno);

NOT IN เป็นการเปรียบเทียบทีละค่าโดยใช้เครื่องหมายเท่ากับ "=" และจะให้ผลเป็น 0 แถวถ้าผลการเปรียบเทียบเป็น False หรือเป็น NULL(Unknown) เช่น
SQL> select 'true' from dual where 1 not in (null,2);
แสดงผลเป็น 0 แถว (Unknown) เนื่องจากเงื่อนไข 1 != null เป็นเงื่อนไขที่ไม่ทราบผล
SQL> select 'true' from dual where 1 != null and 1 != 2;
แสดงผลเป็น 0 แถว (Unknown) เนื่องจากเงื่อนไข 1 != null เป็นเงื่อนไขที่ไม่ทราบผล
SQL> select 'true' from dual where (1,2) not in ((2,3),(2,null));
แสดงผลเป็น 'true' เนื่องจากเงื่อนไข (1,2) != (2,3) และ (1,2) != (2,null) เป็นจริง สาเหตุที่ (1,2) != (2,null) เป็นจริง (แม้ว่าจะมีค่า NULL ในค่าในวงเล็บตัวหลัง) เนื่องจาก
ในกรณี "NOT IN" ถ้าทั้งสองตัวนั้นต่างก็เป็นสมาชิกของ Compound Key (เป็น Key ที่มีสมาชืกมากกว่า 1 เช่น (1,null), (2,null) เป็นต้น) กล่าวคือ ถ้า Compound Key 2 ชุดมีค่า Null อยู่ทั้งคู่ และค่าที่ไม่เป็น Null มีค่าต่างกัน จะถือว่าทั้งคู่มีค่าต่างกันได้เช่น (1,null) != (2,null) อย่างไรก็ตาม (1,null) = (1,null) จะให้ผลเป็น Unknown เนื่องจากเราไม่รู้ว่า NULL คือค่าใดแน่
SQL> select 'true' from dual where (1,null) not in ((1,2),(2,3));
แสดงผลเป็น 0 แถว (Unknown) เนื่องจากเงื่อนไข (1,null) != (1,2) เป็นเงื่อนไขที่ไม่ทราบผล (แม้ว่าเราจะรู้ว่าเงื่อนไข (1,null) != (2,3) เป็นจริง) แต่ Unknown AND True ก็ให้ผลเป็น Unknown อยู่ดี)
SQL> select 'true' from dual where (1,null) not in ((2,3),(2,null));
แสดงผลเป็น 'true' เนื่องจากเงื่อนไข (1,null) != (2,3) และ (1,null) != (2,null) เป็นจริง
SQL> select 'true' from dual where (1,null) not in ((1,2),(2,null));
แสดงผลเป็น 0 แถว (Unknown) เนื่องจากเงื่อนไข (1,null) != (1,2) เป็นเงื่อนไขที่ไม่ทราบผล
SQL> select 'true' from dual where 1 not in (2,3);
แสดงผลเป็น 'true'
SQL> select 'true' from dual where 1 != 2 and 1 != 3;
แสดงผลเป็น 'true'

อย่างไรก็ตามเรายังคงสามารถใช้ "NOT IN" หากเราจะตัดค่า NULL ออกจาก Subquery ในตัวอย่างข้างล่าง empno ถูกกำหนดให้ไม่เป็น NULL

SQL> select ename from emp where empno not in (select mgr from emp where mgr is not null);
SQL> select ename from emp where empno not in (select nvl(mgr,0) from emp);

หากเราเข้าใจความแตกต่างระหว่าง "IN", "EXISTS", "NOT IN" และ "NOT EXISTS" เราก็จะสามารถหลีกเลี่ยงปัญหาเมื่อปรากฏมี NULL ในข้อมูลหรือใน Subquery ได้