что лучше использовать join или подзапросы почему

Tips & tricks for MySQL Developers. Работа с SQL

Эта статья задумана мной как сборник некоторых интересных моментов по использованию и оптимизации SQL запросов в БД MySQL, на мой взгляд, плохо освещенных в интернете. Так, из статьи вы узнаете о конструкции with rollup, и о том, как переписать подзапросы in и not in на join’ы, а так же обновление и удаление данных в нескольких таблицах — одним запросом, и многое другое. Начнем по порядку.

Переписываем подзапросы с in и not in на join’ы

Одни из самых распространённых подзапросов являются запросы с in и not in. Причём, мы знаем, что в MySQL join запросы чаще всего отрабатывают быстрее за счёт эффективного построения плана выполнения запроса оптимизатором (в определённых условиях, а также для других БД это утверждение может быть диаметрально противоположным), поэтому попробуем переписать классические подзапросы на join. Как мы это будем делать? Для начала уясним то, что in запросы выводят все значения, которые присутствуют в обоих таблицах, а значит такому запросу будет однозначно соответствовать внутренний inner join. Запросы с not in наоборот выводят все значения, которые не присутствуют в подзапросе, а значит им уже будет соответствовать внешний outer join. Итак, начало положено, попробуем с этим что-нибудь сделать.

Для примера буду использовать тестовую БД world, которую можно взять с официального сайта mysql здесь

В БД world есть таблицы Country (страны) и CountryLanguage (официальные языки). Поставим себе задачу найти все страны, в которых говорят хотя бы на одном языке. Говоря другими словами, мы ищем территории с населением, у которого есть официальные языки. Напишем привычный in подзапрос:

На заметку, этот запрос можно переписать ещё и так:

Теперь, исходя из предположения выше, перепишем подзапрос на inner join:

Почти получилось, но у нас произошло дублирование данных, которое убираем через опцию distinct. Конечный вариант для всех полей таблицы получится таким:

Отлично! Подзапрос in успешно переписан на join.

Теперь немного сложнее — перепишем not in на outer join. Цель запроса — все территории, на которых не проживают люди и нет официальных языков. Снова вначале привожу стандартный not in подзапрос:

И показываю его же для not exists:

Как и в первом случае, перепишем на left join:

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

Такими нехитрыми преобразованиями мы смогли немного помочь оптимизатору запросов.

Сравнение строк в подзапросах

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

Для этих целей существует SQL запрос:

Такой запрос называется «конструктором строк» и может быть подчёркнут функцией ROW(). В этом случае мы бы написали:

Несмотря на свою привлекательность, конструктор строк имеет ряд ограничений:
1. Подзапрос должен возвращать одну строку, а не несколько
2. Вы не можете использовать операторы сравнения или <>, хотя это ограничение можно обойти специальными словами all, any, in или exists

Стоит обратить внимание, что такую конструкцию можно использовать не только для подзапросов, но и в скалярных выражениях:

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

Обновление и удаление данных одновременно из нескольких таблиц.

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

Скорее всего, вы сделаете запрос вида:

С другой стороны, никто не мешает сделать запрос, который обновит данные сразу в двух, трёх и более таблицах:

Правда, он вряд ли будет иметь смысл, но, тем не менее, такое возможно.

С операцией удаления ещё интереснее обстоят дела. Официальная документация декларирует такой синтаксис:

Что соответствует запросам вида:

В этих двух запросах удаление происходит из таблицы t1, а t2 используется для создания условия выборки данных.

И как вы уже догадались, для удаления данных одновременно из двух таблиц делаем так:

Немного про OLAP. Модификатор WITH ROLLUP

Возможно те, кто сталкивался с такими промышленными БД как Oracle или SQL Server при чтении заголовка вскрикнут: «Ну надо же!», — но, увы, я сразу остужу их пламенные возгласы. С версии MySQL 4.1.1, когда появился модификатор with rollup, эта тема не продвинулась ни на миллиметр, поэтому никаких кубов данных вы не сможете построить встроенными средствами данной БД.

Для тех, кто не в курсе, что означает модификатор with rollup кратко поясню, что он используется для создания отчетов, содержащих подытоги и окончательное итоговое значение. В примерах, буду снова использовать базу world.

Предположим, что нам нужно получить суммарное и среднее число проживающих людей на всех географических территориях (регионах), а также на континентах и во всём мире. Если решать в лоб, получим следующие запросы:

