สวัสดีครับผมกิ๊กคนเดิม เพิ่มเติมคือวันนี้จะมาพูดถึง การ JOIN ใน SQL ในลักษณะต่าง ๆ… ก่อนจะเข้าสู่บทความอยากจะขอร้องให้เข้าใจก่อนว่า phpmyadmin ไม่ใช่ ฐานข้อมูล มันเป็นแค่ tools เฉย ๆ แล้วแถมอีกนิด apache กับ appserv ไม่ใช่ PHP นะครับ… ถ้าทุกคนเข้าใจตรงนี้อยู่แล้ว ไปต่อกันเลย ถ้าไม่รู้มาก่อนก็ถือว่ารู้ไว้ใช่ว่านะจ๊ะ

บทความนี้ได้รับแรงบันดาลใจมาจากภาพด้านล่างนี้ครับ คือพอเห็นรูปแล้วรู้สึกถึงพลังบางอย่าง ที่สามารถอธิบายการใช้ JOIN ได้ในภาพเดียว ยิ่งกว่าการพากเพียรเรียนมาในตำราเสียอีก… ถ้าใครยังไม่เข้าใจ อ่านบทความให้จบแล้วเลื่อนกลับมาดูรูปอีกครั้งครับ

https://www.facebook.com/ProgrammersCreateLife/photos/a.241809332534619/1965745276807674/

JOIN IN SQL

การ JOIN table ใน SQL นั้นมันก็คือการที่เราต้องการผลลัพท์ของตารางที่มีการ Query มากกว่า 1 ตารางนั่นเอง ผมจะไม่พูดเยอะนะครับว่ามันคือการทำอะไร เราไปกันต่อเลยดีกว่า

ถ้าเราจะแบ่งประเภทของการ JOIN ใน SQL นั้นตอนที่ผม Research ก็มีมาเจออันที่ไม่เคยใช้เยอะเหมือนกัน ปัจจุบันการ JOIN มีอยู่ด้วยกัน 11 ประเภท

1. EQUI JOIN
2. NON EQUI JOIN
3. INNER JOIN
4. NATURAL JOIN
5. CROSS JOIN
6. OUTER JOIN
7. LEFT JOIN
8. RIGHT JOIN
9. FULL OUTER JOIN
10. JOIN A TABLE TO INSELF
11. SELF JOIN

ในบทความนี้ผมจะยกตัวอย่างการ JOIN มาแค่ 4 อย่างตามที่เราเห็นจากภาพแห่งแรงบันดาลใจของผม อ้อออออออ อีกนิด SQL ไม่ใช่ MySQL หรือ MSSQL หรือ OracleSQL นะครับ

LEFT JOIN

LEFT JOIN เป็นการ JOIN TABLE แบบ เอา TABLE ซ้าย หรือ TABLE แรก เป็นหลัก ถ้าซ้ายมี เอาซ้ายออกมาให้หมด ถ้าขวามี และซ้ายก็มีด้วย ก็จะเอาออกมา ถ้าขวาดันไม่มี แต่ซ้ายมี พวกฟิลด์ต่าง ๆ ที่อยู่ใน TABLE ขวาจะ NULL

LEFT JOIN

SELECT tbl1.col1, tbl2.col2... FROM tbl1 LEFT JOIN tbl2 ON tbl1.field_name = tbl2.field_name;

การจำที่ง่ายที่สุดของ LEFT JOIN คือ เอาซ้ายเป็นหลัก

RIGHT JOIN

RIGHT JOIN เป็นการ JOIN TABLE เหมือน LEFT JOIN ทุกประการแต่… แทนที่จะเอาข้อมูลด้านซ้าย ให้มาเอาด้านขวาแทน… ถ้าขวามี เอาขวาออกมาให้หมด ถ้าขวามี และซ้ายก็มีด้วย ก็จะเอาออกมา ถ้าซ้ายดันไม่มี แต่ขวามี พวกฟิลด์ต่าง ๆ ที่อยู่ใน TABLE ซ้ายจะ NULL

