Die Beantwortung einiger Fragen erfordert zwei Schritte. „Welche Bestellungen liegen über dem Durchschnitt?“ erfordert, dass Sie zuerst den Durchschnitt ermitteln und dann jede Bestellung damit vergleichen. SQL-Unterabfragen ermöglichen es Ihnen, beide Schritte als eine Anweisung zu schreiben.

In diesem Tutorial wird jeder Typ anhand von zwei kleinen Tabellen behandelt. Wir haben diese Abfragen in SQLite 3.50 ausgeführt und die angezeigten Ergebnisse stammen aus diesem Lauf. Die Standard-Unterabfragesyntax wird weitgehend unterstützt und wir weisen auf die Stellen hin, an denen sich die Datenbanken unterscheiden.

Was ist eine Unterabfrage?

Eine Unterabfrage, auch innere oder verschachtelte Abfrage genannt, ist eine SELECT-Anweisung, die in runden Klammern in eine andere Anweisung eingefügt wird. Konzeptionell erzeugt die innere Abfrage ein Ergebnis, das dann von der äußeren Abfrage verwendet wird.

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

Die Beispieltabellen

CREATE TABLE customers (id INT, name TEXT, city TEXT);
CREATE TABLE orders (id INT, customer_id INT, total INT);

INSERT INTO customers VALUES
 (1,'Asha','Pune'), (2,'Ben','Delhi'), (3,'Chitra','Pune'), (4,'Dev','Goa');

INSERT INTO orders VALUES
 (101,1,1200), (102,1,800), (103,2,300), (104,3,2500), (105,3,100);

Beachten Sie, dass Dev keine Befehle hat. Dieses Detail ist für mehrere Beispiele unten wichtig.

Arten von Unterabfragen im Überblick

Skalare Unterabfragen

Eine skalare Unterabfrage gibt genau einen Wert zurück, sodass Sie sie überall dort verwenden können, wo ein einzelner Wert sinnvoll ist. Hier ist die Abfrage von vorhin mit ihrem Ergebnis. Der Durchschnitt aller Bestellungen liegt bei 980:

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

-- 101 | 1200
-- 104 | 2500

Sie konnten WHERE total > AVG(total) nicht schreiben, da Aggregate in WHERE nicht direkt erlaubt sind. Die Unterabfrage umgeht dies, indem sie den Wert separat berechnet.

Was passiert, wenn eine „skalare“ Unterabfrage mehrere Zeilen zurückgibt? Die meisten Datenbanken, einschließlich PostgreSQL, MySQL und SQL Server, lösen einen Fehler aus. SQLite nicht. In unserem Test wurde stillschweigend nur die erste Zeile verwendet, was einen echten Fehler verbergen könnte. Verlassen Sie sich also nicht auf dieses Verhalten.

IN und NICHT IN

Wenn die innere Abfrage eine Liste zurückgibt, verwenden Sie IN. Dadurch werden Kunden mit mindestens einer Bestellung über 1000 gefunden:

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);

-- Asha
-- Chitra

NOT IN macht das Gegenteil und findet Kunden, die noch nie bestellt haben:

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Dev

Das funktioniert hier, aber NOT IN hat einen gefährlichen Randfall, den wir weiter unten behandeln.

Unterabfragen in FROM

Eine Unterabfrage in FROM fungiert als temporäre Tabelle, die oft als abgeleitete Tabelle bezeichnet wird. Dies ist nützlich, wenn Sie zwei Aggregationen durchführen müssen, wie in dieser Abfrage für die durchschnittlichen Ausgaben pro Kunde in jeder Stadt:

SELECT city, AVG(spent) AS avg_spent
FROM (
  SELECT c.city, SUM(o.total) AS spent
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id, c.city
) AS per_customer
GROUP BY city;

-- Delhi |  300.0
-- Pune  | 2300.0

Die innere Abfrage summiert jeden Kunden und die äußere ermittelt den Durchschnitt dieser Summen. Es ist eine gute Angewohnheit, der abgeleiteten Tabelle einen Alias ​​zu geben, da einige Datenbanken dies erfordern. Wenn GROUP BY für Sie neu ist, lesen Sie SQL GROUP BY und HAVING.

Unterabfragen in SELECT

Eine skalare Unterabfrage kann auch eine berechnete Spalte hinzufügen. Dieser zählt Bestellungen pro Kunde:

SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

-- Asha   | 2
-- Ben    | 1
-- Chitra | 2
-- Dev    | 0

Dev erscheint mit Null. Ein innerer Join hätte Dev komplett gelöscht, da Dev keine passenden Zeilen hat. Das gleiche Ergebnis könnten Sie mit LEFT JOIN und GROUP BY erzielen, was wir unter Erklärte SQL-Joins erläutern.

Korrelierte Unterabfragen

