Примеры задач

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

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

В этой статье мы поработаем с типичной базой сотрудников, относящихся к разным отделам. По каждому сотруднику известны его 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

EMPLOYEE

Таблица DEPARTMENT:

DEPARTMENT

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

• Найти имена сотрудников отдела 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:

Наконец, 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 и вернет сырые данные:

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 выборка сузится:

Затем применяется 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:

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:

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:

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 покажет финальный результат:

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

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

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