Основы SQL – DML – SELECT – Часть II

В прошлый раз в этой серии статей я рассказал об операторе “SELECT” в самом простом его проявлении – запросы к одной таблице, без сложных фильтров. Теперь я расскажу о более интересной, но сложной теме – многотабличных запросах.

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

Многотабличные запросы без JOIN

Хотя в более ранних версиях Microsoft SQL Server многотабличные запросы без “JOIN” были более распространены, теперь они используются все реже и реже. И это, с моей точки зрения, правильно. Использование “JOIN” приводит, как минимум, к более структурированному и читаемому коду (что мы увидим чуть позже), а на символах в исходном коде я никогда не экономил :)

Самый простой вариант многотабличного запроса – это двухтабличные запрос без условия соединения таблиц или декартово произведение таблиц. И это, пожалуй, единственный вариант, когда от отсутствия “JOIN” не страдает наглядность:

SELECT * FROM Persons, Phones

В данном случае, если взять количество строк в “Persons” равным N, а в “Phones” – равным M, то мы получим N*M строк, составленных из всевозможных комбинаций строк двух исходных таблиц. То есть, если вам пришлось бы произвести эту операцию вручную на листке бумаги, то пришлось бы нарисовать таблицу, у которой были бы все столбцы из “Persons”, а затем все столбцы из “Phones”. Затем потребовалось бы записать первую строку из “Persons” и добавить справа первую строку из “Phones”. Потом первую из “Persons” и вторую из “Phones” и так далее.

Конечно же, практической пользы от такого запроса немного. Скажу по секрету, в большинстве случаев, подобные “безусловные” запросы с декартовым произведением таблиц являются либо тестовыми, либо просто ошибочными (забыли добавить условие). На всякий случай уточню, что “в большинстве случаев” != “всегда”, вполне возможно, есть специфические приложения, где подобные запросы составляют значительную часть.

Теперь посмотрим на более осмысленный вариант предыдущего запроса – выведем все телефоны вместе с фамилиями и идентификаторами абонентов:

SELECT a.PersonID, a.LastName, t.Phone
    FROM Persons a, Phones t
    WHERE a.PersonID = t.PersonID

Такой запрос выведет M строк (все строки таблицы “Phones”), поскольку для одной строке таблицы “Persons” может соответствовать много строк таблицы “Phones”, но не наоборот. Также нужно отметить, что в результате мы получим только те строки таблицы “Persons”, для которых есть строки в таблице “Phones” (обратное условие обеспечивается внешним ключом, который мы создали ранее).

Обратите внимание – для таблиц используются псевдонимы (“a” и “t”), чтобы удобнее было указывать полное имя столбца. Полное имя столбца требуется указывать в тех случаях, когда в разных таблицах есть столбцы с одинаковыми названиями. В нашем случае это только “PersonID”, но для единообразия я добавил префиксы и для других столбцов. Помимо указания имен столбцов, псевдонимы имеет смысл задавать для более компактной записи запроса (в тех случаях, когда мы набираем его руками).

Для того, чтобы получить, в том числе, и те строки из “Persons”, идентификаторы которых отсутствуют в “Phones”, в ранних версиях Transact-SQL можно было использовать следующий синтаксис (в скрипте ниже также добавляется новая строка и меняется compatibility level, с учетом того, что запускаем скрипт на Microsoft SQL Server 2008):

IF NOT EXISTS(SELECT * FROM Persons
    WHERE FirstName = 'Олег' AND LastName ='Аксенов')
    INSERT Persons (FirstName, LastName) VALUES('Олег', 'Аксенов')
ALTER DATABASE FirstTest SET COMPATIBILITY_LEVEL = 80
GO
SELECT a.PersonID, a.LastName, t.Phone
    FROM Persons a, Phones t
    WHERE a.PersonID *= t.PersonID

ALTER DATABASE FirstTest SET COMPATIBILITY_LEVEL = 100

Именно такой синтаксис мне, в свое время, очень не нравился еще в MS SQL 6.5 из-за плохой наглядности (граничащей с отвратительной, когда таблиц становилось много и добавлялись дополнительные условия в WHERE). Спустя несколько лет я выпилил все подобные запросы, переписав их с помощью “JOIN”. К счастью, начиная с Microsoft SQL Server 2005 подобный синтаксис считается устаревшим и при его использовании возникает соответствующая ошибка.

И, в завершении разговора об устаревшем синтаксисе, добавлю, что никто не мешает добавить фильтрацию по каким-либо столбцам используемых таблиц:

