在連接時進行篩選

改進 PostgreSQL 的查詢效能

Amy McCarty

Instructor

再次回顧連接

 

連接（Join）

  • 以至少 1 個共同欄位連結資料表
SELECT * 
FROM TABLE_A AS A
INNER JOIN TABLE_B AS B 
  ON A.NAME = B.NAME

 

用連接來合併資料

  • 來自多個資料表
  • 內連接與外連接

用連接來篩選資料

  • 內連接
  • 外連接搭配非連結條件
改進 PostgreSQL 的查詢效能

病患與看診資料

看診紀錄（Appointments）
visit_id reason patient_id
01 checkup 999
02 infection 888
病患（Patients）
patient_id name sex
999 Lotte Smith F
888 Zhang Wei M
777 Amelia Hernandez F
改進 PostgreSQL 的查詢效能

用內連接來篩選

SELECT *
FROM appointments a
INNER JOIN patients p
ON a.patient_id = p.patient_id

Visits 表中的 999 patient id 連到 Patients 表中的 999 patient id。Visits 表中的 888 連到 Patients 表中的 888。Patients 表中的 777 patient id 沒有連結。

改進 PostgreSQL 的查詢效能

用內連接來篩選

SELECT *
FROM appointments a
INNER JOIN patients p
ON a.patient_id = p.patient_id
輸出
visit_id reason patient_id name sex
01 checkup 999 Lotte Smith F
02 infection 888 Zhang Wei M
改進 PostgreSQL 的查詢效能

用外連接來篩選

SELECT *
FROM appointments a
LEFT JOIN patients p
  ON a.patient_id = p.patient_id
  AND p.sex = 'M'

Patients 表先篩到男性，因此圈出 888 Zhang Wei M。這是唯一的男性紀錄，所以也是唯一能與 Visits 表的 patient id 連接的紀錄。

改進 PostgreSQL 的查詢效能

用外連接來篩選

SELECT *
FROM appointments a
LEFT JOIN patients p
  ON a.patient_id = p.patient_id
  AND p.sex = 'M'
輸出
visit_id reason patient_id name sex
01 checkup 999
02 infection 888 Zhang Wei M
改進 PostgreSQL 的查詢效能

篩選的陷阱

SELECT * 
FROM appointments a
LEFT JOIN patients p 
  ON a.patient_id = p.patient_id
WHERE p.sex = 'M'

1) FROM Visits 表中的 999 patient id 連到 Patients 表中的 999 patient id。Visits 表中的 888 連到 Patients 表中的 888。Patients 表中的 777 patient id 沒有連結。

 

 

 

 

2) WHERE 一個表格顯示 888 Zhang Wei 的就診與病患資訊為一列；999 Lotte Smith 的就診與病患資料為另一列，但被劃掉。

改進 PostgreSQL 的查詢效能

篩選的陷阱

SELECT * 
FROM appointments a
LEFT JOIN patients p 
  ON a.patient_id = p.patient_id
WHERE p.sex = 'M'
輸出
visit_id reason patient_id name sex
02 infection 888 Zhang Wei M
改進 PostgreSQL 的查詢效能

篩選的陷阱

SELECT * 
FROM appointments a
LEFT JOIN patients p 
  ON a.patient_id = p.patient_id
WHERE p.sex = 'M'
輸出
visit_id reason patient_id name sex
02 infection 888 Zhang Wei M 
SELECT *
FROM appointments a
LEFT JOIN patients p
  ON a.patient_id = p.patient_id
  AND p.sex = 'M'
visit_id reason patient_id name sex
01 checkup 999
02 infection 888 Zhang Wei M
改進 PostgreSQL 的查詢效能

改善篩選陷阱

 

SELECT *
FROM appointments a
INNER JOIN patients p
  ON a.patient_id = p.patient_id
WHERE p.sex = 'M'
visit_id reason patient_id name sex
02 infection 888 Zhang Wei M

 

SELECT *
FROM appointments a
INNER JOIN patients p
  ON a.patient_id = p.patient_id
  AND p.sex = 'M'
改進 PostgreSQL 的查詢效能

一起來練習吧！

改進 PostgreSQL 的查詢效能

Preparing Video For Download...