RIGHT JOIN

SELECT tbl1.col1, tbl2.col2... FROM tbl1 RIGHT JOIN tbl2 ON tbl1.field_name = tbl2.field_name;

การจำที่ง่ายที่สุดของ RIGHT JOIN คือ เอาขวาเป็นหลัก

INNER JOIN

INNER JOIN ให้จำว่า Inner ล้วน ๆ อ่าวไม่ใช่… การทำ Inner join นั้นเป็นการเอาข้อมูลของทั้งสองตารางมา join กัน แต่ว่า จะเอาเฉพาะที่เงือนไขของทั้งสองตารางมีเหมือนกันเท่านั้น

INNER JOIN

SELECT tbl1.col1, tbl2.col2... FROM tbl1 INNER JOIN tbl2 ON tbl1.field_name = tbl2.field_name;

การจำที่ง่ายที่สุดของ INNER JOIN คือ เอาที่มีทั้งคู่เท่านั้นมาแสดง

FULL OUTER JOIN

FULL OUTER JOIN เป็นการ JOIN แบบไม่สนใจโลก ไม่สนใจว่าจะ ตารางซ้าย ตารางขวาคือเอาหมด ขอแค่มันตรงเงื่อนไขที่ระบุไว้ก็พอ ซ้ายมีก็แสดง ขวามีก็แสดง ซ้ายมีขวาไม่มีก็แสดง ส่วนด้านขวาที่ไม่มีจะออกเป็น null ขวามีซ้ายไม่มีก็แสดง ส่วนด้านซ้ายที่ไม่มีจะออกเป็น null

FULL OUTER JOIN

SELECT FROM tbl1 *FULL OUTER JOIN tbl2 ON tbl1.field_name = tbl2.field_name;

ใน MySQL ไม่มี FULL OUTER JOIN นะครับ ถ้าจะใช้แนะนำให้ไปหาอ่าน “UNION” การจำที่ง่ายที่สุดของ FULL OUTER JOIN คือ เอาหมดทั้งซ้ายทั้งขวา

Conclusion

การทำ JOIN TABLE ในบทความนี้จะเห็นว่า ไม่มีการพูดถึง Performance ด้วย เนื่องจากต้องการอธิบายเทคนิคการ JOIN ที่ใช้บ่อยในชีวิตประจำวันสำหรับ Beginner มากกว่า ซึ่งการ JOIN นั้นจะมีผลอย่างมาก ในกรณีที่ Data ที่เราต้องการ Query มีปริมาณมาก อย่างใน MySQL เค้าก็จะมีคำสั่ง EXPLAIN เพื่อเอามาดูว่า Query นี้ทำงานอย่างไร ซึ่งเราก็ควรศึกษา และใช้ให้ถูก ให้ควรด้วย ไม่อย่างนั้นตัว Query นี่แหละที่จะเป็นอีกตัวหนึ่งที่ทำให้ระบบ บึ้มมมมมม

ถ้าเราลองสังเกตุดี ๆ ระลึกชาติกันนิดหน่อย จะเห็นว่าพวกข้อมูลต่าง ๆ ที่เรากำลัง JOIN กันอยู่เนี่ย มันเป็นพื้นฐานมาจากวิชาคณิตศาสตร์ในเรื่องของ set ด้วย เห็นมั้ย เรียนไปได้ใช้…

จบกันไปแล้วนะครับสำหรับ Technic การจำการใช้ JOIN ใน SQL ซึ่งสามารถนำไปประยุกต์ใช้กับ MSSql, MySQL, OracleSQL หรือ Database เจ้าอื่น ๆ กันได้

Foreign Key (FK) คือ การสร้างกฏความสัมพันธ์ระหว่างสองตารางเข้าด้วยกัน โดยอาจเป็นการสร้างความสัมพันธ์ระหว่าง คอลัมน์สองคอลัมน์ หรือกลุ่มของคอลัมน์ โดยที่ Foreign Key จะเป็นคอลัมน์ของตารางลูก ที่ใช้เชื่อมโยงกันกับตารางแม่ ที่เป็น Primary Key (PK) หรือเป็น Index

