���������� ������-������ 2009'3

Home k-press.ru


! ?

Десять распространенных ошибок SQL-программирования

Автор: Пламен Ратчев (Plamen Ratchev)
Опубликовано: 09.07.2010
Версия текста: 1.1

NULL и предикат NOT IN
Функции для индексированных колонок в предикатах
Некорректная колонка в подзапросе
Несоответствие типов данных в предикатах
Порядок вычисления предикатов
Внешние соединения (outer joins) и размещение предикатов
Подзапросы, возвращающие более одного значения
Использование SELECT *
Скалярные пользовательские функции
Излишнее использование курсоров
Заключение

SQL-программирование может быть и увлекательным, и многообещающим занятием. Люди, имеющие опыт программирования на таких традиционных языках программирования, как Java, C, C++ или VB, часто испытывают затруднения с адаптацией к "set-based"-образу мыслей. Даже бывалые SQL-программисты и DBA могут угодить в одну из ловушек языка SQL. Я там уже бывал, и могу поспорить, что и вы тоже. Изучение основ занимает время, и даже при этом некоторые ошибки трудно отследить.

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

SELECT TOP(10) mistake
FROM CommonSQLProgrammingMistakes
ORDER BY CHECKSUM(NEWID());

Вот этот список:

  1. NULL и предикат NOT IN.
  2. Функции для индексированных колонок в предикатах.
  3. Некоректная колонка в подзапросе.
  4. Несовпадение типов данных в предикатах.
  5. Порядок вычисления предикатов.
  6. Внешние соединения (outer joins) и размещение предикатов.
  7. Подзапросы, возвращающие более одного результата.
  8. Использование SELECT *.
  9. Скалярные пользовательские функции.
  10. Избыточное применение курсоров.

Представленные примеры используют диалект Transact-SQL, но большинство концепций пригодны для любой реализации SQL.

NULL и предикат NOT IN

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

Таблица цветов:
color
----------
Black
Blue
Green
Red
Таблица продуктов:
sku  product_description  color
---- -------------------- ------
1    Ball                 Red
2    Bike                 Blue
3    Tent                 NULL

Заметьте, что эти таблицы не блещут дизайном и не следуют правилам нормализации и передовому опыту. Однако это упрощенный сценарий, который должен помочь проиллюстрировать этот пример. На самом деле таблица colors должна бы содержать ключевую колонку с кодами цветов, на которую ссылалась бы таблица products.

Запрос должен выбрать список цветов, еще не использованных в продуктах. Другими словами, нам нужно сконструировать запрос, возвращающий только те цвета, для которых нет продуктов такого цвета. На первый взгляд может показаться, что предикат NOT IN предоставляет очень интуитивный способ создания такого запроса, очень близкий к тому, как проблема выражается на простом английском языке:

SELECT C.color
FROM Colors AS C
WHERE C.color NOT IN (SELECT P.color 
                      FROM Products AS P);

Вы можете ожидать, что запрос вернет две строки ('black' и 'green'), но на самом деле он возвратит пустой результат:

color
----------
 
(0 row(s) affected)

Это, очевидно, «неверно». В чем проблема? Просто SQL использует троичную логику, управляемую наличием NULL, являющимся не значением, а маркером, указывающим на отсутствующую (или неизвестную) информацию. Когда оператор NOT применяется к списку значений из подзапроса в предикате in, это переводится как:

"color NOT IN (Red, Blue, NULL)"

Что эквивалентно:

"NOT(color=Red OR color=Blue OR color=NULL)"

Выражение "color=NULL" вычисляется как UNKNOWN и, по правилам троичной логики, NOT UNKNOWN также сводится к UNKNOWN. В результате все строки отфильтровываются, и запрос выдает пустой результат.

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

Одно из решений – использовать предикат EXISTS вместо IN, так как EXISTS использует двоичную логику предиката, то есть TRUE/FALSE:

