Пришло время поговорить об объединении данных по средствам конструкции union и union all, так как это иногда бывает очень полезно, и без использования такой конструкции бывает порой не обойтись. Примеры будем писать в СУБД MSSQL 2008, используя язык SQL.
И начать хотелось бы с того, что мы с Вами уже рассматривали много примеров написания запросов на SQL, например, оператор select языка SQL, или использование строковых функций SQL, также рассматривали программирование как на plpgsql так и на transact-sql, например, Как написать функцию на PL/pgSQL и Transact-sql – Табличные функции и временные таблицы соответственно.
Я не просто так указал вышеперечисленные статьи, а указал я их, потому, что для более лучшего понимания и усвоения сегодняшнего урока, необходимо начальные знания (это сайт для начинающих программистов), которые как раз Вы можете получить из вышеупомянутого материала.
И так приступим. И для начала давайте рассмотрим, что же это за операторы union и union all.
Что такое UNION и UNION ALL в SQL?
- UNION – это оператор SQL для объединения результирующего набора данных нескольких запросов, и данный оператор выводит только уникальные строки в запросах, т.е. например, Вы объединяете два запроса и в каждом из которых есть одинаковые данные, другими словами полностью идентичные, и оператор union объединит их в одну строку для того чтобы не было дублей;
- UNION ALL – это оператор SQL для объединения результирующего набора данных нескольких запросов, а вот данный оператор, выведет уже абсолютно все строки, даже дубли.
Необходимые условия для операторов union и union all
- Набор полей должен быть одинаковый во всех запросах, т.е. количество полей в каждом запросе, который будет объединяться по средствам конструкции union или union all, должно быть одинаковое;
- Типы данных полей также должны совпадать в каждом запросе, т.е.
например, если Вы захотите написать один запрос, в котором будет тип данных int а во втором запросе тип данных varchar то у Вас запрос не выполнится а окно запроса выведет ошибку;
- В случае сортировки оператор order by можно указать только после последнего запроса.
Теперь давайте поговорим о том, в каких случаях нам может понадобиться использование этих операторов.
Ну, например, у Вас есть несколько баз со схожей структурой, каждая из которых создана, например, для какого-нибудь филиала, а Вам необходимо объединить эти данные для предоставления отчетности по всем филиалам руководству и самое простое как это можно сделать, это написать запросы на SQL, каждый из которых будет обращаться к разным базам, и через конструкцию union или union all объединить их. Также иногда бывает необходимо объединить данные в одной базе таким образом, что обычными объединениями это не реализовать и приходится использовать union. Почему я говорю «приходится» да потому что данная конструкция значительно увеличивает время выполнения запроса, если например данных очень много, и злоупотреблять ею не нужно.
Хватит теории, переходим к практике.
Примечание! Как уже говорилось, запросы будем писать в Management Studio для SQL Server 2008
Примеры использования union и union all
Для начала создадим две простых таблицы test_table и test_table_2
CREATE TABLE [test_table](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[number] [numeric](18, 0) NULL,
[text] [varchar](50) NULL,
CONSTRAINT [PK_test_table] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
—и вторая таблица
CREATE TABLE [test_table_2](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[number] [numeric](18, 0) NULL,
[text] [varchar](50) NULL,
CONSTRAINT [PK_test_table_2] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Они одинаковые для примера, только разные названия. Я заполнил их вот такими данными:
Теперь давайте напишем запрос, который объединит результирующие данные в одни, например через union. Синтаксис очень прост:
Запрос 1
union
Запрос 2
union
Запрос 3
и т.д.
Вот запрос:
select number, text from test_table
union
select number, text from test_table_2
Как Вы видите, вывелось всего 5 строк, так как у нас первая строка в первом запросе и первая строка во втором запросе одинаковые, поэтому они объединились.
Теперь давайте объединим через union all
Вот запрос:
select number, text from test_table
union all
select number, text from test_table_2
Здесь уже вывелись все строки, так как мы указали union all.
А теперь давайте рассмотрим, какие могут быть ошибки даже в этом простом запросе. Например, мы перепутали последовательность полей:
Или мы в первом запросе указали дополнительное поле, а во втором этого не сделали.
Также, например, при использовании order by:
Здесь мы указали сортировку в каждом запросе, а нужно было только в последнем, например:
select number, text from test_table
union all
select number, text from test_table_2
order by number
И напоследок, хотел рассказать об одной хитрости, которую можно использовать тогда когда, например, все-таки необходимо вывести в одном запросе какое-то поле, а в других его нет или просто оно не нужно, для этого можете написать вот такой запрос:
select id ,number, text from test_table
union all
select '', number, text from test_table_2
т.е. как Вы видите просто там, где должно быть поле ставить пусто и запрос отлично отработает, например:
И еще один небольшой совет, так как запросы при объединении через union довольно обширные, то лучше на их основе создать представление (Views), в случае если данный запрос Вам требуется постоянно, и уже к этому представлению обращаться каждый раз, когда требуется, а зачем нужны представления мы с Вами уже рассматривали вот здесь – Что такое представления и зачем они нужны.
Наверное, все, что я хотел рассказать о конструкции union и union all языка SQL я рассказал, если есть вопросы по использованию этих операторов, задавайте их в комментариях. Удачи!
Источник: https://info-comp.ru/obucheniest/340-sql-union-and-union-all.html
Соединение таблиц или действие оператора SQL JOIN на примерах
Оператор JOIN используется для соединения двух или нескольких таблиц. Соединение таблиц может быть внутренним (INNER) или внешним (OUTER), причем внешнее соединение может быть левым (LEFT), правым (RIGHT) или полным (FULL). Далее на примере двух таблиц рассмотрим различные варианты их соединения.
Синтаксис соединения таблиц оператором JOIN имеет вид:
FROM
[INNER]
{{LEFT | RIGHT | FULL } [OUTER]} JOIN
[ON ]
Предикат в этой конструкции определяет условие соединения строк из разных таблиц.
Допустим есть две таблицы (Auto слева и Selling справа), в каждой по четыре записи. Одна таблица содержит названия марок автомобилей (Auto), вторая количество проданных автомобилей (Selling):
id name id sum
— —- — —-
1 bmw 1 250
2 opel 5 450
3 kia 3 300
4 audi 6 400
Далее соединим эти таблицы по полю id несколькими различными способами. Совпадающие значения выделены красным для лучшего восприятия.
1. Внутреннее соединение (INNER JOIN) означает, что в результирующий набор попадут только те соединения строк двух таблиц, для которых значение предиката равно TRUE. Обычно используется для объединения записей, которые есть и в первой и во второй таблице, т. е. получения пересечения таблиц:
Красным выделена область, которую мы должны получить.
Итак, сам запрос:
SELECT * FROM 'Auto'
INNER JOIN 'Selling' ON 'Auto'.id = 'Selling'.id
И результат:
id name id sum
— —- — —-
1 bmw 1 250
3 kia 3 300
Ключевое слово INNER в запросе можно опустить.
В итоге запрос отбирает и соединяет те записи, у которых значение поля id в обоих таблицах совпадает.
2. Внешнее соединение (OUTER JOIN) бывает нескольких видов.
Первым рассмотрим полное внешнее объединение (FULL OUTER JOIN), которое объединяет записи из обоих таблиц (если условие объединения равно true) и дополняет их всеми записями из обоих таблиц, которые не имеют совпадений. Для записей, которые не имеют совпадений из другой таблицы, недостающее поле будет иметь значение NULL. Граф выборки записей будет иметь вид:
Переходим к запросу:
SELECT * FROM 'Auto'
FULL OUTER JOIN 'Selling' ON 'Auto'.id = 'Selling'.id
Результат:
id name id sum
— —- — —-
1 bmw 1 250
2 opel NULL NULL
3 kia 3 300
4 audi NULL NULL
NULL NULL 5 450
NULL NULL 6 400
То есть мы получили все записи, которые есть в обоих таблицах. Записи у которых значение поля id совпадает соединяются, а у записей для которых совпадений не найдено недостающие поля заполняются значением NULL.
Ключевое слово OUTER можно опустить.
3. Левое внешнее объединение (LEFT OUTER JOIN). В этом случае получаем все записи удовлетворяющие условию объединения, плюс все оставшиеся записи из внешней таблицы, которые не удовлетворяют условию объединения. Граф выборки:
Запрос:
SELECT * FROM 'Auto'
LEFT OUTER JOIN 'Selling' ON 'Auto'.id = 'Selling'.id
Результат:
id name id sum
— —- — —-
1 bmw 1 250
2 opel NULL NULL
3 kia 3 300
4 audi NULL NULL
Запрос также можно писать без ключевого слова OUTER.
В итоге здесь мы получили все записи таблицы Auto. Записи для которых были найдены совпадения по полю id в таблице Selling соединяются, для остальных недостающие поля заполняются значением NULL.
Еще существует правое внешнее объединение (RIGHT OUTER JOIN). Оно работает точно также как и левое объединение, только в качестве внешней таблицы будет использоваться правая (в нашем случае таблица Selling или таблица Б на графе).
Далее рассмотрим остальные возможные выборки с использованием объединения двух таблиц.
4. Получить все записи из таблицы А, которые не имеют объединения из таблицы Б. Граф:
То есть в нашем случае, нам надо получить все автомобили из таблицы Auto, которые не имеют продаж в таблице Selling.
Запрос:
SELECT * FROM 'Auto'
LEFT OUTER JOIN 'Selling' ON 'Auto'.id = 'Selling'.id
WHERE 'Selling'.id IS null
Результат:
id name id sum
— —- — —-
2 opel NULL NULL
4 audi NULL NULL
5. И последний вариант, получить все записи из таблицы А и Таблицы Б, которые не имеют объединений. Граф:
В нашем случае мы должны получить все записи из таблицы Auto, которые не связаны с таблицей Selling, и все записи из таблицы Selling, которые не имеют сопоставления из таблицы Auto.
Запрос:
SELECT * FROM 'Auto'
FULL OUTER JOIN 'Selling'
ON 'Auto'.id = 'Selling'.id
WHERE 'Auto'.id IS null
OR 'Selling'.id IS null
Результат:
id name id sum
— —- — —-
2 opel NULL NULL
4 audi NULL NULL
NULL NULL 5 450
NULL NULL 6 400
На этом все, до новых встреч на страницах блога.
Источник: https://webcodius.ru/sql/soedinenie-tablic-ili-dejstvie-operatora-sql-join-na-primerax.html
От CREATE до JOIN: введение в SQL + шпаргалка
Львиная доля мировой информации хранится в реляционных базах данных. Чтобы работать с ней, нужно владеть языком SQL-запросов.
Для решения многих стандартных задач не требуется быть SQL-виртуозом, достаточно изучить азы работы с базами:
- создание и редактирование таблиц;
- сохранение и обновление записей;
- выборка и фильтрация данных;
- индексирование полей.
Этими азами мы и займемся: разберем синтаксис SQL-запросов в теории и на реальных примерах. К счастью, язык баз данных очень похож на простые английские предложения, так что вы легко с ним справитесь.
Чтобы учиться эффективнее, сразу же закрепляйте новые знания практикой. Поиграть с SQL можно на этом замечательном ресурсе. В левой панели вы должны ввести весь код, относящийся к структуре базы данных. После этого начинайте экспериментировать с SELECT'ами в правом поле.
* В примерах используется SQL-синтаксис для MySQL 5.6. Запросы, предназначенные для разных СУБД, могут различаться.
Терминология
- База данных состоит из таблиц, а таблица – из колонок и строк.
- Каждая колонка, или поле таблицы, представляет собой конкретный вид информации, например, имя студента (строка) или зарплата сотрудника (число).
- Каждая строка, или запись таблицы, – это описание конкретного объекта, например, студента или сотрудника.
Уровень: Новичок
Создание и редактирование таблиц
CREATE
Несложно догадаться, что оператор CREATE создает новую таблицу в базе. Ему нужно передать описания всех полей таблицы в формате:
название_поля тип_данных [атрибуты_поля]
Создадим таблицу с данными о собаках и их рационе питания:
# создать таблицу dogs с 5 полями разных типов
CREATE TABLE rations (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
weight INT DEFAULT 0,
color VARCHAR(20),
portion INT
);
ALTER
Не всегда получается создать идеальную таблицу с первого раза. Не бойтесь вносить изменения, добавлять, удалять или изменять существующие поля:
# переименовать таблицу rations в portions
ALTER TABLE rations RENAME TO portions;
# добавить в таблицу portions числовое поле age
ALTER TABLE portions ADD age INT;
# удалить из таблицы portions поле color
ALTER TABLE portions DROP COLUMN color;
# переименовать поле name в dog_name
ALTER TABLE portions CHANGE name dog_name VARCHAR(20) NOT NULL;
DROP и TRUNCATE
Оператор DROP удаляет таблицу из базы целиком:
# удалить таблицу portions
DROP TABLE portions;
Если вам нужно удалить только записи, сохранив саму таблицу, воспользуйтесь оператором TRUNCATE:
# очистить таблицу portions
TRUNCATE TABLE portions;
Атрибуты и ограничения
Можно ограничить диапазон данных, которые попадают в поле, например, запретить устанавливать в качестве возраста или веса отрицательные числа.
Самые распространенные в SQL ограничения целостности (CONSTRAINTS):
- DEFAULT – устанавливает значение по умолчанию;
- AUTO_INCREMENT – автоматически инкрементирует значение поля для каждой следующей записи;
- NOT NULL – запрещает создавать запись с пустым значением поля;
- UNIQUE – следит, чтобы поле или комбинация полей оставались уникальны в пределах таблицы;
- PRIMARY KEY – UNIQUE + NOT NULL. Первичный ключ должен однозначно идентифицировать запись таблицы, поэтому он должен быть уникальным и не может оставаться пустым;
- CHECK – проверяет значение поля на соответствие некоторому условию.
Ограничения можно добавлять при создании таблицы, а затем при необходимости добавлять/изменять/удалять. Они могут действовать на одно поле или комбинацию полей.
Первичный ключ, автоматический инкремент, NOT NULL и значение по умолчанию мы уже использовали в примере с собаками.
Решим новую задачу – составление списка президентов:
# уникальная комбинация страна + имя президента
CREATE TABLE presidents (
country VARCHAR(20),
name VARCHAR(50),
age INT CHECK(age > 50),
UNIQUE(country, name)
);
Ограничение уникальности не позволит занести в таблицу одного и того же президента одной страны дважды. Кроме того, не попадут в список и слишком молодые политики.
Для добавления и удаления ограничений к существующим таблицам используйте оператор ALTER. Ограничениям можно давать имя, чтобы ссылаться на них впоследствии. Для этого предназначена конструкция CONSTRAINT.
CREATE TABLE presidents (
country VARCHAR(20),
name VARCHAR(50),
age INT
);
# добавить именованное ограничение уникальности
ALTER TABLE presidents ADD CONSTRAINT unique_president UNIQUE(country, name);
# удалить именованное ограничение
ALTER TABLE presidents DROP INDEX unique_president;
# добавить неименованное ограничение уникальности
ALTER TABLE presidents ADD UNIQUE(country, name);
# добавить проверку значения
ALTER TABLE presidents ADD CHECK (age > 50);
Еще одно удобное ограничение в SQL – внешний ключ (FOREIGN KEY). Он позволяет связать поля двух разных таблиц.
Для примера возьмем базу данных организации с таблицами сотрудников и отделов:
CREATE TABLE departments (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20)
);
# в поле departament будет храниться id одного из отделов,
# перечисленных в таблице departments
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
department INT,
salary INT,
FOREIGN KEY (department) REFERENCES departments(id)
);
Теперь в поле department таблицы employees нельзя будет указать произвольный отдел. Он обязательно должен содержаться в таблице departments.
Сохранение и обновление записей
INSERT
Добавить в таблицу новую запись (или даже сразу несколько) очень просто:
INSERT INTO portions (dog_name, weight, portion) VALUES («Jack», 25, 250);
INSERT INTO portions (dog_name, weight, portion)
VALUES («Max», 15, 180), («Charlie», 37, 350);
Вы даже можете скопировать записи из одной таблицы и вставить их в другую одним запросом. Для этого нужно скомбинировать операторы INSERT и SELECT:
CREATE TABLE dogs(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
weight INT DEFAULT 0
);
INSERT INTO dogs (name, weight) SELECT dog_name, weight FROM portions;
UPDATE
Оператор UPDATE используется для изменения существующих записей таблицы.
UPDATE employees SET salary = 0;
Вот так легким движением руки мы обнулили зарплату сразу у всех сотрудников.
Запрос можно уточнить, добавив секцию WHERE с условием отбора записей.
UPDATE employees SET salary = 0 WHERE name = «Ivan Ivanov»;
С условиями мы подробно разберемся чуть позже, когда будем говорить о выборке данных из базы.
DELETE
Можно удалить из таблицы все записи сразу или только те, которые соответствуют некоторому условию:
DELETE FROM employees;
# Ивана Иванова пора увольнять
DELETE FROM employees WHERE name = «Ivan Ivanov»;
Уровень: уверенный пользователь
Выборка и фильтрация данных
Для получения данных из базы служит оператор SELECT. В SQL есть множество способов отфильтровать именно те данные, которые вам нужны, а также отсортировать их и разбить по группам.
Вот небольшая демо-база, на которой вы можете попрактиковаться:
CREATE TABLE departments (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20)
);
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
department INT,
salary INT,
boss INT
);
INSERT INTO departments (name)
VALUES («administration»), («accounting»),
(«customer service»), («finance»),
(«legal»), («logistics»),
(«orders»), («purchasing»);
INSERT INTO employees (name, department, salary, boss)
VALUES («John Doe», 1, 40000), («Jane Smith», 1, 35000, 1),
(«Fred Brown», 1, 48000, 1), («Kevin Jones», 2, 36000),
(«Josh Taylor», 2, 22000, 4), («Alex Clark», 2, 29000, 5),
(«Branda Evans», 2, 27000, 5), («Anthony Ford», 4, 32000),
(«David Moore», 4, 29000, 8), («Scott Riley», 5, 20000),
(«Chris Gilmore», 5, 28000, 10), («Roberta Newman», 5, 33000, 11),
(«Kenny Washington», NULL, 55000);
SELECT
# получить все поля из всех записей
SELECT * FROM employees;
# получить только имена и зарплаты всех работников
SELECT name, salary FROM employees;
Можно переименовывать поля для вывода:
SELECT name as employee FROM employees;
Добавление условий:
Источник: https://proglib.io/p/sql-cheat-sheet/
MySQL. Урок 7. Явные операции соединения. JOIN
JOIN — это операция объединения данных из нескольких таблиц по некоторому условию(предикату). Например, в нашей БД library есть две таблицы books и authors.
В таблице books имеется поле author_id, являющееся внешним ключом. Оно связано с полем id таблицы authors. Вполне возможна ситуация, когда мы хотим получить какие-то данные из обеих таблиц(объединить их).
Условием объединения в этом случае будет равенство полей author_id и id этих двух полей. Вот как это можно записать:
SELECT title, price, year, authors.name
FROM books
INNER JOIN authors
ON author_id = authors.id;
| SELECT title, price, year, authors.nameON author_id = authors.id; |
Сначала в операторе SELECT мы перечисляем те поля, которые хотим получить в итоге из обеих таблиц). Допустим, что из таблицы authors нам нужно только имя автора (поле name).
В операторе FROM указывается первая таблица, а после ключевого слова JOIN — имя той таблицы, с которой мы хотим выполнить объединение. Для каждой строки проверяется условие, заданное после ключевого слова ON.
Если условие истинно, тогда объединенная строка попадет в результирующий набор. Именно так выполняется операция внутреннего (INNER JOIN) соединения.
Внешнее объединение. LEFT JOIN и RIGHT JOIN
Если алгоритм внутреннего объединения является полностью симметричным, то есть объединенные строки попадают в результирующий набор только в случае, если выполняется условия предиката.
Допустим, что таблица авторов содержит имена тех авторов, книг которых почему-то не оказалось в таблице books. При выполнении операции внутреннего соединения(INNER JOIN) эти строки в результирующий набор не попадут.
Но их вывод можно получить при помощи операции правого внешнего соединения (RIGHT OUTER JOIN).
Работает этот алгоритм следующим образом: сначала будет выполнено внутреннее объединение, и в результирующий набор попадут объединенные строки для заданного предиката.
После этого в перечень результатов дополнительно будут добавлены те записи, которые не попали во внутреннее соединение по выполнении прошлой операции.
Поля левой таблицы соответствующий данной записи получают значение NULL.
Запишем запрос:
SELECT title, price, year, authors.name
FROM books
RIGHT OUTER JOIN authors
ON author_id = authors.id;
| SELECT title, price, year, authors.nameON author_id = authors.id; |
В случае левого внешнего соединения(LEFT OUTER JOIN) результат схож, но в итоговый набор попадают те записи из левой таблицы, которые не появились после выполнения внутреннего объединения. Соответствующие поля из правой таблицы будут иметь значения NULL.
Источник: https://floppyy.ru/2016/09/08/mysql-join/
Как применять оператор SQL UNION для объединения результатов запросов
Оператор языка SQL UNION предназначен для объединения результирующих таблиц базы данных, полученных с применением слова SELECT. Условие объединения результирующих таблиц: совпадение числа, порядка следования и типа данных столбцов. ORDER BY следует применять к результату объединения и размещать только в конце составного запроса. Оператор UNION имеет следующий синтаксис:
SELECT ИМЕНА_СТОЛБЦОВ (1..N) FROM ИМЯ_ТАБЛИЦЫ UNION SELECT ИМЕНА_СТОЛБЦОВ (1..N) FROM ИМЯ_ТАБЛИЦЫ
В этой конструкции объединяемые запросы могут иметь условия в секции WHERE, а могут не иметь их. При помощи оператора UNION можно объединить запросы на извлечение данных как из одной таблицы, так и из разных.
При использовании оператора UNION без слова ALL результат не содержит дубликатов, а со словом ALL — содержит дубликаты.
Одним запросом можно вывести из таблицы индивидуальные значения столбцов, например, число лет, проработанных сотрудниками фирмы, размеры их заработной платы и другие.
Другим запросом — с использованием агрегатных функций — можно получить, например, сумму заработных плат, получаемых сотрудниками отделов или занимающих те или иные должности, или среднее число лет трудового стажа (в таких запросах применяется группировка с помощью оператора GROUP BY).
А если нам требуется получить в одной таблице и сводку всех индивидуальных значений, и итоговые значения? Здесь на помощь приходит оператор SQL UNION, с помощью которого два запроса объединяются. К результату объединения требуется применить упорядочение, используя оператор ORDER BY. Для чего это необходимо, будет лучше понятно из примеров.
Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке.
Пример 1. В базе данных фирмы есть таблица Staff, содержащая данные о сотрудниках фирмы. В ней есть столбцы Salary (размер заработной платы), Job (должность) и Years (длительность трудового стажа). Первый запрос возвращает индивидуальные размеры заработной платы, упорядоченные по должностям:
SELECT Name, Job, Salary FROM STAFF ORDER BY Job
Результатом выполнения запроса будет следующая таблица:
| Name | Job | Salary |
| Sanders | Mgr | 18357.5 |
| Marenghi | Mgr | 17506.8 |
| Pernal | Sales | 18171.2 |
| Doctor | Sales | 12322.4 |
| Factor | Sales | 16228.7 |
Второй запрос вернёт суммарную заработную плату по должностям. Мы уже готовим этот запрос для соединения с первым, поэтому будем помнить, что условием соединения является равное число столбцов, совпадение их названий, порядка следования и типов данных. Поэтому включаем в таблицу с итогами также столбец Name с произвольным значением 'Z-TOTAL':
SELECT 'Z-TOTAL' AS Name, Job, SUM(Salary) AS Salary FROM STAFF GROUP BY Job
Результатом выполнения запроса будет следующая таблица:
| Name | Job | Salary |
| Z-TOTAL | Mgr | 35864.3 |
| Z-TOTAL | Sales | 46722.3 |
Теперь объединим запросы при помощи оператора UNION и применим оператору ORDER BY к результату объединения. Группировать следует по двум столбцам: должность (Job) и имя (Name), чтобы строки с итоговыми (суммарными) значениями, в которых значение имени — 'Z-TOTAL', находились ниже строк с индивидуальными значениями. Объединение результатов запросов будет следующим:
(SELECT Name, Job, Salary FROM STAFF) UNION (SELECT 'Z-TOTAL' AS Name, Job, SUM(Salary) AS Salary FROM STAFF GROUP BY Job) ORDER BY Job, Name
Результатом выполнения запроса с оператором UNION будет следующая таблица, в которой каждая первая строка в каждой группе должностей будет содержать суммарную заработную плату сотрудников, работающих на этой должности:
| Name | Job | Salary |
| Marenghi | Mgr | 17506.8 |
| Sanders | Mgr | 18357.5 |
| Z-TOTAL | Mgr | 35864.3 |
| Doctor | Sales | 12322.4 |
| Factor | Sales | 16228.7 |
| Pernal | Sales | 18171.2 |
| Z-TOTAL | Sales | 46722.3 |
Написать запросы с использованием UNION самостоятельно, а затем посмотреть решение
Пример 2. Данные — те же, что в примере 1, но задача немного посложнее. Требуется вывести в одной таблице не только индивидуальные размеры заработной платы, упорядоченные по должностям и суммарную заработную плату по должностям, но суммарную заработную плату по всем сотрудникам.
Правильное решение.
Пример 3. В базе данных фирмы есть таблица Staff, содержащая данные о сотрудниках фирмы. В ней есть столбцы Name (фамилия), Dept (номер отдела), и Years (длительность трудового стажа).
| Name | Dept | Years |
| Sanders | 20 | 7 |
| Pernal | 20 | 8 |
| Marenghi | 38 | 5 |
| Doctor | 20 | 5 |
| Factor | 38 | 8 |
Вывести в одной таблице средний трудовой стаж по отделам и индивидуальные значения длительности трудового стажа сотрудников, сгруппированных по номерам отделов.
Правильное решение.
Другие случаи объединения запросов к одной таблице с помощью оператора SQL UNION
Пример 4. В базе данных фирмы есть таблица Staff, содержащая данные о сотрудниках фирмы. В ней есть столбцы Salary (размер заработной платы), Job (должность) и Years (длительность трудового стажа). Первый запрос нужен для получения данных о сотрудниках, заработная плата которых более 21000:
SELECT ID, Name FROM STAFF WHERE SALARY > 21000
Результатом выполнения запроса будет следующая таблица:
| ID | Name |
| 140 | Fraye |
| 160 | Molinare |
| 260 | Jones |
- Второй запрос возвращает имена сотрудников, должность которых «менеждер», а число лет трудового стажа — менее 8:
- SELECT ID, Name FROM STAFF WHERE Job = 'Mgr' AND Years < 8 ORDER BY ID
- Результатом выполнения запроса будет следующая таблица:
| ID | Name |
| 10 | Sanders |
| 30 | Marenghi |
| 100 | Plotz |
| 140 | Fraye |
| 160 | Molinare |
| 240 | Daniels |
- Теперь требуются данные, в которых объединены критерии отбора, применённые в двух запросах. Объединяем запросы при помощи оператора UNION:
- SELECT ID, Name FROM STAFF WHERE SALARY > 21000 UNION SELECT ID, Name FROM STAFF WHERE Job = 'Mgr' AND Years < 8 ORDER BY ID
- Результатом выполнения запроса с оператором UNION будет следующая таблица:
| ID | Name |
| 10 | Sanders |
| 30 | Marenghi |
| 100 | Plotz |
| 140 | Fraye |
| 160 | Molinare |
| 240 | Daniels |
| 260 | Jones |
Запрос с оператором UNION может возвращать и большее количество столбцов, важно, повторимся, чтобы в объединяемых запросах число столбцов, порядок их следования и типы данных совпадали.
Теперь работаем с базой данных «Портал объявлений — 1». Скрипт для создания этой базы данных, её таблицы и заполения таблицы данных — в файле по этой ссылке
Пример 5. Есть база данных портала объявлений.
- Пусть сначала требуется получить данные о категориях и частях категорий объявлений, в которых подано более 100 объявлений в неделю. Пишем следующий запрос:
- SELECT Category, Part, Units, Money FROM ADS WHERE Units > 100
- Результатом выполнения запроса будет следующая таблица:
| Category | Part | Units | Money |
| Транспорт | Автомашины | 110 | 17600 |
| Транспорт | Мотоциклы | 131 | 20960 |
| Электротехника | Телевизоры | 127 | 8255 |
| Электротехника | Холодильники | 137 | 8905 |
| Стройматериалы | Регипс | 112 | 11760 |
| Досуг | Музыка | 117 | 7605 |
- Теперь требуется извлечь данные о категориях и частях категорий объявлений, за которые выручено более 10000 денежных единиц в неделю. Пишем следующий запрос:
- SELECT Category, Part, Units, Money FROM ADS WHERE Money > 10000
- Результатом выполнения запроса будет следующая таблица:
| Category | Part | Units | Money |
| Транспорт | Автомашины | 110 | 17600 |
| Недвижимость | Квартиры | 89 | 18690 |
| Недвижимость | Дачи | 57 | 11970 |
| Транспорт | Мотоциклы | 131 | 20960 |
| Стройматериалы | Регипс | 112 | 11760 |
- Теперь требуется извлечь данные, которые соответствуют критериям и первого, и второго запросов. Объединяем запросы при помощи оператора UNION:
- SELECT Category, Part, Units, Money FROM ADS WHERE Units > 100 UNION SELECT Category, Part, Units, Money FROM ADS WHERE Money > 10000
- Результатом выполнения запроса будет следующая таблица:
| Транспорт | Автомашины | 110 | 17600 |
| Транспорт | Мотоциклы | 131 | 20960 |
| Недвижимость | Квартиры | 89 | 18690 |
| Недвижимость | Дачи | 57 | 11970 |
| Электротехника | Телевизоры | 127 | 8255 |
| Электротехника | Холодильники | 137 | 8905 |
| Стройматериалы | Регипс | 112 | 11760 |
| Досуг | Музыка | 117 | 7605 |
Примеры запросов к базе данных «Портал объявлений-1» есть также в уроках об операторах INSERT, UPDATE, DELETE, HAVING.
Объединение результатов запросов к двум таблицам с помощью оператора SQL UNION
До сих пор мы рассматривали запросы с оператором UNION, в которых объединялись результаты из одной таблицы. Теперь будем объединять результаты из двух таблиц.
Пример 6. Есть база данных склада строительных материалов. В ней есть таблицы, содержащая данные об обоях. Таблица Vinil содержит данные о виниловых обоях, таблица Paper — о бумажных обоях. Требуется узнать данные о ценах обоев из одной и другой таблицы.
- Чтобы извлечь не повторяющиеся данные о ценах на виниловые обои, составим запрос со словом DISTINCT:
- SELECT DISTINCT Price FROM VINIL
- Результатом выполнения запроса будет следующая таблица:
| Price |
| 400 |
| 500 |
| 530 |
| 610 |
| 720 |
| 800 |
| 850 |
- Чтобы извлечь не повторяющиеся данные о ценах на бумажные обои, составим следующий запрос, также со словом DISTINCT:
- SELECT DISTINCT Price FROM PAPER
- Результатом выполнения запроса будет следующая таблица:
| Price |
| 300 |
| 320 |
| 360 |
| 400 |
| 430 |
| 500 |
| 530 |
- Теперь составим объединённый запрос с оператором UNION:
- SELECT DISTINCT Price FROM VINIL UNION SELECT DISTINCT Price FROM PAPER
- Так как мы не используем слово ALL, дубликаты значений 400, 500 и 530 выводиться не будут. Результатом выполнения запроса будет следующая таблица:
| Price |
| 300 |
| 320 |
| 360 |
| 400 |
| 430 |
| 500 |
| 530 |
| 610 |
| 720 |
| 800 |
| 850 |
Пример 7. База данных и таблицы — те же, что и в предыдущем примере.
- Требуется получить все данные о ценах, в том числе повторяющиеся. Запрос на объединение результатов с использованием оператора UNION будет аналогичен запросу в предыдущем примере, но вместо просто UNION пишем UNION ALL:
- SELECT DISTINCT Price FROM VINIL UNION ALL SELECT DISTINCT Price FROM PAPER
- Результатом выполнения запроса будет следующая таблица:
| Price |
| 300 |
| 320 |
| 360 |
| 400 |
| 400 |
| 430 |
| 500 |
| 500 |
| 530 |
| 530 |
| 610 |
| 720 |
| 800 |
| 850 |
При помощи оператора SQL UNION можно объединить как простые запросы, так и запросы, содержащие подзапросы (вложенные запросы). Рассмотрим соответствующий пример.
Пример 8. Есть база данных «Театр». В её таблице Play содержатся данные о постановках (названия — в столбце Name), в таблице Director — даные о режиссёрах (в столбце Fname — имя, в столбце Lname — фамилия).
Первичный ключ таблицы Director — dir_id — идентификационный номер режиссёра. Dir_id также — внешний ключ таблицы Play, он ссылается на первичный ключ таблицы Director.
Требуется вывести спектакли режиссеров John Barton и Trevor Nunn.
Решение. Объединим результаты двух запросов — один возвращает спектакли режиссёра John Barton, другой — режиссёра Trevor Nunn.
А каждый из этих объединяемых запросов к таблице Play делаем с подзапросом к таблице Director, который возвращает dir_id по имени и фамилии режиссёра.
Каждый внешний запрос принимает из вложенного запроса значение ключа dir_id и возвращает названия постановок (Name):
SELECT NAME FROM PLAY WHERE dir_id = (SELECT dir_id FROM DIRECTOR WHERE fname = 'John' AND lname = 'Barton') UNION SELECT NAME FROM PLAY WHERE dir_id = (SELECT dir_id FROM DIRECTOR WHERE fname = 'Trevor' AND lname = 'Nunn')
Поделиться с друзьями
Реляционные базы данных и язык SQL
Источник: https://function-x.ru/sql_union.html
Основные операции SQL | Liblessons
Ни один крупных сайт не обходится без базы данных. Вывод статей, заголовков, ключевых слов — всё это осуществляется из базы данных. В этой статье мы рассмотрим основные операции SQL.
От разработчика требуются навыки составлять правильные запросы к базе (или базам), так как от его умения зависит быстродействие данных операций.
Основные операции SQL:
- SELECT (выборка) — берёт данные из соответствующей таблицы (таблиц) базы данных.
Выбор всех полей из таблицы (* не рекомендуется к использованию, влияет на общее время выполнения запроса):1 SELECT * FROM имя таблицы; SELECT * FROM имя таблицы; Выбор определённых полей из таблицы:
1 SELECT id, title, content FROM имя таблицы; SELECT id, title, content FROM имя таблицы;
- UPDATE
1 UPDATE `имя таблицы` SET name='Ivan' WHERE id=1 UPDATE `имя таблицы` SET name='Ivan' WHERE id=1
- INSERT — добавление значений в таблицу
1
2— NULL заполняем в обязательное к заполнению поле
INSERT INTO имя таблицы (id, name, surname) VALUES(2, 'IVAN', NULL);— NULL заполняем в обязательное к заполнению поле INSERT INTO имя таблицы (id, name, surname) VALUES(2, 'IVAN', NULL);
- DELETE
1 DELETE FROM `имя таблицы` WHERE id = 1; DELETE FROM `имя таблицы` WHERE id = 1;
-
TRUNCATE (Очистка всей таблицы)
1 TRUNCATE TABLE TABLE_NAME TRUNCATE TABLE table_name
Операторы
Можно изменить порядок полей в выборке и тогда результат также будет в другой последовательности:
| 1 | SELECT content, id, title FROM имя таблицы; |
SELECT content, id, title FROM имя таблицы;
Оператор DISTINCT убирает повторяющиеся значения в выборке:
| 1 | SELECT DISTINCT title FROM имя таблицы; |
SELECT DISTINCT title FROM имя таблицы;
Оператор WHERE добавляет ограничение на выборку:
| 1 | SELECT title FROM имя таблицы WHERE id > 2; |
SELECT title FROM имя таблицы WHERE id > 2;
Операторы сравнения:
Правило. Порядок при сравнении: цифры, латинские буквы в верхнем регистре, латинские буквы в нижнем регистре, кириллические символы! (использование не рекомендуется)
| 1 2 |
— такая запись также является верной, смотри правило выше SELECT title FROM имя таблицы WHERE title > 'текст'; |
— такая запись также является верной, смотри правило выше SELECT title FROM имя таблицы WHERE title > 'текст';
- Оператор =
- Оператор >
- Оператор =
- Оператор 100;
— сгруппировать результаты с учётом условия: максимальная цена больше 100 SELECT name, MAX(price) FROM `имя таблицы` GROUP BY name HAVING MAX(price) > 100;
Сортировки
Возможна сортировка не только по числовым данным, но и для текстовых полей (сначала цифры, потом латинские буквы, потом кириллические буквы)
- ORDER BY ASC — сортировка по возрастанию.
ASC можно не писать, подставляется по умолчанию.1 SELECT * FROM `имя таблицы` ORDER BY id ASC; SELECT * FROM `имя таблицы` ORDER BY id ASC;
- ORDER BY DESC — сортировка по убыванию
1 SELECT * FROM `имя таблицы` ORDER BY id DESC; SELECT * FROM `имя таблицы` ORDER BY id DESC;
Пример сортировки сразу по нескольким полям:
| 1 2 |
— прямая сортировка для поля id и обратная для поля name SELECT * FROM `имя таблицы` ORDER BY id, name DESC; |
— прямая сортировка для поля id и обратная для поля name SELECT * FROM `имя таблицы` ORDER BY id, name DESC;
Ограничение выборки LIMIT
| 1 2 3 4 5 6 |
— вывод первых 10 записей SELECT * FROM `имя таблицы` LIMIT 10; — первое значение с какой записи начинать (отсчёт с нуля) — второе значение — сколько взять записей SELECT * FROM `имя таблицы` LIMIT 10, 20; |
— вывод первых 10 записей SELECT * FROM `имя таблицы` LIMIT 10; — первое значение с какой записи начинать (отсчёт с нуля) — второе значение — сколько взять записей SELECT * FROM `имя таблицы` LIMIT 10, 20;
Псевдонимы MySQL
Это именование некоторого выражения каким-то своим именем и оперирование уже с этим именем. Псевдонимы используются исключительно для удобства.
| 1 2 3 4 |
SELECT name AS new_name FROM `имя таблицы`; — пример для агрегатных функций SELECT COUNT(*) AS COUNT FROM `имя таблицы`; |
SELECT name AS new_name FROM `имя таблицы`; — пример для агрегатных функций SELECT COUNT(*) AS count FROM `имя таблицы`;
Для сокращения имён таблиц используется следующая запись:
| 1 | SELECT u.name, o.price FROM users u, orders o WHERE o.user_id = u.id; |
SELECT u.name, o.price FROM users u, orders o WHERE o.user_id = u.id;
Источник: https://liblessons.ru/programming/article-in-mysql/osnovnye-operacii-sql/
Соединение и объединение таблиц в SQL: операторы JOIN,UNION, INTERSECT и EXCEPT
- Соединение таблиц в запросе SELECT выполняется с помощью оператора JOIN.
- Возможно также выполнить соединение и без оператора JOIN с помощью инструкции WHERE используя столбцы соединения, но этот синтаксис считается неявным и устаревшим.
- Выделяют следующие виды соединения, каждому из которых соответствует своя форма оператора JOIN:
- CROSS JOIN — перекрестное или декартово соединение
- [INNER] JOIN — естественное или внутреннее соединение
- LEFT [OUTER] JOIN — левое внешнее соединение
- RIGHT [OUTER] JOIN — правое внешнее соединение
- FULL [OUTER] JOIN — полное внешнее соединение
Существует также тета-соединение, самосоединение и полусоединение.
Естественное соединение
Естественное соединение — внутреннее соединение или соединение по эквивалентности.
SELECT employee.*, department.*
FROM employee INNER JOIN department
ON employee.dept_no = department.dept_no;
| SELECT employee.*, department.*FROM employee INNER JOIN departmentON employee.dept_no = department.dept_no; |
Здесь предложение FROM определяет соединяемые таблицы и в нем явно указывается тип соединения — INNER JOIN. Предложение ON является частью предложения FROM и указывает соединяемые столбцы. Выражение employee.dept_no = department.dept_no определяет условие соединения.
Эквивалентный запрос с применением неявного синтаксиса:
SELECT employee.*, department.*
FROM employee, department
WHERE employee.dept_no = department.dept_no;
| SELECT employee.*, department.*FROM employee, departmentWHERE employee.dept_no = department.dept_no; |
Соединяемые столбцы должны иметь идентичную семантику, т.е. оба столбца должны иметь одинаковое логическое значение. Соединяемые столбцы не обязательно должны иметь одинаковое имя (или даже одинаковый тип данных), хотя часто так и бывает.
Соединяются только строки имеющие одинаковое значение в соединяемых столбцах. Строки, не имеющие таких одинаковых значений в результирующий набор вообще не попадут.
В инструкции SELECT объединить можно до 64 таблиц (ограничение MS SQL), при этом один оператор JOIN соединяет только две таблицы:
SELECT emp_fname, emp_lname
FROM works_on
JOIN employee ON works_on.emp_no=employee.emp_no
JOIN department ON employee.dept_no=department.dept_no
| SELECT emp_fname, emp_lnameJOIN employee ON works_on.emp_no=employee.emp_noJOIN department ON employee.dept_no=department.dept_no |
Декартово произведение (перекрестное соединение)
Декартово произведение (перекрестное соединение) соединяет каждую строку первой таблицы с каждой строкой второй. Результатом декартово произведения первой таблицы с n строками и второй таблицы с m строками будет таблица с n × m строками.
SELECT employee.*, department.*
FROM employee CROSS JOIN department;
| SELECT employee.*, department.*FROM employee CROSS JOIN department; |
Внешнее соединение
Внешнее соединение позволяет в отличие от внутреннего извлечь не только строки с одинаковыми значениями соединяемых столбцов, но и строки без совпадений из одной или обеих таблиц.
Выделяют три вида внешних соединений:
- левое внешнее соединение — в результирующий набор попадают все строки из таблицы с левой стороны оператора сравнения (независимо от того имеются ли совпадающие строки с правой стороны), а из таблицы с правой стороны — только строки с совпадающими значениями столбцов. При этом если для строки из левой таблицы нет соответствий в правой таблице, значениям строки в правой таблице будут присвоены NULLSELECT employee_enh.*, department.location
FROM employee_enh LEFT OUTER JOIN department
ON domicile = location;SELECT employee_enh.*, department.locationFROM employee_enh LEFT OUTER JOIN department - правое внешнее соединение — аналогично левому внешнему соединению, но таблицы меняются местамиSELECT employee_enh.domicile, department.*
FROM employee_enh RIGHT OUTER JOIN department
ON domicile =location;SELECT employee_enh.domicile, department.*FROM employee_enh RIGHT OUTER JOIN department - полное внешнее соединение — композиция левого и правого внешнего соединения: результирующий набор состоит из всех строк обеих таблиц. Если для строки одной из таблиц нет соответствующей строки в другой таблице, всем ячейкам строки второй таблицы присваивается значение NULL.
Тета-соединение
Условие сравнения столбцов соединения не обязательно должно быть равенством, но может быть любым другим сравнением. Соединение, в котором используется общее условие сравнения столбцов соединения, называется тета-соединением:
SELECT emp_fname, emp_lname, domicile, location
FROM employee_enh JOIN department
ON domicile < location;
| SELECT emp_fname, emp_lname, domicile, locationFROM employee_enh JOIN department |
Самосоединение
Самосоединение — это естественное соединение таблицы с самой собой. При этом один столбец таблицы сравнивается сам с собой. Сравнивание столбца с самим собой означает, что в предложении FROM инструкции SELECT имя таблицы употребляется дважды.
Поэтому необходимо иметь возможность ссылаться на имя одной и той же таблицы дважды. Это можно осуществить, используя, по крайней мере, один псевдоним. То же самое относится и к именам столбцов в условии соединения в инструкции SELECT.
Для того чтобы различить столбцы с одинаковыми именами, необходимо использовать уточненные имена.
Полусоединение
Полусоединение похоже на естественное соединение, но возвращает только набор всех строк из одной таблицы, для которой в другой таблице есть одно или несколько совпадений.
Оператор UNION
Оператор UNION объединяет результаты двух или более запросов в один результирующий набор, в который входят все строки, принадлежащие всем запросам в объединении:
select_1 UNION [ALL] select_2 {[UNION [ALL] select_3]}…
| select_1 UNION [ALL] select_2 {[UNION [ALL] select_3]}… |
Параметры select_1, select_2, … представляют собой инструкции SELECT, которые создают объединение. Если используется параметр ALL, отображаются все строки, включая дубликаты. По умолчанию дубликаты удаляются.
Объединять с помощью инструкции UNION можно только совместимые таблицы.
Под совместимыми таблицами имеется в виду, что оба списка столбцов выборки должны содержать одинаковое число столбцов, а соответствующие столбцы должны иметь совместимые типы данных.
Результат объединения можно упорядочить, только используя предложение ORDER BY в последней инструкции SELECT. Предложения GROUP BY и HAVING можно применять с отдельными инструкциями SELECT, но не в самом объединении.
Два других оператора для работы с наборами:
- INTERSECT — пересечение — набор строк, которые принадлежат к обеим таблицам
- EXCEPT — разность двух таблиц — все значения, которые принадлежат к первой таблице и не присутствуют во второй
Источник: https://blog.rc21net.ru/sql-join-union/
Загрузка...
