兩表一對一關係:
| Student 表 | Course 表 |
|---|---|
![]() | ![]() |
SELECT * FROM Student LEFT JOIN Course ON Student.ID = Course.ID

NULLSELECT * FROM Student RIGHT JOIN Course ON Student.ID = Course.ID

NULLSELECT * FROM Student FULL JOIN Course ON Student.ID = Course.ID

NULLSELECT * FROM Student INNER JOIN Course ON Student.ID = Course.ID

SELECT * FROM Student, Course WHERE Student.ID = Course.IDSELECT * FROM Student CROSS JOIN Course

WHERE Student.ID = Course.ID 等同 INNER JOIN調整欄位設計:
Sno, Name, CnoCno, CNameSQL 寫法與一對一完全相同,結果集每筆學生對應其選修課程 (一行學生可能對應多行課程)。
學生可選多門課、課程可被多學生選 → 需中介表 Student_Course
| Student | Course | Student_Course (中介表) |
|---|---|---|
![]() | ![]() | ![]() |
SELECT s.Name, c.CName
FROM Student_Course sc
LEFT JOIN Student s ON s.Sno = sc.Sno
LEFT JOIN Course c ON c.Cno = sc.Cno

中介表至少包含兩表主鍵 (Sno, Cno),可擴充屬性如 SelectDate, Score 等。
| 需求 | 推薦聯結 |
|---|---|
| 僅需雙方皆有資料的交集 | INNER JOIN |
| 保留主表所有資料,附屬表可為空 | LEFT JOIN (主表在左) |
| 保留從表所有資料,主表可為空 | RIGHT JOIN (從表在右) |
| 雙表資料全保留 | FULL JOIN |
| 多對多關係 | 中介表 + 兩次 LEFT JOIN |