SELECT C.color
FROM Colors AS C
WHERE NOT EXISTS(SELECT * 
                 FROM Products AS P
                 WHERE C.color = P.color);

Этот запрос корректно вернет ожидаемый набор результатов:

color
----------
Black
Green

Еще одно решение выглядит так:

/* IS NOT NULL in the subquery */
SELECT C.color
  FROM Colors AS C
  WHERE C.color NOT IN (SELECT P.color 
                          FROM Products AS P 
                            WHERE P.color IS NOT NULL);
 
/* EXCEPT */
SELECT color
  FROM Colors
EXCEPT
SELECT color
FROM Products;
 
/* LEFT OUTER JOIN */
SELECT C.color
  FROM Colors AS C
    LEFT OUTER JOIN Products AS P
      ON C.color = P.color
    WHERE P.color IS NULL;

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

Функции для индексированных колонок в предикатах

Мы часто пробуем писать код путем прямого перевода исходного требования. Например, если у нас просят выбрать всех покупателей, чьи имена начинаются с буквы L, естественно написать примерно вот такой запрос, используя функцию LEFT, чтобы получить первые буквы имен:

SELECT customer_name
  FROM Customers
    WHERE LEFT(customer_name, 1) = 'L';

Вместо этого, если бы нас попросили подсчитать продажи за январь 2009, мы можем написать запрос типа следующего, использующий функцию DATEPART для извлечения нужных месяца и года из колонки sale_date:

SELECT SUM(sale_amount) AS total_sales
FROM Sales
WHERE DATEPART(YEAR, sale_date) = 2009
  AND DATEPART(MONTH, sale_date) = 1;

Эти запросы выглядят очень интуитивно, но вы обнаружите, что индексы, которые у вас (разумеется!) есть для колонок customer_name и sale_date, не используются, и что планы исполнения этих запросов показывают сканирование.

Проблема возникает из-за того, что функции передаются колонки индекса, которые затем движок запросов должен перебирать по одной строке. В таких случаях предикат выражения WHERE оказывается "non-SARGable" (SARG происходит от Search ARGument), и лучшее, что может сделать оптимизатор запросов – сканировать таблицу или индекс.

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

SELECT customer_name
  FROM Customers
    WHERE customer_name LIKE 'L%';

Эквивалентом второго запроса является следующее:

SELECT SUM(sale_amount) AS total_sales
  FROM Sales
    WHERE sale_date >= '20090101'
    AND sale_date <  '20090201';

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

Нужно заметить, что SQL Server развивается и становится «умнее». Например, рассмотрим следующий запрос, использующий функцию CAST для индексированной колонки sale_date:

SELECT SUM(sale_amount) AS total_sales
  FROM Sales
    WHERE CAST(sale_date AS DATE) = '20090101';

Если выполнить этот запрос на SQL Server 2005 или более раннем, вы увидите сканирование индекса. Но на SQL Server 2008 вы увидите поиск по индексу, несмотря на использование функции CAST. План исполнения показывает, что придикат трансформируется во что-то типа следующего:

SELECT SUM(sale_amount) AS total_sales
  FROM Sales
    WHERE sale_date >= '20090101'
    AND sale_date <  '20090102';

Однако, в общем, там, где можно, нужно использовать SARG-предикаты, а не полагаться на рост интеллекта оптимизатора.

Некорректная колонка в подзапросе

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

Давайте посмотрим на две очень простые таблицы. Одна – таблица Sales с данными о продажах, а другая – вспомогательная таблица Calendar, которая содержит календарные даты и праздники (здесь сокращенная).

Таблица Sales:
sale_date  sale_amount
---------- -----------
2009-01-01 120.50
2009-01-02 115.00
2009-01-03 140.80
2009-01-04 100.50
Таблица Calendar:
calendar_date holiday_name
------------- ----------------
2009-01-01    New Year's Day
2009-01-02    NULL
2009-01-03    NULL
2009-01-04    NULL
2009-01-05    NULL

