Способы объединения таблиц sql

Команда UNION

Основные запросы

Условия выборки

Группировка

Сложение строк

Несколько таблиц

Длина строк

Изменение строк

Поиск по строкам

Работа с пробелами

Работа с регистром

Информация

Условия

Полезное

Разное

• Типы полей
• Организация базы данных
  создание правильной структуры —>
• Подзапросы
• SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop; mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price; https://habrahabr.ru/post/133781/ тут еще про переменные цикл получается SET @i = 0; SELECT * FROM product WHERE MOD(@i:=@i+1, 2) = 0; —>Переменные sql

Математические функции

Списки

Извлечение части даты

Получение даты и времени

Преобразование даты

Сложение дат

Тригонометрия

Отдельные символы

Системы счисления

Команда UNION объединяет данные из нескольких таблиц в одну при выборке.

При объединении количество столбцов во всех таблицах должно совпадать, иначе будет ошибка

Имена столбцов будут такие же, как в основной таблице, в которую добавляются данные из других таблиц.

Внимание: если не используется ключевое слово ALL для UNION, все возвращенные строки будут уникальными, так как по умолчанию подразумевается DISTINCT, который удаляет неуникальные значения.

Чтобы отменить такое поведение — нужно указать ключевое слово ALL, вот так: UNION ALL.

См. также команду JOIN, которая объединяет связанные таблицы.

Синтаксис

С удалением дублей:

Без удаления дублей:

Можно объединять не две таблицы, а три или более:

Примеры

Все примеры будут по таблицам countries и cities, если не сказано иное.

id айди	name название
1	Беларусь
2	Россия
3	Украина

id айди	name название	country_id айди страны
1	Минск	1
2	Минск	1
3	Москва	2
4	Киев	3

Пример

В данном примере объединяются записи из двух таблиц:

SQL запрос выберет следующие строки:

id айди	name название
1	Беларусь
2	Россия
3	Украина
1	Минск
2	Минск
3	Москва
4	Киев

Пример

В данном примере отсутствует ключевое слово ALL, однако дубли не будут удалены, так как дублями считается полное совпадение строк:

SQL запрос выберет следующие строки:

id айди	name название
1	Беларусь
2	Россия
3	Украина
1	Минск
2	Минск
3	Москва
4	Киев

Пример

А вот теперь дубли будут удалены (из двух Минсков останется один), так как будет иметь место полное совпадение строк (потому что поле осталось одно, но это не обязательно):

SQL запрос выберет следующие строки:

name название
Беларусь
Россия
Украина
Минск
Москва
Киев

Пример

А теперь добавим слово ALL — и дубли не будут удалятся:

SQL запрос выберет следующие строки:

name название
Беларусь
Россия
Украина
Минск
Минск
Москва
Киев

Пример

В данном примере демонстрируется работа условий WHERE в комбинации с UNION:

SQL запрос выберет следующие строки:

id айди	name имя
2	Россия
3	Украина
1	Минск
2	Минск

Пример

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

SQL запрос выберет следующие строки:

country_id айди	country_name имя
1	Беларусь
2	Россия
3	Украина
1	Минск	1
2	Минск	1
3	Москва	2
4	Киев	3

Пример

Такой запрос выдаст ошибку, так как в таблицах не совпадает количество колонок:

И такой запрос тоже выдаст ошибку в нашем случае — количество колонок в обеих таблицах не совпадает:

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

К примеру, мы хотим забрать 3 поля из второй таблицы, а в первой таблице полей только 2. Решим эту проблему создав поле с именем country_id и содержимым 0 для первой таблицы (вот так: 0 as country_id):

Источник

8 способов объединения (JOIN) таблиц в SQL. Часть 2

Полуобъединение («SEMI» JOIN)

