Порядок выполнения SQL-операций

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

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

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

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

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

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

Таблица EMPLOYEE:

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT
100James Smith78,000ACCOUNTING
101MarySexton82,000IT
102ChunYen80,500ACCOUNTING
103AgnesMiller95,000IT
104DmitryKomer120,000SALES
EMPLOYEE

Таблица DEPARTMENT:

DEPT_NAMEMANAGERBUDGET
ACCOUNTING100300,000
IT101250,000
SALES104700,000
DEPARTMENT

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

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

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

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

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

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT
100JamesSmith78,000ACCOUNTING
101MarySexton82,000IT
102ChunYen80,500ACCOUNTING
103AgnesMiller95,000IT
104DmitryKomer120,000SALES

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

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT
101MarySexton82,000IT
103AgnesMiller95,000IT

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

FIRST_NAMELAST_NAME
MarySexton
AgnesMiller

Отлично! После первого препарирования выяснилось, что простой запрос с операторами 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:

FIRST_NAMELAST_NAME
AgnesMiller
MarySexton

Таким образом, команды 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 и вернет сырые данные:

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT
100JamesSmith78,000ACCOUNTING
101MarySexton82,000IT
102ChunYen80,500ACCOUNTING
103AgnesMiller95,000IT
104DmitryKomer120,000SALES

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

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT
101MarySexton82,000IT
102ChunYen80,500ACCOUNTING
103AgnesMiller95,000IT
104DmitryKomer120,000SALES

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

DEPARTMENT
ACCOUNTING
IT
SALES

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

DEPARTMENTCOUNT(*)
ACCOUNTING1
IT2
SALES1

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

DEPARTMENTCOUNT(*)
IT2
ACCOUNTING1
SALES1

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

  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:

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT
100JamesSmith78,000ACCOUNTING
101MarySexton82,000IT
102ChunYen80,500ACCOUNTING
103AgnesMiller95,000IT
104DmitryKomer120,000SALES

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

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT
100JamesSmith78,000ACCOUNTING
101MarySexton82,000IT
102ChunYen80,500ACCOUNTING
103AgnesMiller95,000IT

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

DEPARTMENTAVG(SALARY)
ACCOUNTING79,250
IT88,500

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

DEPARTMENTAVG(SALARY)
IT88,500

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

DEPARTMENT
IT

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

  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:

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT
100JamesSmith78,000ACCOUNTING
101MarySexton82,000IT
102ChunYen80,500ACCOUNTING
103AgnesMiller95,000IT
104DmitryKomer120,000SALES

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

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENTDEPT_NAMEMANAGERBUDGET
100JamesSmith78,000ACCOUNTINGACCOUNTING100300,000
101MarySexton82,000ITIT101250,000
102ChunYen80,500ACCOUNTINGACCOUNTING100300,000
103AgnesMiller95,000ITIT101250,000
104DmitryKomer120,000SALESSALES104700,000

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

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENTDEPT_NAMEMANAGERBUDGET
100JamesSmith78,000ACCOUNTINGACCOUNTING100300,000
102ChunYen80,500ACCOUNTINGACCOUNTING100300,000
104DmitryKomer120,000SALESSALES104700,000

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

EMPLOYEE_IDLAST_NAME
100Smith
102Yen
104Komer

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

  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 (сортировка).

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

[customscript]techrocks_custom_after_post_html[/customscript]

[customscript]techrocks_custom_script[/customscript]

7 комментариев к “Порядок выполнения SQL-операций”

  1. Юлия Ч

    Потрясающая статья, все, что оставалось не совсем понятным для меня, прояснилось благодаря такой понятной подаче!

  2. Виктор

    Еще можно добавить, что между SELECT и ORDER BY выполнится DISTINCT.
    Или я не прав?

  3. Павел

    Как-то интересно написано про декларативный язык и строгий порядок.
    Порядок определяется планировщиком БД на основе своих расчетов и не обязан поддерживать никакой порядок, он обязан выдать только то, что от него ожидают.
    Например, при использования join в текущем примере база в реальности скорее всего сперва сделает выборку из таблицы department отфильтруя необходимые записи и только потом выберет подходящие записи из employee.

  4. Аноним

    УЖАСНАЯ СТАТЬЯ!
    Что значить SELECT скрывает остальные колонки? Он ничего не скрывает, а выбирает только те колонки, которые указаны.
    Именно поэтому плохо, когда люди указывают в выборке ‘*’, а по факту им нужна одна-две колонки. Плохо это тем, что системе приходится читать не нужные колонки, а это , как минимум, увеличение времени выполнения запроса и нагрузка на оперативку, так как их приходится подгружать в оперативную память. это важно!
    Вы бы хоть раз план выполнения запроса отрыли, да посмотрели как в реальности все происходит.
    Понятно что вы хотели упростить для тех кто вообще не понимает как это работает, но так как написали вы все равно писать нельзя.

Оставьте комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Прокрутить вверх