Наша задача – получить данные о продажах только для праздников. Кажется, что написать такой запрос несложно:

SELECT sale_date, sale_amount
  FROM Sales AS S
    WHERE sale_date IN (SELECT sale_date 
                    FROM Calendar AS C
                    WHERE holiday_name IS NOT NULL);

Однако этот запрос просто возвращает все строки из таблицы Sales! Более пристальное рассмотрение запроса показывает, что виноват список SELECT в подзапросе. Он по ошибке ссылается на колонку sales_date из таблицы Sales вместо колонки calendar_date из таблицы Calendar.

Если проблема в этом, почему мы не получаем сообщение об ошибке? Несмотря на то, что результат не таков, как ожидалось, это по-прежнему корректное SQL-выражение. При использовании подзапросов колонки внешнего запроса видны внутреннему. Здесь мы неумышленно превратили самостоятельный подзапрос, который должен был сработать один раз и передать результат внешнему запросу, в коррелированный подзапрос, логически исполняемый по одному разу для каждой строки, возвращаемой внешним запросом.

В результате подзапрос сводится к sale_date IN (sale_date), что всегда верно, пока есть хоть один праздничный день в таблице Calendar, и наш набор результатов возвращает все строки из таблицы Sales. Конечно, это нетрудно исправить. Нужно просто использовать верную колонку из таблицы Calendar:

SELECT sale_date, sale_amount
  FROM Sales AS S
    WHERE sale_date IN (SELECT C.calendar_date 
                    FROM Calendar AS C
                    WHERE C.holiday_name IS NOT NULL);

Это показывает еще один важный момент: хорошо предварять имена колонок в подзапросах псевдонимами таблиц. Например, если бы мы использовали следующий псевдоним:

SELECT sale_date, sale_amount
FROM Sales AS S
WHERE sale_date IN (SELECT C.sale_date 
                    FROM Calendar AS C
                    WHERE holiday_name IS NOT NULL);

Этот запрос вернул бы ошибку "Error: Invalid column name 'sale_date'".

Несоответствие типов данных в предикатах

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

Например, у нас может быть таблица Customers, в которой есть колонка last_name типа VARCHAR:

CREATE TABLE Customers (
 customer_nbr INT NOT NULL PRIMARY KEY,
 first_name VARCHAR(35) NOT NULL,
 last_name VARCHAR(35) NOT NULL);

Затем используется следующая хранимая процедура для получения информации о пользователях по фамилии пользователя:

CREATE PROCEDURE GetCustomerByLastName
 @last_name NVARCHAR(35)
AS
 SELECT first_name, last_name
 FROM Customers
 WHERE last_name = @last_name;

Заметьте, что здесь параметр @last_name имеет тип данных NVARCHAR. Код «работает», но SQL Server придется выполнять неявное преобразование колонки фамилий в NVARCHAR, поскольку NVARCHAR – более приоритетный тип данных. Это может привести к потере производительности. Неявное преобразование отображается в плане запроса как CONVERT_IMPLICIT. В зависимости от сортировки и других факторов несоответствие типов данных может также мешать использованию поиска по индексу. Использование корректных типов данных решает проблему:

CREATE PROCEDURE GetCustomerByLastName
 @last_name VARCHAR(35)
AS
 SELECT first_name, last_name
 FROM Customers
 WHERE last_name = @last_name;

Во многих случаях эта ошибка является результатом распределения ответственности в команде, когда один член команды занимается дизайном таблиц, а другой реализует хранимые процедуры или пишет код. Еще одной причиной может быть соединение данных из разных источников, в которых некоторые колонки могут иметь разные типы данных. Тот же совет применим не только к ошибкам в символьных типах данных, но и к несоответствиям числовых типов данных (например, INT и FLOAT), или к смешению числовых и буквенных типов.

Порядок вычисления предикатов

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

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT

