SQL-Ex blog

2. Заблуждение относительно IN

Я часто вижу людей, которые спрашивают на форумах SQL, почему это не работает?

DECLARE @list varchar(23) = '1,2,3,4'

SELECT ... FROM tbl WHERE col IN (@list)

Ответ состоит в том, что это работает, но посмотрите сюда:

CREATE TABLE #test (id   int         NOT NULL,

                    col varchar(23)  NOT NULL)

INSERT #test(id, col)

   VALUES(1, 'Something'), (2, '1,2,3,4'), (3, 'Anything')

DECLARE @list varchar(23) = '1,2,3,4'

SELECT id FROM #test WHERE col IN (@list)

SELECT возвращает строку с id = 2, и ничего более.

Люди, которые спрашивают, почему не работает IN, не вполне его понимают, что IN - это не функция. Это оператор, и выражение

col IN (val1, val2, val3, ...)

является просто сокращенной формой для:

col = val1 OR col = val2 OR col = val3 OR ...

val1 и т.д. здесь могут быть столбцами таблицы, переменными или константами. Анализатор переписывает выражение IN в список OR, как только встречает его. (Это объясняет, почему вы получаете множество сообщений об ошибках, когда вам случается ошибиться в имени столбца слева от IN.) Тут нет магического разворачивания значений переменной. Значение '1,2,3,4' означает именно то, что это строка, а не список чисел.

3. Как обработать список

Теперь вы знаете, почему IN (@list) не работает как вам хотелось бы, но если у вас есть разделенный запятыми список, вам по-прежнему нужно знать, как с ним работать. И это то, что вы узнаете в этой главе.

3.1 Табличнозначные параметры

На мой взгляд, лучший подход — вообще пересмотреть вопрос об использовании списка с разделителями. Кроме того, почему не использовать таблицу, коль скоро вы находитесь в реляционной базе данных? Т.е. вам следует передавать данные в табличном параметре (TVP), а не в списке с разделителями. Если вы никогда не использовали прежде TVP, у меня есть статья, где я даю инструкции по передаче TVP из .NET в SQL Server. Статья включает подробное описание передачи списка с разделителями в TVP. Вы увидите, что это удивительно просто.

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

3.2 string_split - встроенное решение

Если у вас SQL 2016 или более поздняя версия, то есть очень быстрое решение:

SELECT ...

FROM   tbl 

WHERE  col IN (SELECT convert(int, value) FROM string_split('1,2,3,4', ','))

string_split является встроенной табличнозначной функцией, которая принимает два параметра. Первый параметр - список с разделителями, а второй - разделитель.

Есть две формы string_split. Используемая как в примере выше, она возвращает результирующий набор в виде единственного столбца value. Возьмите такой запрос:

SELECT * FROM string_split ('a|b|v', '|')

Будет получен следующий результирующий набор:

value

-----

a

b

v

Есть ситуации, когда вы хотите знать порядок элементов в списке. Например, у вас может быть два списка, которые вы хотите синхронизировать. (Мы увидим подобный пример в конце статьи.) Чтобы этого добиться, вы можете добавить в качестве третьего параметра 1. Это добавит второй столбец, ordinal, в результирующий набор. Вот пример:

SELECT * FROM string_split('a|b|v', '|', 1)

Вывод:

value ordinal

----- ----------

a     1

b     2

v     3

Имейте в виду, что этого параметра нет в SQL 2019 и более ранних версиях, он появился только в SQL 2022. Он также доступен в Azure SQL Database и Azure Managed Instance.

Хотя string_split определенно полезен, он имеет пару недостатков, поэтому не всегда вас удовлетворит:

• Разделителем может быть только одиночный символ. Многосимвольные разделители встречаются не так часто, но бывает.


• Как отмечено выше, в SQL 2019 и ранее string_split может только возвращать значения, но вы не можете получить позицию значения в списке.


• string_split может вернуть только строки; и если входной список имеет тип (n)varchar(MAX), то тип возвращаемых строк также будет MAX, несмотря на то, что они обычно короткие, и типы MAX обычно ухудшают производительность. Если у вас имеет список чисел, вам нужно конвертировать их в int, подобно тому, что я делаю в примере выше.