Суммарное и среднее число проживающих людей на всех географических территориях (регионах):

Суммарное и среднее число проживающих людей на всех континентах:

Суммарное и среднее число проживающих людей во всём мире:

Вместо выполнения этих запросов и последующего сложного объединения результатов, можно выполнить всего один:

Обратите внимание, что в некоторых строках в не агрегирующих колонках стоит NULL, что указывает на то, что данная строка является подытогом. Например, строку

нужно читать как в Южной Америке суммарное население составляет 345780000 человек, а среднее значение 24698571.4286

Является окончательным итогом по отношению к численности населения на всём земном шаре.

Положительный эффект модификатора with rollup заключается в том, что проход по записям происходит один раз! Кстати, эта функциональность очень удобна при выводе какой-либо статистики на сайте (программе). Если вас заинтересовала данная функциональность или остались вопросы, то за подробностями прошу в официальную документацию

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

Для просмотра текущих настроек:

Также возможно параметризировать настройки при запуске клиента в шелле

Эстетичный комментарий

И на закуску. После БД, отличных от MySQL, меня всегда удивляло, почему MySQL в однострочном комментарии, выглядящем как двойное тире, обязательно после себя требует пробел, табуляцию или другой управляющий символ, хотя по стандарту обязательного управляющего символа не должно быть. Согласитесь, когда пишешь какой-то запрос и нужно быстро закомментировать часть кода, уж очень долго ставить такое количество символов.

Что я имею ввиду. В MySQL мы пишем так:

(с пробелом перед SELECT), а в других БД:

(без управляющего символа).

Разгадка оказалась очень простой. Дело в том, что если вы напишите такой небрежный запрос

