Есть ли более короткая альтернатива моему MySql-запросу?

Question

Я изучаю Java и тоже занимаюсь SQL. На уроке нам представили пример эскиза базы данных и запрос, который повторяется в этом вопросе.

Я сделал пример с MySql, и у него есть три таблицы,

CREATE TABLE `employed` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

CREATE TABLE `department` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

CREATE TABLE `employees_departments` (
`employed_id` int(11) NOT NULL,
`department_id` int(11) NOT NULL,
PRIMARY KEY (`employed_id`,`department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

занятый был заполнен

(1 'Karl'), (2 'Bengt'), (3 'Adam'), (4 'Stefan')

отдел был заполнен

(4, 'HR'), (5, 'Sälj'), (6, 'New departm')

отделы сотрудников заполнены

1 4
2 5
3 4

Таким образом, у "Стефана" нет отдела, а у "Нового отдела" нет работы.

Я хотел запрос, который бы давал сотрудникам все их отделы и сотрудников без отделов и отделов без сотрудников. Я нашел решение, как это:

select A.name, C.name from employed A
left join employees_departments B on (A.id=B.employed_id)
left join department C on (B.department_id = C.id)
union
select C.name, A.name from department A
left join employees_departments B on (A.id=B.department_id)
left join employed C on (B.employed_id = C.id)

Было бы хорошо, если бы был короткий запрос, чтобы сделать это...

Кроме того, я сделал это без ограничений внешнего ключа, поскольку я хочу сделать это максимально простым для этого примера.

Привет

Answer 1

MySQL не поддерживает операцию соединения FULL OUTER.

Мы можем эмулировать это, комбинируя два набора... результат OUTER JOIN и результат анти-JOIN.

(
  SELECT ee.name        AS employed_name
       , dd.name        AS department_name 
    FROM employed ee
    LEFT
    JOIN employees_departments ed
      ON ed.employed_id = ee.id
    LEFT
    JOIN department dd
      ON dd.id = ed.department_id
)
UNION ALL 
(
  SELECT nn.name        AS employed_name
       , nd.name        AS department_name
    FROM department nd
    LEFT
    JOIN employees_departments ne
      ON ne.deparment_id = nd.id
    LEFT
    JOIN employeed nn
      ON nn.id = nd.employee_id
   WHERE nn.id IS NULL
)

Первый SELECT возвращает все employed имя вместе с соответствием department имя, в том числе employed которые не имеют department,

Второй SELECT возвращает только department имя, которое не имеет соответствующих строк в employed,

Результаты двух SELECT объединяются / объединяются с использованием UNION ALL оператор набора. (The UNION ALL операция избегает потенциально дорогой операции "Использование файловой сортировки", которая была бы принудительной с UNION оператор набора.

Это самый короткий шаблон запроса для возврата этих строк.

---

Мы могли бы сделать SQL немного короче. Например, если у нас есть отношения внешнего ключа между employeed_department а также employed (в исходном посте нет указаний на то, что такие отношения применяются, поэтому мы не предполагаем, что они существуют)... но если это применяется, то мы могли бы опустить employed стол со второго SELECT

UNION ALL
(
  SELECT NULL           AS employed_name
       , nd.name        AS department_name
    FROM department nd
    LEFT
    JOIN employees_departments ne
      ON ne.deparment_id = nd.id
   WHERE ne.department_id IS NULL
)

При наличии подходящих индексов это даст нам наиболее эффективный план доступа.

Есть ли более короткий SQL, который будет возвращать эквивалентный результат? Если есть, скорее всего, он не будет работать так же эффективно, как указано выше.

---