SQLite การต่อไม้: ต่อซ้ายธรรมชาติด้านนอก ด้านใน และแบบไขว้กับตาราง

SQLite รองรับประเภทต่างๆ SQL รวม เช่น INNER JOIN, LEFT OUTER JOIN และ CROSS JOIN JOIN แต่ละประเภทใช้สำหรับสถานการณ์ที่แตกต่างกัน ดังที่เราจะเห็นในบทช่วยสอนนี้

รู้เบื้องต้นเกี่ยวกับ SQLite เข้าร่วมข้อ

เมื่อคุณทำงานกับฐานข้อมูลที่มีหลายตาราง คุณมักจะจำเป็นต้องรับข้อมูลจากหลายตารางเหล่านี้

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

คำสั่ง JOIN ใด ๆ จะต้องมีรูปแบบดังต่อไปนี้:

แต่ละคำสั่งเข้าร่วมประกอบด้วย:

• ตารางหรือแบบสอบถามย่อยซึ่งเป็นตารางด้านซ้าย ตารางหรือแบบสอบถามย่อยก่อนส่วนคำสั่งเข้าร่วม (ทางด้านซ้าย)
• ตัวดำเนินการ JOIN – ระบุประเภทการเข้าร่วม (INNER JOIN, LEFT OUTER JOIN หรือ CROSS JOIN)
• เข้าร่วม-ข้อจำกัด – หลังจากที่คุณระบุตารางหรือแบบสอบถามย่อยที่จะเข้าร่วม คุณจะต้องระบุข้อจำกัดการรวม ซึ่งจะเป็นเงื่อนไขในการเลือกแถวที่ตรงกันซึ่งตรงกับเงื่อนไขนั้น โดยขึ้นอยู่กับประเภทการรวม

หมายเหตุว่าสำหรับสิ่งต่อไปนี้ทั้งหมด SQLite ตัวอย่างตาราง JOIN คุณต้องเรียกใช้ sqlite3.exe และเปิดการเชื่อมต่อกับฐานข้อมูลตัวอย่างตามขั้นตอนต่อไปนี้:

ขั้นตอน 1) ในขั้นตอนนี้ ให้เปิด My Computer แล้วไปยังไดเร็กทอรีต่อไปนี้ “C:\sqlite” จากนั้นเปิดไฟล์ “sqlite3.exe”:

ขั้นตอน 2) เปิดฐานข้อมูล “TutorialsSampleDB.db” โดยใช้คำสั่งต่อไปนี้:

ตอนนี้คุณพร้อมที่จะเรียกใช้แบบสอบถามประเภทใดก็ได้บนฐานข้อมูลแล้ว

SQLite INNER JOIN

คำสั่ง INNER JOIN จะส่งคืนเฉพาะแถวที่ตรงกับเงื่อนไขการเชื่อมต่อ และตัดแถวอื่นๆ ที่ไม่ตรงกับเงื่อนไขการเชื่อมต่อออกไป

ตัวอย่าง

ในตัวอย่างต่อไปนี้ เราจะเชื่อมตาราง "นักเรียน" และ "แผนก" โดยใช้ DepartmentId เพื่อให้ได้ชื่อแผนกสำหรับนักเรียนแต่ละคน ดังนี้:

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

คำอธิบายของรหัส

การทำงานของ INNER JOIN มีดังนี้:

• ในส่วนคำสั่ง Select คุณสามารถเลือกคอลัมน์ใดก็ได้ที่คุณต้องการเลือกจากตารางที่อ้างอิงสองตาราง
• ส่วนคำสั่ง INNER JOIN ถูกเขียนหลังจากตารางแรกที่อ้างอิงด้วยส่วนคำสั่ง “จาก”
• จากนั้นเงื่อนไขการรวมจะถูกระบุด้วย ON
• สามารถระบุนามแฝงสำหรับตารางอ้างอิงได้
• คำ INNER เป็นทางเลือก คุณสามารถเขียน JOIN ได้เลย

เอาท์พุต

คำสั่ง INNER JOIN จะดึงข้อมูลจากทั้งสองตาราง คือตารางนักเรียนและตารางแผนก ที่ตรงกับเงื่อนไข “Students.DepartmentId = Departments.DepartmentId” ส่วนแถวที่ไม่ตรงกันจะถูกละเว้นและไม่รวมอยู่ในผลลัพธ์

