MySQL Підзапит із прикладами

Що таке підзапит у SQL?

A підзапит – це запит SELECT, який міститься всередині іншого запиту. Внутрішній запит на вибір зазвичай використовується для визначення результатів зовнішнього запиту на вибір, тому база даних спочатку оцінює внутрішній оператор, а потім передає його вивід вгору.

Внутрішній запит називається внутрішній запит або вкладений запит, а оператор, що його містить, називається зовнішній запитДавайте розглянемо синтаксис підзапиту.

Наведена вище діаграма показує загальну форму оператора: зовнішній оператор SELECT надає стовпці, які ви хочете побачити, а внутрішній оператор SELECT у дужках надає значення або список значень, з якими порівнюється речення WHERE.

Навіщо використовувати підзапит?

Перш ніж розглядати різні типи, корисно знати, коли підзапит заслуговує на своє місце в операторі.

Підзапит відповідає на запитання, значення фільтра якого заздалегідь невідоме. Його потрібно обчислити з самих даних. Поширеною скаргою клієнтів у відеотеці MyFlix є низька кількість фільмів, і керівництво хоче купувати фільми для категорії, яка має найменшу кількість фільмів. Ніхто не знає, яка це категорія, доки не буде запитана база даних, тому значення потрібно спочатку обчислити, а потім використовувати як фільтр.

Підзапити знаходяться за адресоюtracтивний з трьох практичних причин:

• читаність: Кожна частина логіки знаходиться у власному блоці в дужках, тому твердження читається як послідовність невеликих запитань, а не як один складний вираз.
• Ізоляція: Внутрішній запит можна виконати окремо, щоб підтвердити, що він повертає очікуване значення, що значно спрощує тестування та налагодження.
• Гнучкість: Той самий шаблон працює в реченнях WHERE, HAVING, SELECT та FROM, а також всередині операторів INSERT, UPDATE та DELETE.

Компромісом є швидкість, яка розглядається в порівнянні JOIN далі в цій статті.

Типи підзапитів у MySQL

MySQL підтримує три типи підзапитів, а тип визначається формою результату, який повертає внутрішній запит. Кожен тип пояснюється нижче з робочим прикладом для бази даних myflixdb.

1) Скалярний підзапит

A скалярний підзапит повертає рівно один рядок і один стовпець, тобто повертає одне значення. Оскільки результатом є одне значення, його можна використовувати будь-де, де дозволено літерне значення. Повертаючись до наведеної вище задачі MyFlix, можна використовувати запит, подібний до цього:

SELECT category_name FROM categories
WHERE category_id = (SELECT MIN(category_id) FROM movies);

Це дає результат:

Давайте подивимося, як працює цей запит.

Як видно зі схеми виконання, MySQL перші пробіжки SELECT MIN(category_id) FROM movies, отримує одне значення, і лише потім виконує зовнішній запит з цим значенням. Оскільки повертається одне значення, допустимі оператори – це стандартний набір порівняння: =, <> (Або !=), >, >=, < та <=.

💡 Порада: Якщо підзапит розміщено після = повертає більше одного рядка, MySQL викликає помилку 1242, Підзапит повертає більше 1 рядкаПереключити оператора на INабо звужте внутрішню речення WHERE.

2) Підзапит рядка

A підзапит рядка також повертає один рядок, але цей рядок може містити більше одного стовпця. Тому зовнішній запит порівнює рядок значень з конструктором рядків, а не з одним значенням.

SELECT full_names, contact_number FROM members
WHERE (membership_number, gender) = (SELECT membership_number, gender FROM members WHERE full_names = 'Janet Jones');

Допустимі оператори — це ті ж оператори порівняння, що перелічені вище, застосовані до всього рядка одночасно.

3) Підзапит до таблиці

A підзапит до таблиці повертає кілька рядків, а часто й кілька стовпців, тому зовнішній запит повинен використовувати оператор множини, такий як IN, NOT IN, ANY, ALLабо EXISTS.

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

SELECT full_names, contact_number FROM members
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

Давайте подивимося, як працює цей запит.

У цьому випадку внутрішній запит повертає більше одного результату, тому список номерів членства передається до IN оператор, і повертається кожен відповідний член.

Вкладення підзапитів на кілька рівнів глибини

Досі ви розглядали два рівні. Підзапит може також містити інший підзапит, який створює потрійний вкладений оператор.

Припустимо, що керівництво хоче винагородити найплатнішого учасника. Ми можемо виконати запит типу цього:

SELECT full_names FROM members
WHERE membership_number = (SELECT membership_number FROM payments
    WHERE amount_paid = (SELECT MAX(amount_paid) FROM payments));

Внутрішній запит знаходить найбільший платіж, середній запит перетворює цю суму на номер учасника, а зовнішній запит перетворює номер учасника на ім'я. Наведений вище запит дає такий результат:

Як використовувати підзапити з INSERT, UPDATE та DELETE

Підзапити не обмежуються операторами SELECT. Той самий шаблон у дужках працює всередині операторів модифікації даних, що дозволяє змінити весь набір рядків за один прохід без створення тимчасової таблиці.

INSERT з підзапитом. Підзапит може надати рядки, що вставляються, що копіює дані з однієї таблиці в іншу. Список стовпців оператора SELECT повинен збігатися зі списком стовпців оператора INSERT.

INSERT INTO vip_members (membership_number, full_names)
SELECT membership_number, full_names FROM members
WHERE membership_number IN (SELECT membership_number FROM payments WHERE amount_paid > 5000);

ОНОВЛЕННЯ за допомогою підзапиту. Тут внутрішній запит визначає, яких рядків торкатися. У наведеному нижче прикладі позначено кожного учасника, чия оренда все ще не сплачена.

UPDATE members
SET reminder_sent = 1
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

ВИДАЛИТИ з підзапитом. Та сама ідея видаляє рядки, які задовольняють умову, що міститься в другій таблиці.

DELETE FROM members
WHERE membership_number NOT IN (SELECT membership_number FROM movierentals);

⚠️ Попередження: MySQL не дозволяє оператору змінювати таблицю та вибирати з тієї ж таблиці всередині підзапиту в реченні FROM. Якщо з'являється помилка 1093, оберніть внутрішній запит у похідну таблицю, наприклад SELECT * FROM (SELECT ...) AS t, Так що MySQL матеріалізує результат до застосування змін. Також доцільно спочатку виконати внутрішній SELECT окремо та підтвердити кількість рядків, перш ніж запускати ОНОВЛЕННЯ або DELETE у виробництві.

Підзапити проти об'єднань

Як підзапит, так і JOIN можуть об'єднувати інформацію з кількох таблиць, тому виникає природне питання, яку з них обрати.

Порівняно з об'єднаннями, підзапити прості у використанні та легкі для читання. Вони не такі складні, як з'єднання, і тому їх часто використовують Початківці SQL.

Але підзапити мають проблеми з продуктивністю. Використання об'єднання замість підзапиту може іноді дати приріст продуктивності до 500 разів, оскільки оптимізатор може вирішити об'єднання за один прохід, замість того, щоб багаторазово обчислювати внутрішнє оператор.

За наявності вибору рекомендується використовувати JOIN замість підзапиту. Підзапити слід використовувати лише як резервне рішення, коли ви не можете використати операцію JOIN для досягнення вищезазначеного.

Підзапити також легко розбити на окремі логічні компоненти, що дуже корисно, коли Тестування і налагодження запитів.

Поширені запитання