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

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

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

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

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

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

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

Таблица EMPLOYEE:

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	SALARY	DEPARTMENT
100	James	Smith	78,000	ACCOUNTING
101	Mary	Sexton	82,000	IT
102	Chun	Yen	80,500	ACCOUNTING
103	Agnes	Miller	95,000	IT
104	Dmitry	Komer	120,000	SALES

Таблица DEPARTMENT:

DEPT_NAME	MANAGER	BUDGET
ACCOUNTING	100	300,000
IT	101	250,000
SALES	104	700,000

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

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

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

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

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

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	SALARY	DEPARTMENT
100	James	Smith	78,000	ACCOUNTING
101	Mary	Sexton	82,000	IT
102	Chun	Yen	80,500	ACCOUNTING
103	Agnes	Miller	95,000	IT
104	Dmitry	Komer	120,000	SALES

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

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	SALARY	DEPARTMENT
101	Mary	Sexton	82,000	IT
103	Agnes	Miller	95,000	IT

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

FIRST_NAME	LAST_NAME
Mary	Sexton
Agnes	Miller

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

Таким образом, команды 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_ID	FIRST_NAME	LAST_NAME	SALARY	DEPARTMENT
100	James	Smith	78,000	ACCOUNTING
101	Mary	Sexton	82,000	IT
102	Chun	Yen	80,500	ACCOUNTING
103	Agnes	Miller	95,000	IT
104	Dmitry	Komer	120,000	SALES

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

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	SALARY	DEPARTMENT
101	Mary	Sexton	82,000	IT
102	Chun	Yen	80,500	ACCOUNTING
103	Agnes	Miller	95,000	IT
104	Dmitry	Komer	120,000	SALES

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

DEPARTMENT
ACCOUNTING
IT
SALES

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

DEPARTMENT	COUNT(*)
ACCOUNTING	1
IT	2
SALES	1

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

DEPARTMENT	COUNT(*)
IT	2
ACCOUNTING	1
SALES	1

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

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_ID	FIRST_NAME	LAST_NAME	SALARY	DEPARTMENT
100	James	Smith	78,000	ACCOUNTING
101	Mary	Sexton	82,000	IT
102	Chun	Yen	80,500	ACCOUNTING
103	Agnes	Miller	95,000	IT
104	Dmitry	Komer	120,000	SALES

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

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	SALARY	DEPARTMENT
100	James	Smith	78,000	ACCOUNTING
101	Mary	Sexton	82,000	IT
102	Chun	Yen	80,500	ACCOUNTING
103	Agnes	Miller	95,000	IT

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

DEPARTMENT	AVG(SALARY)
ACCOUNTING	79,250
IT	88,500

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

DEPARTMENT	AVG(SALARY)
IT	88,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_ID	FIRST_NAME	LAST_NAME	SALARY	DEPARTMENT
100	James	Smith	78,000	ACCOUNTING
101	Mary	Sexton	82,000	IT
102	Chun	Yen	80,500	ACCOUNTING
103	Agnes	Miller	95,000	IT
104	Dmitry	Komer	120,000	SALES

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

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	SALARY	DEPARTMENT	DEPT_NAME	MANAGER	BUDGET
100	James	Smith	78,000	ACCOUNTING	ACCOUNTING	100	300,000
101	Mary	Sexton	82,000	IT	IT	101	250,000
102	Chun	Yen	80,500	ACCOUNTING	ACCOUNTING	100	300,000
103	Agnes	Miller	95,000	IT	IT	101	250,000
104	Dmitry	Komer	120,000	SALES	SALES	104	700,000

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

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	SALARY	DEPARTMENT	DEPT_NAME	MANAGER	BUDGET
100	James	Smith	78,000	ACCOUNTING	ACCOUNTING	100	300,000
102	Chun	Yen	80,500	ACCOUNTING	ACCOUNTING	100	300,000
104	Dmitry	Komer	120,000	SALES	SALES	104	700,000

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

EMPLOYEE_ID	LAST_NAME
100	Smith
102	Yen
104	Komer

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

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]