Эта последовательность описывает логический порядок выполнения запроса. Логически выражение FROM обрабатывается первым, определяя исходный набор данных, затем применяются предикаты WHERE, за ними GROUP BY и т.д.

Однако физически запросы обрабатываются не так, и оптимизатор запроса может свободно перемещать выражения в плане запроса, чтобы добиться наиболее эффективного по цене плана выборки данных. Это ведет к распространенному заблуждению, что фильтр в выражении WHERE применяется до обработки следующей фазы. В реальности в физическом плане запроса предикат может быть применен гораздо позже. Кроме того, для выполнения предикатов нет порядка слева направо. Например, если у вас есть выражение WHERE, содержащее "WHERE x=1 AND y=2", нет никакой гарантии, что "x=1" будет рассматриваться первым. Они могут исполняться в любом порядке.

Рассмотрим, например, следующую таблицу Accounts, где в колонке account_reference, Business-счетам соответствует числовая ссылка, а Personal-счетам – буквенная.

account_nbr account_type    account_reference
----------- --------------- -----------------
1           Personal        abc
2           Business Basic  101
3           Personal        def
4           Business Plus   5

В общем, это таблица с плохим дизайном. Колонка account_reference должна быть представлена как два разных атрибута, отдельно для Business- и Personal-счетов, каждый корректного типа данных (и даже не принадлежащих к этой таблице). Однако на практике часто приходится иметь дело с неудачно спроектированными системами, дизайн которых мы не можем изменить.

В данном сценарии корректен запрос всех счетов business-типа со значением ссылки, большим 20 (предполагая, что ссылка на счет имеет какое-то осмысленное числовое значение). Запрос может выглядеть так:

SELECT account_nbr, account_reference AS account_ref_nbr
FROM Accounts
WHERE account_type LIKE 'Business%'
  AND CAST(account_reference AS INT) > 20;

Однако он возвращает ошибку:

"Conversion failed when converting the varchar value 'abc' to data type int"

Этот запрос не проходит потому, что, как отмечено выше, не существует предопределенного порядка исполнения предикатов, и ничто не гарантирует, что предикат "account_type LIKE ‘Business%’" будет обрабатываться раньше, чем предикат "CAST(account_reference AS INT) > 20". В нашем случае второй предикат рассматривается раньше, что приводит к ошибке преобразования из-за несовместимости значений перcональных счетов в колонке account_reference.

Можно попытаться решить эту проблему с помощью производной таблицы (или табличного выражения, CTE), куда сперва отфильтровываются все бизнес-счета, а затем к ним применяется предикат:

SELECT account_nbr, account_ref_nbr
FROM (SELECT account_nbr, 
             CAST(account_reference AS INT) AS account_ref_nbr
      FROM Accounts
      WHERE account_type LIKE 'Business%') AS A
WHERE account_ref_nbr > 20;

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

Как говорилось выше, проблема состоит в плохом дизайне и непонимании того, как SQL Server выполняет запрос физически. В чем же решение? Лучшее решение – корректно спроектировать таблицу и исключить хранение смешанных данных в одной колонке. В данном же случае можно использовать выражение CASE, чтобы гарантировать, что в тип данных INT будут конвертироваться только числовые значения.

SELECT account_nbr, account_reference AS account_ref_nbr
  FROM Accounts
  WHERE account_type LIKE 'Business%'
  AND CASE WHEN account_reference NOT LIKE '%[^0-9]%' 
           THEN CAST(account_reference AS INT)
      END > 20;

Выражение CASE использует паттерн LIKE для проверки корректности числовых значений (используется логика двойного отрицания, которую можно описать так: «здесь нет ни одного символа, не являющегося цифрой»), и выполняет CAST только для этих значений. Для остальных значений выражение CASE возвращает NULL, который отфильтровывается, потому что NULL не совпадает ни с одним значением (даже с NULL).

Внешние соединения (outer joins) и размещение предикатов

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

