* Вложен SELECT
Публикувано на 01 март 2009 в раздел Бази от Данни.
Когато сме разделили базата от данни на множество класове обекти, много често се налага да „прескачаме“ през един или повече обекти. Това се получава, когато между два или повече класа обекти няма пряка връзка с външен ключ или друга колона, по която да бъдат сравнени.
От примера с базата от данни с университет това може да се получи, ако например поискаме да изкараме имената на всички преподаватели от университет с id=1. Ще видите, че в таблицата professors съществува връзка между преподавател и факултет, но няма пряка връзка до таблицата university. Затова ще бъде нужно да използваме веднъж връзката до факултета, след което връзката на факултета до университета:
SELECT professors.firstname, professors.lastname FROM professors WHERE professors.faculty_id IN ( SELECT faculties.id FROM faculties WHERE faculties.univ_id = 1 );
Важно е да отбележим, че тук използвахме операторът IN, който означава „принадлежност към множество“. Вложеният SELECT може да връща повече от един ред в резултата си и затова не е уместно да се използва сравнение с равенство „=“. Действително – университет с id=1 може да има повече от един факултет, т.е. от вложената заявка ще се върне множество от id-та на факултети!
Ето как бихме могли да използваме същото в комбинация с JOIN, като добавим и името на университета в резултата от заявката:
SELECT professors.firstname, professors.lastname, university.name
FROM professors JOIN university
ON professors.faculty_id IN (
SELECT faculties.id
FROM faculties
WHERE faculties.univ_id = university.id
)
WHERE university.id = 1;
Условието в частта ON на FROM ще наричаме „свързващо“ за двете таблици. Условието, което се намира в клаузата WHERE на основния SELECT ще наричаме „ограничаващо„. Практически ние не сме ограничени например да ги разменим или дори обединим с AND – заявката ще работи по същия начин и ще върне същия резултат. Реално между двете условия просто е приложено логическо „И“. Добра практика е все пак да се спазва точно разделението и в „ON“ да се съдържат само свързващите условия, а всички останали да бъдат в „WHERE“.
Що се отнася до свързващите условия – там можем да спестим вложения SELECT по следния начин:
SELECT professors.firstname, professors.lastname, university.name
FROM professors JOIN faculties ON professors.faculty_id=faculties.id
JOIN university ON faculties.univ_id = university.id
WHERE university.id = 1;
Първо към таблицата university се присъединява faculties, а после към резултатната се присъединява и professors. Така „паразитно“ добавихме във FROM таблица faculties, която не участва в нито една колона от изхода. Записът е по-кратък и лесно четим, но реално този подход прави заявките по-бавни. Причината е, че при вложен select не се прави пълно свързване на таблиците, а само на подмножество от тях. Затова когато е възможен ще го предпочитаме. Най-лесното правило за писане на правилни заявки е следното: правете JOIN на само на таблиците споменати в условието на задачата.
Ето още един пример: да се изведат имената на преподавателите от Технически университет и името на факултета, в който работят:
SELECT professors.firstname, professors.lastname, faculties.name
FROM professors JOIN faculties ON professors.faculty_id = faculties.id
WHERE faculties.univ_id = (
SELECT id FROM university
WHERE university.name = 'Technical University'
);
Ясно се вижда, че в „свързващото условие“ се използват външните и първичните ключове на таблиците. В „ограничаващото условие“ пък добавяме допълнителни параметри, които намаляват обема на връщания резултат.
Защо не използвахме LEFT JOIN, вместо INNER ? Така бихме извели в списъка и преподавателите, които нямат запис в кой факултет работят. За нашата примерна база от данни обаче това не е възможно, защото и двете сравнявани полета (professors.faculty_id = faculties.id) са дефинирани като NOT NULL (faculties.id е PRIMARY KEY) при създаването на таблиците. Това означава, че няма да има разлика в резултата при различните видове JOIN, защото не сме допуснали възможност да съществува преподавател, който не принадлежи на някой факултет. На тези моменти трябва да се обръща специално внимание по време на етапа на проектиране на базата от данни.
В много случаи свързващото условие може да се измести към ограничаващото. Това се получава тогава, когато искаме да изведем информация само от едната таблица, а другата се използва само като ограничение. Например нека извадим списък от имената на преподавателите от университет с име „Technical University“ (не знаем неговото id):
SELECT professors.firstname, professors.lastname FROM professors WHERE professors.faculty_id IN ( SELECT faculties.id FROM faculties WHERE faculties.univ_id = ( SELECT id FROM university WHERE university.name = ‘Technical University’ ) );
Дали ще напишем тази заявка с JOIN между таблиците professors и university или ще я напишем по горния начин практически няма значение – резултатът ще бъде еквивалентен. Колкото повече връзки има по „пътя“ на заявката, толкова повече SELECT заявки трябва да вложим. Освен това никой не ни ограничава да използваме вложен SELECT в условията ON на JOIN (вижте втория пример в тази статия).
Ето и още една задача: да се изведе списък на всички предмети от ‘Technical University’ като до тях се долепи името на факултета им:
SELECT faculties.name, subjects.name
FROM subjects JOIN faculties
ON subjects.lead_professor_id IN(
SELECT professors.id
FROM professors
WHERE professors.faculty_id=faculties.id
)
WHERE faculties.univ_id IN(
SELECT university.id
FROM university
WHERE university.name = 'Technical University'
);
Именно при неизбежните вложени заявки изникват и най-големите проблеми на дизайна на базите от данни. Нека погледнем първоначалният вариант на ER диаграмата на базата от данни, която създадохме:
Тук моментално се вижда, че нямаме еднозначна връзка между студент и факултет. Тя е непряка чрез учебните предмети, но в никакъв случай не е достатъчна. Как например ще намерим в кой университет учи студента, ако той още не е записан за нито един учебен предмет?
В текущия случай поправката е сравнително лесна – трябва да добавим връзка между тях (например като колона „faculty_id“ в таблица students), да обновим всички текущи записи на студенти така, че да имат запис в кой факултет учат и накрая да направим новата колона NOT NULL, за да не позволяваме студенти без въведен факултет. Новата ER диаграма ще изглежда по следния начин:
* В горната диаграма атрибутите на класовете са скрити.
Представете си колко сложна е тази операция ако трябва да променим база от данни с множество връзки и огромно количество налична информация. Поради тази причина е изключително важно първо да направим дизайна на базата от данни изключително добре, а след това да попълваме информация в нея…
Задача: Направете предложените промени в дизайна на базата от данни university, използвайки ALTER TABLE, при положение, че таблицата students е все още празна.
kade moga da pro4eta pove4e za JOIN
Ето тук:
Многотаблични заявки SELECT
Внесох малко корекции в статията.
Александър – очаква се, че вие ще изпробвате примерите, а няма дамо да гледате. На много места съм показал и изходните данни, които се получават като резултат от заявката, но все пак смятам, че такива не са нужни.
Може би тък по-удобно би било пак да има снимки като илюстрация на казаното
В случая първата заявка, която съм написал, е сгрешена. Трябва да бъде следната:
Няма смисъл да се преминава от professors през faculties, след като те вече са свързани с JOIN. Съжалявам за грешката – поправя в статията и ще внеса разяснения.
Относно въпроса – прав си, че връщат един и същи резултат. Не винаги, но в общия случай с вложен select изпълнението ще бъде по-бързо. И все пак еднозначен отговор „дали вложен select или join“ не може да се даде лесно. За малки приложения и двете вършат чудесна работа. При сериозни бази данни обаче е нужна сериозна проверка на плана за изпълнение на заявката.
Често при писането на заявки с JOIN, при които присъединявате изкувствено таблици (правите JOIN на таблица, която не учавства в изходните данни) се налага да слагате ключова дума DISTINCT поради наличието на дублирани резултати в изхода. Ако това е факт – това е директен сигнал, че заявката ви не е оптимизирана добре и може би трябва да преминете към вложен SELECT.
Аз лично съм привърженик на вложения SELECT, въпреки лошата му слава от миналото и въпреки, че заявките „не изглеждат добре“. В днешно време по-често дава по-бърз резултат, а и логиката на писането на заявка е много изчистена – правите свързване само на таблиците, които са в изхода и няма други „паразитни“ сред тях.
Заявките
1)
SELECT professors.firstname, professors.lastname, faculties.name
FROM professors JOIN faculties ON professors.faculty_id = faculties.id
WHERE professors.faculty_id IN (
SELECT faculties.id
FROM faculties
WHERE faculties.univ_id = (
SELECT id
FROM university
WHERE university.name = ‘Technical University’
)
);
и
2)
SELECT professors.firstname, professors.lastname, faculties.name
FROM professors
LEFT JOIN faculties ON professors.faculty_id = faculties.id
LEFT JOIN university ON faculties.univ_id = university.id
WHERE university.name = ‘Technical University’
Няма ли да върнат един и същ резултат. Не съм ги пробвал, но на теория би трябвало.
Защо трябва да използваме вложени селекти.
При JOIN се прави произведение на таблици, а при вложения SELECT е така, както каза (създава се таблица, от нея се извлича друга и т.н. колкото на брой са влаганията). Практически правейки въпросното „произведение“ ти правиш аналогичен брой операции. Когато в данните има повторения обаче се налага да се слага атрибут DISTINCT – там вече произведенията стават много по-тежка операция.
Всичко в общи линии зависи от дизайна на базата данни (доколко е нормализирана), също така от индексите и т.н. Ето контра примери (първите два от Google за „join vs nested select“):
http://www.pcreview.co.uk/forums/thread-1166212.php
http://mysqlserverblog.com/2007/11/01/writing-joins-vs-nested-queries.aspx
При добре нормализирана база данни разлика почти няма да има (или ще бъде в една идея в полза на вложения SELECT). Когато записите започват да се дублират и заявките стават все по-сложни – нещата „загрубяват“ и е трудно да се даде еднозначен отговор „кое е по-добре“. Но все пак от това, което съм чел и съм получавал като консултации за подобни спорове – обикновено мнението е, че при добър дизайн на базата данни и на заявката не би трябвало да има съществена разлика в двата подхода. Фактът, че при теб е имало забележима най-вероятно подсказва проблем.
Като цяло със селект е по разбираемо. Затова като започнах да се занимавам с програмиране използвах само вложени селекти. Когато, обаче записите станах прекалено много нещата започнаха да работят много бавно. Причината за това е, че при вложения селект, за всеки запис, който върне външния селект се прави нов селект(вложения). Така селектите стават много. Join-а е оптимизиран от такава гледна точка, всъщност не знам точно как е организиран, но като цяло е в пъти по-бърз. Намерих една статия, в която човека е напрвил конкретни тестове:
http://www.codersrevolution.com/index.cfm/2008/7/31/MySQL-performance-INNER-JOIN-vs-subselect
Може би, както каза, нещата зависят от много фактори, затова сега не съм убеден, че съм прав. Колкото до постовете – в първия пост става въпрос за SQL Server, а във втория разликата е прекалено малка и не става ясно за какъв обем на данните се говори. Ще разуча още (когато имам време) и като открия нещо съществено ще пиша пак) :)
В примера където се иска да се изведат имената на преподаватели и име на университет с id=1 заявката, направена с JOIN е малко по-бърза(разликата е 0.01 sec) от същата заявка със вложен SELECT.Според правилото за писане на заявки би трябвало да е точно обратното.
Подобни проверки могат да се правят само ако таблиците имат поне няколко хиляди записи (по възможност стотици хиляди). Освен това всеки път се чисти кеша и т.н., за да не се рискува едната заявка да е повлияла на следващата (често срещано).
Виж следните две статии:
https://www.cphpvb.net/db/6909-join-%D0%B8%D0%BB%D0%B8-%D0%B2%D0%BB%D0%BE%D0%B6%D0%B5%D0%BD-select/
https://www.cphpvb.net/db/6947-in-vs-exists/
В тях съм направил кратко описание по темата.
Да не би да имаш в предвид „NOT IN“?
Здравейте,
бих искал да попитам как да направя обратното на SELECT column IN (вложен селект), t.e. column (вложен селект), където вложеният селект ще ми върне няколко стойности.
Благодаря.
Пояснение на горния коментар – > стрипна ми знака за различно между column {знак за различно} (вложен селект)
А, това ще да е, благодаря!
Имахме на контролното 3 таблици и 2рата със 3тата имаха връзка много към много. Задачата беше да направим вложен select от 3те таблици. Как става селекта между таблици много към много . Защо няма пример в тази статия как се прави вложен селект с таблици с връзка много към много (предмети – студенти)
Няма никаква разлика, когато връзката е М:М – принципът е един и същи.
Ето пример: да се изведат имената на всички студенти, които учат предмет с име „бази от данни“.
Решение:
SELECT name FROM students WHERE id IN ( SELECT student_id FROM zapisani_studenti WHERE subject_id IN ( SELECT id FROM subjects WHERE name = "Bazi ot danni" ) );при задачата да се изведе списък на всички предмети от ‘Technical University’ като до тях се долепи името на факултета им, заявката извежда само 2 реда следователно трябва да е грешна.
Интересен извод, но погрешен. Направи SELECT * FROM subjects. Ще видиш, че в примерната база от данни така или иначе има въведени само два учебни предмета.