SQL: Как понять, когда надо писать условие в JOIN-условие, а когда в WHERE

• 06, 19, 2012
•  bullgare
•  Databases
• комментария 2

Это в общем-то важно только для LEFT JOIN, для INNER JOIN тут могут быть только теоретические различия в производительности, хотя думаю, что парсер их решит.

Допустим у нас есть сотрудники, оборудование и таблица связи между ними.

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;

Для простоты убрал внешние ключи и лишние столбцы.

Если нам нужно вывести оборудование, которое ещё ни разу никому не выдавалось, то нам нужен запрос:

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;

Если нам нужен полный список оборудования, но при этом для оборудования «на руках» нужно выводить дату, когда оно было выдано, и сотрудника, которому оно было выдано:

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.