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

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

Вот еще одно решение задачи, в котором не используется объединение, но содержится другая ошибка:

Решение 3.12.2

Консоль

Выполнить

SELECT classes.class, COUNT(ship) sunked 
FROM Classes FULL JOIN 
 Ships ON classes.class = ships.class LEFT JOIN 
 (SELECT ship 
 FROM Outcomes 
 WHERE result = 'sunk'
 ) s ON s.ship = ships.name OR 
 s.ship = classes.class 
GROUP BY classes.class;

SELECT classes.class, COUNT(ship) sunked FROM Classes FULL JOIN Ships ON classes.class = ships.class LEFT JOIN (SELECT ship FROM Outcomes WHERE result = 'sunk' ) s ON s.ship = ships.name OR s.ship = classes.class GROUP BY classes.class;

    Схема

Первое (полное) соединение:

Classes FULL JOIN 
Ships ON classes.class = ships.class

будет содержать все возможные классы кораблей. Заметим, что здесь можно было ограничиться левым (LEFT) соединением, так как согласно связи между таблицами в Ships не может быть корабля, класс которого отсутствует в таблице Classes.

Далее выполняется левое соединение с потопленными кораблями из таблицы Outcomes по следующему предикату (множество s содержит все потопленные корабли):

ON s.ship = ships.name OR s.ship = classes.class

То есть мы включаем в результирующий набор корабль, если имя его совпадает с именем потопленного корабля или если класс совпадает с именем потопленного корабля. На рассмотренных выше примерах данных этот запрос будет работать правильно, в отличие от первого рассмотренного решения. Действительно, если в классе «Бисмарк» имеется два потопленных корабля, один из которых является головным и отсутствует в Ships, то оба они будут учтены согласно рассмотренному выше предикату. Если же головной корабль присутствует в таблице Ships, то это ничего не меняет, так как предикат все равно будет выполнен.

В чем же здесь ошибка? Ошибка заключается как раз в предикате последнего соединения. Пусть в таблице Ships имеются корабли некоторого класса (например, два корабля с именами А и В класса Class_1), которые не были потоплены. И пусть в Outcomes имеется потопленный головной корабль этого же класса. Тогда соединяться будут следующие два отношения (приводим здесь только важные для анализа атрибуты):

и

Ship (отношение s) Class_1

по предикату

s.ship = classes.class

В результате будет получено отношение, содержащее корабли, которые не были потоплены, но учитываются этим решением:

Можно сказать иначе, а именно, потопленный головной корабль учтен здесь столько раз, сколько кораблей этого класса имеется в таблице Ships (как потопленных, так и нет). Так или иначе, но COUNT(ship) = 2, что неверно, так как потоплен был всего один корабль.

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