Union sql: UNION (Transact-SQL) — SQL Server

SQL. Объединение таблиц. Операция UNION с примерами

Операция UNION позволяет объединить несколько результатов выборки в один набор строк. Именно объединение строк отличает эту операцию от соединения таблиц через JOIN, которая присоединяет столбцы.

Рассмотрим пример.
Сначала придумаем две таблицы: с участниками забега; с работниками забега.

Таблица «Участники»

Таблица «Работники»

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

--Сформируем первый набор из таблицы участников
select Имя_участника as Имя
from Участники
union
--Сформируем второй набор из таблицы работников
select Имя_работника
from Работники

Результат запроса:

Самый первый SELECT задает названия столбцам итоговой выборки.

Обратите внимание на то, что в вышеприведенном примере для уникальности имен не потребовалось использовать ключевое слово DISTINCT, т.к. операция UNION уникализирует строки после их объединения.
Если нужно получить все записи, в том числе одинаковые, то нужно использовать операцию UNION ALL вместо просто UNION.

Можно объединять более 2 наборов строк – просто продолжать писать «UNION SELECT …».

Количество столбцов должны совпадать во всех объединяемых наборах.
Типы данных для объединяемых столбцов должны совпадать или хотя бы иметь возможность неявно преобразовываться друг к другу. Например:

• для набора №1 первый столбец имеет строковый тип VARCHAR, второй столбец числовой тип FLOAT;
• для набора №2 первый столбец – VARCHAR, второй столбец – числовой тип INT.

Несмотря на то, что второй столбец для разных наборов отличается по типу данных, запрос все равно выполнится, т.к. INT будет преобразован к FLOAT.

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

select col1, col2, col3
from Table1
union all
select col1, col2, col3
from Table2
union all
select col1, col2, col3
from Table3
order by col1 -- Сортируем весь результат

• < Назад

Новые статьи:

• Объединение таблиц – UNION

• Соединение таблиц – операция JOIN и ее виды

• Тест на знание основ SQL

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.

Добавить комментарий

НОУ ИНТУИТ | Лекция | Выполнение сложных SQL-запросов

< Лекция 3 || Лекция 4 || Лекция 5 >

Аннотация: В лекции рассматриваются вопросы построения запросов, в которых применяется объединение.

Ключевые слова: SQL, UNION, INTERSECT, EXCEPT

Объединение запросов

Язык SQL предоставляет два способа объединения таблиц:

• intuit.ru/2010/edi»>указывая соединяемые таблицы (в том числе подзапросы) во фразе FROM оператора SELECT. Сначала выполняется соединение таблиц, а уже потом к полученному множеству применяются указанные фразой WHERE условия, определяемое фразой GROUP BY агрегирование, упорядочивание данных и т.п.;
• определяя объединение результирующих наборов, полученных при обработке оператора SELECT. В этом случае два оператора SELECT соединяются фразой UNION , INTERSECT , EXCEPT или CORRESPONDING.

UNION-объединение

Фраза UNION объединяет результаты двух запросов по следующим правилам:

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

Для того чтобы явно указать требуемый порядок сортировки, следует использовать фразу ORDER BY. При этом можно использовать как имя столбца, так и его номер (рис. 4.3).

Рис.
4.3.
Выполнение UNION-объединения с упорядочиванием результирующего набора

Фраза UNION ALL выполняет объединение двух подзапросов аналогично фразе UNION со следующими исключениями:

• совпадающие строки не удаляются из формируемого результирующего набора;
• объединяемые запросы выводятся в результирующем наборе последовательно без упорядочивания.

При объединении более двух запросов для изменения порядка выполнения операции объединения можно использовать скобки (рис. 4.4).

Рис.
4.4.
Выполнение UNION-объединения для трех запросов

INTERSECT-объединение

Фраза INTERSECT позволяет выбрать только те строки, которые присутствуют в каждом объединяемом результирующем наборе. На рис. 4.5 приведен пример объединения запросов как пересекающихся множеств.

Рис.
4.5.
Выполнение INTERSECT-объединения

EXCEPT-объединение

Фраза EXCEPT позволяет выбрать только те строки, которые присутствуют в первом объединяемом результирующем наборе, но отсутствуют во втором результирующем наборе.

Фразы INTERSECT и EXCEPT должны поддерживаться только при полном уровне соответствия стандарту SQL-92. Так, некоторые СУБД вместо фразы EXCEPT поддерживают опцию MINUS (рис. 4.6).

Рис.
4.6.
Выполнение MINUS(EXCEPT)-объединения

Как и для других типов объединения запросов, при выполнении EXCEPT -объединения совпадающие строки не входят в формируемый результирующий набор, что хорошо видно на приведенном рисунке.