• Допустим, список имеет вид '1,2,,4'. Что вы хотите получить в этом случае? string_split вернет пустую строку, а когда вы примените convert, то получите 0, что вряд ли является правильным. Возможно, вы предпочли бы получить NULL.


• Если значения окружают пробелы, string_split не будет их удалять, а разобьет слепо по разделителю, хотите вы этого или нет.


• Если ваша базе данных имеет уровень совместимости < 130, string_split будет недоступна, даже если вы используете версию SQL 2016 или выше.

В следующих разделах мы рассмотрим альтернативы string_split.

3.3 Две простые многооператорные функции

Если вы поищете в Интернете, то найдете бесконечное число функций для разделения строк в табличный формат. Здесь я представлю две простых функции, которые работают на версиях SQL 2008 и более поздних, одна для списка целых чисел, а другая для списка строк. Сразу хочу вас предупредить, что эти функции не являются самыми эффективными и, следовательно, они не подойдут, если ваш список содержит тысячи элементов. Но они совершенно адекватны, если вы передаете от клиента содержимое выбора нескольких элементов, число которых редко превышает 50.

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

Ниже представлена функция, которая разбивает список целых чисел с разделителями. Эта функция принимает параметр-разделитель, который может иметь длину до 10 символов. Эта функция возвращает список позиций элементов. Пустому элементу отвечает NULL. Если в списке присутствует нечисловое значение, будет получена ошибка преобразования.

CREATE FUNCTION intlist_to_tbl (@list  nvarchar(MAX),

                                @delim nvarchar(10))

   RETURNS @tbl TABLE (listpos int NOT NULL IDENTITY(1,1),

                       n       int NULL) AS

BEGIN

   DECLARE @pos       int = 1,

           @nextpos   int = 1,

           @valuelen  int,

           @delimlen  int = datalength(@delim) / 2



   WHILE @nextpos > 0

   BEGIN

      SELECT @nextpos = charindex(@delim COLLATE Czech_BIN2, @list, @pos)

      SELECT @valuelen = CASE WHEN @nextpos > 0 THEN @nextpos

                              ELSE len(@list) + 1

                         END - @pos

      INSERT @tbl (n)

         VALUES (convert(int, nullif(substring(@list, @pos, @valuelen), '')))

      SELECT @pos = @nextpos + @delimlen

   END

   RETURN

END

Вы, вероятно, удивились предложению COLLATE. Это небольшой ускоритель. Применяя бинарную коллацию, мы предотвращаем применение SQL Server полных правил Unicode при поиске разделителя. Это окупается при сканировании длинных строк. Почему Czech? Язык тут не имеет значения, поэтому я просто выбрал тот, что покороче.

И почему делится на 2 datalength, а не len? datalength возвращает длину в байтах, отсюда деление. len не учитывает концевые пробелы, поэтому она не работает, если разделителем служит пробел.

Вот два примера:

SELECT * FROM intlist_to_tbl('1,2,3, 677,7 , ,-1', ',')

SELECT * FROM intlist_to_tbl('1<->2<->3<-> 677<->7<-><->-1', '<->')

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

listpos     n

----------- -----------

1           1

2           2

3           3

4           677

5           7

6           NULL

7           -1

А вот пример, как вы можете использовать ее в простом запросе:

SELECT ...

FROM   tbl 

WHERE  col IN (SELECT n FROM intlist_to_tbl('1,2,3,4', ','))

Если окажется, что вы используете только списки с разделителем запятая, вам может надоесть каждый раз указывать разделитель. Тогда вам может показаться удобным использовать такую обертку:

CREATE FUNCTION intlisttotbl (@list nvarchar(MAX)) RETURNS TABLE AS

RETURN (

   SELECT listpos, n FROM intlist_to_tbl(@list, ',')

)

Я оставлю читателю в качестве упражнения придумать лучшее имя.

