Конструктор табличных значений, UNNEST(), TABLE(), STRING_SPLIT(), JSON_TABLE() — замена временным таблицам в SQL

[Akina]

Что-то я совсем не понял преамбулы. Вы пишете:

Нужно найти заказ, который не зарегистрировался - это заказ 44.

Прямым SQL-запросом найти отсутствующие значения невозможно, так как из базы нельзя получить значения, которых там нет.

Так, минуточку... вот только-только кто-то русским по белому сказал:

есть список заказов в системе клиента, которые мы ждем: 11, 22, 33, 44, 55

А это, я извиняюсь, ГДЕ? И для вашей системы ЧТО? Если почитать то, что написано далее, то можно сделать вывод, что это не данные, хранящиеся в БД, а параметры, которые передаются в СУБД. И, значит, фраза об отсутствующих данных - она в данном случае вообще ни к месту.

Основной способ для многих СУБД

По-моему, именно основной способ для абсолютно всех СУБД выглядит приблизительно так:

SELECT * 
FROM ( SELECT 11 AS zakaz, 'Анна'     AS name UNION ALL
       SELECT 22         , 'Иван'             UNION ALL
       SELECT 33         , 'Мария'            UNION ALL
       SELECT 44         , 'Петр'             UNION ALL
       SELECT 55         , 'Владимир'
     ) AS temp;

в PostgreSQL самый простой способ это unnest()

Да? А давайте мы подумаем, что должен сделать Постгресс по вашему коду..

Сначала он должен распарсить и понять текст запроса, включая и ваш фрагмент с кодом создания и последующего парсинга массива. Ну разбор текста запроса - он всегда, его на кривой козе не объехать. Но дальше-то...

Постгресс по вашему тексту должен создать массив и положить в него указанные значения. А потом вызвать функцию, которая разберёт этот массив на отдельные значения и преобразует в набор записей. Вам не кажется, что тут какие-то телодвижения являются немножко лишними?

И абсолютно та же история на всех остальных кодах, где набор значений подаётся в виде CSV или JSON и затем парсится в набор записей. Тогда как код с VALUES / ROW() и приведённый мной выше код - он сразу заставляет СУБД создать набор записей.

Да, вариант с передачей параметров в сериализованной форме с десериализацией в SQL-коде - он имеет право на существование. Во-первых, когда сериализованное значение прибывает именно как единый параметр откуда-то издалека - ну да, заслать одну длинную текстовую строку реально проще, чем массив/коллекцию. Во-вторых, когда список значений, подлежащих передаче в запрос, достаточно объёмный, и его использование в "классической" форме приведёт к нечитаемому и плохо контролируемому по причине большого объёма коду. Но эти случае неплохо было бы озвучить явно, чтобы не создавалось впечатление, что использование десериализации хорошо всегда.

[damirg]

это не данные, хранящиеся в БД, а параметры, которые передаются в СУБД

да, это список заказов, которые клиенты в своей учетной системе уже сформировали, но в нашу БД не все еще переданы, но нам они уже известны. Передав в запросе все номера, нужно найти какие еще не попали в нашу БД.

По-моему, именно основной способ для абсолютно всех СУБД выглядит приблизительно так

Да, Вы правы, это основной способ для всех СУБД это через UNION

в PostgreSQL самый простой способ это unnest()

когда сериализованное значение прибывает именно как единый параметр откуда-то издалека - ну да, заслать одну длинную текстовую строку реально проще

да, именно это и имеется ввиду. Скопировать, вставить в запрос и получить результат.

[li_bao]

Откройте для себя CTE, юный падаван

[damirg]

в CTE придется сделать тоже самое, создавать временную таблицу:

WITH temp AS (
SELECT FROM (VALUES (11), (22), (33), (44), (55) ) AS temp(zakaz))
SELECT FROM temp left join order o on temp.zakaz = o.nomer where id is null

Без выделенной части запроса не решить задачу.

Но в PostgreSQL использовать unnest() удобнее, т.к. не нужно вручную формировать список в VALUES() из списка значений, который уже и так есть в удобном виде: (11, 22, 33, 44, 55)

[li_bao]

Нет, можно проще.

[damirg]

Если знаете как проще, лучше было бы конечно привести пример.

[li_bao]

;with cte as (select 11 as n
union all
select n+11
from cte
where n < 60)
select blah blah blah

Это mssql, в других СУБД синтаксис немного варьируется. Для postgres есть ещё такое

select blah blah blah
from generate_series(11, 55, 11) 

[damirg]

номера 11, 22, 33 и т.д. - только для примера, чтобы было проще. там могут быть абсолютно разные номера, этот вариант не подходит.

[li_bao]

Ну так формулируйте примеры четче. Плюс приводите граничные значения. Расскажите когда какой метод применим и какие ещё подводные камни могут возникнуть. Тогда вам скажут спасибо.

[damirg]

Пример в статье исправил. Спасибо.

[damirg]

Новый пример:
в БД есть значения: 45, a32, zak12-1
список, который надо проверить: 45, a32, zak12-1, qwer99
Нужно найти значение из списка, которое отсутствует в БД: ответ qwer99

[li_bao]

В этом случае да, указанные методы подходят.

[OlegIct]

Для нахождения значения, которых еще нет в таблице, воспользуемся оператором EXIST:

SELECT * FROM unnest(ARRAY['5-123', 'zak222', '65_1', '258a', '3456']) AS zakaz WHERE not EXISTS ( SELECT 1 FROM order o WHERE o.nomer = zakaz
);

Такой же результат можно получить с помощью JOIN и not NULL.

Почему не использовать то, что для этого предназначено: EXCEPT в PostgreSQL и MINUS в Oracle? Да и кореллированные подзапросы сложны для выполнения во всех базах.

SELECT * FROM unnest(ARRAY['5-123', 'zak222', '65_1', '258a', '3456']) AS zakaz
EXCEPT             
SELECT * FROM (VALUES ('5-123'), ('zak222'), ('65_1'), ('258a')) AS temp(zakaz);
 zakaz 
-------
 3456
(1 row)

Time: 0.251 ms

SELECT * FROM unnest(ARRAY['5-123', 'zak222', '65_1', '258a', '3456']) AS zakaz
 WHERE not EXISTS (
     SELECT 1 FROM (VALUES ('5-123'), ('zak222'), ('65_1'), ('258a')) o(nomer) WHERE o.nomer = zakaz
);
 zakaz 
-------
 3456
(1 row)

Time: 0.282 ms

[damirg]

Основная цель статьи не показать что лучше: EXIST или EXEPT, а то что без создания "настоящей" временной таблицы через CREATE можно решить задачу, но за подсказку спасибо.