Если применяется фраза INTERSECT ALL или EXCEPT ALL, то при пересечении множеств или вычитании множеств повторяемая строка удаляется столько раз из формируемого результирующего набора, сколько она повторяется в объединяемых результирующих наборах.

Фраза CORRESPONDING BY позволяет использовать в объединяемых запросах различное число столбцов: в результирующий набор будут включены только столбцы, указанные в списке. Этот список также определяет порядок включения столбцов в результирующий набор.

Дальше >>

< Лекция 3 || Лекция 4 || Лекция 5 >

SQL-инъекций UNION атак | Академия веб-безопасности

Когда приложение уязвимо для SQL-инъекций и результаты запроса возвращаются в ответах приложения, можно использовать ключевое слово UNION для извлечения данных из других таблиц в базе данных. Это приводит к атаке UNION с внедрением SQL.

Ключевое слово UNION позволяет выполнить один или несколько дополнительных запросов SELECT и добавить результаты к исходному запросу. Например:

SELECT a, b FROM table1 UNION SELECT c, d FROM table2

Этот запрос SQL вернет один набор результатов с двумя столбцами, содержащими значения из столбцов a и b в table1 и столбцов c и d в table2 .

Чтобы запрос UNION работал, необходимо выполнить два ключевых требования:

• Отдельные запросы должны возвращать одинаковое количество столбцов.
• Типы данных в каждом столбце должны быть совместимы между отдельными запросами.

Чтобы выполнить атаку UNION с внедрением SQL-кода, вам необходимо убедиться, что ваша атака соответствует этим двум требованиям. Обычно это включает в себя выяснение:

• Сколько столбцов возвращается из исходного запроса?
• Какие столбцы, возвращенные исходным запросом, имеют подходящий тип данных для хранения результатов внедренного запроса?

Определение количества столбцов, необходимых для атаки UNION с внедрением SQL

При выполнении атаки UNION с внедрением SQL-кода существует два эффективных метода определения количества столбцов, возвращаемых исходным запросом.

Первый метод включает в себя вставку серии из предложений ORDER BY и увеличение указанного индекса столбца до тех пор, пока не произойдет ошибка. Например, предположим, что точка инъекции — это строка в кавычках в пределах WHERE исходного запроса, вы должны отправить:

' ПОРЯДОК ПО 1--
ЗАКАЗАТЬ ПО 2--
ЗАКАЗАТЬ ПО 3--
и т. д.

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

Позиция ORDER BY номер 3 выходит за пределы количества элементов в списке выбора.

Приложение может фактически вернуть ошибку базы данных в своем HTTP-ответе, или общую ошибку, или просто не вернуть никаких результатов. Если вы можете обнаружить некоторую разницу в ответе приложения, вы можете сделать вывод о том, сколько столбцов возвращается из запроса.

Второй способ предполагает подачу серии из UNION SELECT полезной нагрузки, указывающей другое количество нулевых значений:

'ОБЪЕДИНЕНИЕ ВЫБЕРИТЕ NULL--
' ОБЪЕДИНЕНИЕ SELECT NULL, NULL--
' ОБЪЕДИНЕНИЕ SELECT NULL, NULL, NULL--
и т. д.

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

Все запросы, объединенные с помощью оператора UNION, INTERSECT или EXCEPT, должны иметь одинаковое количество выражений в своих целевых списках.

Опять же, приложение может на самом деле вернуть это сообщение об ошибке или может просто вернуть общую ошибку или никаких результатов. Когда количество пустых значений совпадает с количеством столбцов, база данных возвращает дополнительную строку в результирующем наборе, содержащую нулевые значения в каждом столбце. Влияние на результирующий HTTP-ответ зависит от кода приложения. Если вам повезет, вы увидите дополнительный контент в ответе, например дополнительную строку в таблице HTML. В противном случае нулевые значения могут вызвать другую ошибку, например NullPointerException . В худшем случае ответ может быть неотличим от ответа, вызванного неправильным количеством нулей, что делает этот метод определения количества столбцов неэффективным.

ЛАБОРАТОРИЯ

ПРАКТИК
SQL-инъекция UNION-атака, определяющая количество столбцов, возвращаемых запросом

Note

Дополнительные сведения о синтаксисе, специфичном для базы данных, см. в памятке по SQL-инъекциям.

Поиск столбцов с полезным типом данных в атаке UNION с внедрением SQL

Причина выполнения атаки UNION с внедрением SQL-кода заключается в том, чтобы иметь возможность получить результаты из введенного запроса. Как правило, интересные данные, которые вы хотите получить, будут в форме строки, поэтому вам нужно найти один или несколько столбцов в исходных результатах запроса, тип данных которых является или совместим со строковыми данными.

