SQL Interview Prep · บทเรียน

SELF JOIN สำหรับโครงสร้างลำดับชั้น

เชื่อมตารางเข้ากับตัวเองเพื่อจำลองความสัมพันธ์พนักงานกับผู้จัดการและพ่อแม่กับลูก

SELF JOIN สำหรับโครงสร้างลำดับชั้น เป็นบทเรียน SQL Interview Prep ฟรีบน CoddyKit นี่คือบทเรียนที่ 2 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน SQL Interview Prep และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส SQL Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน

SELF JOIN คืออะไรกันแน่

การเชื่อมตารางกับตัวเองคือการเชื่อมตารางที่ตารางเดียวกันปรากฏอยู่ทั้งสองฝั่ง ไม่มีคำสำคัญ SELF JOIN แบบพิเศษ คุณเพียงเขียน INNER หรือ LEFT JOIN ตามปกติ แล้วอ้างอิงตารางเดียวกันสองครั้ง

เคล็ดลับที่ทำให้วิธีนี้ใช้ได้คือชื่อแทนตาราง คุณตั้งชื่อแทนให้สำเนาแต่ละชุดแตกต่างกัน เพื่อให้กลไกประมวลผลมองว่าสำเนาเหล่านั้นเป็นตารางอิสระสองตาราง

SELECT e.name, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;

เหตุใดจึงต้องมีชื่อแทน

หากไม่มีชื่อแทนที่แตกต่างกัน คำสั่งสอบถามจะกำกวม เพราะชื่อคอลัมน์ทุกชื่อปรากฏสองครั้ง และกลไกประมวลผลไม่สามารถทราบได้ว่าคุณหมายถึงสำเนาใด การตั้งชื่อแทนให้แต่ละชุดช่วยแก้ปัญหานี้

ให้อ่านการเชื่อมนี้ว่า 'จับคู่แถวพนักงานแต่ละแถวกับแถวพนักงานที่เป็นผู้จัดการของแถวนั้น' ชื่อแทน e คือผู้ปฏิบัติงาน ส่วน m คือผู้จัดการ และทั้งสองมาจากตารางจริงเดียวกัน

-- e = the employee, m = that employee's manager
SELECT e.id, e.name, m.name AS reports_to
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.id;

โมเดลพนักงาน-ผู้จัดการ

กรณีการเชื่อมตารางกับตัวเองแบบคลาสสิกคือรายการความสัมพันธ์ของโหนดที่อยู่ติดกัน ซึ่งเป็นตารางเดียวที่เก็บแถวต่าง ๆ และแต่ละแถวชี้ไปยังแถวแม่ผ่านคีย์นอกที่อ้างอิงตารางเดียวกัน

ตาราง employees ที่มี manager_id ซึ่งอ้างอิง employees.id สามารถจำลองผังองค์กรทั้งผังไว้ในตารางเดียว ผู้จัดการแต่ละคนก็คือแถวพนักงานอีกแถวหนึ่ง

-- One table holds the whole hierarchy
-- employees(id, name, manager_id)
-- manager_id -> employees.id

แสดงทุกคนพร้อมผู้จัดการ

คำถามเกี่ยวกับการเชื่อมตารางกับตัวเองที่พบบ่อยที่สุดคือ แสดงพนักงานแต่ละคนถัดจากชื่อผู้จัดการของตน ให้เชื่อมสำเนาพนักงานเข้ากับสำเนาผู้จัดการด้วย e.manager_id = m.id

ผลลัพธ์นี้จะมีหนึ่งแถวสำหรับพนักงานแต่ละคนที่มีผู้จัดการอยู่ โปรดสังเกตว่าผู้ที่อยู่บนสุดของผังองค์กรอย่าง CEO มี NULL ใน manager_id จึงถูกตัดออกเมื่อใช้การเชื่อมแบบด้านใน

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;

ใช้ LEFT JOIN เพื่อเก็บโหนดบนสุดของต้นไม้

หากต้องการรวม CEO ซึ่งมีค่า manager_id เป็น NULL ให้เปลี่ยนเป็น LEFT JOIN ฝั่งพนักงานจะยังคงอยู่ ส่วนคอลัมน์ผู้จัดการจะมีค่าเป็น NULL สำหรับแถวที่ไม่มีแถวแม่