Ключом к корректному использованию внешних соединений является понимание логических шагов выполнения внешнего соединения в запросе. Вот соответствующие шаги:

  1. Формируется перекрестное соединение (декартово произведение) двух таблиц в операторе FROM. Результатом декартова произведения являются все возможные комбинации строк из первой и второй таблиц.
  2. Применяются предикаты ON, отфильтровывающие строки, соответствующие логике предикатов.
  3. Все Outer-строки, отфильтрованные предикатами на шаге 2, возвращаются обратно. Строки из сохраняемой таблицы добавляются с их актуальными значениями атрибутов (значениями колонок), а атрибуты (колонки) несохраняемых таблиц устанавливаются в NULL.
  4. Применяются предикаты выражения WHERE.

Запрос с внешним соединением может принести совершенно разные результаты в зависимости от того, как вы его напишете, и от того, где в этом запросе размещены предикаты. Давайте посмотрим на пример, построенный на двух следующих таблицах, Customers и Orders:

Таблица Customers:
customer_nbr customer_name
------------ --------------
1            Jim Brown
2            Jeff Gordon
3            Peter Green
4            Julie Peters
Таблица Orders:
order_nbr   order_date customer_nbr order_amt
----------- ---------- ------------ ----------
1           2008-10-01 1            15.50
2           2008-12-15 2            25.00
3           2009-01-02 1            18.00
4           2009-02-20 3            10.25
5           2009-03-05 1            30.00

Задача в том, чтобы получить список всех покупателей и общую стоимость заказов с начала 2009 года. Инстинктивно можно написать такой запрос:

SELECT C.customer_name, SUM(COALESCE(O.order_amt, 0)) AS total_2009
  FROM Customers AS C
    LEFT OUTER JOIN Orders AS O
    ON C.customer_nbr = O.customer_nbr
  WHERE O.order_date >= '20090101'
  GROUP BY C.customer_name;

Но результаты выглядят неутешительно:

customer_name  total_2009
-------------- ------------
Jim Brown      48.00
Peter Green    10.25

Покупатели Jeff и Julie отсутствуют в результатах. В чем проблема? Чтобы понять, что пошло не так, давайте воспроизведем запрос по шагам, следуя логическому порядку его обработки. Первый шаг – перекрестное соединение двух исходных таблиц:

SELECT C.customer_name, O.order_amt
FROM Customers AS C
CROSS JOIN Orders AS O;

Результат – все возможные комбинации строк из обеих таблиц:

customer_name    order_amt  order_date
---------------- ---------- ----------
Jim Brown        15.50      2008-10-01
Jim Brown        25.00      2008-12-15
Jim Brown        18.00      2009-01-02
Jim Brown        10.25      2009-02-20
Jim Brown        30.00      2009-03-05
Jeff Gordon      15.50      2008-10-01
Jeff Gordon      25.00      2008-12-15
Jeff Gordon      18.00      2009-01-02
Jeff Gordon      10.25      2009-02-20
Jeff Gordon      30.00      2009-03-05
Peter Green      15.50      2008-10-01
Peter Green      25.00      2008-12-15
Peter Green      18.00      2009-01-02
Peter Green      10.25      2009-02-20
Peter Green      30.00      2009-03-05
Julie Peters     15.50      2008-10-01
Julie Peters     25.00      2008-12-15
Julie Peters     18.00      2009-01-02
Julie Peters     10.25      2009-02-20
Julie Peters     30.00      2009-03-05

Следующий шаг – применение предикатов ON оператора JOIN.

SELECT C.customer_name, O.order_amt, O.order_date
  FROM Customers AS C
    INNER JOIN Orders AS O
    ON C.customer_nbr = O.customer_nbr;

Результат запроса содержит только покупателей, делавших заказы. Поскольку покупатель Julie не делал заказов, он исключен из набора результатов:

customer_name  order_amt  order_date
-------------- ---------- ----------
Jim Brown      15.50      2008-10-01
Jeff Gordon    25.00      2008-12-15
Jim Brown      18.00      2009-01-02
Peter Green    10.25      2009-02-20
Jim Brown      30.00      2009-03-05

