Sunday, June 7, 2009

(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 ได้

5 comments:

theerapat said...

ขอบคุณครับผม
จาก เด็กทดลองงานเขียนโปรแกรม

Unknown said...

ขอถามหน่อยนะครับ สมมุติถ้ามีข้อมูลเป็น string ที่เป็นจำนวนเงิน เช่น
"1,000,209.34" ต้องการเก็บลงใน oracle ที่มี type เป็น Number(20,3)
ให้มีค่าเป็น 1000209.34 (ให้เอาไปคำนวณได้ โดยการตัด comma หลักพันทิ้ง) oracle มี function อะไรในการ convert ให้หรือไม่ครับ และผมต้องการทำเป็น trigger ด้วย สามารถทำได้ไหมครับ (คือจริงๆ ไม่อยากให้ application มันทำหน้าที่ตรงนี้ มันดูยุ่งยากเกินไปครับ)

Tanakorn Tavornsasnavong said...

ขอบคุณครับ ผมไม่แน่ใจว่าใช้ Tool อะไรในการนำข้อมูลเข้านะครับ สมมติว่าได้โหลดข้อมูลเป็นรูปแบบเท็กซ์ ลงใน Oracle แล้วละกันนะครับ 1. เราสามารถใช้วิธีการ Replace เอาคอมมาออกไป โดยใช้ฟังก์ชั่น REPLACE แล้วอาจจะใช้ TO_NUMBER เพื่อแปลงให้เป็นเลขก่อนเข้าสู่ฟิลด์ที่เป็น Number ครับ

SQL> create table test (amount number(20,3));

Table created

SQL> insert into test (amount) select to_number(replace('1,000,209.34',',')) from dual;

1 row inserted

SQL> commit;

Commit complete

SQL> select amount from test;

AMOUNT
----------------------
1000209.340

SQL> insert into test (amount) select replace('2,000,209.34',',') from dual;

1 row inserted

SQL> commit;

Commit complete

SQL> select amount from test;

AMOUNT
----------------------
1000209.340
2000209.340

2. หรือถ้าคุณ Tanangular ใช้ SQL Loader ก็สามารถจะโหลดข้อมูลที่เป็น Text ลงในฟิลด์ Number โดยใช้ฟังก์ชั่น Replace เช่นกัน
ดังตัวอย่างข้างล่างเป็นบรรทัดหนึ่งใน Control File ที่ระบุว่าข้อมูลจากตัวอักษรที่ 1 ถึง 20 จะถูกแปลงเป็นจำนวน โดยคอมมาที่ติดมากับข้อมูลจะถูกลบออก

AMOUNT position(1:20) DECIMAL "REPLACE(:AMOUNT,',')",

3. หรือถ้านำเข้าโดยผ่าน Application ก็อาจจะใช้ฟังก์ชั่น REPLACE ในการ Insert ข้อมูลเข้าครับ เช่น
insert into test values (REPLACE(ตัวแปรจาก Application ของคุณ,','));

Unknown said...

พอดีเพิ่งเข้ามาอ่าน ขอเสริมเพิ่มเติมนะครับ สามารถใช้ function to_number() ก็ได้เช่นกันครับ ตัวอย่าง
select to_number('1,000,209.34','99,999,999,999,999,999,999.999') as res from dual;
ตัวเลข '99,999,999,999,999,999,999.999' คืกการบอก format ให้กับ function นี้รู้ว่าสตริงค์ที่เข้ามาเป็นแบบไหน (ของคุณกำหนดเป็น Number(20,3) ก็ใส่เลข 9 หน้าจุดทศนิยมไป 20 ตัวตามด้วย comma(,) และจำนวนเลขหลังจุดทศนิยม)
ดูเพิ่มเติมจาก oracle doc นะครับ

TooN said...

ต้องขอขอบคุณมากจริงๆ ครับ ผมเชื่อเลย ในส่วนนี้ ถ้าใครไม่รู้ลึกจริงๆ คงไม่เชื่อว่า คำสั่งนี้จะหาค่าไม่ได้

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

แสดงผลเป็น 0 แถว

Post a Comment