ผู้สัมภาษณ์ใช้ข้อนี้ตรวจสอบว่าคุณจำได้หรือไม่ว่าการเชื่อมตารางกับตัวเองแบบด้านในจะตัดโหนดรากออก วิธีแก้ก็เหมือนกับกรณีการเชื่อมภายนอกที่ต้องเก็บแถวที่ไม่ตรงกันทุกประการ

SELECT e.name AS employee,
       COALESCE(m.name, '(top level)') AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

การนับผู้ใต้บังคับบัญชาโดยตรงต่อผู้จัดการ

คำถามต่อยอดที่พบบ่อยคือ ผู้จัดการแต่ละคนมีผู้รายงานตรงกี่คน ให้เชื่อมตารางกับตัวเอง แล้วจัดกลุ่มตามผู้จัดการ

เราเชื่อมพนักงานกับผู้จัดการ จัดกลุ่มตามข้อมูลประจำตัวของผู้จัดการ และนับจำนวนพนักงาน วิธีนี้นับเฉพาะผู้ใต้บังคับบัญชาโดยตรง ไม่รวมทั้งกิ่งย่อยที่อยู่ถัดลงไป

SELECT m.name AS manager, COUNT(*) AS direct_reports
FROM employees e
JOIN employees m ON e.manager_id = m.id
GROUP BY m.id, m.name
ORDER BY direct_reports DESC;

การลงลึกสองระดับ

หากต้องการพนักงาน ผู้จัดการของพนักงาน และผู้จัดการของผู้จัดการ ให้เชื่อมสำเนาตารางสามชุดต่อกัน แต่ละระดับคือการเชื่อมตารางกับตัวเองอีกหนึ่งครั้ง

วิธีนี้ใช้ได้กับความลึกที่กำหนดตายตัวและทราบล่วงหน้า หากต้องการความลึกเท่าใดก็ได้ การเชื่อมตารางกับตัวเองเพียงอย่างเดียวไม่เพียงพอ นั่นเป็นสัญญาณให้ใช้ CTE แบบเรียกซ้ำ ซึ่งผู้สัมภาษณ์คาดหวังว่าคุณจะกล่าวถึง

SELECT e.name AS employee,
       m.name AS manager,
       g.name AS grand_manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
LEFT JOIN employees g ON m.manager_id = g.id;

การเชื่อมตารางกับตัวเองเทียบกับ CTE แบบเรียกซ้ำ

ความแตกต่างสำคัญที่ผู้สัมภาษณ์ใช้ตรวจสอบมีดังนี้:

การเชื่อมตารางกับตัวเองรองรับระดับจำนวนตายตัว สำเนาสามชุดหมายถึงสามระดับ และไม่มากกว่านั้น
CTE แบบเรียกซ้ำรองรับความลึกไม่จำกัดด้วยการเชื่อมตารางเข้ากับตัวเองซ้ำไปจนกว่าจะไม่มีแถวใหม่ปรากฏ

ดังนั้น การ 'แสดงพนักงานแต่ละคนกับผู้จัดการโดยตรงของตน' จึงใช้การเชื่อมตารางกับตัวเอง แต่การ 'แสดงบรรพบุรุษทุกคนที่อยู่ต่อเนื่องขึ้นไปตามสายบังคับบัญชา' ต้องใช้การเรียกซ้ำ

หมวดหมู่แบบแม่-ลูก

รูปแบบเดียวกันนี้ใช้จำลองต้นไม้ได้ทุกประเภท เช่น หมวดหมู่สินค้า ชุดข้อความสนทนา และภูมิภาคทางภูมิศาสตร์ ตาราง categories ที่มี parent_id อ้างอิง id ของตัวเองมีโครงสร้างเหมือนกับกรณีพนักงาน-ผู้จัดการทุกประการ

ข้อสังเกตที่นำไปใช้ซ้ำได้คือ การตระหนักว่า 'ตารางที่มีคีย์นอกอ้างอิงตัวเอง' หมายถึง 'การเชื่อมตารางกับตัวเองหรือการเรียกซ้ำ'

SELECT c.name AS category,
       p.name AS parent_category
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.id;

ข้อผิดพลาดที่พบบ่อยในการเชื่อมตารางกับตัวเอง

