2 Responses so far.
Leave a Comment
SQL: Как понять, когда надо писать условие в JOIN-условие, а когда в WHERE
Это в общем-то важно только для LEFT JOIN, для INNER JOIN тут могут быть только теоретические различия в производительности, хотя думаю, что парсер их решит.
Допустим у нас есть сотрудники, оборудование и таблица связи между ними.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE TABLE Employee ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `login` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY ( `login` ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `Equipment` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `inv_number` VARCHAR(255) NOT NULL, `employee_equipment_id` INT(10) UNSIGNED, PRIMARY KEY (`id`), UNIQUE KEY ( `inv_number` ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS EmployeeEquipment ( `employee_id` INT(10) UNSIGNED NOT NULL, `equipment_id` INT(10) UNSIGNED NOT NULL, `handed_at` DATETIME NOT NULL, `returned_at` DATETIME, PRIMARY KEY (`employee_id`, `equipment_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
Для простоты убрал внешние ключи и лишние столбцы.
Если нам нужно вывести оборудование, которое ещё ни разу никому не выдавалось, то нам нужен запрос:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT eq.*, e.id AS employee_id, e.name AS employee_name, ee.handed_at FROM Equipment as eq LEFT JOIN EmployeeEquipment AS ee ON ee.equipment_id = eq.id LEFT JOIN Employee AS e ON e.id = ee.employee_id WHERE ee.handed_at IS NULL; |
Если нам нужен полный список оборудования, но при этом для оборудования «на руках» нужно выводить дату, когда оно было выдано, и сотрудника, которому оно было выдано:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT eq.*, e.id AS employee_id, e.name AS employee_name, ee.handed_at FROM Equipment as eq LEFT JOIN EmployeeEquipment AS ee ON ee.equipment_id = eq.id AND ee.returned_at IS NULL LEFT JOIN Employee AS e ON e.id = ee.employee_id; |
Хитрость второго запроса — в том, что он учитывает ситуации, когда оборудование было выдано сотруднику, а потом его возвратили обратно.
То есть первый запрос ограничивает всю выборку (поэтому условие пишется в WHERE), а второй запрос ограничивает «участие LEFTJOINed таблицы» в выборке, поэтому условие пишется в условии ON.
Similar Posts
LEAVE A COMMENT
Для отправки комментария вам необходимо авторизоваться.
если я не ошибаюсь то с условием в JOIN мы создаем временную таблицу?
проверьте на большом объеме данных
Создания временной таблицы можно избежать, простроив правильно индексы на таблицах для выборки (к примеру).
На использование временной таблицы можно наткнуться даже при выборке из одной таблицы (попробуйте сделать WHERE по одному полю и GROUP BY или ORDER BY по другому полю).
Также создание временной таблицы не зависит от объёма данных. При большом объёме данных временная таблица будет создана на диске, что существенно замедлит запрос.
Будет ли создана временная таблица, можно узнать по полю extra у EXPLAIN (там будет написано using temporary). Где она будет создана — узнать из EXPLAIN нельзя, к сожалению.
Не надо при этом считать, кстати, что using filesort говорит о том, что временная таблица будет создана на диске, вот маленькая статья на этот счёт — http://www.mysqlperformanceblog.com/2009/03/05/what-does-using-filesort-mean-in-mysql/