Оператор join в sql: SQL: оператор JOIN. Основные типы объединения

SQL операторы JOIN, UNION, INTERSECT и EXCEPT

Содержание

• Естественное соединение
• Декартово произведение (перекрестное соединение)
• Внешнее соединение
• Тета-соединение
• Самосоединение
• Полусоединение
• Оператор UNION

Соединение таблиц в запросе SELECT выполняется с помощью оператора JOIN.

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

Выделяют следующие виды соединения, каждому из которых соответствует своя форма оператора JOIN:

• CROSS JOIN — перекрестное или декартово соединение
• [INNER] JOIN — естественное или внутреннее соединение
• LEFT [OUTER] JOIN — левое внешнее соединение
• RIGHT [OUTER] JOIN — правое внешнее соединение
• FULL [OUTER] JOIN — полное внешнее соединение

Существует также тета-соединение, самосоединение и полусоединение.

Естественное соединение

Естественное соединение — внутреннее соединение или соединение по эквивалентности.

Transact-SQL

SELECT employee.*, department.* FROM employee INNER JOIN department ON employee.dept_no = department.dept_no;

Здесь предложение FROM определяет соединяемые таблицы и в нем явно указывается тип соединения — INNER JOIN. Предложение ON является частью предложения FROM и указывает соединяемые столбцы. Выражение employee.dept_no = department.dept_no определяет условие соединения.

Эквивалентный запрос с применением неявного синтаксиса:

Transact-SQL

SELECT employee.*, department.* FROM employee, department WHERE employee.dept_no = department.dept_no;

Соединяемые столбцы должны иметь идентичную семантику, т.е. оба столбца должны иметь одинаковое логическое значение. Соединяемые столбцы не обязательно должны иметь одинаковое имя (или даже одинаковый тип данных), хотя часто так и бывает.

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

В инструкции SELECT объединить можно до 64 таблиц (ограничение MS SQL), при этом один оператор JOIN соединяет только две таблицы:

Transact-SQL

SELECT emp_fname, emp_lname FROM works_on JOIN employee ON works_on.emp_no=employee.emp_no JOIN department ON employee.dept_no=department.dept_no

Декартово произведение (перекрестное соединение)

Декартово произведение (перекрестное соединение) соединяет каждую строку первой таблицы с каждой строкой второй. Результатом декартово произведения первой таблицы с n строками и второй таблицы с m строками будет таблица с n × m строками.

Transact-SQL

SELECT employee.*, department.* FROM employee CROSS JOIN department;

Внешнее соединение

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

Выделяют три вида внешних соединений:

• левое внешнее соединение — в результирующий набор попадают все строки из таблицы с левой стороны оператора сравнения (независимо от того имеются ли совпадающие строки с правой стороны), а из таблицы с правой стороны — только строки с совпадающими значениями столбцов. При этом если для строки из левой таблицы нет соответствий в правой таблице, значениям строки в правой таблице будут присвоены NULL
  

  Transact-SQL

  SELECT employee_enh.*, department.location FROM employee_enh LEFT OUTER JOIN department ON domicile = location;

  SELECT employee_enh.*, department.location FROM employee_enh LEFT OUTER JOIN department ON domicile = location;

• правое внешнее соединение
  — аналогично левому внешнему соединению, но таблицы меняются местами
  

  Transact-SQL

  SELECT employee_enh.domicile, department.* FROM employee_enh RIGHT OUTER JOIN department ON domicile =location;

  SELECT employee_enh.domicile, department.* FROM employee_enh RIGHT OUTER JOIN department ON domicile =location;

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

Тета-соединение

Условие сравнения столбцов соединения не обязательно должно быть равенством, но может быть любым другим сравнением. Соединение, в котором используется общее условие сравнения столбцов соединения, называется тета-соединением:

Transact-SQL

SELECT emp_fname, emp_lname, domicile, location FROM employee_enh JOIN department ON domicile < location;

Самосоединение

Самосоединение — это естественное соединение таблицы с самой собой. При этом один столбец таблицы сравнивается сам с собой. Сравнивание столбца с самим собой означает, что в предложении FROM инструкции SELECT имя таблицы употребляется дважды. Поэтому необходимо иметь возможность ссылаться на имя одной и той же таблицы дважды. Это можно осуществить, используя, по крайней мере, один псевдоним. То же самое относится и к именам столбцов в условии соединения в инструкции SELECT. Для того чтобы различить столбцы с одинаковыми именами, необходимо использовать уточненные имена.

Полусоединение

Полусоединение похоже на естественное соединение, но возвращает только набор всех строк из одной таблицы, для которой в другой таблице есть одно или несколько совпадений.