ในการสัมภาษณ์งาน ควรระวังเรื่องต่อไปนี้:

ลืมตั้งชื่อแทน ทำให้เกิดข้อผิดพลาดจากคอลัมน์กำกวม
ใช้ INNER JOIN แล้วตัดแถวรากที่มีพาเรนต์เป็น NULL ออกโดยไม่แจ้งเตือน
เชื่อมกลับทิศทางผิด โดยใช้ e.id = m.manager_id แทน e.manager_id = m.id

ก่อนเขียน ON ควรกล่าวให้ชัดเจนเสมอว่าชื่อแทนใดคือแถวลูก และชื่อแทนใดคือแถวแม่

เมื่อใดควรใช้การเชื่อมตารางกับตัวเอง

เลือกใช้การเชื่อมตารางกับตัวเองเมื่อใดก็ตามที่ตารางหนึ่งเชื่อมโยงแถวกับแถวอื่นในตารางเดียวกัน:

โครงสร้างลำดับชั้นที่ต้องค้นหาในระดับตายตัวระดับหนึ่ง เช่น พนักงานกับผู้จัดการ
การจับคู่หรือเปรียบเทียบแถวภายในตารางเดียวกัน ซึ่งจะกล่าวถึงในบทถัดไป

หากความสัมพันธ์เป็นแบบเรียกซ้ำและไม่มีขอบเขต ให้ระบุว่า CTE แบบเรียกซ้ำเป็นเครื่องมือที่เหมาะกว่า ความเข้าใจในรายละเอียดนี้ทำให้เห็นความแตกต่างระหว่างผู้เริ่มต้นกับผู้มีประสบการณ์ระดับกลาง

ตรวจสอบความเข้าใจ

ทดสอบความเข้าใจเกี่ยวกับการเชื่อมตารางกับตัวเองในโครงสร้างลำดับชั้น

สรุป: SELF JOIN สำหรับโครงสร้างลำดับชั้น

ประเด็นสำคัญ:

การเชื่อมตารางกับตัวเองคือการเชื่อมตามปกติที่ใช้ตารางเดียวกันทั้งสองฝั่ง โดยแยกความแตกต่างด้วยชื่อแทน
รายการความสัมพันธ์ของโหนดที่อยู่ติดกัน ซึ่งใช้คีย์นอกอ้างอิงตัวเอง เช่น manager_id สามารถจำลองต้นไม้ไว้ในตารางเดียว
ใช้ INNER JOIN สำหรับคู่ที่ตรงกัน และใช้ LEFT JOIN เพื่อเก็บแถวรากที่มีพาเรนต์เป็น NULL
การเชื่อมตารางกับตัวเองรองรับความลึกตายตัว ส่วนการไล่โครงสร้างที่ไม่จำกัดความลึกต้องใช้ CTE แบบเรียกซ้ำ

คำถามที่พบบ่อย

บทเรียน “SELF JOIN สำหรับโครงสร้างลำดับชั้น” ฟรีหรือไม่

ใช่ — ข้อความเต็มของ “SELF JOIN สำหรับโครงสร้างลำดับชั้น” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส SQL Interview Prep ให้อัปเกรดเป็น CoddyKit PRO คอร์ส SQL Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน

คุณจะเรียนรู้อะไรในบทเรียน “SELF JOIN สำหรับโครงสร้างลำดับชั้น”

เชื่อมตารางเข้ากับตัวเองเพื่อจำลองความสัมพันธ์พนักงานกับผู้จัดการและพ่อแม่กับลูก คุณปฏิบัติ SQL Interview Prep ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน

คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน SQL Interview Prep หรือไม่

ไม่จำเป็นต้องมีประสบการณ์มาก่อน SQL Interview Prep บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 2 จากทั้งหมด 4 บทเรียน

บทเรียน “SELF JOIN สำหรับโครงสร้างลำดับชั้น” ใช้เวลานานแค่ไหน

บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย

ฉันเขียนและรันโค้ดในบทเรียน SQL Interview Prep นี้ได้ไหม

ได้ บทเรียน SQL Interview Prep ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ

บทเรียนทั้งหมดในหลักสูตรนี้

← กลับไปที่ SQL Interview Prep