Продвинутое использование DML: объединение таблиц и подзапросы

Продвинутое использование DML (Data Manipulation Language) включает в себя сложные операции с данными, такие как объединение таблиц и использование подзапросов. Эти техники позволяют выполнять более сложные запросы и манипуляции данными в базе данных.

Объединение таблиц

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

JOIN (Объединение)

Команда JOIN используется для объединения строк из двух или более таблиц на основе условия объединения. Существует несколько типов JOIN:

• INNER JOIN: Возвращает только те строки, которые удовлетворяют условию объединения в обеих таблицах.

  SELECT employees.name, departments.department_name
  FROM employees
  INNER JOIN departments ON employees.department_id = departments.id;

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

• LEFT JOIN (или LEFT OUTER JOIN): Возвращает все строки из левой таблицы и соответствующие строки из правой таблицы. Если нет соответствующих строк в правой таблице, возвращаются NULL-значения.

  SELECT employees.name, departments.department_name
  FROM employees
  LEFT JOIN departments ON employees.department_id = departments.id;

  Этот запрос возвращает всех сотрудников и названия их отделов, при этом сотрудники без отдела будут отображены с NULL в столбце department_name.

• RIGHT JOIN (или RIGHT OUTER JOIN): Возвращает все строки из правой таблицы и соответствующие строки из левой таблицы. Если нет соответствующих строк в левой таблице, возвращаются NULL-значения.

  SELECT employees.name, departments.department_name
  FROM employees
  RIGHT JOIN departments ON employees.department_id = departments.id;

  Этот запрос возвращает все отделы и имена сотрудников, при этом отделы без сотрудников будут отображены с NULL в столбце name.

• FULL JOIN (или FULL OUTER JOIN): Возвращает все строки, когда есть соответствие в одной из таблиц. Если нет соответствующих строк в одной из таблиц, возвращаются NULL-значения.

  SELECT employees.name, departments.department_name
  FROM employees
  FULL JOIN departments ON employees.department_id = departments.id;

  Этот запрос возвращает все строки из обеих таблиц, с NULL в тех столбцах, где нет соответствий.

• CROSS JOIN: Возвращает декартово произведение двух таблиц, то есть каждую строку из первой таблицы комбинирует с каждой строкой из второй таблицы.

  SELECT employees.name, departments.department_name
  FROM employees
  CROSS JOIN departments;

  Этот запрос возвращает все возможные комбинации сотрудников и отделов.

UNION и INTERSECT

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

  SELECT name FROM employees
  UNION
  SELECT name FROM contractors;

  Этот запрос объединяет имена сотрудников и подрядчиков в один список.

• INTERSECT: Возвращает только те строки, которые присутствуют в обоих запросах.

  SELECT name FROM employees
  INTERSECT
  SELECT name FROM contractors;

  Этот запрос возвращает имена, которые встречаются и среди сотрудников, и среди подрядчиков.

Подзапросы

Подзапросы (или вложенные запросы) — это запросы, встроенные в другие запросы. Они позволяют выполнять более сложные выборки и манипуляции данными.

Типы подзапросов:

• Подзапрос в SELECT-части:

  SELECT name, (SELECT department_name FROM departments WHERE id = employees.department_id) AS department
  FROM employees;

  Этот запрос возвращает имена сотрудников и названия их отделов, которые извлекаются из подзапроса.

• Подзапрос в WHERE-части:

  SELECT name
  FROM employees
  WHERE department_id IN (SELECT id FROM departments WHERE department_name = 'Sales');

  Этот запрос возвращает имена сотрудников, работающих в отделе продаж.

• Подзапрос в FROM-части (табличные выражения):

  SELECT dept_name, avg_salary
  FROM (SELECT department_id, AVG(salary) AS avg_salary
        FROM employees
        GROUP BY department_id) AS dept_avg
  INNER JOIN departments ON dept_avg.department_id = departments.id;

  Этот запрос сначала вычисляет среднюю зарплату по отделам в подзапросе, а затем объединяет результат с таблицей departments для получения названия отдела.

• Коррелированный подзапрос:

  SELECT name, salary
  FROM employees e1
  WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

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

Заключение

• Объединение таблиц: Используется для сочетания данных из нескольких таблиц, включая JOIN (INNER, LEFT, RIGHT, FULL) и операторы UNION, INTERSECT.

• Подзапросы: Вложенные запросы, которые используются для выполнения более сложных выборок и манипуляций, включая подзапросы в SELECT, WHERE, FROM и коррелированные подзапросы.

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