Join vs. Unterabfrage

Question

Ich bin ein MySQL-Anwender der alten Schule und habe immer die JOIN über die Unterabfrage. Aber heutzutage verwendet jeder Sub-Query, und ich hasse es; ich weiß nicht, warum.

Mir fehlt das theoretische Wissen, um selbst zu beurteilen, ob es einen Unterschied gibt. Ist eine Sub-Query genauso gut wie eine JOIN und gibt es deshalb keinen Grund zur Sorge?

Answer 1

Um die beiden zu vergleichen, sollten Sie zunächst Abfragen mit Unterabfragen unterscheiden:

1. eine Klasse von Unterabfragen, die immer eine entsprechende gleichwertige Abfrage haben, die mit Joins geschrieben wurde
2. eine Klasse von Unterabfragen, die nicht durch Joins umgeschrieben werden können

Für die erste Klasse von Abfragen wird ein gutes RDBMS Joins und Unterabfragen als gleichwertig ansehen und dieselben Abfragepläne erstellen.

Heutzutage macht das sogar mysql.

Dennoch, manchmal tut es nicht, aber das bedeutet nicht, dass Joins immer gewinnen - ich hatte Fälle, in denen die Verwendung von Unterabfragen in Mysql die Leistung verbesserte. (Zum Beispiel, wenn es etwas gibt, das den mysql-Planer daran hindert, die Kosten korrekt abzuschätzen, und wenn der Planer die Join-Variante und die Subquery-Variante nicht als gleich ansieht, dann können Subqueries die Joins übertreffen, indem sie einen bestimmten Pfad erzwingen).

Die Schlussfolgerung ist, dass Sie Ihre Abfragen sowohl für Join- als auch für Subquery-Varianten testen sollten, wenn Sie sicher sein wollen, welche Variante besser funktioniert.

Für die zweite Klasse der Vergleich macht keinen Sinn, da diese Abfragen nicht mit Joins umgeschrieben werden können und in diesen Fällen sind Unterabfragen der natürliche Weg, um die erforderlichen Aufgaben zu erledigen, und man sollte sie nicht diskriminieren.

Answer 2

Was in den zitierten Antworten meines Erachtens zu kurz gekommen ist, ist die Frage der vervielfältigt und problematische Ergebnisse, die sich aus bestimmten (Nutzungs-)Fällen ergeben können.

(obwohl Marcelo Cantos es erwähnt)

Ich möchte ein Beispiel aus den Lagunita-Kursen von Stanford über SQL anführen.

Studententisch

+------+--------+------+--------+
| sID  | sName  | GPA  | sizeHS |
+------+--------+------+--------+
|  123 | Amy    |  3.9 |   1000 |
|  234 | Bob    |  3.6 |   1500 |
|  345 | Craig  |  3.5 |    500 |
|  456 | Doris  |  3.9 |   1000 |
|  567 | Edward |  2.9 |   2000 |
|  678 | Fay    |  3.8 |    200 |
|  789 | Gary   |  3.4 |    800 |
|  987 | Helen  |  3.7 |    800 |
|  876 | Irene  |  3.9 |    400 |
|  765 | Jay    |  2.9 |   1500 |
|  654 | Amy    |  3.9 |   1000 |
|  543 | Craig  |  3.4 |   2000 |
+------+--------+------+--------+

Tabelle anwenden

(Bewerbungen für bestimmte Universitäten und Studiengänge)

+------+----------+----------------+----------+
| sID  | cName    | major          | decision |
+------+----------+----------------+----------+
|  123 | Stanford | CS             | Y        |
|  123 | Stanford | EE             | N        |
|  123 | Berkeley | CS             | Y        |
|  123 | Cornell  | EE             | Y        |
|  234 | Berkeley | biology        | N        |
|  345 | MIT      | bioengineering | Y        |
|  345 | Cornell  | bioengineering | N        |
|  345 | Cornell  | CS             | Y        |
|  345 | Cornell  | EE             | N        |
|  678 | Stanford | history        | Y        |
|  987 | Stanford | CS             | Y        |
|  987 | Berkeley | CS             | Y        |
|  876 | Stanford | CS             | N        |
|  876 | MIT      | biology        | Y        |
|  876 | MIT      | marine biology | N        |
|  765 | Stanford | history        | Y        |
|  765 | Cornell  | history        | N        |
|  765 | Cornell  | psychology     | Y        |
|  543 | MIT      | CS             | N        |
+------+----------+----------------+----------+

Versuchen wir, die GPA-Werte der Studenten zu finden, die sich an folgenden Universitäten beworben haben CS Hauptfach (unabhängig von der Universität)

Verwendung einer Unterabfrage:

select GPA from Student where sID in (select sID from Apply where major = 'CS');

+------+
| GPA  |
+------+
|  3.9 |
|  3.5 |
|  3.7 |
|  3.9 |
|  3.4 |
+------+

Der Durchschnittswert für diese Ergebnismenge ist:

select avg(GPA) from Student where sID in (select sID from Apply where major = 'CS');

+--------------------+
| avg(GPA)           |
+--------------------+
| 3.6800000000000006 |
+--------------------+

Verwendung einer Verknüpfung:

select GPA from Student, Apply where Student.sID = Apply.sID and Apply.major = 'CS';

