Руководство по SQL. Объединения.
Для комбинирования результатов двух и более SQL запросов без возвращения повторяющихся данных используется оператор UNION.
Для того, чтобы мы могли использовать данный оператор, каждый из запросов SELECT должен содержать одинаковое количество выбранных колонок, одинаковое количество выражений, одинаковые типы данных и иметь одинаковый порядок.
Запрос с использованием оператора UNION имеет следующий вид:
Предположим, что у нас есть две таблицы:
developers:
tasks:
Попробуем выполнить следующий запрос:
В результате мы получим следующий результат:
Элемент UNION ALL
Элемент UNION ALL комбинирует результаты двух запросов SELECT, исключая повторяющиеся записи.
Данный запрос имеет следующий вид:
Предположим, что у нас есть две таблицы:
developers:
tasks:
Попробуем выполнить следующий запрос:
В результате мы получим следующую таблицу:
Как мы видим, таблица содержит результаты обоих запросов SELECT и данные повторяются.
Существует два других оператора, чьё поведение крайне схоже с UNION:
- INTERSECT
Комбинирует два запроса SELECT, но возвращает записи только первого SELECT, которые имеют совпадения во втором элементе SELECT. - EXCEPT
Комбинирует два запроса SELECT, но возвращает записи только первого SELECT, которые не имеют совпадения во втором элементе SELECT.
На этом мы заканчиваем изучение способов объединения данных.
В следующей статье мы рассмотрим индексы.
MySQL. Вложеные запросы. JOIN LEFT/RIGHT.
В SQL подзапросы — или внутренние запросы, или вложенные запросы — это запрос внутри другого запроса SQL, который вложен в условие WHERE.
Вложеные запросы
SQL подзапрос — это запрос, вложенный в другой запрос.
Подзапрос используется для возврата данных, которые будут использоваться в основном запросе, в качестве условия для дальнейшей фильтрации данных, подлежащих извлечению.
Существует несколько правил, которые применяются к подзапросам:
- Подзапросы должны быть заключены в круглые скобки.
- Подзапрос может иметь только один столбец в условии SELECT, если только несколько столбцов не указаны в основном запросе для подзапроса для сравнения выбранных столбцов.
- Подзапросы, которые возвращают более одной строки, могут использоваться только с несколькими операторами значений, такими как оператор IN.
- Команда ORDER BY не может использоваться в подзапросе, хотя в основном запросе она использоваться может. В подзапросе может использоваться команда GROUP BY для выполнения той же функции, что и ORDER BY.
- С подзапросом не может использоваться оператор BETWEEN. Однако оператор BETWEEN может использоваться внутри подзапроса.
- Не рекомендуется создавать запросы со степенью вложения больше трех. Это приводит к увеличению времени выполнения и к сложности восприятия кода.
SELECT
Подзапросы чаще всего используются с инструкцией SELECT. При этом используется следующий синтаксис
Ниже представлена струтура таблицы для демонстрации примеров
| snum | sname | city | comm |
|---|---|---|---|
| 1 | Колованов | Москва | 10 |
| 2 | Петров | Тверь | 25 |
| 3 | Плотников | Москва | 22 |
| 4 | Кучеров | Санкт-Петербург | 28 |
| 5 | Малкин | Санкт-Петербург | 18 |
| 6 | Шипачев | Челябинск | 30 |
| 7 | Мозякин | Одинцово | 25 |
| 8 | Проворов | Москва | 25 |
| cnum | cname | city | rating | snum |
|---|---|---|---|---|
| 1 | Деснов | Москва | 90 | 6 |
| 2 | Краснов | Москва | 95 | 7 |
| 3 | Кириллов | Тверь | 96 | 3 |
| 4 | Ермолаев | Обнинск | 98 | 3 |
| 5 | Колесников | Серпухов | 98 | 5 |
| 6 | Пушкин | Челябинск | 90 | 4 |
| 7 | Белый | Одинцово | 85 | 1 |
| 8 | Чудинов | Москва | 89 | 3 |
| 9 | Проворов | Москва | 95 | 2 |
| 10 | Лосев | Одинцово | 75 | 8 |
| onum | amt | odate(YEAR) | cnum | snum |
|---|---|---|---|---|
| 1001 | 420 | 2013 | 9 | 4 |
| 1002 | 653 | 2005 | 10 | 7 |
| 1003 | 960 | 2016 | 2 | 1 |
| 1004 | 320 | 2016 | 3 | 3 |
| 1005 | 200 | 2015 | 5 | 4 |
| 1006 | 2560 | 2014 | 5 | 4 |
| 1007 | 1200 | 2013 | 7 | 1 |
| 1008 | 50 | 2017 | 1 | 3 |
| 1009 | 564 | 2012 | 3 | 7 |
| 1010 | 900 | 2018 | 6 | 8 |
Начнем с такого примера и для начала вспомним, как бы делали этот запрос ранее: посмотрели бы в таблицу SALES (или выполнили отдельный запрос), определили бы snum продавца «Плотников» — он равен 3. И выполнили бы запрос SQL с помощью условия WHERE .
| amt | odate |
|---|---|
| 320 | 2016 |
| 50 | 2017 |
Такой запрос, очевидно, не очень универсален, если нам захочется выбрать тоже самое для другого продавца, то всегда придется определять его snum . В SQL предусмотрена возможность объединять такие запросы в один путем превращения одного из них в подзапрос (вложенный запрос).
В этом примере мы определяем с помощью вложенного запроса идентификатор snum по фамилии из таблицы SALES , а затем, в таблице ORDERS определяем по этому идентификатору нужные нам значения.
Этот SQL запрос отличается тем, что вместо знака = здесь используется оператор IN.
То есть в запросе происходит проверка, содержится ли идентификатор snum из таблицы SALES в массиве значений, который вернул вложенный запрос. Если содержится, то SQL выдаст фамилию этого продавца.
| snum | sname |
|---|---|
| 1 | Колованов |
| 3 | Плотников |
Запрос чем то похож на предыдущий, только теперь мы добавляем еще одно поле для сравнения.
| Покупатель | Продавец |
|---|---|
| Проворов | Кучеров |
| Лосев | Мозякин |
| Белый | Колованов |
| Кириллов | Мозякин |
В этом примере мы сравниваем сразу два поля одновременно по идентификаторам. То есть из таблицы ORDERS берутся те строки, которые удовлетворяют условию не позднее 2014 года, затем вместо идентификаторов подставляются значение имен покупателей и продавцов.
Оператор as нужен для того, чтобы при выводе SQL показывал не имена полей, а то, что мы зададим. И после оператора FROM за именами таблиц стоят сокращения, которые потом используются — это псевдонимы. Псевдонимы можно называть любыми именами, в этом запросе они используются для явного определения поля, так как мы несколько раз обращаемся к одному и тому же полю, только из разных таблиц.
CREATE
Задача — создать копию существующей таблицы.
Копия существующей таблицы может быть создана с помощью комбинации CREATE TABLE и SELECT .
Новая таблица будет имеет те же определение столбцов, могут быть выбраны все столбцы или отдельные столбцы . При создании новой таблицы с помощью существующей таблицы, новая таблица будет заполняться с использованием существующих значений в старой таблице.
Создадим копию таблицы city . Вопрос — почему 1=0?
INSERT
Задача — создать копию существующей таблицы.
Подзапросы также могут использоваться с инструкцией INSERT . Инструкция INSERT использует данные, возвращаемые из подзапроса, для вставки в другую таблицу. Выбранные в подзапросе данные могут быть изменены. Основной синтаксис следующий.
Копирование всей таблицы полностью
Копируем города которые находся в стране с численостью не меньше 500тыс.человек, но не больше 1 миллиона.
UPDATE
Подзапрос может использоваться в сочетании с инструкцией UPDATE . Один или несколько столбцов в таблице могут быть обновлены при использовании подзапроса с помощью инструкции UPDATE . Основной синтаксис следующий.
Исходя из того, что у нас есть таблица CITY_BKP , которая является резервной копией таблицы CITY , в следующем примере для всех записей, для которых Population больше или равно 100000, применяет коэффициент 0,25.
DELETE
Подзапрос может использоваться в сочетании с инструкцией DELETE , так же как и со всеми описанными выше инструкциями. Основной синтаксис следующий.
Внутреннее объединение
Для этого проще всего обратиться к таблице CITY
Но, что если нам необходимо, чтобы в ответе на запрос был не код страны, а её название? Вложенные запросы нам не помогут. А нам надо получить данные из двух таблиц и объединить их в одну. Запросы, которые позволяют это сделать, в SQL называются объединениями. Синтаксис самого простого объединения следующий:
Получилось не совсем то, что мы ожидали. Такое объединение научно называется декартовым произведением, когда каждой строке первой таблицы ставится в соответствие каждая строка второй таблицы.
Чтобы результирующая таблица выглядела так, как мы хотели, необходимо указать условие объединения. Мы связываем наши таблицы по идентификатору, это и будет нашим условием.
Т.е. мы в запросе сделали следующее условие: если в обеих таблицах есть одинаковые идентификаторы, то строки с этим идентификатором необходимо объединить в одну результирующую строку.
Как вы понимаете, объединения дают возможность выбирать любую информацию из любых таблиц, причем объединяемых таблиц может быть и три, и четыре, да и условие для объединения может быть не одно.
JOIN LEFT/RIGHT
JOIN — оператор языка SQL, который является реализацией операции соединения реляционной алгебры. Входит в предложение FROM операторов SELECT , UPDATE и DELETE .
JOIN используется для объединения строк из двух или более таблиц на основе соответствующего столбца между ними.
Операция соединения, как и другие бинарные операции, предназначена для обеспечения выборки данных из двух таблиц и включения этих данных в один результирующий набор.
Особенности операции соединения
- в схему таблицы-результата входят столбцы обеих исходных таблиц
- каждая строка таблицы-результата является «сцеплением» строки из одной таблицы со строкой второй таблицы
Определение того, какие именно исходные строки войдут в результат и в каких сочетаниях, зависит от типа операции соединения и от явно заданного условия соединения. Условие соединения, то есть условие сопоставления строк исходных таблиц друг с другом, представляет собой логическое выражение (предикат).
Ниже представлена струтура таблицы для демонстрации примеров
| id | name | city_id |
|---|---|---|
| 1 | Колованов | 1 |
| 2 | Петров | 3 |
| 3 | Плотников | 12 |
| 4 | Кучеров | 4 |
| 5 | Малкин | 2 |
| 6 | Иванов | 13 |
Ниже представлена струтура таблицы для демонстрации примеров
| id | name | population |
|---|---|---|
| 1 | Москва | 100 |
| 2 | Нижний Новгород | 25 |
| 3 | Тверь | 22 |
| 4 | Санкт-Петербург | 80 |
| 5 | Выборг | 18 |
| 6 | Челябинск | 30 |
| 7 | Одинцово | 5 |
| 8 | Павлово | 5 |
INNER JOIN
Оператор внутреннего соединения INNER JOIN соединяет две таблицы. Порядок таблиц для оператора неважен, поскольку оператор является симметричным.
Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.
| Person.id | Person.name | Person.city_id | City.id | City.name | City.population |
|---|---|---|---|---|---|
| 1 | Колованов | 1 | 1 | Москва | 100 |
| 2 | Петров | 3 | 3 | Тверь | 22 |
| 4 | Кучеров | 4 | 4 | Санкт-Петербург | 80 |
| 5 | Малкин | 2 | 2 | Нижний Новгород | 25 |
INNER JOIN
Тело результата логически формируется следующим образом. Каждая строка одной таблицы сопоставляется с каждой строкой второй таблицы, после чего для полученной «соединённой» строки проверяется условие соединения (вычисляется предикат соединения). Если условие истинно, в таблицу-результат добавляется соответствующая «соединённая» строка.
LEFT JOIN
Возвращает все строки из левой таблицы, даже если в правой таблице нет совпадений.
| Person.id | Person.name | Person.city_id | City.id | City.name | City.population |
|---|---|---|---|---|---|
| 1 | Колованов | 1 | 1 | Москва | 100 |
| 2 | Петров | 3 | 3 | Тверь | 22 |
| 3 | Плотников | 12 | NULL | NULL | NULL |
| 4 | Кучеров | 4 | 4 | Санкт-Петербург | 80 |
| 5 | Малкин | 2 | 2 | Нижний Новгород | 25 |
| 6 | Иванов | 13 | NULL | NULL | NULL |
RIGHT JOIN
Возвращает все строки из правой таблицы, даже если в левой таблице нет совпадений.