SQL — Урок 6. Объединение таблиц (внутреннее объединение)
Предположим, мы хотим узнать, какие темы, и какими авторами были созданы. Для этого проще всего обратиться к таблице Темы (topics):
Но, что если нам необходимо, чтобы в ответе на запрос были не идентификаторы авторов, а их имена? Вложенные запросы нам не помогут, т.к. в конечном итоге они выдают данные из одной таблицы. А нам надо получить данные из двух таблиц (Темы и Пользователи) и объединить их в одну. Запросы, которые позволяют это сделать, в SQL называются Объединениями.
Синтаксис самого простого объединения следующий:
SELECT имена_столбцов_таблицы_1, имена_столбцов_таблицы_2 FROM имя_таблицы_1, имя_таблицы_2;
Давайте создадим простое объединение:
Получилось не совсем то, что мы ожидали. Такое объединение научно называется декартовым произведением, когда каждой строке первой таблицы ставится в соответствие каждая строка второй таблицы. Возможно, бывают случаи, когда такое объединение полезно, но это явно не наш случай.
Чтобы результирующая таблица выглядела так, как мы хотели, необходимо указать условие объединения. Мы связываем наши таблицы по идентификатору автора, это и будет нашим условием. Т.е. мы укажем в запросе, что необходимо выводить только те строки, в которых значения поля id_author таблицы topics совпадают со значениями поля id_user таблицы users:
На схеме будет понятнее:
Т.е. мы в запросе сделали следующее условие: если в обеих таблицах есть одинаковые идентификаторы, то строки с этим идентификатором необходимо объединить в одну результирующую строку.
-
Если в одной из объединяемых таблиц есть строка с идентификатором, которого нет в другой объединяемой таблице, то в результирующей таблице строки с таким идентификатором не будет. В нашем примере есть пользователь Oleg (id=5), но он не создавал тем, поэтому в результате запроса его нет.
Вообще, корректный синтаксис объединения с условием выглядит так:
SELECT имя_таблицы_1.имя_столбца1_таблицы_1, имя_таблицы_1.имя_столбца2_таблицы_1, имя_таблицы_2.имя_столбца1_таблицы_2, имя_таблицы_2.имя_столбца2_таблицы_2 FROM имя_таблицы_1, имя_таблицы_2 WHERE имя_таблицы_1.имя_столбца_по_которому_объединяем = имя_таблицы_2.имя_столбца_по_которому_объединяем;
Если имя столбца уникально, то название таблицы можно опустить (как мы делали в примере), но делать это не рекомендуется.
Как вы понимаете, объединения дают возможность выбирать любую информацию из любых таблиц, причем объединяемых таблиц может быть и три, и четыре, да и условие для объединения может быть не одно.
Для примера давайте создадим запрос, который покажет нам все сообщения, к каким темам они относятся и авторов этих сообщений. Конечно, вся эта информация хранится в таблице Сообщения (posts):
Но чтобы вместо идентификаторов отображались имена и названия, нам придется сделать объединение трех таблиц:
Т.е. мы объединили таблицы Сообщения и Пользователи условием posts.id_author=users.id_user, а таблицы Сообщения и Темы — условием posts.id_topic=topics.id_topic
Объединения, которые мы сегодня рассматривали, называются Внутренними объединениями. Такие объединения связывают строки одной таблицы со строками другой таблицы (а может еще и третьей таблицы). Но бывают ситуации, когда необходимо, чтобы в результат были включены строки, не имеющие связанных. Например, когда мы создавали запрос, какие темы и какими авторами были созданы, пользователь Oleg в результирующую таблицу не попал, т.к. тем не создавал, а потому и связанной строки в объединяемой таблице не имел.
Поэтому, если нам потребуется составить несколько иной запрос — вывести всех пользователей и темы, которые они создавали, если таковые имеются — то нам придется воспользоваться Внешним объединением, позволяющим выводить все строки одной таблицы и имеющиеся связанные с ними строки из другой таблицы. О таких объединениях мы и будем говорить в следующем уроке.
Научись программировать на Python прямо сейчас!
- Научись программировать на Python прямо сейчас
- Бесплатный курс
Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.
Выборка данных из нескольких таблиц (JOIN)
Довольно часто приходится выбирать данные из нескольких таблиц. Для показа в одном запросе данных из нескольких таблиц Oracle позволяет выполнять так называемые соединения таблиц. Имеется два относящихся к соединению таблиц правила, о которых следует постоянно помнить. Данные из двух (или нескольких) таблиц могут быть объединены в том случае, если у обеих таблиц имеется совпадающий столбец (с тем же самым или с другим именем) и этот столбец в одной из таблиц является первичным ключом (или частью этого ключа).
| ENAME | DEPTNO |
|---|---|
| CLARK | 10 |
| KING | 10 |
| MILLER | 10 |
| JONES | 20 |
| FORD | 20 |
| ADAMS | 20 |
| SMITH | 20 |
| SCOTT | 20 |
| WARD | 30 |
| TURNER | 30 |
| ALLEN | 30 |
| JAMES | 30 |
| BLAKE | 30 |
| MARTIN | 30 |
| DEPTNO | DNAME |
|---|---|
| 10 | ACCOUNTING |
| 20 | RESEARCH |
| 30 | SALES |
| 40 | OPERATIONS |
Давайте рассмотрим пример оператора соединения (join), использующего традиционный синтаксис Oracle, где мы объединяем вместе содержимое таблиц emp и dept для получения перечня всех сотрудников и названий отделов, где они работают:
SELECT e.ename, e.deptno, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno; | ENAME | DEPTNO | DNAME |
|---|---|---|
| CLARK | 10 | ACCOUNTING |
| KING | 10 | ACCOUNTING |
| MILLER | 10 | ACCOUNTING |
| JONES | 20 | RESEARCH |
| FORD | 20 | RESEARCH |
| ADAMS | 20 | RESEARCH |
| SMITH | 20 | RESEARCH |
| SCOTT | 20 | RESEARCH |
| WARD | 30 | SALES |
| TURNER | 30 | SALES |
| ALLEN | 30 | SALES |
| JAMES | 30 | SALES |
| BLAKE | 30 | SALES |
| MARTIN | 30 | SALES |
Обратите внимание на многие важные компоненты этого соединения таблиц. Использование во фразе FROM двух таблиц четко указывает на то, что имеет место соединиения таблиц. Обратите также внимание на то, что перед именем каждой таблицы присутствует буква: e для таблицы emp или d для таблицы dept. Это служит иллюстрацией интересной концепции – столбцы могут иметь псевдонимы точно так же, как их имеют таблицы. Псевдонимы служат важной цели – они не дают Oracle запутаться в том, какую таблицу использовать при выводе данных в столбец deptno. Вспомните, что в обеих таблицах (emp и dept) имеются столбцы с именем deptno.
Неоднозначности при соединении таблиц можно также избежать, если в качестве префикса перед именем столбца указать имена таблиц.
SELECT emp.ename, emp.deptno, dept.dname FROM emp, dept WHERE emp.deptno = dept.deptno; Заметьте, что в нашу фразу WHERE включено сравнение по полю deptno, соединяющему данные в emp с данными в dept. В случае отсутствия этой связи в выходные данные были вы включены все данные из emp и dept.
Синтаксис соединения по ANSI/ISO
В соответствии с синтаксисом ANSI/ISO, для того, чтобы соединить содержимое двух таблиц для получения единого результата, мы должны включить в SQL-оператор фразу JOIN имятаблицы ON условиесоединения. Если вы хотите в соответствии с этим синтаксисом выполнить то же соединение таблиц, которое мы делали раньше, наш оператор будет выглядеть следующим образом:
SELECT ename, emp.deptno, dname FROM emp JOIN dept ON emp.deptno = dept.deptno; | ENAME | DEPTNO | DNAME |
|---|---|---|
| CLARK | 10 | ACCOUNTING |
| KING | 10 | ACCOUNTING |
| MILLER | 10 | ACCOUNTING |
| JONES | 20 | RESEARCH |
| FORD | 20 | RESEARCH |
| ADAMS | 20 | RESEARCH |
| SMITH | 20 | RESEARCH |
| SCOTT | 20 | RESEARCH |
| WARD | 30 | SALES |
| TURNER | 30 | SALES |
| ALLEN | 30 | SALES |
| JAMES | 30 | SALES |
| BLAKE | 30 | SALES |
| MARTIN | 30 | SALES |
Обратите внимание на различия между этим синтаксисом и синтаксисом Oracle. Во-первых, в синтаксисе ANSI/ISO сравнения, используемые для соединения, отделяются от всех остальных сравнений с помощью специального ключевого слова ON, указывающего на то, что именно это сравнение используется для соединения. Вы по-прежнему можете включать в соответствующие ANSI/ISO запросы на соединение фразу WHERE. Единственное отличие состоит в том, что фраза WHERE теперь будет содержать только дополнительные операторы сравнения, используемые дл дополнительной фильтрации данных. Кроме того, вы не должны теперь указывать во фразе FROM имена всех объединяемых таблиц. Вместо этого сразу же после фразы FROM вы должны использовать фразу JOIN, в которой и будут определены имена всех соединяемых таблиц.
Естественные соединения (NATURAL JOIN)
Естественным соединением называется соединение между двумя таблицами, в котором Oracle соединяет таблицы по одинаково называющемуся столбцу (столбцам) обеих таблиц (естественным образом!). Естественное соединение выполняется в том случае, если указано ключевое слово NATURAL.
Единственным совпадающим столбцом для таблиц emp и dept является столбец depnto,
SELECT ename, deptno, dname FROM emp NATURAL JOIN dept ORDER BY deptno; | ENAME | DEPTNO | DNAME |
|---|---|---|
| CLARK | 10 | ACCOUNTING |
| KING | 10 | ACCOUNTING |
| MILLER | 10 | ACCOUNTING |
| JONES | 20 | RESEARCH |
| FORD | 20 | RESEARCH |
| ADAMS | 20 | RESEARCH |
| SMITH | 20 | RESEARCH |
| SCOTT | 20 | RESEARCH |
| WARD | 30 | SALES |
| TURNER | 30 | SALES |
| ALLEN | 30 | SALES |
| JAMES | 30 | SALES |
| BLAKE | 30 | SALES |
| MARTIN | 30 | SALES |
Нетрудно заменить, что естественные соединения позволяют в значительной степени упростить запросы с соединением за счет устранения псевдонимов таблиц и сравнений дл соединения.
Tags: Выборка данных из нескольких таблиц, JOIN
PL/SQL
tags: Администрирование Oracle DataBase || SQL & PL/SQL
Исходные коды проекта хранятся на github. Можете заводить Issue и Discussions, при необходимости.
Чтобы задать вопрос, добавить свои знания, исправить ошибки и неточности, пишите в телеграм чате.
Соединение двух таблиц
SQL имел бы очень маленькое практическое применение, если бы позволял получать данные только из одной таблицы.
При проектировании структуры данных стараются исключать дублирование данных (см нормальные формы).
Посмотрим на таблицу городов ( city )
SELECT * FROM city | city_id | name | timezone_id |
|---|---|---|
| 1 | Москва | 2 |
| 2 | Санкт-Петербург | 2 |
| 3 | Новосибирск | 5 |
| . | . | . |
В ней нет никакой информации o часовом поясе, только его идентификатор. Чтобы получить информацию о часовом поясе, нужно соединить таблицу городов ( city ) с таблицей часовых поясов ( timezone ) по идентификатору ( timezone_id ), т.е. для каждой записи из таблицы городов нужно получить ровно одну запись из таблицы часовых поясов с соответствующим идентификатором.
Рассмотрим запрос соединения таблиц городов и часовых поясов:
SELECT * FROM city JOIN timezone ON timezone.timezone_id = city.timezone_id | city_id | name | timezone_id | timezone_id | time_offset |
|---|---|---|---|---|
| 1 | Москва | 2 | 2 | UTC+3 |
| 2 | Санкт-Петербург | 2 | 2 | UTC+3 |
| 3 | Новосибирск | 5 | 5 | UTC+6 |
| . | . | . | . | . |
SELECT * -- выбрать все поля FROM city -- из таблицы city JOIN timezone -- присоединить таблицу timezone ON timezone.timezone_id = city.timezone_id -- для каждой записи из city сопоставить запись -- из timezone с совпадающим timezone_id Обрати внимание на столбцы полученного результата, состоящего из 5 столбцов
- city_id — идентификатор города из таблицы city ;
- name — название города из таблицы city ;
- timezone_id — идентификатор часового пояса из таблицы city ;
- timezone_id — идентификатор часового пояса из таблицы timezone ;
- time_offset — время относительно UTC из таблицы timezone .
Столбец timezone_id встретился дважды в результате, потому что он есть в каждой из таблиц.
Синтаксис соединения таблиц:
SELECT список_полей FROM таблица1 JOIN таблица2 ON условия_присоединения_таблицы2 JOIN таблица3 ON условия_присоединения_таблицы3 . Как вывести данные из двух таблиц в sql
До сих пор все наши запросы обращались к одной таблице. Однако SQL позволяет в одном запросе обращаться к нескольким таблицам. Именно это свойство и сделало язык SQL столь популярным.
Полное имя столбца в таблице фактически состоит из имени таблицы, за которым идет точка и имя столбца. (По правде говоря, вначале еще указывается и имя пользователя, но это скорее относится к правам доступа и мы вернемся к этому позднее). Примеры имен:
Salespeople.snum Salespeople.city Orders.odate
До этого в запросах мы опускали имена таблиц, потому что мы запрашивали только одну таблицу. Если мы хотим связать столбцы разных таблиц, то их надо указать с именами Salespeople.city или Customers.city, чтобы сервер мог их различать.
Предположим вы хотите увидеть комбинации торговых агентов и заказчиков по городам.Это делается так:
SELECT Customers.cname, Salespeople.sname, Salespeople.city FROM Salespeople, Customers WHERE Salespeople.city = Customers.city
Результат запроса:
cname sname city -------------------- ------- ---------- ТОО Рога и копыта Иванов Москва ОАО "Валют-транзит" Егоров Караганда
т.к. поле city имеется в таблицах Торговые агенты и Заказчики, имена таблиц должны использоваться как префиксы.
Как работает этот запрос? SQL Server проверяет каждую комбинацию строк двух таблиц и проверяет их на условие указанное в предложении WHERE. Если эта комбинация удовлетворяет ему, то она выводится.
Для объединения таблиц можно использовать любые условия, а не только равенство. Например:
SELECT Salespeople.sname, Customers.cname FROM Salespeople, Customers WHERE Salespeople.sname < Customers.cname AND Customers.rating < 200
sname cname -------- ------------------ Егоров ТОО Рога и копыта Иванов ТОО Рога и копыта Петров ТОО Рога и копыта Сидоров ТОО Рога и копыта Егоров ОАО "ООО" Иванов ОАО "ООО"
В принципе это не слишком полезный запрос. Он воспроизводит все комбинации имени продавца и имени заказчика так, что первый предшествует последнему в алфавитном порядке, а заказчик имеет рейтинг меньше 200.
Предположим, что мы хотим найти все заказы заказчиков, не находящихся в одном городе с агентом. Для этого требуется связать три таблицы:
SELECT Orders.onum, Customers.cname, Orders.cnum, Orders.snum FROM Salespeople, Customers, Orders WHERE Customers.city <> Salespeople.city AND Orders.cnum = Customers.cnum AND Orders.snum = Salespeople.snum
onum cname cnum snum ----- ---------------------------------- ----- ----- 3001 ОАО "Валют-транзит" 2008 1007 3002 ОАО "ООО" 2007 1004 3005 Фирма ХХХ 2003 1002 3006 AО Бендер и К 2002 1007 3007 Концерн "Дети лейтенанта Шмидта" 2004 1002 3008 Clemens 2006 1001 3009 AО Бендер и К 2002 1003 3010 Концерн "Дети лейтенанта Шмидта" 2004 1002 3011 Clemens 2006 1001
Теперь вы можете строить запросы к нескольким таблицам одновременно. Вы можете устанавливать любые критерии отбора записей и условия связывания таблиц - собственно то ради чего и создавался SQL. Далее мы обсудим использование комбинаций запросов, где один запрос будет производить вывод, который будет управлять работой другого запроса.