ด้วยเหตุนี้จึงมีนักเรียนเพียง 8 คนจาก 10 คนที่ถูกค้นหาพบผลลัพธ์จากคำสั่งค้นหานี้ โดยนักเรียนชื่อ "Jena" และ "George" ไม่ได้ถูกรวมอยู่ด้วย เนื่องจากพวกเขามีรหัสแผนกเป็นค่าว่าง ซึ่งไม่ตรงกับคอลัมน์ departmentId ในตารางแผนก ดังนี้:

SQLite เข้าร่วม … การใช้

INNER JOIN สามารถเขียนได้โดยใช้ส่วนคำสั่ง "USING" เพื่อหลีกเลี่ยงความซ้ำซ้อน ดังนั้น แทนที่จะเขียน "ON Students.DepartmentId = Departments.DepartmentId" คุณสามารถเขียนแค่ "USING(DepartmentID)" ได้

คุณสามารถใช้ “JOIN .. USING” เมื่อใดก็ตามที่คอลัมน์ที่คุณจะเปรียบเทียบในเงื่อนไขการรวมมีชื่อเดียวกัน ในกรณีเช่นนี้ ไม่จำเป็นต้องทำซ้ำโดยใช้เงื่อนไข on และเพียงระบุชื่อคอลัมน์และ SQLite จะตรวจพบสิ่งนั้น

ความแตกต่างระหว่าง INNER JOIN และ JOIN .. USING:

การใช้ “JOIN … USING” ไม่จำเป็นต้องเขียนเงื่อนไขการเชื่อมต่อ เพียงแค่เขียนคอลัมน์ที่เหมือนกันระหว่างสองตารางที่เชื่อมต่อกัน แทนที่จะเขียนว่า “INNER JOIN table2 ON table1.cola = table2.cola” เราจะเขียนว่า “table1 JOIN table2 USING(cola)”

ตัวอย่าง

ในตัวอย่างต่อไปนี้ เราจะเชื่อมตาราง "นักเรียน" และ "แผนก" โดยใช้ DepartmentId เพื่อให้ได้ชื่อแผนกสำหรับนักเรียนแต่ละคน ดังนี้:

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
INNER JOIN Departments USING(DepartmentId);

คำอธิบาย

• แตกต่างจากตัวอย่างก่อนหน้านี้ เราไม่ได้เขียนว่า “ON Students.DepartmentId = Departments.DepartmentId” แต่เราเขียนเพียงว่า “USING(DepartmentId)”
• SQLite อนุมานเงื่อนไขการเข้าร่วมโดยอัตโนมัติและเปรียบเทียบ DepartmentId จากทั้งตาราง – นักศึกษาและแผนก
• คุณสามารถใช้ไวยากรณ์นี้เมื่อใดก็ตามที่ทั้งสองคอลัมน์ที่คุณกำลังเปรียบเทียบมีชื่อเดียวกัน

เอาท์พุต

นี่จะให้ผลลัพธ์ที่เหมือนกันทุกประการกับตัวอย่างก่อนหน้านี้:

SQLite เข้าร่วมอย่างเป็นธรรมชาติ

NATURAL JOIN คล้ายกับ JOIN...การใช้ ความแตกต่างคือจะทดสอบความเท่าเทียมกันระหว่างค่าของทุกคอลัมน์ที่มีอยู่ในทั้งสองตารางโดยอัตโนมัติ

ความแตกต่างระหว่าง INNER JOIN และ NATURAL JOIN:

• ในการใช้ INNER JOIN คุณต้องระบุเงื่อนไขการเชื่อมต่อ ซึ่ง INNER JOIN จะใช้ในการเชื่อมตารางทั้งสองเข้าด้วยกัน ในขณะที่ในการใช้ Natural JOIN คุณไม่ต้องเขียนเงื่อนไขการเชื่อมต่อ คุณเพียงแค่เขียนชื่อตารางทั้งสองโดยไม่มีเงื่อนไขใดๆ จากนั้น Natural JOIN จะตรวจสอบความเท่าเทียมกันของค่าในแต่ละคอลัมน์ที่มีอยู่ในทั้งสองตารางโดยอัตโนมัติ Natural JOIN จะกำหนดเงื่อนไขการเชื่อมต่อโดยอัตโนมัติ
• ใน NATURAL JOIN คอลัมน์ทั้งหมดจากทั้งสองตารางที่มีชื่อเดียวกันจะถูกจับคู่กัน ตัวอย่างเช่น หากเรามีตารางสองตารางที่มีชื่อคอลัมน์สองคอลัมน์เหมือนกัน (ทั้งสองคอลัมน์มีชื่อเดียวกันในตารางทั้งสอง) การรวมแบบธรรมชาติจะรวมทั้งสองตารางเข้าด้วยกันโดยการเปรียบเทียบค่าของทั้งสองคอลัมน์ ไม่ใช่แค่จากคอลัมน์เดียว คอลัมน์.

