Характерные ошибки при решении задач на написание запросов на выборку (SELECT)

База данных «Корабли»

Решение 3.2.4

Подход на основе объединения не является единственно возможным. Следующее решение использует соединения.

Консоль

Выполнить

SELECT Classes.class
FROM Outcomes RIGHT OUTER JOIN
 Classes ON Outcomes.ship = Classes.class LEFT OUTER JOIN
 Ships ON Classes.class = Ships.class
GROUP BY Classes.class
HAVING (COUNT(COALESCE (Outcomes.ship, Ships.name)) = 1);

SELECT Classes.class FROM Outcomes RIGHT OUTER JOIN Classes ON Outcomes.ship = Classes.class LEFT OUTER JOIN Ships ON Classes.class = Ships.class GROUP BY Classes.class HAVING (COUNT(COALESCE (Outcomes.ship, Ships.name)) = 1);

    Схема

Правое соединение таблиц Outcomes и Classes дает нам головные корабли, при этом столбец Outcomes.ship будет содержать NULL-значение, если головного корабля нет в таблице Outcomes. Затем выполняется левое соединение таблиц Classes и Ships по внешнему ключу. Столбец Ships.name будет содержать NULL-значение, если класс не имеет кораблей в таблице Ships. Полученный набор записей группируется по классу, после чего выполняется фильтрация по предикату

COUNT(COALESCE (Outcomes.ship, Ships.name)) = 1

Остановимся подробнее на этом элегантном приеме.

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

NULL в столбце name для класса Bismarck означает, что головной корабль имеется только в таблице Outcomes. Корабли California и Tennessee класса Tennessee имеются в таблице Ships, при этом головной корабль есть также в таблице Outcomes. В третьем случае два корабля класса Yamato присутствуют в таблице Ships, в таблице же Outcomes нет головного корабля данного класса. Для четвертого случая класс Class_1 не имеет кораблей в базе данных.

Вернемся к предикату. Функция COALESCE (Outcomes.ship, Ships.name) вернет первое не NULL значение своих аргументов или NULL, если оба аргумента есть NULL-значение. Подробнее о функции COALESCE можно почитать в пункте 5.10.

Агрегатная функция COUNT, имеющая аргумент, вернет количество не NULL-значений аргумента в группе. Поэтому для класса Bismarck мы получим 1, для Tennessee и Yamato — 2 и, наконец, для Class_1 — 0. В результирующий набор из этих четырех классов попадает только Бисмарк, так как только он отвечает предикату.

Это решение не содержит ошибки предыдущего решения 3.2.3, то есть головной корабль, присутствующий как в таблице Ships, так и в таблице Outcomes, будет учтен только один раз. Это обусловлено тем, что при используемом соединении в результирующем наборе будет только одна строка.

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

Если вы еще не обнаружили ошибки, упомянутой на врезке, загляните в ПиР.

ПиР