• จากตัวอย่าง ตาราง user คือตารางแม่ โดยมีคอลัมน์ id เป็น Primary Key
• และตาราง orders ซึ่งเป็นตารางลูก มีคอลัมน์ member_id เป็น Foreign Key ที่เชื่อมโยงกับ id ในตาราง user

สองตารางด้านบน ใช้คำสั่งสร้างดังนี้ DROP TABLE IF EXISTS user; CREATE TABLE user ( id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(20) COLLATE utf8_unicode_ci NOT NULL, create_date date DEFAULT NULL ) ENGINE=InnoDB;

DROP TABLE IF EXISTS orders; CREATE TABLE orders ( id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, member_id int(11) NOT NULL, order_no varchar(20) COLLATE utf8_unicode_ci NOT NULL, CONSTRAINT fk_member FOREIGN KEY (member_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE=InnoDB;

INSERT INTO orders (id, member_id, order_no) VALUES (1, 1, '0001'), (2, 1, '0002'), (3, 2, '0003');

INSERT INTO user (id, name, create_date) VALUES (1, 'สมชาย', '2018-10-14'), (2, 'สมหญิง', '2018-10-14');

อธิบายคำสั่งสร้าง Foreign Key กันซะหน่อย ` [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) REFERENCES tbl_name (index_col_name,...) [ON DELETE referenceoption] [ON UPDATE referenceoption]

` ON DELETE หมายถึงเมื่อมีการลบ และ ON UPDATE หมายถึงเมื่อมีการอัปเดทข้อมูล จะไปทำตามค่า reference_option ที่ระบุ ได้แก่

• RESTRICT (ค่าเริ่มต้น) หมายถึง ไม่สามารถเปลี่ยนแปลง หรือ ลบข้อมูลในคอลัมน์ที่เชื่อมโยง ของตารางแม่ได้เลย
• CASCADE หมายถึง เมื่อทำการลบข้อมูลในตารางแม่ จะลบข้อมูลที่เกียวข้องในตารางลูกด้วย
• SET NULL หมายถึง เมื่อมีการลบข้อมูลในตารางแม่ ให้เปลี่ยนข้อมูลในคอลัมน์ที่เชื่อมโยงกันในตารางลูกให้เป็น NULL
• NO ACTION หมายถึง สามารถลบหรือแก้ไขได้อิสระ ไม่มีการตรวจสอบใดๆ
• SET DEFAULT หมายถึง เมื่อมีการลบข้อมูลในตารางแม่ ให้เปลี่ยนข้อมูลในคอลัมน์ที่เชื่อมโยงกันในตารางลูกให้เป็นค่าตามที่กำหนดเป็น DEFAULT

หมายเหตุ

1. ใน MariaDB การกำหนดค่า Foreign Key สามารถทำได้บน engine InnoDB เท่านั้น
2. การกำหนด Foreign Key ควรทำตั้งแต่ตอนสร้างตาราง หรือ ทำเมื่อไม่มีข้อมูลอยู่ในตาราง
3. คอลัมน์ที่เป็น Foreign Key จะต้องเป็น Index หรือ Uniqe ด้วย
   
   

จากตัวอย่าง หากมีการล้างตารางด้วย Truncate จะเกิด Error ตามด้านล่าง

1701 - Cannot truncate a table referenced in a foreign key constraint (`orders`, CONSTRAINT `fk_member` FOREIGN KEY (`member_id`) REFERENCES `user` (`id`))

หรือหากมีการอัปเดทด้วยข้อมูลที่ไม่มีอยู่จริง UPDATE orders SET member_id=3

จะเกิด Error

1452 - Cannot add or update a child row: a foreign key constraint fails (`orders`, CONSTRAINT `fk_member` FOREIGN KEY (`member_id`) REFERENCES `user` (`id`) ON DELETE CASCADE)

ข้อแตกต่างระหว่าง Primary Key และ Foreign Key คือ คอลัมน์ที่เป็น Primary Key สามารถมีได้แค่รายการเดียวเท่านั้น (คอลัมน์ หรือ กลุ่มของคอลัมน์) และ ไม่สามารถมีค่าซ้ำได้ และ ห้ามเป็น NULL ส่วน Foreign Key สามารถมีได้หลายคอลัมน์ (กลุ่มของ FK สำหรับอ้างอิงไปยังตารางอื่นๆ) และสามารถซ้ำได้ (ตามกฏของคอลัมน์ เช่นเป็น INDEX หรือ UNIQE โดยที่ PK 1 รายการ สามารถอ้างอิงไปยัง FK ได้หลายรายการ) และสามารถเป็น NULL ได้ (หมายถึง ไม่มีความสัมพันธ์ใดๆกับตารางแม่เลย)

จุดประสงค์หลักของ Foreign Key คือการสร้างกฏความสัมพันธ์ระหว่างตารางเข้าด้วยกัน เพื่อป้องกันการกระทำกับข้อมูลที่ผิดพลาด

ข้อดีของ Foreign Key ก็ตามจุดประสงค์ของมันแหละครับ ใช้ในป้องกันการกระทำกับข้อมูลที่ผิดพลาด หรือใช้ในการสร้างฟอร์มโดยอัตโนมัติ เช่น การ INSERT ข้อมูลบน phpMyAdmin หรือฟอร์มที่สร้างโดยฐานข้อมูล access ก็จะมีตัวเลือกที่ได้ทำการเชื่อมโยงไว้มาให้ ป้องกันการกรอกค่าที่ไม่มีอยู่จริง

ส่วนข้อเสียของ Foreign Key หลักๆก็คือ การ INSER UPDATE DELETE จะช้าลง เนื่องจากจะต้องมีการตรวจสอบความสัมพันธ์ของข้อมูลให้ถูกต้องก่อนเสมอ (การลบข้อมูลที่ไม่สามารถทำได้ในบางกรณี ไม่ถือเป็นข้อเสีย เพราะมันเป็นไปตามกฏของฐานข้อมูลที่ได้กำหนดไว้)

แล้วเราจำเป็นต้องใช้ Foreign Key หรือไม่ คำตอบก็จะอยู่ที่เงื่อนไขในการใช้งานฐานข้อมูลของเรา

• มีการกระทำ กับฐานข้อมูลตรงๆหรือไม่ เช่น ลบข้อมูลด้วย command line หรือ ลบบนตัวจัดการฐานข้อมูลโดยตรง การมี Foreign Key จะช่วยป้องกันความผิดพลาดในการลบฐานข้อมูลได้
• มีการใช้งานฟอร์มที่สร้างโดยอัตโนมัติหรือไม่ เช่น การ INSERT ข้อมูลบน phpMyAdmin ถ้ามีการทำ Foreign Key ไว้ ก็จะมีตัวเลือกที่สัมพันธ์กันมาให้เลือกโดยอัตโนมัติ ลดโอกาสความผิดพลาดในการกรอกข้อมูล หรือในบางภาษา เช่น VB หรือ Access จะมีการใช้ความสามารถของ Foreign Key ในการเชื่อมโยงตารางในการแสดงผล

ส่วนตัว ผมไม่ค่อยได้ใช้ Foreign Key สักเท่าไร เนื่องจากผมมักจะเขียนคำสั่งเพื่อจัดการฐานข้อมูลด้วยตัวเอง เช่น ในการลบ ผมก็จะเขียนคำสั่งเพื่อตรวจสอบข้อมูลก่อนเสมอ ผมเลยไม่จำเป็นต้องใช้ Foreign Key อีก เนื่องจากจะเป็นการทำงานซ้ำซ้อนกัน