ตัวอย่าง

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
Natural JOIN Departments;

คำอธิบาย

• เราไม่จำเป็นต้องเขียนเงื่อนไขการเชื่อมต่อโดยใช้ชื่อคอลัมน์ (เหมือนที่เราทำใน INNER JOIN) และเราไม่จำเป็นต้องเขียนชื่อคอลัมน์เลยด้วยซ้ำ (เหมือนที่เราทำใน JOIN USING)
• การรวมแบบธรรมชาติจะสแกนทั้งสองคอลัมน์จากทั้งสองตาราง จะตรวจพบว่าเงื่อนไขควรประกอบด้วยการเปรียบเทียบ DepartmentId จากทั้ง 2 ตาราง Student และ Departments

เอาท์พุต

การเชื่อมตารางแบบ Natural Join จะให้ผลลัพธ์ที่เหมือนกับผลลัพธ์ที่ได้จากการเชื่อมตารางแบบ Inner Join และ JOIN USING ในตัวอย่าง เพราะในตัวอย่างของเรา คำสั่ง SQL ทั้งสามแบบนั้นเทียบเท่ากัน แต่ในบางกรณี ผลลัพธ์จากการเชื่อมตารางแบบ Inner Join อาจแตกต่างจาก Natural Join ตัวอย่างเช่น หากมีหลายตารางที่มีชื่อเดียวกัน Natural Join จะจับคู่คอลัมน์ทั้งหมดเข้าด้วยกัน แต่ Inner Join จะจับคู่เฉพาะคอลัมน์ในเงื่อนไขการเชื่อมตารางเท่านั้น

SQLite ซ้าย OUTER เข้าร่วม

มาตรฐาน SQL กำหนด OUTER JOIN ไว้ 3 ประเภท ได้แก่ LEFT, RIGHT และ FULL แต่ SQLite รองรับเฉพาะ LEFT OUTER JOIN ที่เป็นธรรมชาติเท่านั้น

ใน LEFT OUTER JOIN ค่าทั้งหมดของคอลัมน์ที่คุณเลือกจากตารางด้านซ้ายจะถูกรวมอยู่ในผลลัพธ์ของคิวรี ดังนั้นไม่ว่าค่าจะตรงกับเงื่อนไขการเชื่อมต่อหรือไม่ก็ตาม ก็จะถูกรวมอยู่ในผลลัพธ์

ดังนั้น หากตารางด้านซ้ายมี 'n' แถว ผลลัพธ์ของการค้นหาจะมี 'n' แถว อย่างไรก็ตาม สำหรับค่าของคอลัมน์ที่มาจากตารางด้านขวา หากค่าใดไม่ตรงกับเงื่อนไขการเชื่อมต่อ ค่าที่ได้จะเป็น "null"

ดังนั้นคุณจะได้จำนวนแถวเท่ากับจำนวนแถวในการรวมด้านซ้าย เพื่อที่คุณจะได้แถวที่ตรงกันจากทั้งสองตาราง (เช่นผลลัพธ์ INNER JOIN) บวกกับแถวที่ไม่ตรงกันจากตารางด้านซ้าย

ตัวอย่าง

ในตัวอย่างต่อไปนี้ เราจะลองใช้ “LEFT JOIN” เพื่อรวมตารางสองตาราง “นักศึกษา” และ “แผนก”:

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students             -- this is the left table
LEFT JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

คำอธิบาย

• SQLite ไวยากรณ์ LEFT JOIN เหมือนกับ INNER JOIN; คุณเขียน LEFT JOIN ระหว่างทั้งสองตาราง จากนั้นเงื่อนไขการรวมจะอยู่หลังส่วนคำสั่ง ON
• ตารางแรกหลัง from clause คือตารางด้านซ้าย ในขณะที่ตารางที่สองที่ระบุหลังจาก LEFT JOIN แบบธรรมชาติเป็นตารางด้านขวา
• ส่วนคำสั่ง OUTER เป็นทางเลือก LEFT OUTER JOIN ตามธรรมชาติจะเหมือนกับ LEFT JOIN

เอาท์พุต

อย่างที่คุณเห็น ข้อมูลทุกแถวจากตารางนักเรียนถูกรวมไว้ ซึ่งมีนักเรียนทั้งหมด 10 คน แม้ว่านักเรียนคนที่สี่และคนสุดท้าย คือ เจน่าและจอร์จ จะมี departmentId ที่ไม่มีอยู่ในตาราง Departments แต่ก็ยังถูกรวมไว้ด้วยเช่นกัน

