Поддерживаемые Oracle типы соединений в SQL: JOIN и другие. Предложение JOIN советы и хитрости программирования Full join sql описание
Это учебное пособие Oracle объясняет, как использовать JOINS (INNER и OUTER)
в Oracle с синтаксисом и примерами.
Описание
Oracle JOINS используются для извлечения данных из нескольких таблиц. JOIN выполняется всякий раз, когда две или более таблиц объединяются в SQL предложении.
Есть 4 различных типа присоединения Oracle:
Рассмотрим синтаксис Oracle JOIN, а также изучим примеры Oracle JOIN.
INNER JOIN (простое соединение)
Скорее всего, вы уже писали запросы в которых используются Oracle INNER JOIN. Это наиболее распространенный тип соединения. Oracle INNER JOINS возвращает все строки из нескольких таблиц, где выполняется условия соединения.
Синтаксис
Синтаксис INNER JOIN в Oracle/PLSQL:
SELECT columns
FROM table1
INNER JOIN table2
В этом рисунке, Oracle INNER JOIN возвращает затененную область:
Oracle INNER JOIN будет возвращать записи, где table1 и table2 будут пересекаться.
Пример
Ниже приведен пример Oracle INNER JOIN:
Oracle PL/SQL
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers INNER JOIN orders |
Этот пример Oracle INNER JOIN возвращает все строки из таблиц suppliers и orders , где имеются соответствующие значение поля supplier_id в обоих таблицах.
Рассмотрим некоторые данные, чтобы понять, как работает INNER JOIN:
Если мы выполним Oracle оператор SELECT (который содержит INNER JOIN) ниже:
Oracle PL/SQL
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id;
Строки для Microsoft и NVIDIA из таблицы suppliers будут опущены, так как значения supplier_id 10002 и 10003 не существует в обеих таблицах. Строка order_id 500127 из таблицы orders будет опущена, так как supplier_id 10004 не существует в таблице suppliers .
Старый Синтаксис
В качестве последнего примечания, стоит отметить, что приведенный выше пример Oracle INNER JOIN можно переписать, используя старый неявный синтаксис следующим образом (но рекомендуется использовать синтаксис INNER JOIN):
Другой тип соединения называется Oracle LEFT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с левосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны.
Синтаксис
Синтаксис для Oracle LEFT OUTER JOIN:
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
В некоторых базах данных LEFT OUTER JOIN заменяется на LEFT JOIN.
На этом рисунке, Oracle LEFT OUTER JOIN возвращает затененную область:
Oracle LEFT OUTER JOIN возвратит все записи из table1 и только те записи из table2 , которые пересекаются с table1 .
Пример
Oracle PL/SQL
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers LEFT OUTER JOIN orders ON suppliers.supplier_id=orders.supplier_id; |
Этот пример LEFT OUTER JOIN возвратит все строки из таблицы suppliers , и только те строки из таблицы orders , где объединяемые поля равны.
Если значение supplier_id в таблице suppliers не существует в таблице orders , все поля таблицы orders будут отображаться в результирующем наборе как NULL.
Рассмотрим некоторые данные, чтобы понять, как работает LEFT OUTER JOIN:
У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name ) которая содержит следующие данные:
Если мы выполним Oracle оператор SELECT (который содержит LEFT OUTER JOIN) ниже:
Oracle PL/SQL
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers LEFT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;
Строки для Microsoft и NVIDIA будут включены, так как был использован LEFT OUTER JOIN. Тем не менее, вы заметите, что поле order_date для этих записей содержит значение NULL.
Другой тип соединения называется Oracle RIGHT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с правосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны.
Синтаксис
Синтаксис Oracle RIGHT OUTER JOIN:
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;
В некоторых базах данных, RIGHT OUTER JOIN заменяется на RIGHT JOIN.
На этом рисунке, Oracle RIGHT OUTER JOIN возвращает затененную область:
Oracle RIGHT OUTER JOIN возвратит все записи из table2 и только те записи из table1 , которые пересекаются с table2 .
Пример
Ниже приведен пример Oracle RIGHT OUTER JOIN:
Oracle PL/SQL
SELECT orders.order_id, orders.order_date, suppliers.supplier_name FROM suppliers RIGHT OUTER JOIN orders ON suppliers.supplier_id=orders.supplier_id; |
Этот пример RIGHT OUTER JOIN возвращает все строки из таблицы orders и только те строки из таблицы suppliers , где объединяемые поля равны.
Если значение supplier_id в таблице orders не существует в таблице suppliers , все поля в таблице suppliers будут отображаться в результирующем наборе как NULL.
Рассмотрим некоторые данные, чтобы понять, как работает RIGHT OUTER JOIN:
У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name ) которая содержит следующие данные:
Если мы выполним Oracle оператор SELECT (который содержит RIGHT OUTER JOIN) ниже:
Oracle PL/SQL
SELECT orders.order_id, orders.order_date, suppliers.supplier_name FROM suppliers RIGHT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;
Строка для order_id 500127 будет включена, так как был использован RIGHT OUTER JOINS. Тем не менее, вы заметите, что поле supplier_name для этой записи содержит значение NULL.
Другой тип соединения называется Oracle FULL OUTER JOIN. Этот тип соединения возвращает все строки из левой таблицы и правой таблицы с NULL — значениями в месте, где условие объединения не выполняется.
Синтаксис
Синтаксис для Oracle FULL OUTER JOIN:
SELECT columns
FROM table1
FULL JOIN table2
ON table1.column = table2.column;
В некоторых базах данных, FULL OUTER JOIN заменяются FULL JOIN.
На этом рисунке, FULL OUTER JOIN возвращает затененную область:
Oracle FULL OUTER JOIN будет возвращать все записи из обеих таблиц table1 и table2 .
Ранее мы рассмотрели применение инструкции 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 использует операцию полусоединения для реализации функциональности, называющейся соединением типа "звезда".
Оператор JOIN используется для выполнения операции соединения данных из двух наборов в один результирующий набор. Может быть использовано несколько разных типов операций соединения при выполнении SELECT . Какие строки войдут в результирующий набор зависит от типа операции соединения и от явно определенного условия соединения. Условие соединения, т.е. условие сопоставления строк таблиц друг с другом, представляет собой логическое выражение.
При необходимости объединении нескольких таблиц операция соединения должна применяться последовательно несколько раз.
Синтаксис оператора JOIN
Синтаксис подключения к запросу дополнительной таблицы с использованием оператора JOIN можно представить в следующем виде:
SELECT field1, field1, [,... n]
FROM Table1 t1
{INNER | {LEFT | RIGHT | FULL} OUTER | CROSS } JOIN
Table2 {ON
В большинстве СУБД при использовании оператора JOIN в сочетании с ключевыми словами LEFT, RIGHT, FULL можно опустить операнд OUTER. Операнд INNER также в большинстве СУБД можно не использовать.
Если названия столбцов, по которым происходит соединение таблиц, совпадают, то вместо ON можно использовать USING . Для оператора CROSS JOIN условие не указывается.
Тестовые таблицы для проверки JOIN
Для дальнейших примеров создадим две простые тестовые таблицы. Справочные таблицы пользователей и их автомобилей. В первой таблице будет храниться идентификатор пользователя и его имя (nick). Во второй таблице список автомобилей (идентификатор, наименование) и идентификатор их владельцев/пользователей. Необходимо отметить, что пользователь может иметь несколько автомобилей или не иметь вообще.
Таблица пользователей
CREATE TABLE users (id int not null, name varchar(32) not null, primary key (id)); -- Вставим в таблицу несколько записей insert into users (id, name) values (1, "alex"); insert into users (id, name) values (2, "piter"); insert into users (id, name) values (3, "serg"); insert into users (id, name) values (4, "olga"); insert into users (id, name) values (5, "ostap");
Таблица автомобилей
CREATE table autos (id int not null, -- идентификатор авто name varchar(32) not null, -- наименование авто oid int not null, -- идентификатор владельца primary key (id)); -- Вставим в таблицу несколько записей insert into autos (id, oid, name) values (1, 1, "toyota camry"); insert into autos (id, oid, name) values (2, 1, "toyota prado"); insert into autos (id, oid, name) values (3, 2, "renault megane"); insert into autos (id, oid, name) values (4, 3, "nissan x-trail"); insert into autos (id, oid, name) values (5, 4, "suzuki swift"); insert into autos (id, oid, name) values (6, 4, "suzuki vitara");
Внутреннее соединение, INNER JOIN
INNER JOIN - это оператор внутреннего соединения двух таблиц. Он является симметричным, поэтому порядок таблиц для оператора неважен.
Применяется INNER JOIN для получения только тех строк, для которых существует соответствие записей в главной и присоединяемой таблице. Алгоритм формирования результата: каждая строка главной таблицы сопоставляется с каждой строкой присоединяемой таблицы. После этого проверяется условие соединения. Если условие истинно, в результирующий набор добавляется соответствующая «соединённая» строка.
Запрос с выбором колонок результирующего набора select u.name as owner, a.name as auto from users u inner join autos a ON a.oid = u.id -- Результат запроса owner auto alex toyota camry alex toyota prado piter renault megane serg nissan x-trail olga suzuki swift olga suzuki vitara -- Запрос без выбора колонок результирующего набора select * from users u inner join autos a ON a.oid = u.id -- Результат запроса id name id1 name1 oid 1 alex 1 toyota camry 1 1 alex 2 toyota prado 1 2 piter 3 renault megane 2 3 serg 4 nissan x-trail 3 4 olga 5 suzuki swift 4 4 olga 6 suzuki vitara 4
Чтобы получить данные, которые не подходят по условию, необходимо использовать внешнее объединение - OUTER JOIN .
Внешнее объединение, OUTER JOIN
При соединении двух таблиц оператором OUTER JOIN в результирующий набор в обязательном порядке войдут строки либо одной из таблиц, либо обеих таблиц. Ключевое слово OUTER можно опустить. Запись LEFT JOIN идентична LEFT OUTER JOIN.
Существует два типа внешнего объединения. Это LEFT OUTER JOIN и RIGHT OUTER JOIN . Работают данные операторы одинаково. Разница заключается в том, что при использовании LEFT JOIN основной таблицей является таблица, указанная после оператора FROM. К строкам данной таблицы при заданных условиях добавляются данные присоединяемой таблицы. Для оператора RIGHT OUTER JOIN все с точностью до наоборот.
Оператор внешнего соединения OUTER JOIN не является симметричным, поэтому порядок установления связи между таблицами для оператора важен.
Пример использования оператора LEFT OUTER JOIN
Select u.name as owner, a.name as auto
from users u
left join autos a ON a.oid = u.id
-- Результат запроса
owner auto
alex toyota camry
alex toyota prado
piter renault megane
serg nissan x-trail
olga suzuki swift
olga suzuki vitara
ostap
Графически результат работы можно представить следующим образом:
Оператор LEFT OUTER JOIN с фильтрацией
Добавив в код предыдущего примере условие "where a.name is null". В выборке останется только одна запись "ostap", так как только у него не определен автомобиль.
Графически результат работы можно представить следующим образом:
Оператор перекрёстного соединения, CROSS JOIN
CROSS JOIN - это оператор перекрёстного соединения (декартово произведение). Оператор является симметричным и порядок таблиц для оператора неважен.
Результатирующий набор формируется следующим образом: каждая строка одной таблицы соединяется с каждой строкой второй таблицы, давая тем самым в результате все возможные сочетания строк двух таблиц. Для нашего примера это получается 30 строк результирующего набора.
Select * from users cross join autos -- Результат запроса id name id1 name1 oid 1 alex 1 toyota camry 1 2 piter 1 toyota camry 1 3 serg 1 toyota camry 1 4 olga 1 toyota camry 1 5 ostap 1 toyota camry 1 1 alex 2 toyota prado 1 2 piter 2 toyota prado 1 3 serg 2 toyota prado 1 4 olga 2 toyota prado 1 5 ostap 2 toyota prado 1 . . .
В результатах набора приведены только первые 12 строк.
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
Продолжаем изучать основы SQL , и пришло время поговорить о простых объединениях JOIN. И сегодня мы рассмотрим, как объединяются данные по средствам операторов LEFT JOIN, RIGHT JOIN, CROSS JOIN и INNER JOIN , другими словами, научимся писать запросы, которые объединяют данные, и как обычно изучать все это будем на примерах.
Объединения JOIN очень важны в SQL, так как без умения писать запросы с объединением данных разных объектов, просто не обойтись программисту SQL, да и просто админу который время от времени выгружает какие-то данные из базы данных, поэтому это относится к основам SQL и каждый человек, который имеет дело с SQL, должен иметь представление, что это такое.
Примечание! Все примеры будем писать в Management Studio SQL Server 2008.
Мы с Вами уже давно изучаем основы SQL, и если вспомнить начинали мы с оператора select , и вообще было уже много материала на этом сайте по SQL, например:
И много другого, даже уже рассматривали объединения union и union all , но, так или иначе, более подробно именно об объединениях join мы с Вами не разговаривали, поэтому сегодня мы восполним этот пробел в наших знаниях.
И начнем мы как обычно с небольшой теории.
Объединения JOIN — это объединение двух или более объектов базы данных по средствам определенного ключа или ключей или в случае cross join и вовсе без ключа. Под объектами здесь подразумевается различные таблицы, представления (views) , табличные функции или просто подзапросы sql , т.е. все, что возвращает табличные данные.
Объединение SQL LEFT и RIGHT JOIN
LEFT JOIN – это объединение данных по левому ключу, т.е. допустим, мы объединяем две таблицы по left join, и это значит что все данные из второй таблицы подтянутся к первой, а в случае отсутствия ключа выведется NULL значения, другими словами выведутся все данные из левой таблицы и все данные по ключу из правой таблицы.
RIGHT JOIN – это такое же объединение как и Left join только будут выводиться все данные из правой таблицы и только те данные из левой таблицы в которых есть ключ объединения.
Теперь давайте рассматривать примеры, и для начала создадим две таблицы:
CREATE TABLE ( (18, 0) NULL, (50) NULL) ON GO CREATE TABLE ( (18, 0) NULL, (50) NULL) ON GO
Вот такие простенькие таблицы, И я для примера заполнил их вот такими данными:
Теперь давайте напишем запрос с объединением этих таблиц по ключу number, для начала по LEFT:
SELECT t1.number as t1_number,t1.text as t1_text, t2.number as t2_number, t2.text as t2_text FROM test_table t1 LEFT JOIN test_table_2 t2 ON t1.number=t2.number
Как видите, здесь данные из таблицы t1 вывелись все, а данные из таблицы t2 не все, так как строки с number = 4 там нет, поэтому и вывелись NULL значения.
А что будет, если бы мы объединяли по средствам right join, а было бы вот это:
SELECT t1.number as t1_number,t1.text as t1_text, t2.number as t2_number, t2.text as t2_text FROM test_table t1 RIGHT JOIN test_table_2 t2 ON t1.number=t2.number
Другими словами, вывелись все строки из таблицы t2 и соответствующие записи из таблицы t1, так как все те ключи, которые есть в таблице t2, есть и в таблице t1, и поэтому у нас нет NULL значений.
Объединение SQL INNER JOIN
Inner join – это объединение когда выводятся все записи из одной таблицы и все соответствующие записи из другой таблице, а те записи которых нет в одной или в другой таблице выводиться не будут, т.е. только те записи которые соответствуют ключу. Кстати сразу скажу, что inner join это то же самое, что и просто join без Inner. Пример:
SELECT t1.number as t1_number,t1.text as t1_text, t2.number as t2_number, t2.text as t2_text FROM test_table t1 INNER JOIN test_table_2 t2 on t1.number=t2.number
А теперь давайте попробуем объединить наши таблицы по двум ключам, для этого немного вспомним, как добавлять колонку в таблицу и как обновить данные через update, так как в наших таблицах всего две колонки, и объединять по текстовому полю как-то не хорошо. Для этого добавим колонки:
ALTER TABLE test_table ADD number2 INT ALTER TABLE test_table_2 ADD number2 INT
Обновим наши данные, просто проставим в колонку number2 значение 1:
UPDATE test_table SET number2 = 1 UPDATE test_table_2 SET number2 = 1
И давайте напишем запрос с объединением по двум ключам:
SELECT t1.number as t1_number,t1.text as t1_text, t2.number as t2_number, t2.text as t2_text FROM test_table t1 INNER JOIN test_table_2 t2 ON t1.number=t2.number AND t1.number2=t2.number2
И результат будет таким же, как и в предыдущем примере:
Но если мы, допустим во второй таблице в одной строке изменим, поле number2 на значение скажем 2, то результат будет уже совсем другой.
UPDATE test_table_2 set number2 = 2 WHERE number=1
Запрос тот же самый, а вот результат:
Как видите, по второму ключу у нас одна строка не вывелась.
Объединение SQL CROSS JOIN
CROSS JOIN – это объединение SQL по которым каждая строка одной таблицы объединяется с каждой строкой другой таблицы. Лично у меня это объединение редко требуется, но все равно иногда требуется, поэтому Вы также должны уметь его использовать. Например, в нашем случае получится, конечно, не понятно что, но все равно давайте попробуем, тем более синтаксис немного отличается:
SELECT t1.number as t1_number,t1.text as t1_text, t2.number as t2_number, t2.text as t2_text FROM test_table t1 CROSS JOIN test_table_2 t2
Здесь у нас каждой строке таблицы test_table соответствует каждая строка из таблицы test_table_2, т.е. в таблице test_table у нас 4 строки, а в таблице test_table_2 3 строки 4 умножить 3 и будет 12, как и у нас вывелось 12 строк.
И напоследок, давайте покажу, как можно объединять несколько таблиц, для этого я, просто для примера, несколько раз объединю нашу первую таблицу со второй, смысла в объединение в данном случае, конечно, нет но, Вы увидите, как можно это делать и так приступим:
SELECT t1.number as t1_number, t1.text as t1_text, t2.number as t2_number, t2.text as t2_text, t3.number as t3_number, t3.text as t3_text, t4.number as t4_number, t4.text as t4_text FROM test_table t1 LEFT JOIN test_table_2 t2 on t1.number=t2.number RIGHT JOIN test_table_2 t3 on t1.number=t3.number INNER JOIN test_table_2 t4 on t1.number=t4.number
Как видите, я здесь объединяю и по left и по right и по inner просто, для того чтобы это было наглядно.
С объединениями я думаю достаточно, тем более ничего сложного в них нет. Но на этом изучение SQL не закончено в следующих статьях мы продолжим, а пока тренируйтесь и пишите свои запросы. Удачи!