Теперь функция для списка строк. Она принимает входной параметр типа nvarchar(MAX), но возвращает как varchar, так и nvarchar столбец. Я объясню причину чуть позже. Подобно intlist_to_tbl, она возвращает позицию в списке. При этом обрезаются лидирующие и конечные пробелы. В отличие от intlist_to_tbl, пустые элементы возвращаются как пустые строки, а не NULL.

CREATE FUNCTION strlist_to_tbl (@list  nvarchar(MAX),

                                @delim nvarchar(10))

   RETURNS @tbl TABLE (listpos int NOT NULL IDENTITY(1,1),

                       str     varchar(4000) NOT NULL,

                       nstr    nvarchar(4000) NOT NULL) AS

BEGIN

   DECLARE @pos       int = 1,

           @nextpos   int = 1,

           @valuelen  int,

           @nstr      nvarchar(4000),

           @delimlen  int = datalength(@delim) / 2

   WHILE @nextpos > 0

   BEGIN

      SELECT @nextpos = charindex(@delim COLLATE Czech_BIN2, @list, @pos)

      SELECT @valuelen = CASE WHEN @nextpos > 0 THEN @nextpos

                              ELSE len(@list) + 1

                         END - @pos

      SELECT @nstr = ltrim(rtrim(substring(@list, @pos, @valuelen)))

      INSERT @tbl (str, nstr)

         VALUES (@nstr, @nstr)

      SELECT @pos = @nextpos + @delimlen

   END

   RETURN

END

Два примера:

SELECT * FROM strlist_to_tbl(N'Alpha (α) | Beta (β)|Gamma (γ)|Delta (δ)|', '|')

SELECT * FROM strlist_to_tbl(N'a///b///c///v///x', '///')

А вот результат:

listpos     str        nstr

----------- ---------- ---------- 

1           Alpha (a)  Alpha (α)

2           Beta (ß)   Beta (β)

3           Gamma (?)  Gamma (γ)

4           Delta (d)  Delta (δ)

5                      

 

listpos     str        nstr

----------- ---------- -----------

1           a          a

2           b          b

3           c          c

4           v          v

5           x          x

Заметьте, что в первом результирующем наборе греческие символы были заменены запасными символами в столбце str. Они не изменились в столбце nstr. (Хотя, если вы имеете коллацию Greek или UTF‑8, оба столбца будут идентичны.)

Вот два примера использования этой функции:

SELECT ...

FROM   tbl 

WHERE  varcharcol IN (SELECT str FROM strlist_to_tbl('a,b,c', ','))



SELECT ...

FROM   tbl 

WHERE  nvarcharcol IN (SELECT nstr FROM strlist_to_tbl('a,b,c', ','))

Эти примеры демонстрируют, зачем нужны два столбца. Если вы собираетесь использовать список со столбцом varchar, вам нужно использовать столбец str. Это важно по причине правил преобразования типов в SQL Server. Если вы по ошибке будете сравнивать столбец varcharcol с nstr, varcharcol будет преобразован в nvarchar, и это может сделать любой индекс на varcharcol неприменимым для запроса, что приведет к проблемам производительности, т.к. придется сканировать таблицу. И, наборот, если у вас столбец nvarchar, вам нужно сравнивать его со значением nvarchar, поскольку в противном случае результаты могут оказаться неправильными из-за замены сомволов при преобразовании их к varchar.

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

3.4 Что делать, если вы не можете использовать функцию?

Если вы находитесь в неприятной ситуации, когда string_split не работает, и вы не имеете разрешений на создание функций, что тогда делать? Один из вариантов, конечно, - это встроить тело функции в ваш код, но это не вполне привлекательно.

Альтернативным вариантом, популярным среди некоторых людей, является конвертация списка в документ XML. Это работает на всех версиях от SQL 2005 и выше:

DECLARE @list nvarchar(MAX) = '1<->99<->22<->33<->45',

        @xml  xml

SELECT @xml = '<x>' + replace(@list COLLATE Czech_BIN2, '<->', '</x><x>') + '</x>'

SELECT X.x.value('.', 'int') AS val

       --, row_number() OVER(ORDER BY X.x) AS listpos

FROM   @xml.nodes('/x/text()') X(x)