SELECT a.PersonID, a.LastName, t.Phone
    FROM Persons a, Phones t
    WHERE a.PersonID = t.PersonID AND t.Phone LIKE '101%'

Многотабличные запросы (JOIN)

После небольшого экскурса в историю пришло время разобраться с многотабличным запросами, использующими соединение таблиц (“JOIN”).

На мой взгляд, проще всего, для начала, посмотреть на аналоги запросов из предыдущей главы. Сразу отмечу, что префикс “INNER” (подразумевающий наличие соответствующих строк с обеих сторон соединения) используется в Transact-SQL по умолчанию и его можно не указывать.

Так мы получаем декартово произведение:

SELECT * FROM Persons CROSS JOIN Phones

Обратите внимание – использование “CROSS JOIN” предпочтительнее, чем использование обычного соединения с заведомо верным условием (например 1=1) хотя бы потому, что не оставляет сомнений в намерениях автора кода, в отличие от такого варианта:

SELECT * FROM Persons JOIN Phones ON 1=1

Вот все телефоны вместе с фамилиями и идентификаторами абонентов:

SELECT a.PersonID, a.LastName, t.Phone
    FROM Persons a JOIN Phones t ON a.PersonID = t.PersonID

А это запрос с учетом отсутствия телефона:

SELECT a.PersonID, a.LastName, t.Phone
    FROM Persons a LEFT JOIN Phones t ON a.PersonID = t.PersonID

Но основное преимущество заметно тогда, когда есть фильтры помимо условий соединения:

SELECT a.PersonID, a.LastName, t.Phone
    FROM Persons a JOIN Phones t ON a.PersonID = t.PersonID
    WHERE t.Phone LIKE '101%'

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

Для того, чтобы предыдущий запрос вывел всех лиц, независимо от наличия телефона, можно воспользоваться “LEFT JOIN”. Только нужно учесть, что у нас есть фильтр в “WHERE” по необязательной в данном случае таблице “Phones”. Для того, чтобы он не отфильтровал результаты, убрав лиц без телефона, необходимо перенести условие в “LEFT JOIN”:

SELECT a.PersonID, a.LastName, t.Phone
    FROM Persons a LEFT JOIN Phones t ON a.PersonID = t.PersonID
        AND t.Phone LIKE '101%'

Подробнее о синтаксисе

Теперь постараюсь не слишком формально и доступно рассказать о синтаксисе фразы “JOIN”. Более подробно ознакомиться с этим синтаксисом можно в статье MSDN о FROM.

“CROSS JOIN” как вы знаете, позволяет получить декартово произведение. Можно только добавить, что можно аналогичным образом получить соединение трех и более таблиц, просто добавив еще один “CROSS JOIN”. Также можно “перемножить” таблицу саму на себя, если это требуется.

“INNER JOIN” подразумевает, что условие соединения (часть после “ON”) выполняется всегда (как если оно присутствовало бы в “WHERE”), поэтому, если нет соответствующих строк в одной из таблиц, то в результат не попадает и “одинокая” строка из другой таблицы.
Как я уже говорил ранее, слово “INNER” можно не указывать – вряд ли вы встретите разработчика использующего Transact-SQL, который не знает об этом и по этой причине не сразу поймет такой запрос. А если встретите – объясните ему ситуацию, пожалуйста ;)

“LEFT JOIN”, в отличие от “INNER JOIN”, позволяет выводить строки из таблицы слева если нет соответствующих строк справа. Если справа у нас родительская таблица, внешний ключ на которую (например, столбец с идентификатором) не позволяет хранить значения NULL, то использование “LEFT JOIN”, очевидно, не имеет смысла. Смысл есть, когда ссылка не может быть NULL или когда родительская таблица находится слева, а дочерняя – справа. Также есть менее распространенный вариант, когда соединение происходит не по внешним ключам.

Обратите внимание – я сознательно не рассматриваю “RIGHT JOIN”, хотя он поддерживается Transact-SQL и аналогичен “LEFT JOIN”, только в этом случае выводятся строки из таблицы справа, если нет строк слева. Причина в том, что смешивание “RIGHT JOIN” и “LEFT JOIN” очень отрицательно сказывается на понимании запроса. И я рекомендую не только не использовать их в рамках одного запроса, но и вообще всегда использовать только один вариант. И лучше, чтобы вся ваша команда разделяла выбранный подход.

“FULL JOIN” я ранее не упоминал – он совмещает “LEFT” и “RIGHT”, то есть соответствующие строки могут отсутствовать и слева и справа. Используется не очень часто, но знать о нем полезно, чтобы не ломать голову над решением проблемы, которую он решит запросто.

Подзапросы

