Filtering while joining

Improving Query Performance in PostgreSQL

Amy McCarty

Instructor

Joins revisited

Joins

Link tables using at least 1 common field

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

Joins to combine data

From multiple tables
Inner and outer

Joins to filter data

Inner join
Outer join with non-linking join condition

Patient and appointments data

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 joins to filter

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

The 999 patient id in the Visits table links to the 999 patient id in the Patients table. The 888 patient id in the Visits table links to the 888 patient id in the Patients table. The 777 Patient id in the Patients table has no link.

Inner joins to filter

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 joins to filter

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

The Patients table filters to Males so has patient 888 Zhang Wei M circled. This is the only male record, so it is the only record that joins to a patient id in the Visits table.

Outer joins to filter

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

Filter pitfalls

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

1) FROM The 999 patient id in the Visits table links to the 999 patient id in the Patients table. The 888 patient id in the Visits table links to the 888 patient id in the Patients table. The 777 Patient id in the Patients table has no link.

2) WHERE A table with the 888 Zhang Wei visit, and patient information as one row and the 999 Lotte Smith visit and patient record as a second, crossed out row.

Filter pitfalls

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

Filter pitfalls

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

Filter pitfalls improved

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'

Let's practice!

Improving Query Performance in PostgreSQL