PostgreSQL | Как вычесть один столбец идентификаторов из другого столбца?

У нас есть два набора идентификаторов. Это выглядит как два столбца таблицы.

SELECT unnest(ARRAY[1,2,3,4,5,6,7,8,9]);

SELECT unnest(ARRAY[5,6,7,8,9,10,11,12,13]);

Я называю словом «набор», потому что каждый элемент уникален.

Первый столбец с идентификаторами — это тот из которого мы хотим вычесть второй столбец.

Мы из первого вычитаем второй. Именно так. Потому что от перемены мест наборов, итоговые результаты будут отличаться.

Два набора столбцов идентификатором в PostgreSQL

То есть мы хотим получить такой набор идентификаторов, который есть в первом столбце, но отсутствует во втором. Как это сделать?

Как найти разницу между двумя наборами (столбцами) идентификаторов в PostgreSQL через оператор EXCEPT?

Я записал SQL-команды в виде преобразований массивов в столбцы таблицы, чтобы было понятнее. По сути это любые столбцы с ID целочисленных первичных ключей.

Для вычитания мы будем использовать один из операторов «сочетания запросов». Нам понадобится оператор EXCEPT.

Полная SQL-команда для вычитания множеств будет выглядеть так:

SELECT unnest(ARRAY[1,2,3,4,5,6,7,8,9])
EXCEPT
SELECT unnest(ARRAY[5,6,7,8,9,10,11,12,13]);

В результате мы получим новый столбец: [1, 4, 2, 3]

Вычли один столбец с идентификаторами из другого столбца в PostgreSQL

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

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

Что изменится, если мы поменяем столбцы местами в выражении с EXCEPT?

Полная SQL-команда для вычитания множеств будет выглядеть так:

SELECT unnest(ARRAY[5,6,7,8,9,10,11,12,13])
EXCEPT
SELECT unnest(ARRAY[1,2,3,4,5,6,7,8,9]);

Результат будет другим:

Поменяли местами столбцы в операторе EXCEPT в PostgreSQL

Как найти разницу между двумя наборами (столбцами) идентификаторов в PostgreSQL через команду LEFT JOIN?

Ту же самую задачу по вычитанию двух столбцов можно выполнить через предложение LEFT JOIN.

-- Вычитание через JOIN
SELECT e FROM (SELECT unnest(ARRAY[1,2,3,4,5,6,7,8,9]) AS e) AS q
LEFT JOIN
(SELECT unnest(ARRAY[5,6,7,8,9,10,11,12,13]) AS r) AS w
ON q = w
WHERE r IS NULL;

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

Вычли один столбец с идентификаторами из другого столбца через JOIN в PostgreSQL

Так как я делаю это на подзапросах, то обязательно проставляю конструкцию AS.

Использую ЛЕВЫЙ ДЖОИН для того чтобы соединить два столбца повторяющимися элементами в одну таблицу из двух столбцов.

Там где при пересечении результаты совпадать не будут, там останутся NULLы. А затем вместо всех столбцов мы отбираем только столбец e (первый) и записи те, в которых r = null.

Пересечение двух столбцов через LEFT JOIN в PostgreSQL

Информационные ссылки

Официальный сайт WEB-оболочки pgAdmin — https://www.pgadmin.org

Официальный сайт СУБД PostgreSQL — https://www.postgresql.org

Команды SQL — https://postgrespro.ru/docs/postgresql/15/sql-commands

Табличные выражения — https://postgrespro.ru/docs/postgresql/15/queries-table-expressions