Оператор UNION

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

Transact-SQL

select_1 UNION [ALL] select_2 {[UNION [ALL] select_3]}…

Параметры select_1, select_2, … представляют собой инструкции SELECT, которые создают объединение.

Объединять с помощью инструкции UNION можно только совместимые таблицы. Под совместимыми таблицами имеется в виду, что оба списка столбцов выборки должны содержать одинаковое число столбцов, а соответствующие столбцы должны иметь совместимые типы данных. Результат объединения можно упорядочить, только используя предложение ORDER BY в последней инструкции SELECT. Предложения GROUP BY и HAVING можно применять с отдельными инструкциями SELECT, но не в самом объединении.

Два других оператора для работы с наборами:

• INTERSECT — пересечение — набор строк, которые принадлежат к обеим таблицам
• EXCEPT — разность двух таблиц — все значения, которые принадлежат к первой таблице и не присутствуют во второй

UNION в SQL — CodeChick

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

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

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

Если эти критерии выполняются, то таблицы совместимы с оператором UNION.

Базовый синтаксис оператора UNION представлен следующим образом:

SELECT список_столбцов FROM таблица1
UNION SELECT список_столбцов FROM таблица2;

Чтобы лучше понять, как работает оператор UNION, предположим, что в таблицах employees и departments существуют некоторые гипотетические поля first_name и last_name. Обратите внимание, что эти поля на самом деле не существуют в демонстрационных таблицах.

+----+------------+-----------+--------+
| id | first_name | last_name | salary |
+----+------------+-----------+--------+
|  1 | Ethan      | Hunt      |   5000 |
|  2 | Tony       | Montana   |   6500 |
|  3 | Sarah      | Connor    |   8000 |
|  4 | Rick       | Deckard   |   7200 |
|  5 | Martin     | Blank     |   5600 |
+----+------------+-----------+--------+

+----+------------+-----------+----------+
| id | first_name | last_name | city     |
+----+------------+-----------+----------+
|  1 | Maria      | Anders    | Berlin   |
|  2 | Fran       | Wilson    | Madrid   |
|  3 | Dominique  | Perrier   | Paris    |
|  4 | Martin     | Blank     | Turin    |
|  5 | Thomas     | Hardy     | Portland |
+----+------------+-----------+----------+

Выполним оператор UNION, чтобы объединить результаты двух запросов.

Следующая команда вернет имена и фамилии всех клиентов и сотрудников:

SELECT first_name, last_name FROM employees 
UNION
 
SELECT first_name, last_name FROM customers;

После выполнения приведенной выше команды вы получите такой результат:

+---------------+--------------+
| first_name    | last_name    |
+---------------+--------------+
| Ethan         | Hunt         |
| Tony          | Montana      |
| Sarah         | Connor       |
| Rick          | Deckard      |
| Martin        | Blank        |
| Maria         | Anders       |
| Fran          | Wilson       |
| Dominique     | Perrier      |
| Thomas        | Hardy        |
+---------------+--------------+

Оператор UNION по умолчанию удаляет дублирующиеся строки из объединенного набора результатов. Вот почему приведенный выше запрос возвращает только 9 строк. Если вы заметили, имя Martin Blank

Однако если вы хотите оставить дублирующиеся строки, используйте ключевое слово ALL, как показано ниже:

SELECT first_name, last_name FROM employees 
UNION ALL
SELECT first_name, last_name FROM customers;

SQL JOIN (с примерами)

В этом руководстве вы узнаете об операторе SQL JOIN с помощью примеров.

SQL JOIN объединяет две таблицы на основе общего столбца и выбирает записи, имеющие совпадающие значения в этих столбцах.

Пример

 -- объединение таблиц "Клиенты" и "Заказы"
-- на основе общих значений их столбцов customer_id
ВЫБЕРИТЕ Customers.customer_id, Customers.first_name, Orders.item
ОТ клиентов
ПРИСОЕДИНЯЙТЕСЬ к заказам
ON Customers.customer_id = Orders.customer_id; 

Здесь команда SQL объединяет таблицы Customers и Orders на основе общих значений в столбцах customer_id обеих таблиц.

Набор результатов будет состоять из

• столбцов customer_id и first_name из таблицы Customers
• позиция столбец из таблицы Orders

Синтаксис SQL JOIN

Синтаксис SQL Оператор JOIN :

 SELECT columns_from_both_tables
ИЗ таблицы1
ПРИСОЕДИНЯЙТЕСЬ к таблице2
ON table1.column1 = table2.column2 

Здесь

• table1 и table2 — две таблицы, которые должны быть объединены
• столбец 1 — это столбец в table1 , связанный с column2 в table2

