Лекция 14: Извлечение данных при помощи Transact-SQL
Предложение FROM
Предложение FROM содержит имена таблиц и представлений, из которых извлекаются данные. Каждый оператор SELECT обязательно должен иметь предложение FROM, за исключением случаев, когда список выборки не содержит имен колонок (а содержит лишь константы, переменные и арифметические выражения). Вы уже видели некоторые простые примеры предложений FROM, но предложения FROM могут содержать также и производные таблицы, соединения и алиасы.
Производные таблицы
Производная таблица (derived table) – набор результатов оператора SELECT, вложенный в предложение FROM. Набор результатов вложенного оператора SELECT используется как таблица, из которой извлекаются данные внешний оператор SELECT. Ниже дан пример запроса, использующего производную таблицу, чтобы найти имена всех магазинов (stores), предоставляющих хотя бы один вид скидок (discounts):
USE pubs
GO
SELECT s.stor_name
FROM stores AS s, (SELECT stor_id, COUNT(DISTINCT discounttype)
AS d_count
FROM discounts
GROUP BY stor_id) AS d
WHERE s.stor_id = d.stor_id AND
d.d_count >= 1
GOВ результате запуска этой команды будет извлечена одна строка, это означает, что предоставляет скидки только один из магазинов, имеющихся в базе данных.
Заметьте, что в этом запросе применяются сокращения для имен таблиц (s для таблицы stores (магазины) и d для таблицы discounts (скидки)). Эти сокращения называются алиасы таблиц (см. раздел "Алиасы таблиц" далее).
Примечание. Производные таблицы нельзя применять в предложениях WHERE. В качестве условия поиска в предложениях WHERE используется оператор SELECT. Об этом будет рассказано более подробно в разделе "Предложения WHERE и условия поиска" далее в этой лекции.Соединения таблиц
Соединение таблиц (joined table) – это набор результатов от операции соединения (join operation), выполненной над двумя или несколькими таблицами. Над таблицами можно выполнять различные типы соединений: внутренние соединения, полные внешние соединения, левые внешние соединения, правые внешние соединения, перекрестные соединения. Давайте рассмотрим их более подробно.
Внутренние соединения. Внутреннее соединение (inner join) является типом соединений, принятым по умолчанию. Внутреннее соединение задает набор результатов, в который будут включены лишь те строки таблиц, которые соответствуют условию ON, а все несоответствующие строки будут отброшены. Чтобы задать соединение, применяйте ключевое слово JOIN. Для задания условия поиска, на котором основывается соединение, применяется ключевое слово ON. Ниже приведен пример запроса, в котором соединяются таблицы stores и discounts; этот запрос показывает, в каких магазинах предоставляются скидки и типы этих скидок. (Это соединение – внутреннее, потому что по умолчанию применяется внутреннее соединение; поэтому будут возвращены лишь строки, соответствующие условию поиска ON.)
SELECT s.stor_id, d.discounttype FROM stores s JOIN discounts d ON s.stor_id = d.stor_id GO
Набор результатов будет выглядеть примерно так:
stor_id discounttype ------------------------------ 8042 Customer Discount
Как видите, скидки предоставляет лишь один магазин, причем скидки лишь одного типа. Выданная строка является единственной строкой с совпадающими значениями stor_id из таблицы stores и stor_id из таблицы discounts. Возвращаются это значение stor_id и соответствующее ему значение discounttype.
Полные внешние соединения. Полное внешнее соединение (full outer join) задает набор результатов, состоящий как из строк, соответствующих условию ON, так и из строк, не соответствующих условию ON. Для строк, не соответствующих условию ON, значением колонки, несоответствующей условию, станет NULL. В нашем примере NULL будет означать, что либо магазин не предоставляет никаких скидок (если некоторое значение stor_id имеется в таблице stores, но отсутствует в таблице discounts), либо этот тип скидки не предоставляется никаким магазином. Запрос в следующем примере полностью совпадает с предыдущим, за исключением того, что в нем стоят ключевые слова FULL OUTER JOIN:
SELECT s.stor_id, d.discounttype FROM stores s FULL OUTER JOIN discounts d ON s.stor_id = d.stor_id GO
Набор результатов будет выглядеть примерно так:
stor_id discounttype --------- ----------------- NULL Initial Customer NULL Volume Discount 6380 NULL 7066 NULL 7067 NULL 7131 NULL 7896 NULL 8042 Customer Discount
Условию поиска будет соответствовать лишь одна строка в наборе результатов (последняя). Остальные строки имеют NULL в одной из колонок.
Левые внешние соединения. Левое внешнее соединение (left outer join) возвращает строки, в которых произошло соответствие условию поиска, плюс все строки из таблицы, заданной слева от ключевого слова JOIN. Ниже дан наш старый пример запроса, но теперь в нем используются ключевые слова LEFT OUTER JOIN:
SELECT s.stor_id, d.discounttype FROM stores s LEFT OUTER JOIN discounts d ON s.stor_id = d.stor_id GO
Набор результатов будет выглядеть примерно так:
stor_id discounttype ------- --------------------- 6380 NULL 7066 NULL 7067 NULL 7131 NULL 7896 NULL 8042 Customer Discount
В набор результатов попадут и те строки из таблицы stores, для которых не нашлось строк из таблицы discounts с совпадающим значением stor_id (в колонке discounttype набора результатов в этих строках будет стоять значение NULL ). В набор результатов попадет также одна строка, для которой произошло соответствие условию ON.
Правые внешние соединения. Правое внешнее соединение (right outer join) противоположно левому внешнему соединению: в него войдут строки, соответствующие условию поиска, плюс все строки из таблицы, заданной справа от ключевого слова JOIN. Ниже дан наш старый пример запроса, но теперь в нем используются ключевые слова RIGHT OUTER JOIN:
SELECT s.stor_id, d.discounttype FROM stores s RIGHT OUTER JOIN discounts d ON s.stor_id = d.stor_id
Набор результатов будет выглядеть примерно так:
stor_id discounttype ------- -------------------- NULL Initial Customer NULL Volume Discount 8042 Customer Discount
В набор результатов попали и те строки из таблицы discounts, которые не имеют значений stor_id, совпадающих со значениями, имеющимися в таблице stores (значением stor_id для этих строк в наборе результатов станет NULL ). В набор результатов попадет также строка, соответствующая условию ON.
Перекрестные соединения. Перекрестное соединение (cross join) – это произведение двух таблиц, в котором не задано предложение WHERE. Когда предложение WHERE задано, перекрестное соединение работает как внутреннее соединение. А без предложения WHERE будет возвращаться такой результат: каждая строка из первой таблицы сопоставляется с каждой строкой из второй таблицы, поэтому размер набора результатов будет равен числу строк первой таблицы, умноженному на число строк второй таблицы.
Чтобы понять, что такое перекрестное соединение, давайте рассмотрим еще несколько примеров. Сначала рассмотрим перекрестное соединение без предложения WHERE, а затем рассмотрим три примера перекрестных соединений с предложениями WHERE. Ниже даны три простых примера. Запустите эти запросы и обратите внимание на количество строк в наборах результатов.
SELECT * FROM stores GO SELECT * FROM sales GO SELECT * FROM stores CROSS JOIN sales GO
Примечание. Если вы поместите в предложение FROM две таблицы, то это даст такой же результат, как если бы задать CROSS JOIN, как в следующем примере:SELECT * FROM stores, sales GO
Чтобы не потонуть в массе ненужной информации (если информации выдано больше, чем нужно), мы можем сузить запрос, добавив в него предложение WHERE, вот так:
SELECT * FROM sales CROSS JOIN stores WHERE sales.stor_id = stores.stor_id GO
Этот оператор возвращает только те строки, которые соответствуют условию поиска в предложении WHERE, что уменьшит набор результатов до 21 строки. Из-за предложения WHERE перекрестное соединение работает как внутреннее соединение (т.е. возвращаться будут лишь строки, соответствующие условию поиска). Запрос, показанный в последнем примере, возвращает строки из таблицы sales, соединенные со строками из таблицы stores, имеющими совпадающие значения stor_id. Строки, у которых нет совпадений, не возвращаются.
Чтобы еще больше сузить набор результатов, вы можете задать, из какой именно таблицы будут извлекаться все строки и колонки, поместив перед звездочкой имя таблицы, как это сделано в следующем нашем примере. Вы также можете указать, какой таблице принадлежит колонка, поместив перед именем колонки имя таблицы (отделив его точкой):
SELECT sales.*, stores.city FROM sales CROSS JOIN stores WHERE sales.stor_id = stores.stor_id GO
Этот запрос возвращает все колонки из таблицы sales (продажи), к которым добавлена колонка city ("город) из таблицы stores, для строк с одинаковыми значениями stor_id. Фактически, в набор результатов будет включены названия городов для магазинов, в которых проводились продажи, присоединенные к строкам из таблицы sales, имеющим значения stor_id, совпадающие со значениями stor_id из таблицы stores.
Ниже приведен еще один пример запроса, не имеющий символа "*" (из таблицы sales извлекается только колонка stor_id):
SELECT sales.stor_id, stores.city FROM sales CROSS JOIN stores WHERE sales.stor_id = stores.stor_id GO