คนค้นคนตอน พฤติกรรมของ Left Join

RDBMS: MSSQL2008 R2

เวลาเราทำการ Left/Right Join ร่วมกับ Where Condition เราอาจจะเจอปัญหาที่ทำให้เรางงกับวิธีการประมวลผลของคิวรี
ลองดูตัวอย่างข้างล่าง

1. สร้างตารางทดสอบชื่อ DEPT กับ EMP ด้วยสคริปต์ข้างล่าง EMP.DEPT_ID เป็น FK ที่ชี้ไปหา DEPT.DEPT_ID

create table dept (dept_id int,dept_name varchar(20))
create table emp (emp_id int,emp_name varchar(20), dept_id int)

insert into dept values (10,'IT')
insert into dept values (20,'FINANCE')
insert into dept values (30,'HR')

insert into emp values (1,'SOMCHAI',10)
insert into emp values (2,'TANAKORN',20)
insert into emp values (3,'PETER',30)
insert into emp values (4,'PHAIBOON',null)
insert into emp values (5,'SUKWAN',null)

2. ถ้าเรา Left Join สองตารางนี้ปกติ
select * 
from emp e
left join dept d 
on d.dept_id = e.dept_id 

3. และถ้าเราเพิ่ม Condition ลงในส่วน On ของ Join
select * 
from emp e
left join dept d 
on d.dept_id = e.dept_id and e.emp_name = 'PETER'

4. ลองเปลี่ยน Condition จากบน On มาเป็น Where
select * 
from emp e
left join dept d 
on d.dept_id = e.dept_id
where e.emp_name = 'PETER'

ผลที่ได้จากการรันคิวรีหลายตัวข้างบนแสดงให้เห็นว่ากรณีการทำ Outer Join การใช้เงื่อนไขบนส่วนที่เป็น On กับ Where Clause นั้นให้ผลต่างกันซึ่งอธิบายได้ดังนี้

1. Left Join กับ Condition ใน ON
select * 
from emp e
left join dept d 
on d.dept_id = e.dept_id and e.emp_name = 'PETER'

ความหมายของคิวรีข้างบนคือ
1. ให้ Left Join โดยเอา EMP เป็น Base (ตัวตั้ง) เพราะฉะนั้นจะ Return ทุก ๆ Rows ของ EMP
2. เอา DEPT มา Left Join โดยใช้ DEPT_ID และเอาเฉพาะ Rows ที่มีค่า EMP_NAME เป็น 'PETER' มา Join เท่านั้น ตัวที่ไม่ตรงตามเงื่อนไขนี้ให้ถือว่า Join ไม่เจอกับตาราง EMP
3. การที่ EMP Left Join กับ DEPT ถ้าแถวใด ๆ ของ DEPT ไม่สามารถ Map กับเงื่อนไขใน On ได้ ก็จะถือว่า Map ไม่เจอดังนั้นคอลัมน์ใด ๆ ที่เป็นของ DEPT ในผลการ Join จึงเป็น NULL


2. Left Join กับ Condition ใน WHERE
select * 
from emp e
left join dept d 
on d.dept_id = e.dept_id
where e.emp_name = 'PETER'

ความหมายของคิวรีข้างบนคือ
1. ให้ Left Join โดยเอา EMP เป็น Base (ตัวตั้ง) เพราะฉะนั้นจะ Return ทุก ๆ Rows ของ EMP
2. เอา DEPT มา Left Join โดยใช้ DEPT_ID แถวที่ไม่สามารถ Map กันระหว่าง EMP กับ DEPT ได้ คอลัมน์ของ DEPT จะเป็น NULL (เป็นลักษณะของ Outer Join โดยปกติ)
3. เอาผลที่ได้จากข้อ 2 มาตัดเอาเฉพาะ Row ที่มีค่า EMP_NAME เป็น PETER