Примечание: В SQL существует 4 типа JOIN. Но ВНУТРЕННЕЕ СОЕДИНЕНИЕ и JOIN относятся к одному и тому же.

Пример 1: SQL JOIN

 -- объединение таблиц Customers и Orders на основе
-- customer_id столбца "Клиенты" и столбца "Заказы"
ВЫБЕРИТЕ Customers. customer_id, Customers.first_name, Orders.amount
ОТ клиентов
ПРИСОЕДИНЯЙТЕСЬ к заказам
ON Customers.customer_id = Orders.customer; 

Вот как работает этот код:

Здесь команда SQL выбирает customer_id и first_name столбцы (из таблицы Клиенты ) и столбец сумма (из таблицы Заказы ).

Набор результатов будет содержать те строки, в которых есть совпадение между customer_id (из таблицы Customers ) и customer (из таблицы Orders ).

Типы SQL JOIN

Как мы упоминали, команда JOIN , которую мы выполнили в этой статье, называется INNER JOIN 9.0006 .

В SQL у нас есть четыре основных типа соединений:

• INNER JOIN
• ЛЕВОЕ СОЕДИНЕНИЕ
• ПРАВОЕ СОЕДИНЕНИЕ
• ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ

SQL JOIN и псевдонимы

Мы можем использовать псевдонимы AS с именами таблиц, чтобы сделать наш запрос коротким и понятным. Например,

 -- используйте псевдоним C для таблицы "Клиенты".
-- использовать псевдоним O для таблицы Orders
ВЫБЕРИТЕ C.customer_id, C.first_name, O.amount
ОТ клиентов AS C
ПРИСОЕДИНЯЙТЕСЬ к заказам КАК O
ON C.customer_id = O.customer; 

Здесь команда SQL объединяет таблицы Customers и Orders , присваивая им псевдонимы C и O соответственно.

Кроме того, мы можем временно изменить имена столбцов, используя псевдонимы AS . Например,

 -- используйте псевдоним C для таблицы "Клиенты".
-- использовать псевдоним O для таблицы Orders
ВЫБЕРИТЕ C.customer_id AS cid, C.first_name AS имя, O.amount
ОТ клиентов AS C
ПРИСОЕДИНЯЙТЕСЬ к заказам КАК O
ON C.customer_id = O.customer; 

Помимо предоставления псевдонимов таблицам, приведенная выше команда SQL также назначает псевдонимы столбцам таблицы Customers :

• столбец customer_id имеет псевдоним cid
• first_name столбец имеет псевдоним имя

Содержание

Соединения SQL — типы, синтаксис и примеры

В этом руководстве рассматриваются следующие темы:

• Что такое соединения в SQL?
• Синтаксис соединения SQL
• Пример соединения SQL
• Типы соединений SQL
  • Внутренние соединения в SQL
  • Левые соединения в SQL
  • Правые соединения в SQL
  • ПОЛНЫЕ соединения в SQL

Повысьте свои навыки SQL сегодня с помощью нашего видео о соединениях SQL. Смотри!

 SELECT Name, City, Marks, Phone_no
ОТ Student_detail, Personal_detail
ГДЕ Student_details.Name=Personal_detail.Name; 

 ВЫБЕРИТЕ Имя, Город, Знаки, Телефон_номер
ОТ Student_detail, Personal_detail
ГДЕ Student_details.Name=Personal_detail.Name;
 

 SELECT Column_list
ИЗ ТАБЛИЦЫ 1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ТАБЛИЦА 2
ON Table1.ColName = Table2.ColName 

 SELECT student_data.RollNo, student_data.Name, student_data.Address, student_mark.Marks, student_mark.Grade
ОТ student_data
ВНУТРЕННЕЕ СОЕДИНЕНИЕ student_mark ON student_data.RollNo = student_mark.RollNo; 

 SELECT имя_столбца
ИЗ таблицы1
ЛЕВОЕ СОЕДИНЕНИЕ таблица2
ON table1.column_name=table2.column_name;
 

 SELECT Student_details.Name, Address, Roll_no, Email_id
ОТ Student_details
ВЛЕВО ПРИСОЕДИНЯЙСЯ
ON Student_details.Name= Personal_details.Name;
 

 SELECT имя_столбца
ИЗ таблицы1
ПРАВОЕ СОЕДИНЕНИЕ таблица2
ON table1.column_name=table2.column_name;
 

 SELECT Personal_details.Name, Address, Roll_no, Email_id
ОТ Student_details
ПРИСОЕДИНЯЙТЕСЬ ПРАВО Personal_details
 

 ON Student_details.Name= Personal_details.Name;