MySQL Подзаявка с примери

Какво е подзаявка в SQL?

A подзаявка е SELECT заявка, която се съдържа в друга заявка. Вътрешната SELECT заявка обикновено се използва за определяне на резултатите от външната 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 с подзаявка. Същата идея премахва редове, които отговарят на условие, съдържащо се във втора таблица.

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

⚠️ Предупреждение: MySQL не позволява оператор да променя таблица и да избира от същата таблица в подзаявка в клаузата FROM. Ако се появи грешка 1093, обвийте вътрешната заявка в производна таблица, например SELECT * FROM (SELECT ...) AS t, Така че MySQL материализира резултата, преди промяната да бъде приложена. Също така е разумно първо да се изпълни вътрешният SELECT самостоятелно и да се потвърди броят на редовете, преди да се изпълни АКТУАЛИЗАЦИЯ или ИЗТРИЙ в производството.

Подзаявки срещу съединения

Както подзаявката, така и JOIN могат да комбинират информация от повече от една таблица, така че естественият въпрос е коя да изберете.

В сравнение със съединенията, подзаявките са лесни за използване и четене. Те не са толкова сложни, колкото Се присъедини къми следователно те често се използват от SQL начинаещи.

Но подзаявките имат проблеми с производителността. Използването на join вместо подзаявка понякога може да ви даде до 500 пъти по-висока производителност, защото оптимизаторът може да разреши join наведнъж, вместо да оценява вътрешния оператор многократно.

При избор се препоръчва използването на JOIN над подзаявка. Подзаявките трябва да се използват само като резервно решение, когато не можете да използвате операция JOIN, за да постигнете горното.

Подзаявките също така лесно се разделят на отделни логически компоненти, което е много полезно, когато тестване и отстраняване на грешки в заявките.

Въпроси и Отговори