Уже определив количество необходимых столбцов, вы можете проверить каждый столбец, чтобы проверить, может ли он содержать строковые данные, отправив серию из UNION SELECT полезные данные, которые помещают строковое значение в каждый столбец по очереди. Например, если запрос возвращает четыре столбца, вы должны отправить:

' ОБЪЕДИНЕНИЕ ВЫБЕРИТЕ 'a', NULL, NULL, NULL--
' UNION SELECT NULL,'a',NULL,NULL--
' UNION SELECT NULL, NULL, 'a', NULL--
' UNION SELECT NULL,NULL,NULL,'a'--

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

Ошибка преобразования при преобразовании значения varchar 'a' в тип данных int.

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

ЛАБОРАТОРИЯ

ПРАКТИК
Атака UNION с внедрением SQL, поиск столбца, содержащего текст

Использование атаки UNION с внедрением SQL для извлечения интересных данных

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

Предположим, что:

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

В этой ситуации вы можете получить содержимое таблицы пользователей , отправив ввод:

' ОБЪЕДИНЕНИЕ ВЫБЕРИТЕ имя пользователя, пароль ОТ пользователей--

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

ЛАБОРАТОРИЯ

ПРАКТИК
Атака UNION внедрения SQL, получение данных из других таблиц

Подробнее

Изучение базы данных при атаках с внедрением SQL

В предыдущем примере предположим, что запрос возвращает только один столбец.

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

' UNION SELECT имя пользователя || '~' || пароль ОТ пользователей--

Это использует последовательность двойной трубы || , который является оператором конкатенации строк в Oracle. Введенный запрос объединяет значения полей имя пользователя и пароль , разделенные символом ~ .

Результаты запроса позволят вам прочитать все имена пользователей и пароли, например:

...
администратор~s3cure
Винер~Питер
Карлос ~ Монтойя
...

Обратите внимание, что разные базы данных используют разный синтаксис для выполнения конкатенации строк. Дополнительные сведения см. в памятке по SQL-инъекциям.

ЛАБОРАТОРИЯ

ПРАКТИК
Внедрение SQL Атака UNION, получение нескольких значений в одном столбце

DB2 — оператор SQL UNION

Используя UNION, можно указать несколько операторов SELECT, а их результаты можно объединить в
единый результирующий набор.

UNION должен состоять из двух или более операторов SELECT, разделенных ключевым словом
СОЮЗ.

Каждый запрос в UNION должен содержать одни и те же столбцы, выражения или агрегатные функции
Типы данных столбцов должны быть совместимы

Имена столбцов в результирующем наборе обычно совпадают с именами столбцов в первом операторе SELECT в UNION.

Синтаксис: UNION

ВЫБЕРИТЕ имя_столбца (ов) ИЗ таблицы 1
  СОЮЗ
ВЫБЕРИТЕ имя_столбца (ов) ИЗ таблицы2; 

UNION автоматически удаляет все повторяющиеся строки из набора результатов запроса.

Если вы хотите вернуть все вхождения всех совпадений, вы можете использовать UNION ALL.

Синтаксис: UNION ALL

ВЫБЕРИТЕ имя_столбца (ов) ИЗ таблицы 1
  СОЮЗ ВСЕХ
ВЫБЕРИТЕ имя_столбца (ов) ИЗ таблицы2; 

DB2 Database:

Let’s look at the «Orders» table data below:

Посмотрите на «Клиенты» ниже:

0249

Ченнаи

Пример 1:

Следующая инструкция SQL возвращает города (только отдельные значения) из таблиц «Заказы» и «Клиенты».

ВЫБЕРИТЕ город ИЗ заказов
  СОЮЗ ВСЕХ
ВЫБЕРИТЕ город ИЗ клиентов
  ЗАКАЗАТЬ ПО ГОРОДУ; 

Если «Заказы» или «Клиенты» имеют один и тот же город, каждый город будет указан только один раз, потому что UNION выбирает только разные значения. Используйте UNION ALL, чтобы включить повторяющиеся значения.

Result:

Если вы хотите вернуть все вхождения всех совпадений, вы можете использовать UNION ALL вместо UNION.

Пример 2:

Следующая инструкция SQL возвращает города (с дубликатами) из таблиц «Заказы» и «Клиенты».

ВЫБЕРИТЕ город ИЗ заказов
  СОЮЗ
ВЫБЕРИТЕ город ИЗ клиентов
  ЗАКАЗАТЬ ПО ГОРОДУ; 

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