Подзапросы, по крайней мере, в моем понимании этого слова, делятся на подзапросы внутри списка столбцов (select list) и подзапросы внутри списка таблиц (from).

Для демонстрации возможностей первого типа подзапросов, можно переделать последний запрос (возвращающий всех лиц и показывающий их телефоны, если они содержат “101”) следующим образом:

SELECT a.PersonID, a.LastName, (SELECT t.Phone
        FROM Phones t WHERE a.PersonID = t.PersonID
            AND t.Phone LIKE '101%') AS Phone
    FROM Persons a

Если вы использовали скрипт для заполнения данных из предыдущих статей, у вас оба запроса выведут одинаковые результаты. Однако, внимательный читатель наверняка обратил внимание на то, что запросы не эквивалентны.

Этот запрос, в отличие от предыдущего, будет работать только тогда, когда подзапрос для каждой строки будет возвращать не более одной строки. В противном случае мы получим ошибку при выполнении запроса. Поэтому, в тех случаях, когда подзапрос может вернуть больше одной строки, но его необходимо использовать, к подзапросу добавляют ограничение “TOP 1”.

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

SELECT a.PersonID, a.LastName, t.Phone
    FROM Persons a LEFT JOIN (
        SELECT PersonID, Phone
        FROM Phones WHERE Phone LIKE '101%') t
        ON a.PersonID = t.PersonID

В данном случае назначение подзапроса не очень очевидно, однако подобные подзапросы очень упрощают жизнь, когда необходимо произвести несколько промежуточных вычислений, в частности – группировок. Пример такого запроса в следующей главе.

Пример нетривиального запроса

После всего, что я рассказал, захотелось привести пример, который демонстрировал бы большинство элементов оператора SELECT. Для разнообразия буду использовать системные таблицы, содержащие метаданные о таблицах и столбцах.

Сформулируем задачу следующим образом – получить список первых 10 по количеству столбцов системных таблиц, с названием первого столбца и количества столбцов для каждой. Эту задачу решает (надо отметить, что, как это часто бывает с SQL, решение не единственное) следующий запрос:

SELECT TOP 10 o.name, c.name as firstColumn,
    t.columnCount
FROM sys.objects o
    JOIN (
        SELECT c.object_id, MIN(c.column_id) AS column_id, 
                COUNT(*) AS columnCount
            FROM sys.columns c
            GROUP BY c.object_id
        ) t ON o.object_id = t.object_id
    JOIN sys.columns c ON o.object_id = c.object_id 
        AND c.column_id = t.column_id
WHERE o.type = 'S'
ORDER BY columnCount DESC

Думаю, большая часть запроса для вас очевидна, поясню только смысл последнего JOIN. В подзапросе мы нашли минимальный идентификатор, но мы хотим вывести название столбца, и только ради этого мы присоединяем таблицу “sys.columns”, чтобы взять у нее, в итоге “firstColumn”.

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

Рекомендации для многотабличных запросов

1. Повторюсь, очень важно стараться использовать только один тип “OUTER JOIN”, в моем случае, это LEFT, просто потому что для меня (кстати, как и для моих коллег по работе) он выглядит более удобным и логичным.
2. При чередовании INNER и LEFT JOIN во FROM иногда используются круглые скобки, однако я предпочитаю сначала написать все “INNER JOIN”, а потом все “LEFT JOIN”. Запрос получается, на мой взгляд, более читаемым и его проще дорабатывать. Только в том случае, когда это невозможно, я использую круглые скобки. Если же смешивать INNER и LEFT JOIN без скобок, в получившемся запросе часто бывает достаточно трудно разобраться.
3. Если вам кажется, что в запросе используется слишком много таблиц и/или он возвращает слишком много лишних данных не бойтесь разбить его на несколько. Попытка сделать все “одним запросом” похвальна, но только тогда, когда это не ухудшает производительность.
4. В продолжении предыдущего совета – не тащите много лишних данных из базы. Помните, вместо закачки тысячи ненужных строк есть альтернатива с paging/TOP; вместо получения десятков лишних столбцов из-за “*” есть альтернатива указать необходимые столбцы вручную и т.п. Не стоит, конечно, бросаться в крайности – если, скажем, одно и то же ФИО в результате JOIN’а дублируется десяток-другой раз, а получаем в результате сотню строк – это не страшно, скорее всего :)

Анонс

В следующей части рассказа об операторе “SELECT” я продолжу рассказ о многотабличных запросах прежде всего с точки зрения группировок данных.

Если у вас есть пожелания или новые темы – пишите в комментариях или на olegaxenow.reformal.ru. Постараюсь учесть.