Третий шаг логической обработки запроса – возврат Outer-строк. Эти строки были исключены на предыдущем шаге, поскольку не отвечают предикатам JOIN.

SELECT C.customer_name, O.order_amt, O.order_date
  FROM Customers AS C
    LEFT OUTER JOIN Orders AS O
    ON C.customer_nbr = O.customer_nbr;

Сейчас покупатель Julie возвращается в набор результатов. Заметьте, что внешние строки из сохраняемой таблицы (Customers) имеют значения выбранных атрибутов (customer_name), а строки не сохраняемой таблицы (Orders) содержат NULL вместо значений атрибутов (order_amt и order_date):

customer_name  order_amt  order_date
-------------- ---------- ----------
Jim Brown      15.50      2008-10-01
Jim Brown      18.00      2009-01-02
Jim Brown      30.00      2009-03-05
Jeff Gordon    25.00      2008-12-15
Peter Green    10.25      2009-02-20
Julie Peters   NULL       NULL

Последний шаг – применение предикатов оператора WHERE:

SELECT C.customer_name, O.order_amt, O.order_date
  FROM Customers AS C
  LEFT OUTER JOIN Orders AS O
    ON C.customer_nbr = O.customer_nbr
  WHERE O.order_date >= '20090101';

Теперь картина ясна! Виноваты предикаты оператора WHERE. Покупатель Jeff удален из результатов запроса, потому что он не делал заказов после 1 января 2009 года, а покупатель Julie – потому что у нее вовсе не было заказов (поскольку строка для Julie содержит NULL в колонке order_date). В сущности, в данном случае предикат оператора WHERE превращает OUTER-соединение в INNER.

Чтобы исправить исходный запрос, достаточно переместить предикат WHERE в условие соединения.

SELECT C.customer_name, SUM(COALESCE(O.order_amt, 0)) AS total_2009
  FROM Customers AS C
  LEFT OUTER JOIN Orders AS O
    ON C.customer_nbr = O.customer_nbr
   AND O.order_date >= '20090101'
  GROUP BY C.customer_name;

Теперь запрос выдаст правильный результат, поскольку Jeff и Julie отфильтровываются в предикатах JOIN, но затем возвращаются обратно, когда возвращаются внешние строки.

customer_name  total_2009
-------------- ------------
Jeff Gordon    0.00
Jim Brown      48.00
Julie Peters   0.00
Peter Green    10.25

В более сложном примере с множественными соединениями некорректная фильтрация может произойти в последующих табличных операторах (например, соединения с другой таблицей), а не в операторе WHERE. Например, допустим, что у нас есть таблица OrderDetails, содержащая SKU продукта и количество. Запрос должен выдать список всех покупателей, с объемом заказа и количеством, для выбранных SKU. Следующий запрос кажется корректным:

SELECT C.customer_name, O.order_amt, D.qty
FROM Customers AS C
LEFT OUTER JOIN Orders AS O
  ON C.customer_nbr = O.customer_nbr
INNER JOIN OrderDetails AS D
  ON D.order_nbr = O.order_nbr
 AND D.sku = 101;

Однако здесь INNER JOIN с таблицей OrderDetails играет точно ту же роль, что и оператор WHERE в предыдущем примере, в сущности, превращая LEFT OUTER JOIN в INNER JOIN. Корректный запрос, удовлетворяющий требованиям, должен использовать LEFT JOIN для соединения с таблицей OrderDetails:

SELECT C.customer_name, 
       O.order_amt, 
       D.qty
  FROM Customers AS C
  LEFT OUTER JOIN Orders AS O
  ON C.customer_nbr = O.customer_nbr
LEFT JOIN OrderDetails AS D
  ON D.order_nbr = O.order_nbr
 AND D.sku = 101;

Подзапросы, возвращающие более одного значения

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