Чтобы объяснить, что тут происходит, вот результирующий XML:

<x>1</x><x>99</x><x>22</x><x>33</x><x>45</x>

Вы можете использовать запрос XML непосредственно в основном запросе, но, вероятно, проще поместить результат в временную таблицу и работать с ней.

Как видно, в запросе есть столбец listpos, который я закомментировал. Хотя кажется, что он дает желаемый результат, это, насколько мне известно, не задокументировано, и что вы не можете на это положиться. То есть в какой-то момент он может перестать работать.

Если вы используете SQL 2016, SQL 2017 или SQL 2019, и вам нужна позиция в списке, но вы не можете написать свою собственную функцию, есть вариант, который проще использовать, чем XML, а именно JSON:

DECLARE @list nvarchar(MAX) = '1,99,22,33,45'

SELECT convert(int, [key]) + 1 AS listpos, convert(int, value) AS n

FROM   OPENJSON('[' + @list + ']')

Т.е. просто заключите список в скобки и вперед. Если вы имеет разделитель, отличный от запятой, вам потребуется заменить его на запятую, чтобы удовлетворить синтаксису JSON.

OPENJSON возвращает результирующий набор из трех столбцов, но нас интересуют только key и value. Оба столбца имеют тип nvarchar(4000), поэтому вам нужно преобразовать их к int. Обратите внимание, что значения в ключе начинаются с нуля.

В этих трех примерах я использовал список целых чисел. Я должен предупредить, если вы планируете использовать XML или JSON для списка строк. Если значения строго алфавитные, то нет проблем. Но если имеются символы, специальные для XML или JSON, то метод даст сбой. Возможно сохранить представление с помощью секций CDATA, которые защищают специальные символы, как в примере, который я заимствовал у Yitzhak Khabinsky:

DECLARE @list nvarchar(MAX),

        @xml  xml

SET @list = 'Dog & [Pony],Always < then,Glenn & Co. > 100';

SELECT @xml = '<x><![CDATA[' + 

                   replace(@list COLLATE Czech_BIN2, ',', ']]></x><x><![CDATA[') + 

                ']]></x>';

SELECT @xml

SELECT X.x.value('.', 'nvarchar(30)') AS val

       --, row_number() OVER(ORDER BY X.x) AS listpos

FROM   @xml.nodes('/x/text()') X(x);

Вывод:

val

-------------------------

Dog & [Pony]

Always < then

Glenn & Co. > 100

Если вы чувствуете, что у вас в этот момент начинает кружиться голова, я вам сочувствую. Несмотря на сложность, это возможно лучшее решение, когда вы не можете написать функцию. Если вам нужна альтернатива, вы можете рассмотреть метод CTE, который я описываю в моей длинной статье. Этот метод может дать вам список позиций гарантированным способом.

Если речь идет о скорости, XML и JSON быстрее, чем функции, которые я показал вам в предыдущем разделе, и они будут работать хорошо со списками из тысяч значений. Особенно обратите внимание на добавление функции text() в методе .nodes. Без нее обработка XML займет на 50% времени больше. (Этим трюком я благодарен Yitzhak Khabinsky.)

3.5 Антипаттерн

Как ни удивительно, но я периодически встречаю людей, кто использует или предлагает использовать динамический SQL. Что-то типа этого:

SELECT @sql = 'SELECT ...FROM tbl WHERE col IN (' + @list + ')'

Здесь собраны все типы проблем. Риск SQL-инъекции. Это делает код более сложным для понимания и поддержки. (Просто представьте себе большой запрос на 50 строк, который кто-то завернул в динамический SQL только из-за списка). Может быть проблемой разрешение на использование. Это приводит к засорению кэша. И вишенкой на торте плохая производительность. Выше я говорил, что представленные функции не хороши для длинных списков - но они определенно лучше, чем динамический SQL. Он заставляет SQL Server долго парсить длинный список значений в IN.

Никогда не используйте его!

4. Списки с разделителями в столбце таблицы

