Базовый курс SQL. Подзапросы

Forums:

[к оглавлению]

Всё, что мы изучали в предыдущих главах можно назвать запросами к БД. Это извлечение данных с помощью SELECT или другие операции: обновление, удаление, добавление данных. Что же такое подзапрос?

Подзапрос - запрос, который вложен внутрь другого запроса.

Давайте рассмотрим, для чего они используются.

Фильтрация с помощью подзапросов

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

Внешний ключ - это поле таблицы, в котором хранится первичный ключ другой таблицы. Посредством внешнего ключа СУБД понимает, какая именно строка второй таблицы соответствует данной строке.

Допустим, нам нужно узнать фамилии всех студентов, обучающихся в группе № 304F. Но таблица Students "не знает" названий групп, а только group_id. Чтобы получить нужную нам информацию, нам пришлось бы сделать 2 запроса:

  • Извлечь из Groups - group_id, зная group_name
    SELECT group_id
    FROM Groups
    WHERE group_name = '304F';

    Получим:

    group_id
    --------------
    31548
  • Извлечь из Students список student_surname, зная group_id
    SELECT student_surname
    FROM Students
    WHERE group_id = '31548';

    Результат:

    student_surname
    --------------------
    Петрашевский                                      
    Сейдинай                                          
    Адамченко                                         
    Распопов                                          
    Колобков                                          
    Шульгина  

Но это увеличивает количество запросов к БД пропорционально количеству списков, которые нам надо получить. Плюс к этому, мы достаём избыточные данные: group_id.

Вместо 2х запросов мы можем в условие WHERE поместить целый запрос, который найдёт, какая именно группа нам требуется. Это и будет подзапрос:

SELECT student_surname
FROM Students
WHERE group_id = (SELECT group_id
                 FROM Groups
                 WHERE group_name = '304F');

Результат получим идентичный:

student_surname
--------------------
Петрашевский                                      
Сейдинай                                          
Адамченко                                         
Распопов                                          
Колобков                                          
Шульгина  

"Изнутри наружу"

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

Что же лучше: два запроса или подзапрос?

Обычно лучше конечно подзапрос. Он помогает избежать лишней транспортировки между серверами и раскрытия избыточных данных. Это очень важный момент - конфиденциальность информации. Чем меньше полей мы задействуем для получения результата, тем защищённее наши данные, тем "красивее" код. Однако подзапросы могут негативно сказаться на производительности вашего кода, поэтому решение может быть разным для конкретных ситуаций. Также есть альтернативы, которые мы рассмотрим в главе 13.

Ещё пример

С помощью дополнительного подзапроса мы можем усовершенствовать пример, рассмотренный выше, и получить список студентов, обучающихся не только в одной группе, но и на всём факультете Компьютерных технологий. Для этого заменим условие в запросе к таблице Groups, и попросим СУБД найти все группы, faculty_id которых мы найдём с помощью подзапроса:

SELECT student_surname
FROM Students
WHERE group_id IN (SELECT group_id
                 FROM Groups
                 WHERE faculty_id = (SELECT faculty_id
                                   FROM Faculties
                                   WHERE faculty_name = 'Компьютерных технологий'));
  1. Сначала мы с помощью подзапроса к Faculties получили faculty_id
  2. Затем нашли массив групп данного faculty_id с помощью подзапроса к Groups
  3. И в конце получили список фамилий студентов, добавив условие group_id IN ('результат подзапроса')

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

student_surname
-----------------------
Петрашевский                                      
Сейдинай                                          
Егорова                                           
Адамченко                                         
Легран                                            
Распопов                                          
Колобков                                          
Шульгина                                          
Грошев 

Лимита на количество вложенных подзапросов нет, но мы помним о снижении производительности.

Форматирование

Если вы пишите SQL запросы, и особенно подзапросы, не пренебрегайте форматированием кода: переносите каждую команду на следующую строку, а каждый следующий подзапрос выравнивайте отступами. Это поможет избежать ошибок и сложностей в отладке кода.

Подзапросы как вычисляемые поля

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

  • Получить список всех студентов и их id:
    SELECT student_id, student_surname
    FROM Students;

    Получим:

    student_id  |  student_surname
    -------------------
    92571	  |  Петрашевский                                      
    92522	  |  Сейдинай                                          
    92435	  |  Егорова                                           
    92540	  |  Адамченко                                         
    92415	  |  Легран                                            
    92526	  |  Распопов                                          
    92527	  |  Колобков                                          
    92518	  |  Шульгина                                          
    92599	  |  Римский                                           
    92410	  |  Грошев                                            
    
  • Для каждого student_id выполнить запрос к таблице Grades: достать все grade_values и найти среднее между ними с помощью итоговой функции:
    SELECT AVG(grade_value) AS avg_grade
    FROM Grades
    WHERE student_id = 92571;

    Получим:

    avg_grade
    --------------
    4

    и так для каждого студента

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

SELECT student_id,
        student_surname,
        (SELECT AVG(grade_value)
         FROM Grades
         WHERE Grades.student_id = Students.student_id) AS avg_grade
FROM Students;

Результат:

student_id  |  student_surname  | avg_grade
---------------------------------------------------
92571	    |  Петрашевский    |  4                                      
92522	    |  Сейдинай        |  4                                         
92435	    |  Егорова         |  3                                           
92540	    |  Адамченко       |  <null>                                     
92415	    |  Легран          |  <null>                                            
92526	    |  Распопов        |  <null>                                          
92527	    |  Колобков        |  <null>                                          
92518	    |  Шульгина        |  <null>                                          
92599	    |  Римский         |  <null>                                           
92410	    |  Грошев          |  <null>                                            

Мы получили 3 столбца, один из которых является вычисляемым полем, по данным из двух таблиц. СУБД сама выполняет подзапрос для каждой строки из Students, не нужно повторяться и хранить лишние данные.

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

Обратите внимание на то, что мы также использовали полное название столбцов student_id для обоих таблиц: Students.student_id и Grades.student_id. Это стало необходимым, потому что имена столбцов совпадают, и в противном случае СУБД сравнивала бы значение столбца Grades с самим собой и результат получился бы некорректным:

student_id  |  student_surname  | avg_grade
---------------------------------------------------
92571	    |  Петрашевский    |  3.8                                      
92522	    |  Сейдинай        |  3.8                                         
92435	    |  Егорова         |  3.8                                           
92540	    |  Адамченко       |  3.8                                     
92415	    |  Легран          |  3.8                                            
92526	    |  Распопов        |  3.8                                          
92527	    |  Колобков        |  3.8                                          
92518	    |  Шульгина        |  3.8                                          
92599	    |  Римский         |  3.8                                           
92410	    |  Грошев          |  3.8                                            

Следующая страница. JOIN-ы, или объединения таблиц.
Оглавление.