+------+
| GPA  |
+------+
|  3.9 |
|  3.9 |
|  3.5 |
|  3.7 |
|  3.7 |
|  3.9 |
|  3.4 |
+------+

Durchschnittswert für diese Ergebnismenge:

select avg(GPA) from Student, Apply where Student.sID = Apply.sID and Apply.major = 'CS';

+-------------------+
| avg(GPA)          |
+-------------------+
| 3.714285714285714 |
+-------------------+

Es ist offensichtlich, dass der zweite Versuch in unserem Anwendungsfall zu irreführenden Ergebnissen führt, da er für die Berechnung des Durchschnittswerts Duplikate zählt. Es ist auch offensichtlich, dass die Verwendung von distinct mit der join - basierten Anweisung wird no das Problem zu beseitigen, da es fälschlicherweise eines von drei Vorkommen der 3.9 punkten. Der richtige Fall ist die Berücksichtigung von ZWEI (2) Vorkommen des 3.9 Punktzahl, da wir tatsächlich über ZWEI (2) Studenten mit dieser Punktzahl, die unsere Abfragekriterien erfüllen.

Es scheint, dass in einigen Fällen eine Unterabfrage der sicherste Weg ist, abgesehen von etwaigen Leistungsproblemen.

Answer 3

MSDN-Dokumentation für SQL Server sagt

Viele Transact-SQL-Anweisungen, die Unterabfragen enthalten, können alternativ als Joins formuliert werden. Andere Fragen können nur mit Unterabfragen gestellt werden. In Transact-SQL gibt es normalerweise keinen Leistungsunterschied zwischen einer Anweisung, die eine Subquery enthält, und einer semantisch äquivalenten Version ohne Subquery. In einigen Fällen, in denen das Vorhandensein geprüft werden muss, bringt ein Join jedoch eine bessere Leistung. Andernfalls muss die geschachtelte Abfrage für jedes Ergebnis der äußeren Abfrage verarbeitet werden, um die Beseitigung von Duplikaten zu gewährleisten. In solchen Fällen würde ein Join-Ansatz bessere Ergebnisse liefern.

Wenn Sie also etwas brauchen wie

select * from t1 where exists select * from t2 where t2.parent=t1.id

versuchen, stattdessen join zu verwenden. In anderen Fällen macht es keinen Unterschied.

Sage ich: Erstellung von Funktionen für Unterabfragen beseitigen das Problem des Clutters und ermöglichen es Ihnen, zusätzliche Logik in Unterabfragen zu implementieren. Ich empfehle daher, wann immer möglich, Funktionen für Unterabfragen zu erstellen.

Unordnung im Code ist ein großes Problem, und die Industrie arbeitet schon seit Jahrzehnten daran, sie zu vermeiden.

Answer 4

Nach meiner Beobachtung wie zwei Fälle, wenn eine Tabelle hat weniger als 100.000 Datensätze dann die Verbindung wird schnell funktionieren.

Aber wenn eine Tabelle mehr als 100.000 Datensätze hat, ist eine Unterabfrage das beste Ergebnis.

Ich habe eine Tabelle mit 500.000 Datensätzen, für die ich die folgende Abfrage erstellt habe, und die Ergebniszeit ist wie folgt

SELECT * 
FROM crv.workorder_details wd 
inner join  crv.workorder wr on wr.workorder_id = wd.workorder_id;

Ergebnis: 13,3 Sekunden

select * 
from crv.workorder_details 
where workorder_id in (select workorder_id from crv.workorder)

Ergebnis: 1,65 Sekunden

Answer 5

Unterabfragen werden in der Regel verwendet, um eine einzelne Zeile als atomaren Wert zurückzugeben, obwohl sie auch verwendet werden können, um Werte mit mehreren Zeilen mit dem Schlüsselwort IN zu vergleichen. Sie sind an fast jeder sinnvollen Stelle in einer SQL-Anweisung zulässig, einschließlich der Zielliste, der WHERE-Klausel usw. Eine einfache Unterabfrage kann als Suchbedingung verwendet werden. Zum Beispiel zwischen einem Paar von Tabellen:

SELECT title 
FROM books 
WHERE author_id = (
    SELECT id 
    FROM authors 
    WHERE last_name = 'Bar' AND first_name = 'Foo'
);

Beachten Sie, dass die Verwendung eines normalen Wertoperators auf die Ergebnisse einer Unterabfrage voraussetzt, dass nur ein Feld zurückgegeben werden muss. Wenn Sie das Vorhandensein eines einzelnen Wertes innerhalb einer Reihe anderer Werte prüfen möchten, verwenden Sie IN:

SELECT title 
FROM books 
WHERE author_id IN (
    SELECT id FROM authors WHERE last_name ~ '^[A-E]'
);

Dies unterscheidet sich natürlich von einem LEFT-JOIN, bei dem Sie nur Daten aus Tabelle A und B verknüpfen möchten, selbst wenn die Verknüpfungsbedingung keinen passenden Datensatz in Tabelle B findet usw.

Wenn Sie nur wegen der Geschwindigkeit besorgt sind, müssen Sie Ihre Datenbank überprüfen und eine gute Abfrage schreiben, um zu sehen, ob es einen signifikanten Unterschied in der Leistung gibt.