В реляционной алгебре существует операция полуобъединения (semi join), которая, к сожалению, не имеет синтаксического представления в SQL. Если бы синтаксис для данной операции существовал, вероятно, он имел бы следующий вид: LEFT SEMI JOIN и RIGHT SEMI JOIN, аналогичный реализованному в Cloudera Impala.

Что же представляет собой операция «SEMI» JOIN? Рассмотрим следующий воображаемый запрос:

В результате данного запроса мы хотим получить всех актеров, снимавшихся в фильмах, но при этом нам не нужны сами фильмы. Более того, мы не хотим, чтобы данный актер появлялся в результате несколько раз (по одному разу на каждый фильм, в котором он играл), мы хотим получить каждого актера только один (или ноль) раз.

«Semi» – это латинское слово, обозначающее «половину». То есть данная операция реализует «половину объединения», в данном случае, левую половину.

В SQL мы можем использовать два варианта альтернативного синтаксиса, чтобы реализовать операцию «SEMI» JOIN.

Альтернативный синтаксис: EXISTS

Представленный ниже вариант является более мощным и чуть более многословным:

SELECT * FROM film_actor fa

WHERE a.actor_id = fa.actor_id

Мы извлекаем всех актеров, для которых существует (exists) фильм, то есть актеров, сыгравших хотя бы в одном в фильме. При рассмотрении данного синтаксиса (где код, реализующий «SEMI» JOIN, помещен в предложении WHERE) сразу становится очевидно, что мы можем получить в результате каждого актера максимум один раз.

Следует отметить, что в данном синтаксисе отсутствует ключевое слово JOIN. Несмотря на это, большинство СУБД способны распознать, что данный запрос выполняет именно «SEMI» JOIN, а не просто обычным образом использует предикат EXISTS(). Для примера рассмотрим план выполнения приведенного выше запроса в Oracle:

Обратите внимание, Oracle называет эту операцию «HASH JOIN (SEMI)» («SEMI» присутствует в названии).

Аналогично в PostgreSQL:

Аналогично в SQL Server:

Применение «SEMI» JOIN вместо INNER JOIN для решения поставленной задачи не только более корректно, но также обеспечивает преимущество в отношении производительности. Это объясняется тем, что после того, как найдено первое совпадение, СУБД не будет искать другие совпадения!

Альтернативный синтаксис: IN

Варианты синтаксиса на основе IN и EXISTS являются эквивалентными реализациями операции «SEMI» JOIN. Большинство СУБД (за исключением MySQL) сформируют идентичный план выполнения, как для рассмотренного выше запроса на основе EXISTS, так и для представленного ниже запроса на основе IN:

Если ваша СУБД поддерживает оба описанных выше синтаксиса, вы может выбрать любой из них, руководствуясь стилистическими соображениями.

Антиобъединение («ANTI» JOIN)

Операция «ANTI» JOIN является противоположностью операции «SEMI» JOIN. Представим себе следующий воображаемый запрос:

В результате этого запроса мы хотим получить всех актеров, которые не сыграли ни в одном фильме. К сожалению, данная операция также не имеет специального синтаксиса в SQL, но мы можем реализовать ее с помощью NOT EXISTS.

Альтернативный синтаксис: NOT EXISTS

Следующий запрос выполняет поставленную задачу:

(Опасный) альтернативный синтаксис: NOT IN

Будьте осторожны! В то время как синтаксисы на основе EXISTS и IN эквивалентны, синтаксисы на основе NOT EXISTS и NOT IN не эквивалентны. Это связано со спецификой NULL-значений.

В данном конкретном случае представленный ниже запрос на основе NOT IN даст тот же результат, что и предыдущий запрос на основе NOT EXISTS, поскольку таблица film_actor имеет ограничение NOT NULL для столбца film_actor.actor_id:

Однако если бы столбец actor_id мог содержать значения NULL, запрос оказался бы неверным. Не верите? Попробуйте выполнить следующий запрос:

Этот запрос не вернет никакого результата, поскольку NULL является неопределенным (UNKNOWN) значением в SQL. Таким образом, предикат можно переписать следующим образом:

Поскольку невозможно сказать, принадлежит ли actor_id ко множеству значений, одно из которых является неопределенным, весь предикат становится неопределенным:

Подробнее о трехзначной логике вы можете прочитать в этой статье.

Как сказал Лукас Эдер (Lukas Eder): «Никогда не используйте предикат NOT IN в SQL, за исключением тех случаев, когда вы указали в нем константные, не содержащие NULL значения».

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

(Опасный) альтернативный синтаксис: LEFT JOIN / IS NULL

Как ни странно, некоторые люди предпочитают следующий синтаксис:

Этот синтаксис является корректным, поскольку мы выполняем следующее:

• Объединяем актеров и фильмы.
• Получаем всех актеров, в том числе не сыгравших ни в одном фильме (LEFT JOIN).
• Оставляем только тех актеров, которые не сыграли ни в одном фильме (film_id IS NULL).

На мой взгляд, данный вариант синтаксиса является не очень удачным, поскольку он не выражает намерение выполнить «ANTI» JOIN. Кроме того, с большой вероятностью этот запрос будет медленнее аналогов, поскольку оптимизатор СУБД не сможет распознать, что программист хочет выполнить «ANTI» JOIN. Поэтому, вместо данного варианта опять же рекомендуется использовать NOT EXISTS.

Интересную (правда, немного устаревшую) статью, в которой сравниваются три рассмотренных варианта синтаксиса, вы можете найти здесь.

Латеральное объединение (LATERAL JOIN)

Ключевое слово LATERAL появилось в стандарте SQL относительно недавно. Это ключевое слово поддерживается в PostgreSQL и Oracle. СУБД SQL Server предоставляет альтернативный синтаксис на основе ключевого слова APPLY (который лично для меня является предпочтительным). Давайте рассмотрим пример использования ключевого слова LATERAL в PostgreSQL / Oracle:

И правда, вместо того, чтобы выполнять CROSS JOIN, объединяя все отделы со всеми днями, почему бы просто не сгенерировать необходимые дни для каждого отдела? Именно эту задачу и выполняет LATERAL. Ключевое слово LATERAL применяется в качестве префикса для правого операнда любой операции JOIN (в том числе INNER JOIN, LEFT OUTER JOIN и т.д.) и позволяет правому операнду получить доступ к столбцам левого операнда.

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

Другим распространенным практическим примером является ситуация, в которой мы хотим объединить результат «топ N» запроса с обычной таблицей. В частности, мы можем запросить каждого актера и 5 его самых кассовых фильмов:

Получим следующий результат:

Не беспокойтесь по поводу длинного списка операций JOIN, таким образом мы просто устанавливаем связь между таблицами film и payment в базе данных Sakila:

Подзапрос вычисляет 5 самых кассовых фильмов каждого актера. Таким образом, это не «классическая» производная таблица, а коррелирующий подзапрос (correlated subquery), возвращающий более чем одну строку и один столбец. Все мы привыкли писать коррелирующие подзапросы следующим образом:

Результатом представленного выше коррелирующего подзапроса является одна строка и один столбец. Если мы хотим возвратить из коррелирующего подзапроса более одной строки и/или более одного столбца, в этом нам поможет LATERAL или APPLY.

Обратите внимание, поскольку совместно с LATERAL мы использовали LEFT OUTER JOIN, для корректности синтаксиса необходимо было использовать предложение ON true. Операция OUTER JOIN с ключевым словом LATERAL всегда возвращает левый операнд JOIN, то есть мы также получаем актеров, которые не сыграли ни в одном фильме.

Свойства

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

Это справедливо даже в том случае, если правая таблица создана построчно на основе левой таблицы.

Альтернативный синтаксис: APPLY