В итоге, чаще всего в своей работе для однострочных комментариев я использую символ решётки (#), нежели двойное тире с управляющим символом 🙂

UDP:
В комментариях есть сомнения в целесообразности переписывания запросов с in на join. Ниже, мой небольшой бенчмарк.

JOIN vs IN vs EXISTS

При работе с индексом JOIN (7.84 сек) сильно проигрывает по сравнению с IN (1.74 сек) и EXISTS (2.44 сек).
Ниже пример, когда колонки t11 и t22 без индекса:

Поставил limit, чтобы долго не ждать ответ. На результат он не влияет.

OUTER JOIN vs NOT IN vs NOT EXISTS

Без индекса эти запросы в MySQL 5.5 отрабатываются примерно за одинаковое время.
Ниже примеры с использованием индекса:

Как итог — результат зависит от версии БД и исходных данных!

Источник

Как думать на SQL?

Если вы похожи на меня, то согласитесь: SQL — это одна из тех штук, которые на первый взгляд кажутся легкими (читается как будто по-английски!), но почему-то приходится гуглить каждый простой запрос, чтобы найти правильный синтаксис.

А потом начинаются джойны, агрегирование, подзапросы, и получается совсем белиберда. Вроде такой:

Буэ! Такое спугнет любого новичка, или даже разработчика среднего уровня, если он видит SQL впервые. Но не все так плохо.

Легко запомнить то, что интуитивно понятно, и с помощью этого руководства я надеюсь снизить порог входа в SQL для новичков, а уже опытным предложить по-новому взглянуть на SQL.

Не смотря на то, что синтаксис SQL почти не отличается в разных базах данных, в этой статье для запросов используется PostgreSQL. Некоторые примеры будут работать в MySQL и других базах.

1. Три волшебных слова

2. Наша база

Давайте взглянем на базу данных, которую мы будем использовать в качестве примера в этой статье:

У нас есть книжная библиотека и люди. Также есть специальная таблица для учета выданных книг.

3. Простой запрос

Давайте начнем с простого запроса: нам нужны имена и идентификаторы (id) всех книг, написанных автором “Dan Brown”

Запрос будет таким:

Довольно просто. Давайте разберем запрос чтобы понять, что происходит.

3.1 FROM — откуда берем данные

Сейчас это может показаться очевидным, но FROM будет очень важен позже, когда мы перейдем к соединениям и подзапросам.

FROM указывает на таблицу, по которой нужно делать запрос. Это может быть уже существующая таблица (как в примере выше), или таблица, создаваемая на лету через соединения или подзапросы.

3.2 WHERE — какие данные показываем

WHERE просто-напросто ведет себя как фильтр строк, которые мы хотим вывести. В нашем случае мы хотим видеть только те строки, где значение в колонке author — это “Dan Brown”.

3.3 SELECT — как показываем данные

Весь запрос можно визуализировать с помощью простой диаграммы:

4. Соединения (джойны)

Теперь мы хотим увидеть названия (не обязательно уникальные) всех книг Дэна Брауна, которые были взяты из библиотеки, и когда эти книги нужно вернуть:

borrowings JOIN books ON borrowings.bookid=books.bookid — это, считай, новая таблица, которая была сформирована комбинированием всех записей из таблиц «books» и «borrowings», в которых значения bookid совпадают. Результатом такого слияния будет:

А потом мы делаем запрос к этой таблице так же, как в примере выше. Это значит, что при соединении таблиц нужно заботиться только о том, как провести это соединение. А потом запрос становится таким же понятным, как в случае с «простым запросом» из пункта 3.

Давайте попробуем чуть более сложное соединение с двумя таблицами.

Теперь мы хотим получить имена и фамилии людей, которые взяли из библиотеки книги автора “Dan Brown”.

На этот раз давайте пойдем снизу вверх:

Шаг Step 1 — откуда берем данные? Чтобы получить нужный нам результат, нужно соединить таблицы “member” и “books” с таблицей “borrowings”. Секция JOIN будет выглядеть так:

Результат соединения можно увидеть по ссылке.

Шаг 2 — какие данные показываем? Нас интересуют только те данные, где автор книги — “Dan Brown”

Шаг 3 — как показываем данные? Теперь, когда данные получены, нужно просто вывести имя и фамилию тех, кто взял книги:

Супер! Осталось лишь объединить три составные части и сделать нужный нам запрос:

Отлично! Но имена повторяются (они не уникальны). Мы скоро это исправим.

5. Агрегирование

Грубо говоря, агрегирования нужны для конвертации нескольких строк в одну. При этом, во время агрегирования для разных колонок используется разная логика.

Давайте продолжим наш пример, в котором появляются повторяющиеся имена. Видно, что Ellen Horton взяла больше одной книги, но это не самый лучший способ показать эту информацию. Можно сделать другой запрос:

Что даст нам нужный результат:

Каждая строка в результате представляет собой результат агрегирования каждой группы.

В примере выше функция count обрабатывала все строки (так как мы считали количество строк). Другие функции вроде sum или max обрабатывают только указанные строки. Например, если мы хотим узнать количество книг, написанных каждым автором, то нужен такой запрос:

Здесь функция sum обрабатывает только колонку stock и считает сумму всех значений в каждой группе.

6. Подзапросы

Подзапросы это обычные SQL-запросы, встроенные в более крупные запросы. Они делятся на три вида по типу возвращаемого результата.

6.1 Двумерная таблица

Есть запросы, которые возвращают несколько колонок. Хороший пример это запрос из прошлого упражнения по агрегированию. Будучи подзапросом, он просто вернет еще одну таблицу, по которой можно делать новые запросы. Продолжая предыдущее упражнение, если мы хотим узнать количество книг, написанных автором “Robin Sharma”, то один из возможных способов — использовать подзапросы:

6.2 Одномерный массив

Запросы, которые возвращают несколько строк одной колонки, можно использовать не только как двумерные таблицы, но и как массивы.

Допустим, мы хотим узнать названия и идентификаторы всех книг, написанных определенным автором, но только если в библиотеке таких книг больше трех. Разобьем это на два шага:

1. Получаем список авторов с количеством книг больше 3. Дополняя наш прошлый пример:

Можно записать как: [‘Robin Sharma’, ‘Dan Brown’]

2. Теперь используем этот результат в новом запросе:

Это то же самое, что:

6.3 Отдельные значения

Бывают запросы, результатом которых являются всего одна строка и одна колонка. К ним можно относиться как к константным значениям, и их можно использовать везде, где используются значения, например, в операторах сравнения. Их также можно использовать в качестве двумерных таблиц или массивов, состоящих из одного элемента.

Давайте, к примеру, получим информацию о всех книгах, количество которых в библиотеке превышает среднее значение в данный момент.

Среднее количество можно получить таким образом:

Теперь, наконец, можно написать весь запрос:

Это то же самое, что:

7. Операции записи

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

7.1 Update

Синтаксис запроса UPDATE семантически совпадает с запросом на чтение. Единственное отличие в том, что вместо выбора колонок SELECT ‘ом, мы задаем знаения SET ‘ом.

Если все книги Дэна Брауна потерялись, то нужно обнулить значение количества. Запрос для этого будет таким:

7.2 Delete

7.3 Insert

8. Проверка

Вот он в более удобном для чтения виде:

Этот запрос выводит список людей, которые взяли из библиотеки книгу, у которой общее количество выше среднего значения.

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

Источник

MySQL Что правильнее/быстрее: подзапрос в основном запросе или связка всех таблиц с группировкой?

Создал 2 разных запроса к базе MySQL. Оба запроса выводят одинаковый результат, но работают по разному. Хочется понять, какой из запросов более правильный/быстрый. Буду рад любым комментариям.

Запрос 1:

Запрос 2:

Я не силен в синтаксисе оператора EXPLAIN, но второй запрос мне показался более медленным из-за создания временной таблицы для хранения результата (Using temporary).

1 ответ 1

Если судить по Explain, то первый вариант будет оптимальнее. Второй вариант приводит еще и к созданию промежуточных таблиц, что замедлит работу.

Но в общем плане оба запроса не оптимальны, т.к. таблица Shop_orders перебирается полностью без всяких индексов.

Плюс даже без индексов Вы можете оптимизировать запросы, т.к. сейчас они слишком усложнены.

Запрос 1: можно легко обойтись без having (его лучше вообще избегать):

Запрос 2: его надо перевести на нормальный join и все:

Во втором запросе стоит убрать GROUP BY и заменить его на distinct, т.к. индекс по ID, даже если он есть, в данном случае не будет никогда использоваться, и лишняя группировка может привести к доп. ненужной нагрузке, если Вам подсчитывать реально ничего не надо.

Теоретически второй вариант должен быть предпочтительнее, но, как я сказал ранее, для начала надо разобраться с индексами и подумать, как их можно улучшить.

Источник

Что лучше использовать join или подзапросы почему

SQL, Structured query language («язык структурированных запросов») — формальный непроцедурный язык программирования, применяемый для создания, модификации и управления данными в произвольной реляционной базе данных, управляемой соответствующей системой управления базами данных (СУБД).

Какие существуют операторы SQL?

операторы определения данных (Data Definition Language, DDL):

операторы манипуляции данными (Data Manipulation Language, DML):

операторы определения доступа к данным (Data Control Language, DCL):

операторы управления транзакциями (Transaction Control Language, TCL):

Что означает NULL в SQL?

Что такое «временная таблица»? Для чего она используется?

Что такое «представление» (view) и для чего оно применяется?

Особенностями операции соединения являются следующее:

(INNER) JOIN Результатом объединения таблиц являются записи, общие для левой и правой таблиц. Порядок таблиц для оператора не важен, поскольку оператор является симметричным.

LEFT (OUTER) JOIN Производит выбор всех записей первой таблицы и соответствующих им записей второй таблицы. Если записи во второй таблице не найдены, то вместо них подставляется пустой результат ( NULL ). Порядок таблиц для оператора важен, поскольку оператор не является симметричным.

RIGHT (OUTER) JOIN LEFT JOIN с операндами, расставленными в обратном порядке. Порядок таблиц для оператора важен, поскольку оператор не является симметричным.

FULL (OUTER) JOIN Результатом объединения таблиц являются все записи, которые присутствуют в таблицах. Порядок таблиц для оператора не важен, поскольку оператор является симметричным.

CROSS JOIN (декартово произведение) При выборе каждая строка одной таблицы объединяется с каждой строкой второй таблицы, давая тем самым все возможные сочетания строк двух таблиц. Порядок таблиц для оператора не важен, поскольку оператор является симметричным.

Что лучше использовать JOIN или подзапросы?

Подзапросы лучше использовать в случаях, когда нужно вычислять агрегатные значения и использовать их для сравнений во внешних запросах.

HAVING используется для фильтрации результата GROUP BY по заданным логическим условиям.

Основное отличие ‘WHERE’ от ‘HAVING’ заключается в том, что ‘WHERE’ сначала выбирает строки, а затем группирует их и вычисляет агрегатные функции (таким образом, она отбирает строки для вычисления агрегатов), тогда как ‘HAVING’ отбирает строки групп после группировки и вычисления агрегатных функций. Как следствие, предложение ‘WHERE’ не должно содержать агрегатных функций; не имеет смысла использовать агрегатные функции для определения строк для вычисления агрегатных функций. Предложение ‘HAVING’, напротив, всегда содержит агрегатные функции. (Строго говоря, вы можете написать предложение ‘HAVING’, не используя агрегаты, но это редко бывает полезно. То же самое условие может работать более эффективно на стадии ‘WHERE’.)

GROUP BY используется для агрегации записей результата по заданным признакам-атрибутам.

При использовании GROUP BY все значения NULL считаются равными.

DISTINCT указывает, что для вычислений используются только уникальные значения столбца. NULL считается как отдельное значение. GROUP BY создает отдельную группу для всех возможных значений (включая значение NULL ).

Перечислите основные агрегатные функции.

SQL предоставляет несколько агрегатных функций:

COUNT (*) подсчитывает количество записей в таблице, не игнорируя значение NULL, поскольку эта функция оперирует записями, а не столбцами.

Какие ограничения на целостность данных существуют в SQL?

CHECK используется для ограничения множества значений, которые могут быть помещены в данный столбец. Это ограничение используется для обеспечения целостности предметной области, которую описывают таблицы в базе.

UNIQUE обеспечивает отсутствие дубликатов в столбце или наборе столбцов.

Как создать индекс?

Индекс можно создать либо с помощью выражения CREATE INDEX :

Что такое «хранимая процедура»?

Хранимая процедура — объект базы данных, представляющий собой набор SQL-инструкций, который хранится на сервере. Хранимые процедуры очень похожи на обыкновенные процедуры языков высокого уровня, у них могут быть входные и выходные параметры и локальные переменные, в них могут производиться числовые вычисления и операции над символьными данными, результаты которых могут присваиваться переменным и параметрам. В хранимых процедурах могут выполняться стандартные операции с базами данных (как DDL, так и DML). Кроме того, в хранимых процедурах возможны циклы и ветвления, то есть в них могут использоваться инструкции управления процессом исполнения.

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

Триггер (trigger) — это хранимая процедура особого типа, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено действием по модификации данных: добавлением, удалением или изменением данных в заданной таблице реляционной базы данных. Триггеры применяются для обеспечения целостности данных и реализации сложной бизнес-логики. Триггер запускается сервером автоматически и все производимые им модификации данных рассматриваются как выполняемые в транзакции, в которой выполнено действие, вызвавшее срабатывание триггера. Соответственно, в случае обнаружения ошибки или нарушения целостности данных может произойти откат этой транзакции.

Момент запуска триггера определяется с помощью ключевых слов BEFORE (триггер запускается до выполнения связанного с ним события) или AFTER (после события). В случае, если триггер вызывается до события, он может внести изменения в модифицируемую событием запись. Кроме того, триггеры могут быть привязаны не к таблице, а к представлению (VIEW). В этом случае с их помощью реализуется механизм «обновляемого представления». В этом случае ключевые слова BEFORE и AFTER влияют лишь на последовательность вызова триггеров, так как собственно событие (удаление, вставка или обновление) не происходит.

Курсор — это объект базы данных, который позволяет приложениям работать с записями «по-одной», а не сразу с множеством, как это делается в обычных SQL командах.

Порядок работы с курсором такой:

TIMESTAMP хранит значение равное количеству секунд, прошедших с полуночи 1 января 1970 года по усреднённому времени Гринвича. При получении из базы отображается с учётом часового пояса. Размер: 4 байта

Для каких числовых типов недопустимо использовать операции сложения/вычитания?

Какое назначение у операторов PIVOT и UNPIVOT в Transact-SQL?

PIVOT и UNPIVOT являются нестандартными реляционными операторами, которые поддерживаются Transact-SQL.

Расскажите об основных функциях ранжирования в Transact-SQL.

К примеру, у нас имеется набор данных следующего вида:

ROW_NUMBER – функция нумерации в Transact-SQL, которая возвращает просто номер записи.

Вернёт набор данных следующего вида:

DENSE_RANK так же возвращает ранг каждой записи, но в отличие от RANK в случае нахождения одинаковых значений возвращает ранг без пропуска следующего.

Ну, и на последок, продемонстрируем разницу между DENSE_RANK и RANK :

NTILE – функция Transact-SQL, которая делит результирующий набор на группы по определенному столбцу.

Оператор EXCEPT возвращает уникальные записи из левого входного запроса, которые не выводятся правым входным запросом.

Оператор INTERSECT возвращает уникальные записи, выводимые левым и правым входными запросами.

Требуется написать запрос, который вернет максимальное значение id и значение created для этого id :

Источник