Das vorherige Beispiel ist ebenfalls korreliert, da die innere Abfrage auf c.id aus der äußeren Abfrage verweist. Eine nicht korrelierte Unterabfrage kann alleine ausgeführt werden. Eine Korrelation kann nicht erfolgen, da sie von einem Wert aus der aktuellen äußeren Zeile abhängt.

Dadurch werden Bestellungen gefunden, die über dem Durchschnitt desselben Kunden liegen:

SELECT o.id, o.customer_id, o.total
FROM orders o
WHERE o.total > (
  SELECT AVG(i.total)
  FROM orders i
  WHERE i.customer_id = o.customer_id
);

-- 101 | 1 | 1200
-- 104 | 3 | 2500

So lesen Sie es. Für Bestellung 101 ermittelt die innere Abfrage den Durchschnitt der Bestellungen von Kunde 1 (1200 und 800), was 1000 ergibt. Da 1200 größer ist, wird die Zeile beibehalten. Für die Ordnung 102 gilt der gleiche Durchschnitt, aber 800 ist nicht größer und wird daher weggelassen. Die Einzelbestellung von Kunde 2 entspricht seinem eigenen Durchschnitt und wird daher ebenfalls verworfen.

„Einmal pro Zeile“ beschreibt die Logik, und Abfrageoptimierer schreiben diese häufig in etwas weitaus effizienteres um. Dennoch lohnt es sich, eine langsame korrelierte Unterabfrage zu untersuchen. Ein Index auf der inneren Spalte, hier orders.customer_id, kann einen großen Unterschied machen. Siehe Erklärte SQL-Indizes.

EXISTIERT und NICHT EXISTIERT

EXISTS stellt eine Ja- oder Nein-Frage: Gibt die innere Abfrage mindestens eine Zeile zurück? Es kann beim ersten Treffer aufhören, und was Sie darin auswählen, spielt keine Rolle, weshalb die Leute oft schreiben SELECT 1.

SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Asha, Ben, Chitra

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Dev

Die NOT IN NULL-Falle

Dies ist einer der häufigsten Unterabfragefehler. Wir haben eine Tabelle mit einer echten ID und einem NULL-Wert erstellt und dann beide Formen dagegen ausgeführt:

CREATE TABLE r (customer_id INT);
INSERT INTO r VALUES (1), (NULL);

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM r);
-- (no rows at all)

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM r WHERE r.customer_id = c.id);
-- Ben, Chitra, Dev

Warum gibt das erste nichts zurück? id NOT IN (1, NULL) bedeutet id <> 1 AND id <> NULL. Jeder Vergleich mit NULL ist eher unbekannt als wahr, sodass die gesamte Bedingung niemals wahr sein kann. Es gibt keine Fehlermeldung und keine Warnung, sondern nur ein leeres Ergebnis, das plausibel aussieht.

Unterabfrage vs. JOIN vs. CTE

Viele Abfragen können auf mehr als eine Weise geschrieben werden. Früher haben wir mit IN Kunden mit einem Großauftrag gefunden. Hier ist das gleiche Ergebnis mit einem Join:

SELECT DISTINCT c.name FROM customers c
JOIN orders o ON o.customer_id = c.id AND o.total > 1000;

-- Asha
-- Chitra

Beachten Sie die DISTINCT. Ein Join kann Duplikate erzeugen, wenn ein Kunde mehrere Übereinstimmungen hat, während IN und EXISTS dies nicht können.

Die Leistung variiert je nach Datenbank und Daten, und moderne Optimierer erstellen oft ähnliche Pläne. Wählen Sie also zuerst die klarste Form und prüfen Sie dann EXPLAIN, ob sie langsam ist.

Häufige Fehler

• Verwenden Sie = mit einem mehrzeiligen Ergebnis. Verwenden Sie IN oder stellen Sie sicher, dass die innere Abfrage eine Zeile zurückgibt.
• Verwendung von NOT IN für eine Spalte, die NULL-Werte zulässt. Sie können stillschweigend jede Zeile verlieren.
• Verwendung unklarer Aliase. Qualifizieren Sie bei korrelierten Abfragen jede Spalte, da sie sonst möglicherweise an die falsche Tabelle gebunden wird.
• Tiefe Verschachtelung. Drei Ebenen sind schwer zu lesen, also konvertieren Sie sie in CTEs.
• Hinzufügen von ORDER BY im Inneren. Es hat normalerweise keine Auswirkung auf das äußere Ergebnis und einige Datenbanken lehnen es ab.

Fazit

Unterabfragen sind eine natürliche Möglichkeit, zweistufige Fragen auszudrücken. Beginnen Sie mit skalaren Unterabfragen und IN und üben Sie dann korrelierte Unterabfragen, indem Sie jeweils eine einzelne äußere Zeile durchgehen. Und denken Sie an NOT EXISTS, wann immer NULL-Werte möglich sind. Schreiben Sie zum Üben eine Frage auf drei Arten, mit einer Unterabfrage, einem Join und einem CTE, und vergleichen Sie die Ergebnisse und die Lesbarkeit.

Häufige Fragen

Lerne weiter