Таблица Products:
sku   product_description
----- ------------------
1     Bike
2     Ball
3     Phone
Таблица ProductPlants:
sku   plant_nbr
----- -----------
1     1
2     1
3     2

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

SELECT sku, product_description,
      (SELECT plant_nbr
       FROM ProductPlants AS B
       WHERE B.sku = A.sku) AS plant_nbr
FROM Products AS A;

Смысл здесь в том, чтобы просто показать технику; может существовать и более эффективный способ сделать то же самое. Однако все прекрасно работает и мы получаем корректный набор результатов:

sku  product_description plant_nbr
---- ------------------- -----------
1    Bike                1
2    Ball                1
3    Phone               2

Запрос будет прекрасно работать до того дня, когда компания решит делать мячи на заводе 3, чтобы справится с растущим спросом. Таблица ProductPlants теперь выглядит так:

sku   plant_nbr
----- -----------
1     1
2     1
2     3
3     2

Совершенно неожиданно наш запрос начнет выдавать следующую ошибку:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Сообщение об ошибке достаточно информативно. Вместо ожидаемого скалярного результата наш подзапрос выдает набор результатов. При имеющихся требованиях исправить это несложно. Чтобы перечислить все заводы, выпускающие конкретный продукт, нужно использовать JOIN:

SELECT A.sku, A.product_description, B.plant_nbr
FROM Products AS A
JOIN ProductPlants AS B
  ON A.sku = B.sku;

Теперь запрос завершается без ошибок и возвращает конкретный результат:

sku  product_description  plant_nbr
---- -------------------- -----------
1    Bike                 1
2    Ball                 1
2    Ball                 3
3    Phone                2

Заметьте, что та же ошибка может случиться в предикате, где колонка или выражение сравнивается с подзапросом, например, "… column = (SELECT value FROM Table)". В этом случае решение состоит в использовании предиката IN вместо "=".

Использование SELECT *

При первом знакомстве с SQL мы всегда превозносим того гения, который изобрел синтаксис SELECT *! Это так удобно и так просто использовать! Вместо явного перечисления всех имен колонок в запросе, достаточно использовать магический подстановочный символ «*» и получить все колонки. Вот, например, распространенное неверное использование SELECT * – извлечение всех пластиковых продуктов для вставки в другую таблицу с той же структурой:

INSERT INTO PlasticProducts
SELECT *
FROM Products
WHERE material_type = 'plastic';

Все готово! Однако однажды требования изменятся и в таблице Products появятся две новых колонки:

ALTER TABLE Products
ADD effective_start_date DATETIME,
    effective_end_date DATETIME;

Магический запрос неожиданно выдаст ошибку:

Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

Исправление состоит в явном перечислении имен колонок в запросе:

INSERT INTO PlasticProducts (sku, product_description, material_type)
SELECT sku, product_description, material_type
FROM Products
WHERE material_type = 'plastic';

Ситуация может быть еще хуже, если с использованием SELECT * создается представление, а позже в базовые таблицы добавляются (или удаляются) колонки.

ПРИМЕЧАНИЕ

Примечание: Если представление создается с использованием опции SCHEMABINDING, то базовые таблицы не могут измениться так, чтобы повлиять на определение представления.

Чтобы завершить, не используйте SELECT * в рабочем коде! Исключением может быть использование предиката EXISTS. Список выборки в подзапросе для предиката EXISTS игнорируется, поскольку важно только наличие строк.

Скалярные пользовательские функции

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

Одно из таких средств в SQL Server – скалярные пользовательские функции. Кажется очень удобным спрятать все сложные вычисления в функции, а затем просто вызывать ее в запросах. Однако скрытый недостаток состоит в том, что это может привести к существенным потерям производительности. При использовании в запросе скалярные функции вычисляются для каждой строки. При больших таблицах это может привести к очень медленно исполняющимся запросам. Особенно это верно, когда скалярной функции нужно обращаться за данными к другой таблице.

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