Иногда можно встретить столбец таблицы, который содержит список значений с разделителями. Это антипатерн, который, похоже, становится довольно популярным с годами, несмотря на то, что реляционные базы данных построены на принципе, что ячейка (т.е. столбец в строке) должна содержать единственное атомарное значение. Хранение списка с разделителями нарушает этот принцип, и если вы храните данные таким способом, будьте готовы заплатить дорогую цену в смысле производительности и усложнением программирования. Правильно было бы хранить данные списка в дочерней таблице.

Тем не менее, вы можете столкнуться со списком с разделителями, который спроектировал кто-то другой. И даже если у вас есть власть менять проект, вам все же нужно как-то обработать его. Давайте сначала возьмем пример для работы:

CREATE TABLE orders (orderid   int   NOT NULL,

                     custid     int          NOT NULL,

                     orderdate  date         NOT NULL,

                     products   varchar(MAX) NOT NULL,

                     quantities varchar(MAX) NOT NULL,

                     prices     varchar(MAX) NOT NULL,

                     CONSTRAINT pk_orders PRIMARY KEY (orderid)

)

go

INSERT orders (orderid, custid, orderdate, products, quantities, prices)

   VALUES (1, 108, '20201215', 

           'A16769,B1234,B2679,DL123', '1,2,1,1', '100,123,9000,450'),

          (2, 985, '20201216', 

           'A16769,A8744,B1233,CBGB2,E98767', '3,4,1,1,7', '100,560,400,600,320'),

          (3, 254, '20201217', 

           'X5277', '19', '300')

go

SELECT * FROM orders

Это необычно плохой пример с тремя списками с запятыми-разделителями, которые синхронизированы друг с другом. (К счастью, я редко сталкиваюсь с такой дикостью!) Для простоты мы сначала проигнорируем столбцы quantities и prices, и выполним запрос, который перечислит заказы по одному товару на строку:

SELECT o.orderid, o.custid, p.str AS prodid

FROM   orders o

CROSS  APPLY strlist_to_tbl(o.products, ',') AS p

ORDER  BY o.orderid, prodid

Ключевым моментом здесь является оператор CROSS APPLY. Apply - это вид оператора соединения. Когда вы говорите A JOIN B, то добавляете условия в ON, которые связывают A и B, но сама B не может ссылаться на A. Например, B не может быть вызовом табличнозначной функции, которая принимает столбец A в качестве параметра. Но это в точности то, что APPLY делает для вас. С другой стороны, здесь нет предложения ON, т.к. связь между A и B содержится внутри B. (B может также быть подзапросом).

Вот результирующий набор:

orderid     custid      prodid

----------- ----------- ---------

1           108         A16769

1           108         B1234

1           108         B2679

1           108         DL123

2           985         A16769

2           985         A8744

2           985         B1233

2           985         CBGB2

2           985         E98767

3           254         X5277

Замечание. Имеется также OUTER APPLY. Разница между CROSS APPLY и OUTER APPLY не является темой данной статьи (смотрите ссылку в конце стаьи).

При нормальном проектировании должно быть две таблицы orders и orderdetails. Вот скрипт для создания новой таблицы и перемещения данных в столбцах products, quantities и prices в эту новую таблицу:

CREATE TABLE orderdetails (orderid   int NOT NULL,

                           prodid    varchar(10) NOT NULL,

                           qty       int         NOT NULL,

                           price     int         NOT NULL,

                           CONSTRAINT pk_orderdetails PRIMARY KEY (orderid, prodid)

);

INSERT orderdetails (orderid, prodid, qty, price)

   SELECT o.orderid, p.str AS prodid, q.n AS qty, c.n AS price

   FROM   orders o

   CROSS  APPLY strlist_to_tbl(o.products, ',') AS p

   CROSS  APPLY intlist_to_tbl(o.quantities, ',') AS q

   CROSS  APPLY intlist_to_tbl(o.prices, ',') AS c 

   WHERE  p.listpos = q.listpos

     AND  p.listpos = c.listpos;

ALTER TABLE orders DROP COLUMN products, quantities, prices

Чтобы связать значения из списка мы синхронизировали их в столбце listpos.

