Проверка отсутствующих значений в таблице

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

table tl table t2

cl I

a I b I с I

12 1 c2

2 I с

3 I b

4 I a

Объединение LEFT JOIN для поиска всех tl.il, отсутствующих в t2 .12, имеет следующий вид:

SELECT tl.* FROM tl LEFT JOIN t2 ON tl.ll = t2.i2 WHERE t2.12 IS NULL

+-+-+

I 11 I cl I

+-+-+

I 1 I a I

Теперь рассмотрим усложненный вариант запроса: Какие значения отсутствуют? . В проекте Учет успеваемости , впервые упомянутом в главе 1, Знакомство с СУБД MySQL и SQL , имеется три таблицы. Это таблица student со списком учащихся, таблица event со списком прошедших экзаменов, таблица score с оценками, полученными каждым учащимся по каждому тесту и каждой викторине. Однако если учащийся бьш в день проведения викторины или теста болен, запись по данному учащемуся по данному тесту или викторине в таблице score будет отсутствовать. Это значит, что ему придется сдавать этот экзамен дополнительно. Как найти недостающие записи и определить, какие учащиеся должны сдавать дополнительные экзамены?

Задача стоит таким образом: определить учащихся, которые не имеют оценок по определенным экзаменам. По-другому можно сказать, что нам необходимо определить, какие комбинации учащихся и событий в таблице оценок не представлены. Это наводит на мысль, что нам следует обратиться к объединению типа LEFT JOIN. Уже потому, что нам требуется воспользоваться объединением такого типа для комбинации столбцов можно судить, что это достаточно сложное объединение.

Искомые комбинации являются комбинациями учащийся/событие , которые можно получить пересечением таблиц student и event:

FROM student, event

Затем на результат этого объединения наложим объединение left join С таблицей score:

FROM student, event

LEFT JOIN score ON student.student id = score.student id AND event.event id = score.event id

Обратите внимание на то, что предложение on объединяет строки в таблице в полном соответствии со значениями столбцов из различных таблиц. Это ключ к решению задачи. Объединение LEFT join позволяет генерировать строку для каждого пересечения таблиц student и event, даже если в таблице score нет соответствующих им данных. Результирующий набор строк для этих записей с недостающими оценками можно идентифицировать по тому, что эти строки из таблицы score должны быть пустыми. Это определяется в предложении where:

WHERE score.score IS NOLL

Результат сортируется предложением order by. Логичнее всего сортировать по учащемуся и событию. Выберем соответствующий порядок сортировки:

ORDER BY student.student id, event.event id

Теперь остается только указать имена столбцов, которые мы хотим увидеть в результате работы запроса, и дело сделано. Вот как выглядит окончательный запрос.

SELECT

student.name, student. student id,

event.date, event.event id, event.type FROM student, event

LEFT JOIN score ON student.student id = score.student id AND event.event id = score.event id WHERE score.score IS NULL

ORDER BY student.student id, event.event id Вот результат работы этого запроса.

Здесь необходимо обратить внимание на одну особенность. В результате работы оператора получены идентификатор учащегося и идентификатор события. Столбец student id присутствует как в таблице student, так и в таблице score. Таким образом, можно предположить, что перед нами может стоять выбор: помещать в список выборки столбец student. student id или столбец score. student id. Но на самом деле такого выбора у нас нет, так как мы хотим получить из таблицы score только те строки, которые будут иметь значение null. Выборка столбца score. student id в результате даст нам пустой столбец. Аналогичные рассуждения имеют место в случае со столбцом event id, который присутствует как в таблице event, так и в таблице score.

Выполнение операции UNION

Запрос по выборке данных из нескольких таблиц, имеющих одинаковую структуру, во многих базах данных осуществляется оператором UNION. В СУБД MySQL такой оператор отсутствует (по крайней мере до версии 3.24). Его отсутствие можно компенсировать по крайней мере двумя способами.

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

Произведите выборку из каждой таблицы во временную таблицу. Затем сделайте выборку из временной таблицы. Это позволит отсортировать строки в нужном порядке. В СУБД MySQL версии 3.23 и выше эта проблема решается довольно просто благодаря тому, что сервер позволяет легко создавать временную таблицу. Она б>/дет автоматически уничтожена после завершения текущей сессии.

В примере, представленном ниже, таблица удаляется явным образом, чтобы позволить серверу освободить ресурсы, задействованные им под эту таблицу. Это решение подходит в случае, когда сессия будет продолжаться. Для ускорения быстродействия используется таблица в формате heap (хранится в памяти).

CREATE TEMPORARY TABLE hold tbl TYPE=HEAP SELECT ... FROM tablel WHERE ...

INSERT INTO hold tbl SELECT FROM table2 WHERE ... INSERT INTO hold tbl SELECT FROM table2 WHERE ...

SELECT FROM hold tbl ORDER BY DROP TABIE hold tbl

Идея, реализуемая в версиях до 3.23, аналогична. Вся разница заключается в том, что столбцы таблицы holdtbl необходимо объявлять явным образом, и в конце всей процедуры необходимо за-