Базы данных | Практическое занятие 6

Использование UNION, INTERSECT и EXCEPT в PostgreSQL

Введение в операции над множествами

Операции над множествами в SQL рассматривают результаты операторов SELECT как математические множества, позволяя вам комбинировать или сравнивать их. Мы рассмотрим три операции:

• UNION: Объединяет результирующие наборы двух или более операторов SELECT в один результирующий набор, удаляя повторяющиеся строки.
• INTERSECT: Возвращает только те строки, которые присутствуют во всех указанных операторах SELECT.
• EXCEPT: Возвращает строки из первого оператора SELECT, которые отсутствуют в последующих операторах SELECT.

---

Настройка примеров таблиц

Чтобы продемонстрировать эти операции, мы будем использовать две простые таблицы: math_enrollments и science_enrollments. Каждая таблица содержит список студентов, зачисленных на курсы математики или естественных наук, со столбцами student_id (целое число) и student_name (текст).

Выполните следующие команды SQL в редакторе SQL pgAdmin 4, чтобы создать и заполнить эти таблицы:

CREATE TABLE math_enrollments (
    student_id integer,
    student_name text
);

CREATE TABLE science_enrollments (
    student_id integer,
    student_name text
);

INSERT INTO math_enrollments (student_id, student_name) VALUES
(1, 'AZIMJON'),
(2, 'SHOHZODBEK'),
(3, 'SULTONBEK');

INSERT INTO science_enrollments (student_id, student_name) VALUES
(2, 'SHOHZODBEK'),
(4, 'XAYRULLO'),
(5, 'AZIZBEK');

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

---

UNION: Объединение результатов

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

Синтаксис

SELECT column1, column2, ... FROM table1
UNION
SELECT column1, column2, ... FROM table2;

Пример

Чтобы получить список всех студентов, зачисленных либо на математику, либо на естественные науки:

SELECT student_id, student_name FROM math_enrollments
UNION
SELECT student_id, student_name FROM science_enrollments;

Выполните этот запрос в pgAdmin 4. Результат появится на вкладке Data Output:

Пояснение

• Результат включает всех уникальных студентов из обеих таблиц.
• SHOHZODBEK, который присутствует как в math_enrollments, так и в science_enrollments, появляется только один раз, потому что UNION удаляет дубликаты.

Вариант UNION ALL

Если вы хотите включить дубликаты (если они существуют), используйте UNION ALL:

SELECT student_id, student_name FROM math_enrollments
UNION ALL
SELECT student_id, student_name FROM science_enrollments;

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

---

INTERSECT: Поиск общих строк

Операция INTERSECT возвращает только те строки, которые присутствуют во всех указанных операторах SELECT.

Синтаксис

SELECT column1, column2, ... FROM table1
INTERSECT
SELECT column1, column2, ... FROM table2;

Пример

Чтобы найти студентов, зачисленных и на математику, и на естественные науки:

SELECT student_id, student_name FROM math_enrollments
INTERSECT
SELECT student_id, student_name FROM science_enrollments;

Выполните этот запрос. Результат:

Пояснение

• Только SHOHZODBEK присутствует в обеих таблицах, поэтому он является единственной возвращаемой строкой.
• INTERSECT сравнивает целые строки, поэтому должны совпадать и student_id, и student_name.

---

EXCEPT: Поиск различий

Операция EXCEPT возвращает строки из первого оператора SELECT, которые отсутствуют во втором операторе SELECT.

Синтаксис

SELECT column1, column2, ... FROM table1
EXCEPT
SELECT column1, column2, ... FROM table2;

Пример 1: Математика, но не естественные науки

Чтобы найти студентов, зачисленных на математику, но не на естественные науки:

SELECT student_id, student_name FROM math_enrollments
EXCEPT
SELECT student_id, student_name FROM science_enrollments;

Результат:

Пример 2: Естественные науки, но не математика

Чтобы найти студентов, зачисленных на естественные науки, но не на математику:

SELECT student_id, student_name FROM science_enrollments
EXCEPT
SELECT student_id, student_name FROM math_enrollments;

Результат:

Пояснение

• В примере 1, AZIMJON и SULTONBEK присутствуют в math_enrollments, но не в science_enrollments.
• В примере 2, XAYRULLO и AZIZBEK присутствуют в science_enrollments, но не в math_enrollments.
• Порядок операторов SELECT имеет значение при использовании EXCEPT, в отличие от UNION и INTERSECT.

---

Ключевые требования и примечания

Для успешного использования операций над множествами в PostgreSQL:

1. Количество столбцов: Каждый оператор SELECT должен возвращать одинаковое количество столбцов.
2. Типы данных: Соответствующие столбцы должны иметь совместимые типы данных (например, целое число с целым числом, текст с текстом).
3. Имена столбцов: Результирующий набор использует имена столбцов из первого оператора SELECT.
4. Дубликаты: UNION и INTERSECT удаляют дубликаты; используйте ALL (например, UNION ALL), чтобы сохранить их.
5. Несколько операций: Вы можете объединять операции в цепочку, например, SELECT ... UNION SELECT ... UNION SELECT ....

Распространенные ошибки

• Несовпадающие столбцы: Это не сработает:

  SELECT student_id FROM math_enrollments
  UNION
  SELECT student_name FROM science_enrollments;
  

  Ошибка: Типы данных (целое число и текст) не совпадают.

• Разное количество столбцов: Это не сработает:

  SELECT student_id, student_name FROM math_enrollments
  UNION
  SELECT student_id FROM science_enrollments;
  

  Ошибка: Неравное количество столбцов.