SELECT `mt_recruitment`.`no` AS `recruitment_no`, `mt_recruitment`.`status` AS `recruitment_status`, `mt_contract`.`status` AS `contract_status`, `mt_recruitment`.`start_date` AS `recruitment_start_date`, IF(`mt_recruitment`.`status` < 2, NULL, (SELECT `start_date` FROM `mt_contract_partner` WHERE `mt_contract_id` = `mt_contract`.`id` AND `deleted_at` is null)) AS `occupancy_date`, `mt_recruitment`.`id` AS `mt_recruitment_id` FROM `mt_recruitment` LEFT JOIN `mt_contract` ON ( IF( `mt_recruitment`.`mt_room_id` is null, `mt_contract`.`mt_section_id` = `mt_recruitment`.`mt_section_id`, `mt_contract`.`mt_room_id` = `mt_recruitment`.`mt_room_id` ) ) AND `mt_contract`.`deleted_at` is null WHERE `mt_recruitment`.`deleted_at` is null;
관계를 설명드리면,
mt_recruitment : mt_contract = 1 : N
mt_contract_partner : mt_contract = 1 : 1
입니다.
요점은,
mt_recruitment.mt_room_id 가 NULL일 경우 mt_recruitment.mt_section_id 을 사용해야 하고
mt_recruitment.mt_section_id 가 NULL일 경우 mt_recruitment.mt_room_id 을 사용해야 합니다.
IF문을 사용했기 때문에 풀스캔이 되고 있습니다.
mt_contract 의 mt_section_id과 mt_room_id 외래키가 걸려 있습니다.
개선한 쿼리는 아래와 같이 했는 데, 혹시 더 좋은 방법이 있을까요?
감사합니다.
select `mt_recruitment`.`no` AS `recruitment_no`, `mt_recruitment`.`status` AS `recruitment_status`, IF(`e`.`status` is null, `d`.`status`, `e`.`status`), `mt_recruitment`.`start_date` AS `recruitment_start_date`, IF(`mt_recruitment`.`status` < 2, NULL, (SELECT `start_date` FROM `mt_contract_partner` WHERE `mt_contract_id` = IF(`e`.`id` is null, `d`.`id`, `e`.`id`) AND `deleted_at` is null)) AS `occupancy_date`, `mt_recruitment`.`id` AS `mt_recruitment_id` from mt_recruitment left join mt_contract e on e.mt_room_id = mt_recruitment.mt_room_id and e.deleted_at is null left join mt_contract d on d.mt_section_id = mt_recruitment.mt_section_id and d.deleted_at is null WHERE `mt_recruitment`.`deleted_at` is null GROUP BY `mt_recruitment`.`id`;
따옴표(`) 사용과 테이블명을 full 로 사용하는 것은
제 관점에서는 쿼리 가독성이 떨어지는 것 같습니다.
간단한 알리아스 사용하는 것이 보기 좋은 것 같습니다.
SELECT a.no AS recruitment_no , a.status AS recruitment_status , IFNULL(b.status, c.status) AS contract_status , a.start_date AS recruitment_start_date , d.start_date AS occupancy_date , a.id AS mt_recruitment_id FROM mt_recruitment a LEFT JOIN mt_contract b ON a.mt_room_id IS NOT NULL AND a.mt_room_id = b.mt_room_id AND b.deleted_at IS NULL LEFT JOIN mt_contract c ON a.mt_room_id IS NULL AND a.mt_section_id = c.mt_section_id AND c.deleted_at IS NULL LEFT JOIN mt_contract_partner d ON a.status >= 2 AND d.mt_contract_id = IFNULL(b.id, c.id) AND d.deleted_at IS NULL WHERE a.deleted_at IS NULL ;