C, PHP, VB, .NET

* Вложен SELECT

Публикувано на 01 март 2009 в раздел Бази от Данни.

SELECT professors.firstname, professors.lastname
FROM professors
WHERE professors.faculty_id IN (
	SELECT faculties.id
	FROM faculties
	WHERE faculties.univ_id = 1
);

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;

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;

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' 
      );

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’
	)
);

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'
      );

20 коментара

1. kade moga da pro4eta pove4e za JOIN

2. Ето тук:

   Многотаблични заявки SELECT

3. Внесох малко корекции в статията.

4. Александър - очаква се, че вие ще изпробвате примерите, а няма дамо да гледате. На много места съм показал и изходните данни, които се получават като резултат от заявката, но все пак смятам, че такива не са нужни.

5. Може би тък по-удобно би било пак да има снимки като илюстрация на казаното

6. В случая първата заявка, която съм написал, е сгрешена. Трябва да бъде следната:

   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’
   );

   Няма смисъл да се преминава от professors през faculties, след като те вече са свързани с JOIN. Съжалявам за грешката - поправя в статията и ще внеса разяснения.

   Относно въпроса - прав си, че връщат един и същи резултат. Не винаги, но в общия случай с вложен select изпълнението ще бъде по-бързо. И все пак еднозначен отговор "дали вложен select или join" не може да се даде лесно. За малки приложения и двете вършат чудесна работа. При сериозни бази данни обаче е нужна сериозна проверка на плана за изпълнение на заявката.

   Често при писането на заявки с JOIN, при които присъединявате изкувствено таблици (правите JOIN на таблица, която не учавства в изходните данни) се налага да слагате ключова дума DISTINCT поради наличието на дублирани резултати в изхода. Ако това е факт - това е директен сигнал, че заявката ви не е оптимизирана добре и може би трябва да преминете към вложен SELECT.

   Аз лично съм привърженик на вложения SELECT, въпреки лошата му слава от миналото и въпреки, че заявките "не изглеждат добре". В днешно време по-често дава по-бърз резултат, а и логиката на писането на заявка е много изчистена - правите свързване само на таблиците, които са в изхода и няма други "паразитни" сред тях.

7. Заявките
   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'

   Няма ли да върнат един и същ резултат. Не съм ги пробвал, но на теория би трябвало.
   Защо трябва да използваме вложени селекти.

8. При 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). Когато записите започват да се дублират и заявките стават все по-сложни - нещата "загрубяват" и е трудно да се даде еднозначен отговор "кое е по-добре". Но все пак от това, което съм чел и съм получавал като консултации за подобни спорове - обикновено мнението е, че при добър дизайн на базата данни и на заявката не би трябвало да има съществена разлика в двата подхода. Фактът, че при теб е имало забележима най-вероятно подсказва проблем.

9. Като цяло със селект е по разбираемо. Затова като започнах да се занимавам с програмиране използвах само вложени селекти. Когато, обаче записите станах прекалено много нещата започнаха да работят много бавно. Причината за това е, че при вложения селект, за всеки запис, който върне външния селект се прави нов селект(вложения). Така селектите стават много. Join-а е оптимизиран от такава гледна точка, всъщност не знам точно как е организиран, но като цяло е в пъти по-бърз. Намерих една статия, в която човека е напрвил конкретни тестове:
   http://www.codersrevolution.com/index.cfm/2008/7/31/MySQL-performance-INNER-JOIN-vs-subselect

10. Може би, както каза, нещата зависят от много фактори, затова сега не съм убеден, че съм прав. Колкото до постовете - в първия пост става въпрос за SQL Server, а във втория разликата е прекалено малка и не става ясно за какъв обем на данните се говори. Ще разуча още (когато имам време) и като открия нещо съществено ще пиша пак) :)

11. В примера където се иска да се изведат имената на преподаватели и име на университет с id=1 заявката, направена с JOIN е малко по-бърза(разликата е 0.01 sec) от същата заявка със вложен SELECT.Според правилото за писане на заявки би трябвало да е точно обратното.

12. Подобни проверки могат да се правят само ако таблиците имат поне няколко хиляди записи (по възможност стотици хиляди). Освен това всеки път се чисти кеша и т.н., за да не се рискува едната заявка да е повлияла на следващата (често срещано).

    Виж следните две статии:

    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/

    В тях съм направил кратко описание по темата.

13. Да не би да имаш в предвид "NOT IN"?

14. Здравейте,
    бих искал да попитам как да направя обратното на SELECT column IN (вложен селект), t.e. column (вложен селект), където вложеният селект ще ми върне няколко стойности.
    Благодаря.

15. Пояснение на горния коментар - > стрипна ми знака за различно между column {знак за различно} (вложен селект)

16. А, това ще да е, благодаря!

17. Имахме на контролното 3 таблици и 2рата със 3тата имаха връзка много към много. Задачата беше да направим вложен select от 3те таблици. Как става селекта между таблици много към много . Защо няма пример в тази статия как се прави вложен селект с таблици с връзка много към много (предмети - студенти)

18. Няма никаква разлика, когато връзката е М:М - принципът е един и същи.

    Ето пример: да се изведат имената на всички студенти, които учат предмет с име "бази от данни".

    Решение:

    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"
       )
    );

19. при задачата да се изведе списък на всички предмети от ‘Technical University’ като до тях се долепи името на факултета им, заявката извежда само 2 реда следователно трябва да е грешна.

20. Интересен извод, но погрешен. Направи SELECT * FROM subjects. Ще видиш, че в примерната база от данни така или иначе има въведени само два учебни предмета.