Фильтрация при объединении

Оптимизация запросов в PostgreSQL

Amy McCarty

Instructor

Объединения: повторение

Объединения

Связывают таблицы по общим полям

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

Объединения для совмещения данных

Из нескольких таблиц
Внутренние и внешние

Объединения для фильтрации данных

Внутреннее объединение
Внешнее объединение с условием не по ключу

Данные о пациентах и приёмах

Приёмы

visit_id	reason	patient_id
01	checkup	999
02	infection	888

Пациенты

patient_id	name	sex
999	Lotte Smith	F
888	Zhang Wei	M
777	Amelia Hernandez	F

Фильтрация через внутреннее объединение

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

patient_id 999 в таблице Visits связан с patient_id 999 в таблице Patients. patient_id 888 в таблице Visits связан с patient_id 888 в таблице Patients. patient_id 777 в таблице Patients не имеет связи.

Фильтрация через внутреннее объединение

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

Фильтрация через внешнее объединение

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

Таблица Patients отфильтрована по мужчинам: выделен пациент 888 Zhang Wei M. Это единственная мужская запись, поэтому она единственная соединяется с patient_id в таблице Visits.

Фильтрация через внешнее объединение

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

Ловушки при фильтрации

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

1) FROM patient_id 999 в таблице Visits связан с patient_id 999 в таблице Patients. patient_id 888 в таблице Visits связан с patient_id 888 в таблице Patients. patient_id 777 в таблице Patients не имеет связи.

2) WHERE Таблица с записью о приёме 888 Zhang Wei и информацией о пациенте в первой строке, и записью о приёме 999 Lotte Smith — зачёркнутой — во второй строке.

Ловушки при фильтрации

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
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

Как избежать ловушек при фильтрации

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