Join และความสัมพันธ์

table เดียวตอบได้แค่คำถามเกี่ยวกับตัวเองเท่านั้น พลังที่แท้จริงของฐานข้อมูลเชิงสัมพันธ์จะปรากฏเมื่อคุณเลิกยัดทุกอย่างไว้ใน table ใหญ่ table เดียว แล้วหันมาแบ่งข้อมูลออกเป็น table ที่มีจุดโฟกัสชัดเจนและชี้ถึงกันผ่าน key เช่น table authors เก็บข้อมูลคน ส่วน table books เก็บชื่อหนังสือ แล้วใช้ foreign key เชื่อมหนังสือแต่ละเล่มกลับไปหาผู้เขียน ไม่มีอะไรซ้ำซ้อน แต่คุณก็ยังถามคำถามที่ครอบคลุมทั้งสอง table ได้

โมดูลนี้ว่าด้วยการตอบคำถามข้าม table เหล่านั้น คุณจะได้เรียนรู้การเย็บ table กลับเข้าด้วยกันด้วย join, การยุบหลาย row ให้กลายเป็นตัวเลขสรุปด้วย aggregate, การซ้อน query หนึ่งไว้ในอีก query หนึ่งด้วย subquery และ CTE, และสุดท้ายการคำนวณการจัดอันดับราย row และยอดสะสมด้วย window function

schema ตัวอย่าง

ทุกบทเรียนจะใช้ table เล็ก ๆ สาม table ซ้ำกัน authors และ books น่าจะดูคุ้นเคยจากโมดูล CRUD เราเพิ่ม table sales เข้ามาเพื่อให้มีอะไรไว้ aggregate และจัดอันดับ

CREATE TABLE authors (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  born int
);

CREATE TABLE books (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title     text NOT NULL,
  author_id bigint REFERENCES authors (id),
  published int
);

CREATE TABLE sales (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  book_id  bigint REFERENCES books (id),
  region   text NOT NULL,
  copies   int NOT NULL
);

await pool.query(`
  CREATE TABLE sales (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    book_id bigint REFERENCES books (id),
    region  text NOT NULL,
    copies  int NOT NULL
  )
`);

cur.execute("""
    CREATE TABLE sales (
      id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      book_id bigint REFERENCES books (id),
      region  text NOT NULL,
      copies  int NOT NULL
    )
""")

_, err := conn.Exec(ctx, `
  CREATE TABLE sales (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    book_id bigint REFERENCES books (id),
    region  text NOT NULL,
    copies  int NOT NULL
  )`)

sqlx::query(
    "CREATE TABLE sales (
       id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
       book_id bigint REFERENCES books (id),
       region  text NOT NULL,
       copies  int NOT NULL
     )",
)
.execute(&pool)
.await?;

ใน pgAdmin: วางคำสั่ง CREATE TABLE ทั้งสามคำสั่งลงใน Query Tool แล้วรัน table ใหม่จะปรากฏใต้ Schemas แล้วก็ Tables และถ้าคุณขยาย table ออกมา จะเห็น constraint แบบ foreign key แสดงรายการอยู่ใต้ Constraints

table สัมพันธ์กันอย่างไร

แต่ละ table เชื่อมต่อกับ table ถัดไปผ่าน column key หนังสือพก id ของผู้เขียนเอาไว้ และยอดขายก็พก id ของหนังสือเอาไว้ การเชื่อมโยงเหล่านี้คือสิ่งที่ทุก query ในโมดูลนี้เดินตาม

flowchart LR
  A[authors] -->|id referenced by books.author_id| B[books]
  B -->|id referenced by sales.book_id| S[sales]

table เชื่อมโยงกันด้วย key

อ่านลูกศรว่า “ถูกอ้างอิงโดย” ผู้เขียนหนึ่งคนมีหนังสือได้หลายเล่ม และหนังสือหนึ่งเล่มมี row ยอดขายได้หลาย row — รูปแบบ one-to-many คลาสสิกที่ join ถูกออกแบบมาเพื่อท่องผ่าน

โมดูลนี้ครอบคลุมอะไรบ้าง

Joins — รวม row จากสอง table ขึ้นไปบน column key ที่ตรงกัน และเลือกว่าจะเก็บหรือทิ้ง row ที่ไม่มีคู่ที่ตรงกัน
Aggregate และ GROUP BY — ยุบหลาย row ให้กลายเป็น count, sum, และค่าเฉลี่ย แล้วกรองกลุ่มเหล่านั้นด้วย HAVING
Subquery และ CTE — ใช้ผลลัพธ์ของ query หนึ่งภายในอีก query หนึ่ง และตั้งชื่อขั้นตอนระหว่างทางด้วย WITH เพื่อให้อ่านง่าย
Window function — จัดอันดับ row และคำนวณยอดสะสมโดยไม่ทิ้ง row แต่ละ row ไป

เคล็ดลับและข้อควรระวัง

foreign key ทำหน้าที่บันทึกและบังคับใช้ความสัมพันธ์ แต่ join ใน query ของคุณต่างหากที่นำ row ที่เกี่ยวข้องมารวมกันจริง ๆ
คำถามเดียวกันมักเขียนได้ทั้งเป็น join, subquery, หรือ window function การเรียนรู้ทั้งสามแบบทำให้คุณเลือกแบบที่ชัดเจนที่สุดได้
ให้นึกภาพการเชื่อมโยงแบบ one-to-many ข้างต้นไว้ในใจ เพราะเกือบทุก query ในโมดูลนี้เดินตามลูกศรเหล่านั้นเส้นใดเส้นหนึ่ง

🐘 ทำไมต้องแยก table ตั้งแต่แรก?

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