CREATE FUNCTION dbo.GetTotalSales(@sku INT)
RETURNS DECIMAL(15, 2)
AS
BEGIN
  RETURN(SELECT SUM(sale_amount)
         FROM Sales 
         WHERE sku = @sku);
END

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

SELECT sku, product_description, dbo.GetTotalSales(sku) AS total_sales
FROM Products;

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

SELECT P.sku, P.product_description, SUM(S.sale_amount) As total_sales
FROM Products AS P
JOIN Sales AS S
  ON P.sku = S.sku
GROUP BY P.sku, P.product_description;

А вот табличная функция, которую можно использовать для вычисления суммы продаж:

CREATE FUNCTION dbo.GetTotalSales(@sku INT)
RETURNS TABLE
AS
RETURN(SELECT SUM(sale_amount) AS total_sales
       FROM Sales 
       WHERE sku = @sku);

Табличную функцию можно вызывать в запросе с помощью оператора APPLY:

SELECT sku, product_description, total_sales
FROM Products AS P
CROSS APPLY dbo.GetTotalSales(P.sku) AS S;

Излишнее использование курсоров

Давайте скажем, не стесняясь – мы любим циклы! Когда мы начинаем программировать на VB, C, C++, Java или C#, одна из первых встречающихся нам конструкций – какая-то из форм цикла. Они могут удачно решать почти любую проблему, которая может встретиться.

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

Посмотрим на один пример. Дана таблица с ценами продуктов, нужно выполнить ежемесячное обновление цен; новые цены хранятся в другой таблице.

Таблица ProductPrices:
sku  price  effective_start_date effective_end_date
---- ------ -------------------- ------------------
1    10.50  2009-01-01           NULL
2    11.50  2009-01-01           NULL
3    19.00  2009-01-01           NULL
4    11.25  2009-01-01           NULL
Таблица NewPrices:
sku  price
---- ------
2    11.25
4    12.00

Решение с курсором может выглядеть так:

DECLARE @sku INT;
DECLARE @price DECIMAL(15, 2);
 
DECLARE PriceUpdates
CURSOR LOCAL
       FORWARD_ONLY
       STATIC
       READ_ONLY
FOR SELECT sku, price
    FROM NewPrices;
 
OPEN PriceUpdates;
 
FETCH NEXT FROM PriceUpdates
      INTO @sku, @price;
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
  UPDATE ProductPrices
  SET price = @price, 
      effective_start_date = CURRENT_TIMESTAMP
  WHERE sku = @sku; 
 
  FETCH NEXT FROM PriceUpdates
        INTO @sku, @price;
 
END
 
CLOSE PriceUpdates;
DEALLOCATE PriceUpdates;

Миссия закончена! Можно сделать заслуженный перерыв, пока исполняется запрос. Вскоре приходит понимание, что в SQL процедурная обработка строки за строкой не очень хорошо работает. Кроме того, что это очень медленно, наше решение длинное, его трудно читать и поддерживать. В этот момент мы понимаем, что сила SQL – в его работе с наборами данных. Ту же задачу можно решить, используя очень эффективный запрос, который проще понять и поддерживать: 

UPDATE ProductPrices
SET price = (SELECT N.price
             FROM NewPrices AS N
             WHERE N.sku = ProductPrices.sku),
    effective_start_date = CURRENT_TIMESTAMP
WHERE EXISTS(SELECT *
             FROM NewPrices AS N
             WHERE N.sku = ProductPrices.sku);

Есть разные способы написать работающий с наборами данных запрос, решающий данную проблему: использовать выражение MERGE, обновить данные с помощью Common Table Expression, или использовать характерное для SQL Server обновление через JOIN. Суть в использовании сильных сторон языка SQL и техники работы с наборами данных, и том, чтобы избегать процедурного подхода.

ПРИМЕЧАНИЕ

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

Заключение

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

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

Copyright 1994-2016 "-"