Filtrare durante le join

Migliorare le prestazioni delle query in PostgreSQL

Amy McCarty

Instructor

Join: ripasso

Join

Collegano tabelle con almeno 1 campo in comune

SELECT * 
FROM TABLE_A AS A
INNER JOIN TABLE_B AS B 
  ON A.NAME = B.NAME

Join per combinare dati

Da più tabelle
Inner e outer

Join per filtrare dati

Inner join
Outer join con condizione di join non collegante

Dati di pazienti e appuntamenti

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

Inner join per filtrare

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

Il patient_id 999 nella tabella Visits si collega al patient_id 999 in Patients. Il patient_id 888 in Visits si collega al patient_id 888 in Patients. Il patient_id 777 in Patients non ha collegamento.

Inner join per filtrare

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

Output

visit_id	reason	patient_id	name	sex
01	checkup	999	Lotte Smith	F
02	infection	888	Zhang Wei	M

Outer join per filtrare

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

La tabella Patients è filtrata su Maschi, quindi ha 888 Zhang Wei M cerchiato. È l’unico record maschile, quindi è l’unico che si unisce a un patient_id nella tabella Visits.

Outer join per filtrare

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

Output

visit_id	reason	patient_id	name	sex
01	checkup	999
02	infection	888	Zhang Wei	M

Trappole del filtro

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

1) FROM Il patient_id 999 nella tabella Visits si collega al patient_id 999 in Patients. Il patient_id 888 in Visits si collega al patient_id 888 in Patients. Il patient_id 777 in Patients non ha collegamento.

2) WHERE Una tabella con la visita 888 di Zhang Wei e info paziente in una riga e la visita 999 di Lotte Smith con record paziente come seconda riga, barrata.

Trappole del filtro

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

Output

visit_id	reason	patient_id	name	sex
02	infection	888	Zhang Wei	M

Trappole del filtro

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

Output

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

Trappole del filtro: meglio

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'

Ayo berlatih!

Migliorare le prestazioni delle query in PostgreSQL