Правильный порядок обработки инструкции select это

Перевод статьи «SQL Order of Operations».

Мы привыкли, что компьютер выполняет команды программиста последовательно, в том порядке, который указал автор кода. Однако SQL относится к декларативным языкам, то есть SQL-запрос описывает ожидаемый результат, а не способ его получения.

Давайте разберём, в какой последовательности выполняются шесть операций в SQL: SELECT, FROM, WHERE, GROUP BY, HAVING и ORDER BY.

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

База данных сотрудников

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

Таблица EMPLOYEE:

Таблица DEPARTMENT:

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

• Найти имена сотрудников отдела IT
• Посчитать количество сотрудников каждого отдела с зарплатой выше 80 000.

Начнем с получения имён  сотрудников отдела IT:

SELECT LAST_NAME, FIRST_NAME
  FROM EMPLOYEE
 WHERE DEPARTMENT = 'IT'

В первую очередь выполняется FROM EMPLOYEE:

Затем наступает очередь  WHERE DEPARTMENT = ‘IT’, который фильтрует колонку DEPARTMENT:

Наконец,  SELECT FIRST_NAME, LAST_NAME скрывает ненужные колонки и возвращает финальный результат:

Отлично! После первого препарирования выяснилось, что простой запрос с операторами SELECT, FROM, и WHERE выполняется по следующей схеме:

• FROM (выбор таблицы)
• WHERE (фильтрация строк)
• SELECT (возврат результирующего датасета).

Влияние ORDER BY на план выполнения запроса

Допустим, что начальнику не понравился отчет, основанный на предыдущем запросе, потому что он хочет видеть имена в алфавитном порядке. Исправим это с помощью ORDER BY:

SELECT LAST_NAME, FIRST_NAME
    FROM EMPLOYEE
   WHERE DEPARTMENT = 'IT'
ORDER BY FIRST_NAME

Выполняться такой запрос будет так же, как и предыдущий. Только в конце ORDER BY отсортирует строки в алфавитном порядке по колонке FIRST_NAME:

Таким образом, команды SELECT, FROM, WHERE и ORDER BY выполняются в следующей последовательности:

1. FROM (выбор таблицы)
2. WHERE (фильтрация строк)
3. SELECT (возврат результирующего датасета)
4. ORDER BY (сортировка)

GROUP BY и HAVING

Усложним задачу. Посчитаем количество сотрудников каждого отдела с зарплатой выше 80 000 и остортируем результат по убыванию. Нам подойдёт следующий запрос:

SELECT DEPARTMENT, COUNT(*)
    FROM EMPLOYEES
   WHERE SALARY > 80000
GROUP BY DEPARTMENT
ORDER BY COUNT(*) DESC

Как обычно, в первую очередь выполнится FROM EMPLOYEE и вернет сырые данные:

После выполнения WHERE SALARY > 80000 выборка сузится:

Затем применяется GROUP BY. При этом генерируется по одной записи для каждого отдельного значения в указанной колонке. В нашем примере мы создаем по одной записи для каждого отдельного значения колонки DEPARTMENT:

После этого применяется SELECT с COUNT(*), производя промежуточный результат:

Применение ORDER BY завершает выполнение запроса и возвращает конечный результат:

План выполнения данного запроса следующий:

1. FROM (выбор таблицы)
2. WHERE (фильтрация строк)
3. GROUP BY (агрегирование данных)
4. SELECT (возврат результирующего датасета)
5. ORDER BY (сортировка).

Добавим выражение HAVING

HAVING — это аналог WHERE для GROUP BY. С его помощью можно фильтровать агрегированные данные.

Давайте применим HAVING и определим, в каких отделах (за исключением отдела продаж) средняя зарплата сотрудников больше 80 000.

SELECT DEPARTMENT
    FROM EMPLOYEES
   WHERE DEPARTMENT <> 'SALES'
GROUP BY DEPARTMENT
  HAVING AVG(SALARY) > 80000

По уже известной нам схеме сначала выберем все данные из таблицы при помощи FROM EMPLOYEE:

Затем конструкция WHERE избавит нас от данных по отделу SALES:

GROUP BY сгенерирует следующие записи:

HAVING AVG(SALARY) > 80000 ограничит список:

А SELECT вернет финальный результат:

Порядок выполнения для данного запроса следующий:

1. FROM (выбор таблицы)
2. WHERE (фильтрация строк)
3. GROUP BY (агрегирование данных)
4. HAVING (фильтрация агрегированных данных)
5. SELECT (возврат результирующего датасета).

Новый оператор — JOIN

До этого момента мы имели дело с одной таблицей. А что если воспользоваться JOIN  и добавить ещё одну? Выясним фамилии и ID сотрудников, работающих в отделе с бюджетом более 275 000:

SELECT EMPLOYEE_ID, LAST_NAME
  FROM EMPLOYEES
  JOIN DEPARTMENT
    ON DEPARTMENT = DEPT_NAME
 WHERE BUDGET > 275000

FROM EMPLOYEE как обычно запрашивает данные из таблицы EMPLOYEES:

А теперь JOIN запросит сырые данные из DEPARTMENT и скомбинирует данные двух таблиц по условию ON DEPARTMENT = DEPT_NAME:

Потом применяем WHERE BUDGET > 275000:

SELECT EMPLOYEE_ID, LAST_NAME покажет финальный результат:

Для этого запроса план выполнения следующий:

1. FROM (выбор таблицы)
2. JOIN (комбинация с подходящими по условию данными из второй таблицы)
3. WHERE (фильтрация строк)
4. SELECT (возврат результирующего датасета).

Итог

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

1. FROM (выбор таблицы)
2. JOIN (комбинация с подходящими по условию данными из других таблиц)
3. WHERE (фильтрация строк)
4. GROUP BY (агрегирование данных)
5. HAVING (фильтрация агрегированных данных)
6. SELECT (возврат результирующего датасета)
7. ORDER BY (сортировка).

Помните, что если исключить из этого списка один из операторов, то план выполнения может измениться.

Для того чтобы понять, как получается
результат выполнения оператора SELECT,
рассмотрим концептуальную схему его
выполнения. Эта схема является именно
концептуальной, т.к. гарантируется, что
результат будет таким, как если бы он
выполнялся шаг за шагом в соответствии
с этой схемой. На самом деле, реально
результат получается более изощренными
алгоритмами, которыми «владеет»
конкретная СУБД.

Стадия 1. Выполнение одиночного оператора select

Если в операторе присутствуют ключевые
слова UNION, EXCEPT и INTERSECT, то запрос разбивается
на несколько независимых запросов,
каждый из которых выполняется отдельно:

Шаг 1 (FROM). Вычисляется прямое
декартовое произведение всех таблиц,
указанных в обязательном разделе FROM. В
результате шага 1 получаем таблицу A.

Шаг 2 (WHERE). Если в операторе SELECT
присутствует раздел WHERE, то сканируется
таблица A, полученная при выполнении
шага 1. При этом для каждой строки из
таблицы A вычисляется условное выражение,
приведенное в разделе WHERE. Только те
строки, для которых условное выражение
возвращает значение TRUE, включаются в
результат. Если раздел WHERE опущен, то
сразу переходим к шагу 3. Если в условном
выражении участвуют вложенные подзапросы,
то они вычисляются в соответствии с
данной концептуальной схемой. В результате
шага 2 получаем таблицу B.

Шаг 3 (GROUP BY). Если в операторе SELECT
присутствует раздел GROUP BY, то строки
таблицы B, полученной на втором шаге,
группируются в соответствии со списком
группировки, приведенным в разделе
GROUP BY. Если раздел GROUP BY опущен, то сразу
переходим к шагу 4. В результате шага 3
получаем таблицу С.

Шаг 4 (HAVING). Если в операторе SELECT
присутствует раздел HAVING, то группы, не
удовлетворяющие условному выражению,
приведенному в разделе HAVING, исключаются.
Если раздел HAVING опущен, то сразу переходим
к шагу 5. В результате шага 4 получаем
таблицу D.