Разработчики SQL Server не стали использовать неоднозначное ключевое слово LATERAL, а вместо этого ввели ключевое слово APPLY (в частности, CROSS APPLY и OUTER APPLY). Ключевое слово APPLY является более обоснованным, поскольку мы действительно применяем (apply) функцию к каждой строке таблицы. Давайте представим, что у нас есть функция generate_series():

Тогда мы можем использовать CROSS APPLY, чтобы вызвать эту функцию для каждого отдела:

Интересной особенностью данного синтаксиса является то, что, как уже было сказано, мы применяем функцию к каждой строке таблицы, и эта функция генерирует строки. Что это вам напоминает? В Java 8 мы можем реализовать это с помощью Stream.flatMap()! Рассмотрим следующий вариант использования потока:

Представленный выше фрагмент кода выполняет следующее:

• Таблица departments является просто Java-потоком departments.
• Мы применяем flatMap к потоку departments, используя функцию, которая генерирует кортежи для каждого отдела.
• Кортежи содержат сам отдел и день, сгенерированный из серии дней, начиная с дня создания (createdAt) отдела.

Таким образом, CROSS APPLY / CROSS JOIN LATERAL в SQL представляют собой то же самое, что и Stream.flatMap() в Java. В целом, SQL и потоки достаточно похожи. Подробнее об этом сходстве вы можете прочитать здесь.

Отметим, что мы можем применить OUTER APPLY (по аналогии с LEFT OUTER JOIN LATERAL) в том случае, когда хотим сохранить левый операнд выражения JOIN.

Объединение на основе MULTISET

Немногие СУБД поддерживают данный тип объединения (на самом деле, только Oracle), но если задуматься, это крайне полезный вариант операции JOIN, позволяющий создавать вложенные коллекции (nested collection). Если бы все СУБД поддерживали эту операцию, нам больше не понадобилось бы объектно-реляционное отображение (object-relational mapping, ORM)!

Рассмотрим гипотетический пример (на основе стандартного синтаксиса SQL, а не синтаксиса Oracle):

Оператор MULTISET принимает коррелирующий подзапрос в качестве аргумента и агрегирует все его результирующие строки во вложенную коллекцию. Эта операция работает аналогично LEFT OUTER JOIN (где мы извлекаем всех актеров, а также все их фильмы, если они снялись в каких-либо фильмах), но вместо того, чтобы дублировать актеров в результате, мы собираем их во вложенную коллекцию.

Те же действия мы выполняли бы в ORM, при извлечении данных в следующую структуру:

Не обращайте внимания на неполные JPA-аннотации, мы просто хотели продемонстрировать силу вложенных коллекций. В отличие от ORM, оператор MULTISET позволяет собирать во вложенные коллекции произвольные результаты коррелирующих подзапросов, а не только фактические сущности (entity). Это обеспечивает такие возможности, о которых ORM может только мечтать.

Альтернативный синтаксис: Oracle

Как мы уже говорили, Oracle поддерживает MULTISET, однако мы не можем создавать ad-hoc вложенные коллекции. По некоторым причинам, разработчики Oracle решили реализовать номинальную типизацию (nominal typing) для этих вложенных коллекций, а не обычную структурную типизацию (structural typing) в SQL-стиле. Поэтому мы должны объявить наши типы заранее:

Чуть более многословно, но все же решает поставленную задачу!

Альтернативный синтаксис: PostgreSQL

К сожалению PostgreSQL не поддерживает MULTISET. Однако эту проблему несложно решить с помощью массивов! Кстати, здесь мы можем использовать структурные типы! Отлично! Следующий запрос возвращает вложенный массив строк:

Результат является объектно-реляционной мечтой каждого! Мы получили вложенные записи и коллекции (и всего два столбца):

Если это вас не впечатляет, тогда что же?

Заключение

Итак, мы рассмотрели множество различных способов объединения таблиц в SQL. Надеемся, вы нашли в этой статье пару новых трюков. Впрочем, JOIN – это лишь одна из многих очень полезных операций SQL.

Источник