This Oracle tutorial explains how to use JOINS (inner and outer) in Oracle with syntax, visual illustrations, and examples.
Description
Oracle JOINS are used to retrieve data from multiple tables. An Oracle JOIN is performed whenever two or more tables are joined in a SQL statement.
There are 4 different types of Oracle joins:
- Oracle INNER JOIN (or sometimes called simple join)
- Oracle LEFT OUTER JOIN (or sometimes called LEFT JOIN)
- Oracle RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
- Oracle FULL OUTER JOIN (or sometimes called FULL JOIN)
So let"s discuss Oracle JOIN syntax, look at visual illustrations of Oracle JOINS, and explore Oracle JOIN examples.
INNER JOIN (simple join)
Chances are, you"ve already written a statement that uses an Oracle INNER JOIN. It is the most common type of join. Oracle INNER JOINS return all rows from multiple tables where the join condition is met.
Syntax
The syntax for the INNER JOIN in Oracle/PLSQL is:
SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;
Visual Illustration
In this visual diagram, the Oracle INNER JOIN returns the shaded area:
The Oracle INNER JOIN would return the records where table1 and table2 intersect.
Example
Here is an example of an Oracle INNER JOIN:
This Oracle INNER JOIN example would return all rows from the suppliers and orders tables where there is a matching supplier_id value in both the suppliers and orders tables.
Let"s look at some data to explain how the INNER JOINS work:
We have a table called suppliers
| supplier_id | supplier_name |
|---|---|
| 10000 | IBM |
| 10001 | Hewlett Packard |
| 10002 | Microsoft |
| 10003 | NVIDIA |
We have another table called orders
| order_id | supplier_id | order_date |
|---|---|---|
| 500125 | 10000 | 2003/05/12 |
| 500126 | 10001 | 2003/05/13 |
| 500127 | 10004 | 2003/05/14 |
If we run the Oracle SELECT statement (that contains an INNER JOIN) below:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id;
| supplier_id | name | order_date |
|---|---|---|
| 10000 | IBM | 2003/05/12 |
| 10001 | Hewlett Packard | 2003/05/13 |
The rows for Microsoft and NVIDIA from the supplier table would be omitted, since the supplier_id"s 10002 and 10003 do not exist in both tables. The row for 500127 (order_id) from the orders table would be omitted, since the supplier_id 10004 does not exist in the suppliers table.
Old Syntax
As a final note, it is worth mentioning that the Oracle INNER JOIN example above could be rewritten using the older implicit syntax as follows (but we still recommend using the INNER JOIN keyword syntax):
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers, orders WHERE suppliers.supplier_id = orders.supplier_id;
LEFT OUTER JOIN
Another type of join is called an Oracle LEFT OUTER JOIN. This type of join returns all rows from the LEFT-hand table specified in the ON condition and only
Syntax
The syntax for the Oracle LEFT OUTER JOIN is:
SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
In some databases, the LEFT OUTER JOIN keywords are replaced with LEFT JOIN.
Visual Illustration
In this visual diagram, the Oracle LEFT OUTER JOIN returns the shaded area:
The Oracle LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect with table1 .
Example
Here is an example of an Oracle LEFT OUTER JOIN:
This LEFT OUTER JOIN example would return all rows from the suppliers table and only those rows from the orders table where the joined fields are equal.
Let"s look at some data to explain how LEFT OUTER JOINS work:
We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:
| supplier_id | supplier_name |
|---|---|
| 10000 | IBM |
| 10001 | Hewlett Packard |
| 10002 | Microsoft |
| 10003 | NVIDIA |
orders with three fields (order_id, supplier_id, and order_date). It contains the following data:
| order_id | supplier_id | order_date |
|---|---|---|
| 500125 | 10000 | 2003/05/12 |
| 500126 | 10001 | 2003/05/13 |
If we run the SELECT statement (that contains a LEFT OUTER JOIN) below:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers LEFT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;
Our result set would look like this:
| supplier_id | supplier_name | order_date |
|---|---|---|
| 10000 | IBM | 2003/05/12 |
| 10001 | Hewlett Packard | 2003/05/13 |
| 10002 | Microsoft | |
| 10003 | NVIDIA |
The rows for Microsoft
and NVIDIA
would be included because a LEFT OUTER JOIN was used. However, you will notice that the order_date field for those records contains a
Old Syntax
As a final note, it is worth mentioning that the LEFT OUTER JOIN example above could be rewritten using the older implicit syntax that utilizes the outer join operator (+) as follows (but we still recommend using the LEFT OUTER JOIN keyword syntax):
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers, orders WHERE suppliers.supplier_id = orders.supplier_id(+);
RIGHT OUTER JOIN
Another type of join is called an Oracle RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
Syntax
The syntax for the Oracle RIGHT OUTER JOIN is:
SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;
In some databases, the RIGHT OUTER JOIN keywords are replaced with RIGHT JOIN.
Visual Illustration
In this visual diagram, the Oracle RIGHT OUTER JOIN returns the shaded area:
The Oracle RIGHT OUTER JOIN would return the all records from table2 and only those records from table1 that intersect with table2 .
Example
Here is an example of an Oracle RIGHT OUTER JOIN:
This RIGHT OUTER JOIN example would return all rows from the orders table and only those rows from the suppliers table where the joined fields are equal.
If a supplier_id value in the orders table does not exist in the suppliers table, all fields in the suppliers table will display as
Let"s look at some data to explain how RIGHT OUTER JOINS work:
We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:
| supplier_id | supplier_name |
|---|---|
| 10000 | Apple |
| 10001 |
We have a second table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:
| order_id | supplier_id | order_date |
|---|---|---|
| 500125 | 10000 | 2013/08/12 |
| 500126 | 10001 | 2013/08/13 |
| 500127 | 10002 | 2013/08/14 |
If we run the SELECT statement (that contains a RIGHT OUTER JOIN) below:
SELECT orders.order_id, orders.order_date, suppliers.supplier_name FROM suppliers RIGHT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;
Our result set would look like this:
| order_id | order_date | supplier_name |
|---|---|---|
| 500125 | 2013/08/12 | Apple |
| 500126 | 2013/08/13 | |
| 500127 | 2013/08/14 |
The row for 500127
(order_id) would be included because a RIGHT OUTER JOIN was used. However, you will notice that the supplier_name field for that record contains a
Old Syntax
As a final note, it is worth mentioning that the RIGHT OUTER JOIN example above could be rewritten using the older implicit syntax that utilizes the outer join operator (+) as follows (but we still recommend using the RIGHT OUTER JOIN keyword syntax):
SELECT orders.order_id, orders.order_date, suppliers.supplier_name FROM suppliers, orders WHERE suppliers.supplier_id(+) = orders.supplier_id;
FULL OUTER JOIN
Another type of join is called an Oracle FULL OUTER JOIN. This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.
Syntax
The syntax for the Oracle FULL OUTER JOIN is:
SELECT columns FROM table1 FULL JOIN table2 ON table1.column = table2.column;
In some databases, the FULL OUTER JOIN keywords are replaced with FULL JOIN.
Visual Illustration
In this visual diagram, the Oracle FULL OUTER JOIN returns the shaded area:
The Oracle FULL OUTER JOIN would return the all records from both table1 and table2 .
Example
Here is an example of an Oracle FULL OUTER JOIN:
This FULL OUTER JOIN example would return all rows from the suppliers table and all rows from the orders table and whenever the join condition is not met,
If a supplier_id value in the suppliers table does not exist in the orders table, all fields in the orders table will display as
Let"s look at some data to explain how FULL OUTER JOINS work:
We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:
| supplier_id | supplier_name |
|---|---|
| 10000 | IBM |
| 10001 | Hewlett Packard |
| 10002 | Microsoft |
| 10003 | NVIDIA |
We have a second table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:
| order_id | supplier_id | order_date |
|---|---|---|
| 500125 | 10000 | 2013/08/12 |
| 500126 | 10001 | 2013/08/13 |
| 500127 | 10004 | 2013/08/14 |
If we run the SELECT statement (that contains a FULL OUTER JOIN) below:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers FULL OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;
Our result set would look like this:
| supplier_id | supplier_name | order_date |
|---|---|---|
| 10000 | IBM | 2013/08/12 |
| 10001 | Hewlett Packard | 2013/08/13 |
| 10002 | Microsoft | |
| 10003 | NVIDIA | |
| 2013/08/14 |
The rows for Microsoft
and NVIDIA
would be included because a FULL OUTER JOIN was used. However, you will notice that the order_date field for those records contains a
The row for supplier_id 10004 would be also included because a FULL OUTER JOIN was used. However, you will notice that the supplier_id and supplier_name field for those records contain a
Old Syntax
As a final note, it is worth mentioning that the FULL OUTER JOIN example above could not have been written in the old syntax without using a
Ранее мы рассмотрели применение инструкции SELECT для выборки данных из одной таблицы базы данных. Если бы возможности языка Transact-SQL ограничивались поддержкой только таких простых инструкций SELECT, то присоединение в запросе двух или больше таблиц для выборки из них данных было бы невозможно. Следственно, все данные базы данных требовалось бы хранить в одной таблице. Хотя такой подход является вполне возможным, ему присущ один значительный недостаток - хранимые таким образом данные характеризуются высокой избыточностью.
Язык Transact-SQL устраняет этот недостаток, предоставляя для этого оператор соединения JOIN , который позволяет извлекать данные более чем из одной таблицы. Этот оператор, наверное, является наиболее важным оператором для реляционных систем баз данных, поскольку благодаря ему имеется возможность распределять данные по нескольким таблицам, обеспечивая, таким образом, важное свойство систем баз данных - отсутствие избыточности данных.
Оператор UNION, который мы рассмотрели ранее, также позволяет выполнять запрос по нескольким таблицам. Но этот оператор позволяет присоединить несколько инструкций SELECT, тогда как оператор соединения JOIN соединяет несколько таблиц с использованием всего лишь одной инструкции SELECT. Кроме этого, оператор UNION объединяет строки таблиц, в то время как оператор JOIN соединяет столбцы.
Оператор соединения также можно применять с базовыми таблицами и представлениями. Оператор соединения JOIN имеет несколько разных форм. В этой статье рассматриваются следующие основные формы этого оператора:
естественное соединение;
декартово произведение или перекрестное соединение;
внешнее соединение;
тета-соединение, самосоединение и полусоединение.
Прежде чем приступить к рассмотрению разных форм соединений, в этом разделе мы рассмотрим разные варианты оператора соединения JOIN.
Две синтаксические формы реализации соединений
Для соединения таблиц можно использовать две разные синтаксические формы оператора соединения:
явный синтаксис соединения (синтаксис соединения ANSI SQL:1992);
неявный синтаксис соединения (синтаксис соединения "старого стиля").
Синтаксис соединения ANSI SQL:1992 был введен стандартом SQL92 и определяет операции соединения явно, т.е. используя соответствующее имя для каждого типа операции соединения. При явном объявлении соединения используются следующие ключевые слова:
-
LEFT JOIN;
RIGHT JOIN;
FULL JOIN.
Ключевое слово CROSS JOIN определяет декартово произведение двух таблиц. Ключевое слово INNER JOIN определяет естественное соединение двух таблиц, а LEFT OUTER JOIN и RIGHT OUTER JOIN определяют одноименные операции соединения. Наконец, ключевое слово FULL OUTER JOIN определяет соединение правого и левого внешнего соединений. Все эти операции соединения рассматриваются в последующих разделах.
Неявный синтаксис оператора соединения является синтаксисом "старого стиля", где каждая операция соединения определяется неявно посредством предложения WHERE, используя так называемые столбцы соединения.
Для операций соединения рекомендуется использовать явный синтаксис, т.к. это повышает надежность запросов. По этой причине во всех примерах далее, связанных с операциями соединения, используются формы явного синтаксиса. Но в нескольких первых примерах также будет продемонстрирован и синтаксис "старого стиля".
Естественное соединение
Термины "естественное соединение" (natural join) и "соединение по эквивалентности" (equi-join) часто используют синонимично, но между ними есть небольшое различие. Операция соединения по эквивалентности всегда имеет одну или несколько пар столбцов с идентичными значениями в каждой строке. Операция, которая устраняет такие столбцы из результатов операции соединения по эквивалентности, называется естественным соединением. Наилучшим способом объяснить естественное соединение можно посредством примера:
USE SampleDb; SELECT Employee.*, Department.* FROM Employee INNER JOIN Department ON Employee.DepartamentNumber = Department.Number;
Запрос возвращает всю информацию обо всех сотрудниках: имя и фамилию, табельный номер, а также имя, номер и местонахождение отдела, при этом для номера отдела отображаются дубликаты столбцов из разных таблиц.
В этом примере в инструкции SELECT для выборки указаны все столбцы таблиц для сотрудника Employee и отдела Department. Предложение FROM инструкции SELECT определяет соединяемые таблицы, а также явно указывает тип операции соединения - INNER JOIN . Предложение ON является частью предложения FROM и указывает соединяемые столбцы в обеих таблицах. Выражение "Employee.DepartamentNumber = Department.Number" определяет условие соединения, а оба столбца условия называются столбцами соединения .
Эквивалентный запрос с применением неявного синтаксиса ("старого стиля") будет выглядеть следующим образом:
Эта форма синтаксиса имеет два значительных различия с явной формой: список соединяемых таблиц указывается в предложении FROM, а соответствующее условие соединения указывается в предложении WHERE посредством соединяемых столбцов.
На предыдущих примерах можно проиллюстрировать принцип работы операции соединения. Но при этом следует иметь в виду, что это всего лишь представление о процессе соединения, т.к. в действительности компонент Database Engine выбирает реализацию операции соединения из нескольких возможных стратегий. Представьте себе, что каждая строка таблицы Employee соединена с каждой строкой таблицы Department. В результате получится таблица с семью столбцами (4 столбца из таблицы Employee и 3 из таблицы Department) и 21 строкой.
Далее, из этой таблицы удаляются все строки, которые не удовлетворяют условию соединения "Employee.Number = Department.Number". Оставшиеся строки представляют результат первого примера выше. Соединяемые столбцы должны иметь идентичную семантику, т.е. оба столбца должны иметь одинаковое логическое значение. Соединяемые столбцы не обязательно должны иметь одинаковое имя (или даже одинаковый тип данных), хотя часто так и бывает.
Система базы данных не может определить логическое значение столбца. Например, она не может определить, что между столбцами номера проекта и табельного номера сотрудника нет ничего общего, хотя оба они имеют целочисленный тип данных. Поэтому система базы данных может только проверить тип данных и длину строк. Компонент Database Engine требует, что соединяемые столбцы имели совместимые типы данных, например INT и SMALLINT.
База данных SampleDb содержит три пары столбцов, где каждый столбец в паре имеет одинаковое логическое значение (а также одинаковые имена). Таблицы Employee и Department можно соединить по столбцам Employee.DepartmentNumber и Department.Number. Столбцами соединения таблиц Employee и Works_on являются столбцы Employee.Id и Works_on.EmpId. Наконец, таблицы Project и Works_on можно соединить по столбцам Project.Number и Works_on.ProjectNumber.
Имена столбцов в инструкции SELECT можно уточнить. В данном контексте под уточнением имеется в виду, что во избежание неопределенности относительно того, какой таблице принадлежит столбец, в имя столбца включается имя его таблицы (или псевдоним таблицы), отделенное точкой:
table_name.column_name (имя_таблицы.имя_столбца)
В большинстве инструкций SELECT столбцы не требуют уточнения, хотя обычно рекомендуется применять уточнение столбцов с целью улучшения понимания кода. Если же имена столбцов в инструкции SELECT неоднозначны (как, например, столбцы Number в таблицах Project и Department) использование уточненных имен столбцов является обязательным.
В инструкции SELECT с операцией соединения, кроме условия соединения предложение WHERE может содержать и другие условия, как это показано в примере ниже:
USE SampleDb; -- Явный синтаксис SELECT EmpId, Project.Number, Job, EnterDate, ProjectName, Budget FROM Works_on JOIN Project ON Project.Number = Works_on.ProjectNumber WHERE ProjectName = "Gemini"; -- Старый стиль SELECT EmpId, Project.Number, Job, EnterDate, ProjectName, Budget FROM Works_on, Project WHERE Project.Number = Works_on.ProjectNumber AND ProjectName = "Gemini";
Использование уточненного имени столбца Project.Number в примере выше не является обязательным, поскольку в данном случае нет никакой двусмысленности в отношении их имен. В дальнейшем во всех примерах будет использоваться только явный синтаксис соединения.
В примере ниже показано еще одно применение внутреннего соединения:
Соединение более чем двух таблиц
Теоретически количество таблиц, которые можно соединить в инструкции SELECT, неограниченно. (Но одно условие соединения совмещает только две таблицы!) Однако для компонента Database Engine количество соединяемых таблиц в инструкции SELECT ограничено 64 таблицами.
В примере ниже показано соединение трех таблиц базы данных SampleDb:
USE SampleDb; -- Вернет единственного сотрудника "Василий Фролов" SELECT FirstName, LastName FROM Works_on JOIN Employee ON Works_on.EmpId = Employee.Id JOIN Department ON Employee.DepartamentNumber = Department.Number AND Location = "Санкт-Петербург" AND Job = "Аналитик";
В этом примере происходит выборка имен и фамилий всех аналитиков (Job = "Аналитик"), чей отдел находится в Санкт-Петербурге (Location = "Санкт-Петербург"). Результат запроса, приведенного в примере выше, можно получить только в том случае, если соединить, по крайней мере, три таблицы: Works_on, Employee и Department. Эти таблицы можно соединить, используя две пары столбцов соединения:
(Works_on.EmpId, Employee.Id) (Employee.DepartmentNumber, Department.Number)
Обратите внимание, что для осуществления естественного соединения трех таблиц используется два условия соединения, каждое из которых соединяет по две таблицы. А при соединении четырех таблиц таких условий соединения требуется три. В общем, чтобы избежать получения декартового продукта при соединении n таблиц, требуется применять n - 1 условий соединения. Конечно же, допустимо использование более чем n - 1 условий соединения, а также других условий, для того чтобы еще больше уменьшить количество элементов в результирующем наборе данных.
Декартово произведение
В предшествующем разделе мы рассмотрели возможный способ создания естественного соединения. На первом шаге этого процесса каждая строка таблицы Employee соединяется с каждой строкой таблицы Department. Эта операция называется декартовым произведением (cartesian product) . Запрос для создания соединения таблиц Employee и Department, используя декартово произведение, показан в примере ниже:
USE SampleDb; SELECT Employee.*, Department.* FROM Employee CROSS JOIN Department;
Декартово произведение соединяет каждую строку первой таблицы с каждой строкой второй. В общем, результатом декартового произведения первой таблицы с n строками и второй таблицы с m строками будет таблица с n*m строками. Таким образом, результирующий набор запроса в примере выше имеет 7 х 3 = 21 строку (эти строки содержат дублированные значения).
На практике декартово произведение применяется крайне редко. Иногда пользователи получают декартово произведение двух таблиц, когда они забывают включить условие соединения в предложении WHERE при использовании неявного синтаксиса соединения "старого стиля". В таком случае полученный результат не соответствует ожидаемому, т.к. содержит лишние строки. Наличие неожидаемо большого количества строк в результате служит признаком того, что вместо требуемого естественного соединения двух таблиц было получено декартово произведение.
Внешнее соединение
В предшествующих примерах естественного соединения, результирующий набор содержал только те строки с одной таблицы, для которых имелись соответствующие строки в другой таблице. Но иногда кроме совпадающих строк бывает необходимым извлечь из одной или обеих таблиц строки без совпадений. Такая операция называется внешним соединением (outer join) .
В примере ниже показана выборка всей информации для сотрудников, которые проживают и работают в одном и том же городе. Здесь используется таблица EmployeeEnh, которую мы создали в статье "Инструкция SELECT: расширенные возможности" при обсуждении оператора UNION.
USE SampleDb; SELECT DISTINCT EmployeeEnh.*, Department.Location FROM EmployeeEnh JOIN Department ON City = Location;
Результат выполнения этого запроса:
В этом примере получение требуемых строк осуществляется посредством естественного соединения. Если бы в этот результат потребовалось включить сотрудников, проживающих в других местах, то нужно было применить левое внешнее соединение. Данное внешнее соединение называется левым потому, что оно возвращает все строки из таблицы с левой стороны оператора сравнения, независимо от того, имеются ли совпадающие строки в таблице с правой стороны. Иными словами, данное внешнее соединение возвратит строку с левой таблицы, даже если для нее нет совпадения в правой таблице, со значением NULL соответствующего столбца для всех строк с несовпадающим значением столбца другой, правой, таблицы. Для выполнения операции левого внешнего соединения компонент Database Engine использует оператор LEFT OUTER JOIN .
Операция правого внешнего соединения аналогична левому, но возвращаются все строки таблицы с правой части выражения. Для выполнения операции правого внешнего соединения компонент Database Engine использует оператор RIGHT OUTER JOIN .
USE SampleDb; SELECT EmployeeEnh.*, Department.Location FROM EmployeeEnh LEFT OUTER JOIN Department ON City = Location;
В этом примере происходит выборка сотрудников (с включением полной информации) для таких городов, в которых сотрудники или только проживают (столбец City в таблице EmployeeEnh), или проживают и работают. Результат выполнения этого запроса:
Как можно видеть в результате выполнения запроса, когда для строки из левой таблицы (в данном случае EmployeeEnh) нет совпадающей строки в правой таблице (в данном случае Department), операция левого внешнего соединения все равно возвращает эту строку, заполняя значением NULL все ячейки соответствующего столбца для несовпадающего значения столбца правой таблицы. Применение правого внешнего соединения показано в примере ниже:
USE SampleDb; SELECT EmployeeEnh.City, Department.* FROM EmployeeEnh RIGHT OUTER JOIN Department ON City = Location;
В этом примере происходит выборка отделов (с включением полной информации о них) для таких городов, в которых сотрудники или только работают, или проживают и работают. Результат выполнения этого запроса:
Кроме левого и правого внешнего соединения, также существует полное внешнее соединение, которое является объединением левого и правого внешних соединений. Иными словами, результирующий набор такого соединения состоит из всех строк обеих таблиц. Если для строки одной из таблиц нет соответствующей строки в другой таблице, всем ячейкам строки второй таблицы присваивается значение NULL. Для выполнения операции полного внешнего соединения используется оператор FULL OUTER JOIN .
Любую операцию внешнего соединения можно эмулировать, используя оператор UNION совместно с функцией NOT EXISTS. Таким образом, запрос, показанный в примере ниже, эквивалентен запросу левого внешнего соединения, показанному ранее. В данном запросе осуществляется выборка сотрудников (с включением полной информации) для таких городов, в которых сотрудники или только проживают или проживают и работают:
Первая инструкция SELECT объединения определяет естественное соединение таблиц EmployeeEnh и Department по столбцам соединения City и Location. Эта инструкция возвращает все города для всех сотрудников, в которых сотрудники и проживают и работают. Дополнительно, вторая инструкция SELECT объединения возвращает все строки таблицы EmployeeEnh, которые не отвечают условию в естественном соединении.
Другие формы операций соединения
В предшествующих разделах мы рассмотрели наиболее важные формы соединения. Но существуют и другие формы этой операции, которые мы рассмотрим в следующих подразделах.
Тета-соединение
Условие сравнения столбцов соединения не обязательно должно быть равенством, но может быть любым другим сравнением. Соединение, в котором используется общее условие сравнения столбцов соединения, называется тета-соединением . В примере ниже показана операция тета-соединения, в которой используется условие "меньше чем". Данный запрос возвращает все комбинации информации о сотрудниках и отделах для тех случаев, когда место проживания сотрудника по алфавиту идет перед месторасположением любого отдела, в котором работает этот служащий:
USE SampleDb; SELECT FirstName, LastName, City, Location FROM EmployeeEnh JOIN Department ON City
Результат выполнения этого запроса:
В этом примере сравниваются соответствующие значения столбцов City и Location. В каждой строке результата значение столбца City сравнивается в алфавитном порядке с соответствующим значением столбца Location.
Самосоединение, или соединение таблицы самой с собой
Кроме соединения двух или больше разных таблиц, операцию естественного соединения можно применить к одной таблице. В данной операции таблица соединяется сама с собой, при этом один столбец таблицы сравнивается сам с собой. Сравнивание столбца с самим собой означает, что в предложении FROM инструкции SELECT имя таблицы употребляется дважды. Поэтому необходимо иметь возможность ссылаться на имя одной и той же таблицы дважды. Это можно осуществить, используя, по крайней мере, один псевдоним. То же самое относится и к именам столбцов в условии соединения в инструкции SELECT. Для того чтобы различить столбцы с одинаковыми именами, необходимо использовать уточненные имена.
Соединение таблицы с самой собой демонстрируется в примере ниже:
В этом примере происходит выборка всех отделов (с полной информацией), расположенных в том же самом месте, как и, по крайней мере, один другой отдел. Результат выполнения этого запроса:
Здесь предложение FROM содержит два псевдонима для таблицы Department: t1 и t2. Первое условие в предложении WHERE определят столбцы соединения, а второе - удаляет ненужные дубликаты, обеспечивая сравнение каждого отдела с другими отделами.
Полусоединение
Полусоединение похоже на естественное соединение, но возвращает только набор всех строк из одной таблицы, для которой в другой таблице есть одно или несколько совпадений. Использование полусоединения показано в примере ниже:
Результат выполнения запроса:
Как можно видеть, список выбора SELECT в полусоединении содержит только столбцы из таблицы Employee. Это и есть характерной особенностью операции полусоединения. Эта операция обычно применяется в распределенной обработке запросов, чтобы свести к минимуму объем передаваемых данных. Компонент Database Engine использует операцию полусоединения для реализации функциональности, называющейся соединением типа "звезда".
Операция соединения, как и другие бинарные операции, предназначена для обеспечения выборки данных из двух таблиц и включения этих данных в один результирующий набор. Отличительной особенностью операции соединения является следующее:
- в схему таблицы-результата входят столбцы обеих исходных таблиц (таблиц-операндов), то есть схема результата является «сцеплением» схем операндов;
- каждая строка таблицы-результата является «сцеплением» строки из одной таблицы-операнда со строкой второй таблицы-операнда.
Определение того, какие именно исходные строки войдут в результат и в каких сочетаниях, зависит от типа операции соединения и от явно заданного условия соединения . Условие соединения, то есть условие сопоставления строк исходных таблиц друг с другом, представляет собой логическое выражение (предикат).
При необходимости соединения не двух, а нескольких таблиц, операция соединения применяется несколько раз (последовательно).
Описание оператора
SELECT FIELD [ ,... n] FROM Table1 { INNER | { LEFT | RIGHT | FULL } OUTER | CROSS } JOIN Table2 ON < condition>
В большинстве СУБД при указании слов LEFT, RIGHT, FULL слово OUTER можно опустить. Слово INNER также в большинстве СУБД можно опустить.
В общем случае СУБД при выполнении соединения проверяет условие (предикат) condition . Для CROSS JOIN условие не указывается.
Для перекрёстного соединения (декартова произведения) CROSS JOIN в некоторых реализациях SQL используется оператор «запятая» (, ):
SELECT FIELD [ ,... n] FROM Table1, Table2
Виды оператора JOIN
Для дальнейших пояснений будут использоваться следующие таблицы:
Люди, проживающие в городах (таблица Person)
INNER JOIN
Оператор внутреннего соединения INNER JOIN соединяет две таблицы. Порядок таблиц для оператора неважен, поскольку оператор является симметричным .
Заголовок конкатенацией
Тело результата логически формируется следующим образом. Каждая строка одной таблицы сопоставляется с каждой строкой второй таблицы, после чего для полученной «соединённой» строки проверяется условие соединения (вычисляется предикат соединения). Если условие истинно, в таблицу-результат добавляется соответствующая «соединённая» строка.
Описанный алгоритм действий является строго логическим, то есть он лишь объясняет результат, который должен получиться при выполнении операции, но не предписывает, чтобы конкретная СУБД выполняла соединение именно указанным образом. Существует множество способов реализации операции соединения, например соединение вложенными циклами (англ. inner loops join ), соединение хэшированием (англ. hash join ), соединение слиянием (англ. merge join ). Единственное требование состоит в том, чтобы любая реализация логически давала такой же результат, как при применении описанного алгоритма.
SELECT * FROM Person INNER JOIN City ON Person. CityId = City. Id
Результат:
| Person.Name | Person.CityId | City.Id | City.Name |
|---|---|---|---|
| Андрей | 1 | 1 | Москва |
| Леонид | 2 | 2 | Санкт-Петербург |
| Сергей | 1 | 1 | Москва |
OUTER JOIN
Соединение двух таблиц, в результат которого в обязательном порядке входят строки либо одной, либо обеих таблиц.
LEFT OUTER JOIN
Оператор левого внешнего соединения LEFT OUTER JOIN соединяет две таблицы. Порядок таблиц для оператора важен, поскольку оператор не является симметричным .
Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.
Тело p .
- p .
- Затем в результат добавляются те записи левой таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие правой таблице, заполняются значениями NULL .
SELECT * FROM Person LEFT OUTER JOIN City ON Person. CityId = City. Id
Результат:
| Person.Name | Person.CityId | City.Id | City.Name |
|---|---|---|---|
| Андрей | 1 | 1 | Москва |
| Леонид | 2 | 2 | Санкт-Петербург |
| Сергей | 1 | 1 | Москва |
| Григорий | 4 | NULL | NULL |
RIGHT OUTER JOIN
Оператор правого внешнего соединения RIGHT OUTER JOIN соединяет две таблицы. Порядок таблиц для оператора важен, поскольку оператор не является симметричным .
Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.
Тело результата логически формируется следующим образом. Пусть выполняется соединение левой и правой таблиц по предикату (условию) p .
- В результат включается внутреннее соединение (INNER JOIN) левой и правой таблиц по предикату p .
- Затем в результат добавляются те записи правой таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие левой таблице, заполняются значениями NULL .
SELECT * FROM Person RIGHT OUTER JOIN City ON Person. CityId = City. Id
Результат:
| Person.Name | Person.CityId | City.Id | City.Name |
|---|---|---|---|
| Андрей | 1 | 1 | Москва |
| Сергей | 1 | 1 | Москва |
| Леонид | 2 | 2 | Санкт-Петербург |
| NULL | NULL | 3 | Казань |
FULL OUTER JOIN
Оператор полного внешнего соединения FULL OUTER JOIN соединяет две таблицы. Порядок таблиц для оператора неважен, поскольку оператор является симметричным .
Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.
Тело результата логически формируется следующим образом. Пусть выполняется соединение первой и второй таблиц по предикату (условию) p . Слова «первой» и «второй» здесь не обозначают порядок в записи (который неважен), а используются лишь для различения таблиц.
- В результат включается внутреннее соединение (INNER JOIN) первой и второй таблиц по предикату p .
- В результат добавляются те записи первой таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие второй таблице, заполняются значениями NULL .
- В результат добавляются те записи второй таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие первой таблице, заполняются значениями NULL .
SELECT * FROM Person FULL OUTER JOIN City ON Person. CityId = City. Id
Результат:
| Person.Name | Person.CityId | City.Id | City.Name |
|---|---|---|---|
| Андрей | 1 | 1 | Москва |
| Сергей | 1 | 1 | Москва |
| Леонид | 2 | 2 | Санкт-Петербург |
| NULL | NULL | 3 | Казань |
| Григорий | 4 | NULL | NULL |
CROSS JOIN
Оператор перекрёстного соединения , или декартова произведения CROSS JOIN соединяет две таблицы. Порядок таблиц для оператора неважен, поскольку оператор является симметричным .
Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.
Тело результата логически формируется следующим образом. Каждая строка одной таблицы соединяется с каждой строкой второй таблицы, давая тем самым в результате все возможные сочетания строк двух таблиц.
SELECT * FROM Person CROSS JOIN City
SELECT * FROM Person, City
Результат:
| Person.Name | Person.CityId | City.Id | City.Name |
|---|---|---|---|
| Андрей | 1 | 1 | Москва |
| Андрей | 1 | 2 | Санкт-Петербург |
| Андрей | 1 | 3 | Казань |
| Леонид | 2 | 1 | Москва |
| Леонид | 2 | 2 | Санкт-Петербург |
| Леонид | 2 | 3 | Казань |
| Сергей | 1 | 1 | Москва |
| Сергей | 1 | 2 | Санкт-Петербург |
| Сергей | 1 | 3 | Казань |
| Григорий | 4 | 1 | Москва |
| Григорий | 4 | 2 | Санкт-Петербург |
| Григорий | 4 | 3 | Казань |
Если в предложении WHERE добавить условие соединения, то есть ограничения на сочетания кортежей, то результат эквивалентен операции INNER JOIN с таким же условием:
Таким образом, CROSS JOIN + WHERE(предикат ) и INNER JOIN(предикат ) синтаксически являются альтернативными формами записи одной и той же логической операции внутреннего соединения. Синтаксис CROSS JOIN + WHERE для операции соединения называют устаревшим, поскольку его не рекомендует стандарт SQL ANSI.
Примечания
Ссылки
- Описание видов Join в Oracle (рус.)
- Jeff Atwood, A Visual Explanation of SQL Joins (англ.)
| SQL | |
|---|---|
| Версии | SQL-86 SQL-89 SQL-92 SQL:1999 SQL:2003 SQL:2008 |
| Ключевые слова | Create Delete From Having Insert Join Merge Null Order by Prepare Select Top Truncate Union Update |
| Related | Эдгар Кодд Реляционная база данных |
В Oracle поддерживается несколько типов соединений, отличающихся способом, которым производится объединение строк из двух или более таблиц или представлений. В этой заметке моего блога будут описаны типы соединений, применяемые в Oracle наиболее часто.
Эквисоединение
При эквисоединении (equi-join) две или более таблиц соединяются на основании условия равенства между столбцами. Другими словами, один и тот же столбец имеет одинаковое значение во всех соединяемых таблицах. Ниже приведен пример применения эквисоединения:
SQL> SELECT e.last_name, d.dept FROM emp e, dept d WHERE e.emp_id = d.emp_id;
Для показанного выше оператора соединения также можно использовать и следующий новый синтаксис:
SQL> SELECT e.last_name, d.dept FROM emp e JOIN dept d USING (emp_id);
При желании соединить несколько столбцов, можно перечислить их имена в виде разделенного запятыми списка, например: USING (dept_id , emp_name).
Естественное соединение
Естественным соединением (natural join) называется эквисоединение, при котором столбцы, которые должны сопоставляться для выполнения соединения, специально не указываются. Oracle автоматически определяет подлежащие соединению столбцы на основании совпадающих столбцов в двух таблицах. Ниже приведен пример применения естественного соединения:
SQL> SELECT e.last_name, d.dept FROM emp e NATURAL JOIN dept d;
В этом примере условием для выполнения соединения служит наличие идентичных значений в столбце last_name в таблицах emp и dept .
Рефлексивное соединение
Под рефлексивным соединением (self join) подразумевается соединение таблицы с самой собой за счет использования псевдонимов. В следующем примере осуществляется соединение таблицы employees с самой собой при помощи псевдонима с удалением всех дублированных строк.
SQL> DELETE FROM employees X WHERE ROWID > 2 (select MIN(rowid) FROM employees Y 3 where X.key_values = Y.key_values);
Внутреннее соединение
Внутреннее соединение (inner join), также называемое простым соединением (simple join), предусматривает возврат всех строк, которые удовлетворяют указанному условию соединения. Раньше в синтаксисе внутреннего соединения для указания того, каким образом должны соединяться таблицы, нужно было использовать конструкцию WHERE , например, так:
SQL> SELECT e.flast_name, d.dept FROM emp e, dept d WHERE e.emp_id = d.emp_id;
Теперь Oracle позволяет задавать критерии соединения в синтаксисе внутреннего (или простого) соединения за счет применения новой конструкции ON или USING , например:
SQL> SELECT DISTINCT NVL(dname, "No Dept"), COUNT(empno) nbr_emps FROM emp JOIN DEPT ON emp.deptno = dept.deptno WHERE emp.job IN ("MANAGER", "SALESMAN", "ANALYST") GROUP BY dname;
Внешнее соединение
Внешнее соединение (outer join) применяется для возврата всех строк, которые удовлетворяют указанному условию соединения, плюс некоторых или всех строк из таблицы, в которой нет подходящих строк, удовлетворяющих указанному условию соединения. Существуют три вида внешнего соединения: левое внешнее соединение (left outer join), правое внешнее соединение (right outer join) и полное внешнее соединение (full outer join). В операторе полного внешнего соединения слово OUTER обычно опускается.
Разработка любой базы данных подразумевает не только создание и наполнение таблиц разнообразной информацией, но и дальнейшую работу с данными. Для корректного выполнения разнообразных задач по выбору данных из таблиц и формированию отчетов, используется стандартная конструкция Select.
Выборки данных из таблиц
Если рассматривать задачу выбора данных или построения некоторого отчета, можно определить уровень сложности данной операции. Как правило, при работе с серьезными (по объему информации) базами данных, которые формируются, например, в интернет-магазинах или крупных компаниях, выборка данных не будет ограничиваться лишь одной таблицей. Как правило, выборки могут быть из довольно большого количества не только связанных между собой таблиц, но и вложенных запросов/подзапросов, которые составляет сам программист, в зависимости от поставленной перед ним задачи. Для выборки из одной таблицы можно использовать простейшую конструкцию:
| Select * from Person |
где Person - имя таблицы, из которой необходимо сделать выборку данных.
Если же будет необходимость выбрать данные из нескольких таблиц, можно использовать одну из стандартных конструкций для объединения нескольких таблиц.
Способы подключения дополнительных таблиц
Если рассматривать использование такого рода конструкций на начальном уровне, то можно выделить следующие механизмы подключения необходимого количества таблиц для выборки, а именно:
- Оператор Inner Join.
- Left Join или, это второй способ записи, Left Outer Join.
- Cross Join.
- Full Join.
Использование операторов объединения таблиц на практике можно усвоить, рассмотрев применение оператора SQL - Inner Join. Пример его использования будет выглядеть следующим образом:
Select * from Person |
Язык SQL и оператор Join Inner Join можно использовать не только для объединения двух и более таблиц, но и для подключения иных подзапросов, что значительно облегчает работу администраторов базы данных и, как правило, может значительно ускорить выполнение определенных, сложных по структуре запросов.
Объединение данных в таблицах построчно
Если рассматривать подключение большого количества подзапросов и сборку данных в единую таблицу строка за строкой, то можно использовать также операторы Union, и Union All.
Применение этих конструкций будет зависеть от поставленной перед разработчиком задачи и результата, которого он хочет достичь в итоге.
Описание оператора Inner Join
В большинстве случаев для объединения нескольких таблиц в языке SQL используется оператор Inner Join. Описание Inner Join в SQL довольно простое для понимания среднестатистического программиста, который только начинает разбираться в базах данных. Если рассмотреть описание механизма работы этой конструкции, то получим следующую картину. Логика оператора в целом построена на возможности пересечения и выборки только тех данных, которые есть в каждой из входящих в запрос таблиц.
Если рассмотреть такую работу с точки зрения графической интерпретации, то получим структуру оператора SQL Inner Join, пример которой можно показать с помощью следующей схемы:
К примеру, мы имеем две таблицы, схема которых показана на рисунке. Они в свою очередь, имеют разное количество записей. В каждой из таблиц есть поля, которые связаны между собой. Если попытаться пояснить работу оператора исходя из рисунка, то возвращаемый результат будет в виде набора записей из двух таблиц, где номера связанных между собой полей совпадают. Проще говоря, запрос вернет только те записи (из таблицы номер два), данные о которых есть в таблице номер один.
Синтаксис оператора Inner Join
Как уже говорилось ранее, оператор Inner Join, а именно его синтаксис, необычайно прост. Для организации связей между таблицами в пределах одной выборки достаточно будет запомнить и использовать следующую принципиальную схему построения оператора, которая прописывается в одну строчку программного SQL-кода, а именно:
- Inner Join [Имя таблицы] on [ключевое поле из таблицы, к которой подключаем] = [Ключевому полю подключаемой таблицы].
Для связи в данном операторе используются главные ключи таблиц. Как правило, в группе таблиц, которые хранят информацию о сотрудниках, ранее описанные Person и Subdivision имеют хотя бы по одной похожей записи. Итак, рассмотрим подробнее Inner Join, пример которого был показан несколько ранее.
Пример и описание подключения к выборке одной таблицы
У нас есть таблица Person, где хранится информация обо всех сотрудниках, работающих в компании. Сразу отметим, что главным ключем данной таблицы является поле - Pe_ID. Как раз по нему и будет идти связка.
Вторая таблица Subdivision будет хранить информацию о подразделениях, в которых работают сотрудники. Она, в свою очередь, связана с помощью поля Su_Person с таблицей Person. О чем это говорит? Исходя из схемы данных можно сказать, что в таблице подразделений для каждой записи из таблицы «Сотрудники» будет информация об отделе, в котором они работают. Именно по этой связи и будет работать оператор Inner Join.
Для более понятного использования рассмотрим оператор SQL Inner Join (примеры его использования для одной и двух таблиц). Если рассматривать пример для одной таблицы, то тут все довольно просто:
Select * from Person Inner join Subdivision on Su_Person = Pe_ID |
Пример подключения двух таблиц и подзапроса
Оператор SQL Inner Join, примеры использования которого для выборки данных из нескольких таблиц можно организовать вышеуказанным образом, работает по чуть усложненному принципу. Для двух таблиц усложним задачу. Скажем, у нас есть таблица Depart, в которой хранится информация обо всех отделах в каждом из подразделений. В в эту таблицу записан номер подразделения и номер сотрудника и нужно дополнить выборку данных названием каждого отдела. Забегая вперед, стоит сказать, что для решения этой задачи можно воспользоваться двумя методами.
Первый способ заключается в подключении таблицы отделов к выборке. Организовать запрос в этом случае можно таким образом:
Стоит отметить, что такая конструкция не всегда может ускорить работу запроса. Иногда бывают случаи, когда приходится использовать дополнительно выборку данных во временную таблицу (если их объем слишком большой), а потом ее объединять с основной выборкой.
Пример использования оператора Inner Join для выборок из большого количества таблиц
Построение сложных запросов подразумевает использование для выборки данных значительного количества таблиц и подзапросов, связанных между собой. Этим требованиям может удовлетворить SQL Inner Join синтаксис. Примеры использования оператора в данном случаем могут усложняться не только выборками из многих мест хранения данных, но и с большого количества вложенных подзапросов. Для конкретного примера можно взять выборку данных из системных таблиц (оператор Inner Join SQL). Пример - 3 таблицы - в этом случае будет иметь довольно сложную структуру.
В данном случае подключено (к основной таблице) еще три дополнительно и введено несколько условий выбора данных.
При использовании оператора Inner Join стоит помнить о том, что чем сложнее запрос, тем дольше он будет реализовываться, поэтому стоит искать пути более быстрого выполнения и решения поставленной задачи.
Заключение
В итоге хотелось бы сказать одно: работа с базами данных - это не самое сложное, что есть в программировании, поэтому при желании абсолютно каждый человек сможет овладеть знаниями по построению баз данных, а со временем, набравшись опыта, получится работать с ними на профессиональном уровне.