ในกรณีเหล่านี้ ค่า departmentName ของทั้งเจน่าและจอร์จจะเป็น "null" เนื่องจากตาราง departments ไม่มี departmentName ที่ตรงกับค่า departmentId ของพวกเขา

เรามาอธิบายการใช้ left join ในแบบสอบถามก่อนหน้านี้ให้ละเอียดขึ้นโดยใช้แผนภาพเวนน์กัน:

การใช้ LEFT JOIN จะดึงชื่อนักเรียนทั้งหมดจากตารางนักเรียน แม้ว่านักเรียนคนนั้นจะมีรหัสแผนกที่ไม่ปรากฏในตารางแผนกก็ตาม ดังนั้น คำสั่ง SQL นี้จะไม่แสดงเฉพาะแถวที่ตรงกันเหมือนกับการใช้ INNER JOIN แต่จะแสดงส่วนเพิ่มเติมที่มีแถวที่ไม่ตรงกันจากตารางด้านซ้าย ซึ่งก็คือตารางนักเรียนด้วย

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

SQLite ข้ามเข้าร่วม

CROSS JOIN ให้ผลคูณคาร์ทีเซียนสำหรับคอลัมน์ที่เลือกของสองตารางที่รวมเข้าด้วยกัน โดยจับคู่ค่าทั้งหมดจากตารางแรกกับค่าทั้งหมดจากตารางที่สอง

ดังนั้น สำหรับทุกค่าในตารางแรก คุณจะได้รับการจับคู่ 'n' จากตารางที่สอง โดยที่ n คือจำนวนแถวของตารางที่สอง

ต่างจาก INNER JOIN และ LEFT OUTER JOIN CROSS JOIN ไม่จำเป็นต้องระบุเงื่อนไขการเชื่อมต่อ เนื่องจาก SQLite ไม่จำเป็นต้องใช้สำหรับ CROSS JOIN

การขอ SQLite จะได้ชุดผลลัพธ์เชิงตรรกะโดยการนำค่าทั้งหมดจากตารางแรกมารวมกับค่าทั้งหมดจากตารางที่สอง

ตัวอย่างเช่น หากคุณเลือกคอลัมน์หนึ่งจากตารางแรก (colA) และอีกคอลัมน์หนึ่งจากตารางที่สอง (colB) คอลัมน์ colA จะมีค่าสองค่า (1, 2) และคอลัมน์ colB ก็จะมีค่าสองค่า (3, 4)

จากนั้นผลลัพธ์ของ CROSS JOIN จะเป็นสี่แถว:

• สองแถวโดยการรวมค่าแรกจาก colA ซึ่งเป็น 1 กับสองค่าของ colB (3,4) ซึ่งจะเป็น (1,3), (1,4)
• ในทำนองเดียวกันสองแถวโดยการรวมค่าที่สองจาก colA ซึ่งเป็น 2 เข้ากับค่าสองค่าของ colB (3,4) ซึ่งคือ (2,3), (2,4)

ตัวอย่าง

ในแบบสอบถามต่อไปนี้ เราจะลองใช้ CROSS JOIN ระหว่างตารางนักศึกษาและแผนก:

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
CROSS JOIN Departments;

คำอธิบาย

• ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร SQLite เลือกจากหลายตาราง เราเพิ่งเลือกสองคอลัมน์ “ชื่อนักเรียน” จากตารางนักเรียน และ “ชื่อแผนก” จากตารางแผนก
• สำหรับการเชื่อมต่อแบบ Cross Join เราไม่ได้ระบุเงื่อนไขการเชื่อมต่อใดๆ เพียงแค่รวมสองตารางเข้าด้วยกันแล้วใช้คำสั่ง CROSS JOIN เชื่อมตรงกลาง

เอาท์พุต

ดังที่คุณจะเห็น ผลลัพธ์คือ 40 แถว โดยมีค่า 10 ค่าจากตารางนักศึกษาที่ตรงกับ 4 แผนกจากตารางแผนก ดังนี้

• ค่าสี่ค่าสำหรับสี่แผนกจากตารางแผนกตรงกับมิเชลนักเรียนคนแรก
• ค่าทั้งสี่สำหรับสี่แผนกจากตารางแผนกตรงกับนักเรียนคนที่สองชื่อจอห์น
• ค่าทั้งสี่สำหรับสี่แผนกจากตารางแผนกตรงกับนักเรียนคนที่สามชื่อแจ็ค… และเป็นเช่นนี้เรื่อยไป

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