Бывают ситуации, когда есть список значений, и нужно проверить, каких значений из списка нет в БД. Например, есть список: ['5-123', 'zak222', '65_1', '258a', '3456'], нужно найти значения, которых нет в БД.
В таблице 4 строки:
Нужно найти строку, которого нет в БД - это 258a.
SQL-запросом без временной таблицы найти отсутствующие значения невозможно, так как из базы нельзя получить значения, которых там нет. При небольшом количестве строк разницу можно заметить визуально, но с тысячами значений это превращается в утомительный ручной труд. Классическое решение — экспорт списка из БД в Excel с последующим ВПР (VLOOKUP) для сравнения с полным списком. Результат: где ВПР возвращает ошибку #Н/Д — это и есть те значения, которых нет в БД, но это неудобно и долго.
Можно было бы создать временную таблицу, но не у всех есть права для этого, а задачу надо решить. В SQL для решения таких проблем можно использовать литеральную таблицу (или конструктор табличных значений, table value constructor) — это способ создания временного набора данных прямо в SQL‑запросе без обращения к постоянным таблицам. Вы буквально перечисляете строки и столбцы «руками» прямо в коде, и SQL воспринимает их как таблицу.
Синтаксис
-
Основной способ для многих СУБД, проверено на PostgreSQL, Oracle, MS SQL:
SELECT * FROM (VALUES
('5-123', 'Анна'),
('zak222', 'Иван'),
('65_1', 'Мария'),
('258a', 'Петр'),
('3456', 'Владимир')
) AS temp(zakaz, name);В MySQL тоже работает, но синтаксис чуть другой, подробнее об этом будет ниже.
2. PostgreSQL
Для поиска нам достаточно одного поля:SELECT * FROM (VALUES ('5-123'), ('zak222'), ('65_1'), ('258a'), ('3456')) AS temp(zakaz);
Но когда нужен только один столбец, в PostgreSQL самый простой способ это unnest()— это функция, которая разворачивает массив в отдельные строки.
Из ARRAY[1, 2, 3] делает три строки: 1, 2, 3. Идеальна для создания литеральных таблиц прямо в запросе вместо VALUES:
SELECT * FROM unnest(ARRAY['5-123', 'zak222', '65_1', '258a', '3456']) AS zakaz;
В дальнейшем эти данные можно использовать как обычную таблицу.
Для нахождения значения, которых еще нет в таблице, воспользуемся оператором 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.
3. В старых версиях Oracle основной способ не работает, но есть функция с коллекциями TABLE()— разворачивает коллекцию в строки таблицы. Из TABLE(sys.ODCIVarchar2List('A','B')) делает две строки: 'A', 'B':
SELECT COLUMN_VALUE FROM TABLE(sys.ODCIVarchar2List('a', 'b', 'c')); - для строкSELECT COLUMN_VALUEFROM TABLE(sys.ODCINumberList(1, 2, 3));- для чисел
Для двух столбцов в Oracle:select
objectschema id, objectname name from table(sys.ODCIObjectList( sys.odciobject('5-123', 'Anna'), sys.odciobject('zak222', 'Ivan')));
Если нужно больше столбцов, то уже нужно создать кастомный объектный тип.
4. В MS SQL работает основной способ, и начиная с версии SQL Server 2016, может быть применена функция STRING_SPLIT():SELECT value FROM STRING_SPLIT('5-123,zak222,65_1,258a,3456', ',');
Функция STRING_SPLIT в MS SQL Server — это очень удобная встроенная функция, которая разбивает строку с разделителями на отдельные подстроки и возвращает их в виде таблицы.
На входе даете функции исходную строку и указываете разделитель, а выходе получаете таблицу из одной колонки с именем value.
5. В MySQL основной способ работает, но синтаксис чуть другой, перед значением нужно добавить ROW: SELECT * FROM (
VALUES
ROW('5-123', 'Анна'),
ROW('zak222', 'Иван'),
ROW('65_1', 'Мария'),
ROW('258a', 'Петр'),
ROW('3456', 'Владимир')
) AS t(id, name);
Еще в MySQL для таких целей есть JSON_TABLE():
Для одного столбца удобнее так, особенно, если значения в виде списка:SELECT jt.order_id
FROM JSON_TABLE(
'["5-123", "zak222", "65_1", "258a", "3456"]',
'$[*]' COLUMNS (order_id VARCHAR(20) PATH '$')
) jt
Возможно даже для нескольких столбцов будет так удобнее, если значения в виде списка:SELECT orders.order_id, customers.customer_id
FROM JSON_TABLE(
'["5-123", "zak222", "65_1", "258a", "3456"]',
'$[*]' COLUMNS (
order_id VARCHAR(20) PATH '$',
row_num FOR ORDINALITY
)
) AS orders,
JSON_TABLE(
'["Анна", "Иван", "Мария", "Петр", "Владимир"]',
'$[*]' COLUMNS (
customer_id VARCHAR(20) PATH '$',
row_num FOR ORDINALITY
)
) AS customers
WHERE orders.row_num = customers.row_num;
Это уже для произвольного количества столбцов, но данные в JSON:SELECT *
FROM JSON_TABLE(
'[{"order_id":"5-123","name":"Анна", "price": 123},
{"order_id":"zak222","name":"Иван", "price": 234},
{"order_id":"65_1","name":"Мария", "price": 345},
{"order_id":"258a","name":"Петр", "price": 456},
{"order_id":"3456","name":"Владимир", "price": 567}]',
'$[*]' COLUMNS (
order_id VARCHAR(20) PATH '$.order_id',
name VARCHAR(20) PATH '$.name',
price INT PATH '$.price')
) AS jt;
Через VALUES и JSON_TABLE — основные способы в MySQL для литеральных таблиц. JSON_TABLE - синтаксис мощный, но громоздкий и хорош только когда данные уже в JSON.
Выводы
Используя эти функции для создания литеральных таблиц, можно генерировать временные таблицы прямо в запросе без создания реальных объектов БД. Это особенно удобно, когда нет прав для создания объектов в БД, для тестовых данных, списков ID и задач с EXISTS/NOT EXISTS. Многие БД поддерживают основной способ через VALUES, но каждая СУБД еще имеет свой «родной» способ.
Комментарии (14)
li_bao
10.03.2026 08:17Откройте для себя CTE, юный падаван
damirg Автор
10.03.2026 08:17в CTE придется сделать тоже самое, создавать временную таблицу:
WITH temp AS (
SELECTFROM (VALUES (11), (22), (33), (44), (55) ) AS temp(zakaz))
SELECTFROM temp left join order o on temp.zakaz = o.nomer where id is nullБез выделенной части запроса не решить задачу.
Но в PostgreSQL использовать unnest() удобнее, т.к. не нужно вручную формировать список в VALUES() из списка значений, который уже и так есть в удобном виде: (11, 22, 33, 44, 55)
li_bao
10.03.2026 08:17Нет, можно проще.
damirg Автор
10.03.2026 08:17Если знаете как проще, лучше было бы конечно привести пример.
li_bao
10.03.2026 08:17;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 Автор
10.03.2026 08:17номера 11, 22, 33 и т.д. - только для примера, чтобы было проще. там могут быть абсолютно разные номера, этот вариант не подходит.
OlegIct
10.03.2026 08:17Для нахождения значения, которых еще нет в таблице, воспользуемся оператором
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 msdamirg Автор
10.03.2026 08:17Основная цель статьи не показать что лучше: EXIST или EXEPT, а то что без создания "настоящей" временной таблицы через CREATE можно решить задачу, но за подсказку спасибо.
Akina
Что-то я совсем не понял преамбулы. Вы пишете:
Так, минуточку... вот только-только кто-то русским по белому сказал:
А это, я извиняюсь, ГДЕ? И для вашей системы ЧТО? Если почитать то, что написано далее, то можно сделать вывод, что это не данные, хранящиеся в БД, а параметры, которые передаются в СУБД. И, значит, фраза об отсутствующих данных - она в данном случае вообще ни к месту.
По-моему, именно основной способ для абсолютно всех СУБД выглядит приблизительно так:
Да? А давайте мы подумаем, что должен сделать Постгресс по вашему коду..
Сначала он должен распарсить и понять текст запроса, включая и ваш фрагмент с кодом создания и последующего парсинга массива. Ну разбор текста запроса - он всегда, его на кривой козе не объехать. Но дальше-то...
Постгресс по вашему тексту должен создать массив и положить в него указанные значения. А потом вызвать функцию, которая разберёт этот массив на отдельные значения и преобразует в набор записей. Вам не кажется, что тут какие-то телодвижения являются немножко лишними?
И абсолютно та же история на всех остальных кодах, где набор значений подаётся в виде CSV или JSON и затем парсится в набор записей. Тогда как код с VALUES / ROW() и приведённый мной выше код - он сразу заставляет СУБД создать набор записей.
Да, вариант с передачей параметров в сериализованной форме с десериализацией в SQL-коде - он имеет право на существование. Во-первых, когда сериализованное значение прибывает именно как единый параметр откуда-то издалека - ну да, заслать одну длинную текстовую строку реально проще, чем массив/коллекцию. Во-вторых, когда список значений, подлежащих передаче в запрос, достаточно объёмный, и его использование в "классической" форме приведёт к нечитаемому и плохо контролируемому по причине большого объёма коду. Но эти случае неплохо было бы озвучить явно, чтобы не создавалось впечатление, что использование десериализации хорошо всегда.
damirg Автор
да, это список заказов, которые клиенты в своей учетной системе уже сформировали, но в нашу БД не все еще переданы, но нам они уже известны. Передав в запросе все номера, нужно найти какие еще не попали в нашу БД.
Да, Вы правы, это основной способ для всех СУБД это через UNION
да, именно это и имеется ввиду. Скопировать, вставить в запрос и получить результат.