Вот та же операция, но с использованием string_split и третьего параметра для получения позиции в списке. Как отмечалось выше, это требует версии SQL 2022 или выше.

SELECT o.orderid, p.value AS prodid, 

       convert(int, q.value) AS qty, convert(int, c.value) AS price

FROM   orders o

CROSS  APPLY string_split(o.products, ',', 1) AS p

CROSS  APPLY string_split(o.quantities, ',', 1) AS q

CROSS  APPLY string_split(o.prices, ',', 1) AS c 

WHERE  p.ordinal = q.ordinal

  AND  p.ordinal = c.ordinal

Теперь, скажем, вам требуется обновить одно из значерий в списке. Ну, разве я не говорил, что вам нужно изменить проект, чтобы список с разделителями стал дочерней таблицей? ОК, вы затрудняетесь с дизайном, так что вам делать? Ответ: вам нужно распаковать данные во временную таблицу, выполнить обновление, а затем перестроить список. Как я говорил, реляционная база данных не предназначена для этого паттерна.

Вот как вы могли бы перестроить список, если у вас установлен SQL 2017 или выше. Для краткости я покажу только как перестроить столбец products. Два других я оставлю для упражнения читателю.

SELECT orderid, string_agg(prodid, ',') WITHIN GROUP (ORDER BY prodid)

FROM   orderdetails

GROUP  BY orderid

Функция string_agg является агрегатной функцией типа SUM или COUNT и строит конкатенированный список из всех входных значений, разделяемых строкой, которую вы укажите во втором параметре. Предложение WITHIN GROUP позволяет вам указать порядок в списке.

Если вы имеете SQL 2016 или ранее, то можете использовать FOR XML PATH, который является более окольным путем с интуитивно не вполне понятным синтаксисом:

; WITH CTE AS (

   SELECT orderid, p.products.value('.', 'nvarchar(MAX)') AS products

   FROM   orders o

   CROSS  APPLY (SELECT od.prodid + ','

                 FROM   orderdetails od

                 WHERE  o.orderid = od.orderid

                 ORDER  BY od.prodid

                 FOR XML PATH(''), TYPE) AS p(products)

)

SELECT orderid, substring(products, 1, len(products) - 1)

FROM   CTE

Я воздержусь от попыток объяснить, как это работает. Просто попытайтесь адаптировать этот шаблон к вашим потребностям. Или, в конце концов, измените дизайн таблицы...

5. Совет относительно производительности

Если у вас всего несколько элементов в списке, используемый вами метод разбиения списка не окажет сколь-нибудь значительного влияния на производительность. Более важно то, как вы получаете значения из списка в остальной запрос. Для упрощения я покажу такой пример:

SELECT ...

FROM   tbl 

WHERE  col IN (SELECT n FROM intlist_to_tbl('1,2,3,4', ','))

Однако оптимизатору сложно придумать лучший план, поскольку он не знает, что вернется из функции. Это становится более очевидным, если запрос сложный и включает в себя пару соединений и тому подобное. Это может вызвать плохую производительность, поскольку оптимизатор остановится на сканировании таблицы там, где он мог бы использовать индекс или наоборот. Это справедливо вне зависимости от того, используете вы собственную функцию, string_split или что-то типа XML или JSON.

По этой причине я рекомендую вам распаковывать список значений во временную таблицу, а затем использовать эту временную таблицу в вашем запросе, например, так:

CREATE TABLE #values (n int NOT NULL PRIMARY KEY)

INSERT #values(n)

   SELECT number FROM intlist_to_tbl('1,2,3,4', ',')



SELECT ...

FROM   tbl 

WHERE  col IN (SELECT n FROM #values)

Поскольку временная таблица имеет статистику, оптимизатор имеет знания о фактических значениях в списке и, следовательно, повышаются шансы на построение хорошего плана.

Ссылки по теме
1. Функция STRING_SPLIT
2. Как использовать функциональность массивов в SQL Server?
3. Значение уровня совместимости базы данных в SQL Server
4. Разница между различными бинарными коллациями (языки, версии, BIN против BIN2)
5. Оператор CROSS APPLY
6. Функция string_agg