Шаг 5 (SELECT). Каждая группа, полученная
на шаге 4, генерирует одну строку
результата следующим образом. Вычисляются
все скалярные выражения, указанные в
разделе SELECT. По правилам использования
раздела GROUP BY, такие скалярные выражения
должны быть одинаковыми для всех строк
внутри каждой группы. Для каждой группы
вычисляются значения агрегатных функций,
приведенных в разделе SELECT. Если раздел
GROUP BY отсутствовал, но в разделе SELECT есть
агрегатные функции, то считается, что
имеется всего одна группа. Если нет ни
раздела GROUP BY, ни агрегатных функций, то
считается, что имеется столько групп,
сколько строк отобрано к данному моменту.
В результате шага 5 получаем таблицу E,
содержащую столько колонок, сколько
элементов приведено в разделе SELECT и
столько строк, сколько отобрано групп.

Стадия 2. Выполнение операций union, except, intersect

Если в операторе SELECT присутствовали
ключевые слова UNION, EXCEPT и INTERSECT, то таблицы,
полученные в результате выполнения 1-й
стадии, объединяются, вычитаются или
пересекаются.

Стадия 3. Упорядочение результата

Если в операторе SELECT присутствует раздел
ORDER BY, то строки полученной на предыдущих
шагах таблицы упорядочиваются в
соответствии со списком упорядочения,
приведенном в разделе ORDER BY.

SQL is a declarative language. The result of a query must be what you would get if you evaluated as follows (from Microsoft):

Logical Processing Order of the SELECT statement

The following steps show the logical
processing order, or binding order,
for a SELECT statement. This order
determines when the objects defined in
one step are made available to the
clauses in subsequent steps. For
example, if the query processor can
bind to (access) the tables or views
defined in the FROM clause, these
objects and their columns are made
available to all subsequent steps.
Conversely, because the SELECT clause
is step 8, any column aliases or
derived columns defined in that clause
cannot be referenced by preceding
clauses. However, they can be
referenced by subsequent clauses such
as the ORDER BY clause. Note that the
actual physical execution of the
statement is determined by the query
processor and the order may vary from
this list.

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

The optimizer is free to choose any order it feels appropriate to produce the best execution time. Given any SQL query, is basically impossible to anybody to pretend it knows the execution order. If you add detailed information about the schema involved (exact tables and indexes definition) and the estimated cardinalities (size of data and selectivity of keys) then one can take a guess at the probable execution order.

Ultimately, the only correct ‘order’ is the one described ion the actual execution plan. See Displaying Execution Plans by Using SQL Server Profiler Event Classes and Displaying Graphical Execution Plans (SQL Server Management Studio).

A completely different thing though is how do queries, subqueries and expressions project themselves into ‘validity’. For instance if you have an aliased expression in the SELECT projection list, can you use the alias in the WHERE clause? Like this:

SELECT a+b as c
FROM t
WHERE c=...;

Is the use of c alias valid in the where clause? The answer is NO. Queries form a syntax tree, and a lower branch of the tree cannot be reference something defined higher in the tree. This is not necessarily an order of ‘execution’, is more of a syntax parsing issue. It is equivalent to writing this code in C#:

void Select (int a, int b)
{
   if (c = ...) then {...}
   int c = a+b;
}

Just as in C# this code won’t compile because the variable c is used before is defined, the SELECT above won’t compile properly because the alias c is referenced lower in the tree than is actually defined.

Unfortunately, unlike the well known rules of C/C# language parsing, the SQL rules of how the query tree is built are somehow esoteric. There is a brief mention of them in Single SQL Statement Processing but a detailed discussion of how they are created, and what order is valid and what not, I don’t know of any source. I’m not saying there aren’t good sources, I’m sure some of the good SQL books out there cover this topic.

Note that the syntax tree order does not match the visual order of the SQL text. For example the ORDER BY clause is usually the last in the SQL text, but as a syntax tree it sits above everything else (it sorts the output of the SELECT, so it sits above the SELECTed columns so to speak) and as such is is valid to reference the c alias:

SELECT a+b as c
FROM t
ORDER BY c;