SQL-Subquery: Ein umfassender Leitfaden

SQL-Unterabfragen sind ein mächtiges Werkzeug in der Datenbankverwaltung, das eine komplexere und effizientere Datenabfrage ermöglicht. Dieser Leitfaden führt dich durch die Grundlagen von SQL-Subqueries und bietet Einblicke in ihre praktischen Anwendungen und fortgeschrittenen Techniken. Egal, ob du ein Anfänger oder ein erfahrener Profi bist, die Beherrschung von Unterabfragen kann deine SQL-Kenntnisse erheblich verbessern.

Wenn du neu in SQL bist, solltest du mit unserem SQL-Kurs für Fortgeschrittene beginnen, um eine solide Grundlage zu schaffen. Außerdem finde ich das SQL Basics Cheat Sheet, das du herunterladen kannst, sehr hilfreich, weil es alle gängigen SQL-Funktionen enthält. Abschließend möchte ich sagen, dass Unterabfragen eine häufige SQL-Interviewfrage sind, Wenn du dich also auf ein Vorstellungsgespräch vorbereitest, bist du hier genau richtig, um dir einen Überblick zu verschaffen.

Was ist eine SQL-Subquery?

Eine Subquery ermöglicht es, SQL-Abfragen modularer zu gestalten, indem sie Aufgaben übernimmt, die sonst mehrere separate Abfragen erfordern würden.

Definition und Zweck

Eine SQL-Subquery ist eine Abfrage, die in eine andere SQL-Abfrage eingebettet ist. Sie wird verwendet, um Operationen durchzuführen, die mehrere Schritte oder eine komplexe Logik erfordern. Zu den Aufgaben von Unterabfragen in SQL gehören die folgenden:

• Filtern von Datensätzen anhand von Daten aus Bezugstabellen.
• Daten aggregieren und Berechnungen dynamisch durchführen.
• Querverweise von Daten zwischen Tabellen, um bestimmte Erkenntnisse zu gewinnen.
• Bedingte Auswahl von Zeilen, ohne dass explizite Joins oder externe Codelogik erforderlich sind.

Das hört sich nach viel an, aber es wird Sinn machen, wenn wir diese Dinge im Lernprogramm erkunden.

Arten von Unterabfragen

Es mag dich überraschen zu erfahren, dass es verschiedene Arten von Unterabfragen gibt. Die verschiedenen Typen sind in Gruppen eingeteilt und eignen sich für unterschiedliche Arten der Datenabfrage. Je nachdem, welche Operation du durchführen möchtest, kannst du aus den folgenden Unterabfragen wählen:

Skalare Unterabfragen

Skalare Unterabfragen geben einen einzelnen Wert zurück, z. B. eine Zeile und eine Spalte. Sie werden oft verwendet, wenn ein einzelner Wert erwartet wird, z. B. bei Berechnungen, Vergleichen oder Zuweisungen in SELECT oder WHERE Klauseln.

Im folgenden Beispiel gibt die skalare Subquery (SELECT AVG(salary) FROM employees) einen einzigen Wert, das Durchschnittsgehalt, zurück und vergleicht ihn mit dem Gehalt der einzelnen Mitarbeiter.

-- Example of Scalar Subquery 
-- Compares each salary to the average salary
SELECT employee_name, 
       salary,
       (SELECT AVG(salary) FROM employees) AS average_salary 
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Spaltenunterabfragen

Spaltenunterabfragen geben eine einzelne Spalte, aber mehrere Zeilen zurück. Diese Unterabfragen werden oft mit Operatoren wie IN oder ANY verwendet, wobei die äußere Abfrage Werte aus mehreren Zeilen vergleicht.

Die folgende Unterabfrage gibt zum Beispiel eine Liste von Abteilungs-IDs für Abteilungen in New York zurück, die die Hauptabfrage dann verwendet, um die Beschäftigten in diesen Abteilungen zu filtern.

-- Example of Column Subquery 
-- Filters based on departments in New York
SELECT employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

Zeilenunterabfragen

Zeilenunterabfragen geben eine einzelne Zeile mit mehreren Spalten zurück. Diese Unterabfragen werden in der Regel mit Vergleichsoperatoren verwendet, die eine Datenzeile vergleichen können, wie z. B. die Operatoren = oder IN, wenn mehrere Werte erwartet werden.

Die folgende Unterabfrage ruft die Abteilung und die Berufsbezeichnung eines Managers ab, und die äußere Abfrage findet Mitarbeiter mit übereinstimmenden Werten.

-- Example of Row Subquery 
-- Matches department and job title with a specific manager
SELECT employee_name
FROM employees
WHERE (department_id, job_title) = (SELECT department_id, job_title FROM managers WHERE manager_id = 1);

Tabellenunterabfragen (abgeleitete Tabellen)

Tabellenunterabfragen, oder abgeleitete Tabellen, geben eine vollständige Tabelle mit mehreren Zeilen und Spalten zurück. Diese werden häufig in der FROM Klausel als temporäre Tabellen innerhalb einer Abfrage verwendet.

Die folgende Unterabfrage erstellt zum Beispiel eine abgeleitete Tabelle mit den Durchschnittsgehältern nach Abteilungen, die dann in der äußeren Abfrage verwendet wird, um Abteilungen mit einem Durchschnittsgehalt über einem bestimmten Schwellenwert zu finden.

-- Example of Table Subquery 
-- Uses derived table for average department salary comparison
SELECT dept_avg.department_id, dept_avg.avg_salary
FROM 
    (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) AS dept_avg
WHERE dept_avg.avg_salary > 50000;

SQL-Unterabfragen verstehen

In den obigen Beispielen haben wir gesehen, dass eine Unterabfrage in Klammern eingeschlossen ist. Lass uns nun die Mechanismen einer Subquery erkunden, einschließlich ihrer Syntax und Ausführungsreihenfolge.

Syntax und Struktur

Die Syntax einer Subquery hängt davon ab, wo sie in der Haupt-SQL-Anweisung verwendet wird, z. B. innerhalb der Klauseln SELECT, FROM oder WHERE. Unterabfragen werden in der Regel in Klammern ( ) eingeschlossen, um eine separate Abfrage zu kennzeichnen. 

Das folgende Beispiel zeigt eine Unterabfrage innerhalb einer WHERE Klausel, die es uns ermöglicht, Daten in der Hauptabfrage auf der Grundlage der Ergebnisse einer verschachtelten Abfrage zu filtern. 

-- Selects the main column to retrieve from the main table to query
SELECT column_name
FROM table_name
-- Applies a condition to filter rows based on the subquery result
WHERE column_name operator 
      -- Subquery retrieves data for comparison in the WHERE clause
      (SELECT column_name FROM table_name WHERE condition);  

Vollstreckungsauftrag

Die Ausführungsreihenfolge für Unterabfragen hängt davon ab, ob sie korreliert oder unkorreliert sind.

Nicht-korrelierte Unterabfragen

Nicht-korrelierte Unterabfragen sind unabhängig von der äußeren Abfrage und werden zuerst ausgeführt. Das Ergebnis der Subquery wird dann an die äußere Abfrage übergeben. Nicht-korrelierte Unterabfragen werden häufig für skalare oder spaltenbasierte Berechnungen und Filter verwendet.

Die folgende Abfrage folgt der Ausführungsreihenfolge:

• Die Unterabfrage (SELECT AVG(salary) FROM employees) läuft zuerst und berechnet das Durchschnittsgehalt.

• Die äußere Abfrage sucht dann die Arbeitnehmer heraus, deren Gehalt über diesem Durchschnitt liegt.

-- Retrieves names of employees with above-average salary
SELECT employee_name  
FROM employees
-- Subquery: calculates average salary across all employees
WHERE salary > (SELECT AVG(salary) FROM employees);  

Ich empfehle, den Kurs Einführung in SQL Server von DataCamp zu besuchen, um mehr über die Gruppierung und Aggregation von Daten sowie die Verknüpfung von Tabellen zu erfahren.

Korrelierte Unterabfragen

Korrelierte Unterabfragen hängen in Bezug auf einige ihrer Daten von der äußeren Abfrage ab, sodass sie für jede von der äußeren Abfrage verarbeitete Zeile neu ausgewertet werden. 

Die folgende Abfrage wird in dieser Reihenfolge ausgeführt:

• Für jede Zeile in employees (alias e1) berechnet die Unterabfrage (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id) das Durchschnittsgehalt für diese bestimmte Abteilung.

• Die äußere Abfrage vergleicht dann das Gehalt eines jeden Mitarbeiters mit dem Durchschnittsgehalt der Abteilung und schließt nur diejenigen ein, die mehr verdienen.

-- Retrieves names of employees with above-average salary in their department
SELECT e1.employee_name  
FROM employees e1
 -- Subquery: calculates average salary for each department
WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id); 

Anwendungen der SQL Subquery

SQL-Unterabfragen sind wichtig für die Datenabfrage und -umwandlung und ermöglichen es dir, komplexe Abfragen zu schreiben und fortgeschrittene Datenanalyseaufgaben zu erledigen. Im Folgenden findest du praktische Anwendungen von Unterabfragen in der Datenbankverwaltung.

Daten filtern

Unterabfragen sind nützlich, wenn du Daten auf der Grundlage dynamischer Bedingungen filterst, vor allem, wenn die Filterung den Vergleich von Werten in mehreren Tabellen oder die Durchführung von Berechnungen erfordert.

Die folgende Unterabfrage ruft die category_id von "Produkt A" ab, und die Hauptabfrage findet alle Produkte in dieser Kategorie.

-- Retrieves names of products in the same category as 'Product A'
SELECT product_name  
FROM products
 -- Subquery: finds category ID of 'Product A'
WHERE category_id = (SELECT category_id FROM products WHERE product_name = 'Product A'); 

Datenaggregation

Unterabfragen werden auch für die Datenaggregation verwendet, vor allem bei der Erstellung von zusammenfassenden Statistiken oder Erkenntnissen für Berichte und Analysen. Die Unterabfrage (SELECT department_id, AVG(sales) AS avg_sales FROM sales GROUP BY department_id) berechnet den durchschnittlichen Umsatz pro Abteilung. Die äußere Abfrage filtert dann Abteilungen mit einem durchschnittlichen Umsatz über 50.000. 

-- Retrieves department IDs and their average sales
-- Filters for departments with average sales over 50,000
SELECT department_id, avg_sales  
FROM (SELECT department_id, AVG(sales) AS avg_sales FROM sales GROUP BY department_id) AS dept_sales  -- Subquery: calculates average sales per department
WHERE avg_sales > 50000;  

Leistungsüberlegungen und bewährte Praktiken

Obwohl Unterabfragen beim Schreiben komplexer Abfragen sehr leistungsfähig sind, können sie sich auf die Leistung auswirken, besonders wenn du mit großen Datensätzen arbeitest. Es ist wichtig, die häufigsten Fallstricke und Best Practices für eine bessere Leistung zu berücksichtigen.

Optimieren der Leistung von Subqueries

Die Optimierung der Leistung von Subqueries sorgt für eine verbesserte Ausführungszeit von Abfragen und eine bessere Reaktionsfähigkeit der Datenbank. Im Folgenden findest du Möglichkeiten zur Optimierung der Subquery.

• Index Relevante Spalten: Um den Datenabruf zu beschleunigen, solltest du sicherstellen, dass die in WHERE und JOIN Klauseln und Vergleichsoperationen verwendeten Spalten indiziert sind.

• Schränke die Verwendung von korrelierten Unterabfragen ein: Wenn möglich, verwende JOIN-Operationen oder CTEs anstelle von korrelierten Unterabfragen, da sie Daten oft schneller verarbeiten können, indem sie Mengenoperationen statt zeilenweiser Verarbeitung verwenden.

• Begrenze die Anzahl der Spalten in Unterabfragen: Wähle nur die Spalten aus, die du in Unterabfragen brauchst, um den Datenabruf zu minimieren, den Speicherverbrauch zu verringern und der Datenbank die Möglichkeit zu geben, die Ausführung zu optimieren.

• Verwende EXISTS anstelle von IN: Wenn eine Subquery einen großen Datensatz zurückgibt, kann die Verwendung von EXISTS anstelle von IN die Leistung verbessern. Der EXISTS Operator stoppt die Abfrageverarbeitung, wenn er eine übereinstimmende Zeile findet, während der IN Operator das gesamte Ergebnis der Unterabfrage weiter auswertet.

Vermeiden von Fallstricken

Wenn du Unterabfragen falsch schreibst, kannst du Probleme bei der Ausführung bekommen. Sehen wir uns an, wie du diese Fallstricke vermeiden kannst.

• Vermeide unnötige korrelierte Unterabfragen: Korrelierte Unterabfragen sind ressourcenintensiv, daher solltest du sie vermeiden, wenn das Ergebnis auch mit einer nicht korrelierten Unterabfrage oder einem Join erreicht werden kann.

• Achte auf NULL-Werte in Unterabfragen: NULL Werte können zu unerwarteten Ergebnissen führen, insbesondere in Unterabfragen, die Vergleichsoperatoren wie IN oder = verwenden. Um Fehler zu vermeiden, solltest du COALESCE verwenden, um mit Nullen umzugehen, oder sicherstellen, dass Spalten, die für Vergleiche verwendet werden, nicht nullbar sind.

• Vermeide die Verwendung von SELECT * in Unterabfragen: Die Verwendung von SELECT * kann zu Ineffizienzen führen, da sie alle Spalten abruft, auch wenn du sie nicht brauchst. Das erhöht die Speichernutzung und kann die Ausführung von Abfragen verlangsamen, besonders bei großen Datensätzen.

• Verwende aussagekräftige Aliase: Benenne deine Tabellen und Unterabfragen klar und deutlich, um die Lesbarkeit zu verbessern.

Probiere unseren Lernpfad für SQL Server-Entwickler/innen aus, der dich mit den Fähigkeiten ausstattet, deine Abfragen mit SQL Server zu schreiben, Fehler zu beheben und zu optimieren. 

Weitere fortgeschrittene SQL-Subquery-Techniken

Während Unterabfragen eine effiziente Möglichkeit bieten, komplexe Abfragen zu schreiben, gibt es auch fortgeschrittene Methoden für den Umgang mit hierarchischen Daten. Lass uns die fortgeschrittenen Techniken und Strategien für die Anwendung von SQL-Unterabfragen untersuchen.

Rekursive Unterabfragen

Mit rekursiven Unterabfragen (auch bekannt als rekursive Common Table Expressions oder CTEs) kannst du hierarchische Daten abrufen, z. B. Organisationsstrukturen, Produktkategorien oder graphenbasierte Beziehungen, bei denen jedes Element in den Daten mit einem anderen verknüpft ist.

Angenommen, du hast eine Tabelle employees mit employee_id, manager_id und employee_name. Du möchtest die Hierarchie der Mitarbeiter unter einer bestimmten Führungskraft abrufen.

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor Query: Start with the specified manager
    SELECT employee_id, manager_id, employee_name, 1 AS level
    FROM employees
 -- Assuming the top-level manager has NULL as manager_id
    WHERE manager_id IS NULL 
    
    UNION ALL
    
    -- Recursive Query: Find employees who report to those in the previous level
    SELECT e.employee_id, e.manager_id, e.employee_name, eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;

In der obigen Abfrage:

• Die sogenannte Ankerabfrage wählt den obersten Manager aus ( manager_id ist NULL).

• Die rekursive Abfrage verknüpft employees mit dem CTE selbst (EmployeeHierarchy) und findet so die Mitarbeiter, die jedem zuvor abgerufenen Mitarbeiter unterstellt sind.

• Die Rekursion wird so lange fortgesetzt, bis sich keine Arbeitnehmer mehr bei den gefundenen melden.

Kombinieren von Unterabfragen mit anderen SQL-Funktionen

Du kannst Unterabfragen mit anderen SQL-Funktionen wie Fensterfunktionen, CASE Anweisungen und Gruppierungsfunktionen integrieren. Diese Kombinationen ermöglichen eine erweiterte Datenbearbeitung und umfassendere Berichte.

Kombinieren von Unterabfragen mit Fensterfunktionen

Unterabfragen können verwendet werden, um den Datensatz, auf den die Fensterfunktionen wirken, zu verfeinern, was sie für Ranglisten, kumulative Summen und gleitende Durchschnitte nützlich macht. Angenommen, du möchtest Produkte nach den Verkäufen in jeder Region ordnen. Du kannst eine Unterabfrage verwenden, um die relevanten Daten auszuwählen und dann eine Fensterfunktion für das Ranking anzuwenden.

 -- Ranks products by sales within each region
SELECT region, product_id, sales, 
       RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS sales_rank 
-- Subquery: calculates total sales per product in each region
FROM (SELECT region, product_id, SUM(sales) AS sales  
      FROM sales_data
      GROUP BY region, product_id) AS regional_sales;

Verwendung von Unterabfragen mit CASE-Anweisungen

Die Kombination von Unterabfragen mit CASE Anweisungen kann dir helfen, komplexe Bedingungen auf der Grundlage dynamischer Berechnungen anzuwenden. Die folgende Abfrage klassifiziert Produkte als "High", "Medium" oder "Low" Performer basierend auf ihrem Umsatz im Verhältnis zum durchschnittlichen Umsatz ihrer Kategorie.

 -- Categorize above-average sales, average sales, and below-average sales
SELECT product_id, category_id, sales,
       CASE 
           WHEN sales > (SELECT AVG(sales) FROM products WHERE category_id = p.category_id) THEN 'High' 
           WHEN sales = (SELECT AVG(sales) FROM products WHERE category_id = p.category_id) THEN 'Medium' 
           ELSE 'Low'
       END AS performance
FROM products AS p;

Unterabfragen mit Aggregatfunktionen für bedingte Aggregation

Du kannst auch bedingte Aggregate berechnen, indem du Unterabfragen innerhalb von Aggregatfunktionen verwendest. Angenommen, du möchtest den Gesamtumsatz berechnen, der nur von aktiven Kunden erzielt wird. Im folgenden Beispiel ruft die Subquery alle aktiven Kunden ab. Die Hauptabfrage filtert dann die Bestellungen heraus, die von aktiven Kunden aufgegeben wurden, und berechnet den Gesamtumsatz aus dieser Gruppe.

 -- Calculates total revenue from active customers
SELECT SUM(order_total) AS active_customer_revenue 
FROM orders
  -- Subquery: retrieves IDs of active customers
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE status = 'Active'
);

Wenn SQL-Unterabfragen verwendet werden

SQL-Unterabfragen bieten vielseitige praktische Anwendungen für die Datenanalyse, weshalb sie so häufig verwendet werden. Im Folgenden findest du einige branchenspezifische oder mathematische Anwendungen von SQL-Subqueries. Um auf diese Ideen zu kommen, habe ich versucht, an Datenanalyse- oder Data-Engineering-Aufgaben zu denken, die mehrere Schritte erfordern, und bei denen Unterabfragen meiner Meinung nach wirklich helfen.

Branchenspezifische Beispiele

Unterabfragen können nützliche Lösungen im Finanz-, Gesundheits- und Einzelhandelssektor bieten. Hier sind einige Ideen:

• Risikobewertung für Kreditgenehmigungen (Finanzen): Ich stelle mir vor, wie Banken mit Kennzahlen wie dem Verhältnis von Schulden zu Einkommen und der Kreditwürdigkeit jonglieren. Durch die Verschachtelung dieser Metriken in Unterabfragen können Analysten komplizierte Finanzkennzahlen besser verstehen. Vielleicht kann eine Unterabfrage den durchschnittlichen Kreditbetrag für Kunden in bestimmten Einkommensklassen berechnen.

• Erkennen von Mustern in Patientendiagnosen (Gesundheitswesen): Im Gesundheitswesen kann die Verwaltung von Patientendaten eine große Aufgabe sein. Unterabfragen können helfen, diese Komplexität zu reduzieren. Ich könnte mir vorstellen, Unterabfragen zu verwenden, um die Häufigkeit von Diagnosen in verschiedenen Altersgruppen oder Risikofaktoren zu verfolgen.

• Optimierung der Produktplatzierung anhand der Kaufhistorie (Einzelhandel): Für Einzelhändler ist es wichtig, das Kaufverhalten zu verstehen. Unterabfragen können verschachtelte Kaufdaten anzeigen, um festzustellen, welche Produkte häufig zusammen gekauft werden. Das kann helfen, ergänzende Artikel strategisch zu platzieren und den Umsatz zu steigern.

Mathematische Verbindungen

Unterabfragen werden auch verwendet, um Datenmuster und Trends in mathematischen und logischen Zusammenhängen zu erkennen. Im Folgenden sind einige Szenarien aufgeführt, in denen Unterabfragen in der Mathematik angewendet werden.

• Gleitende Durchschnitte für die Zeitreihenanalyse: Bei der Analyse von Trends im Zeitverlauf vereinfachen Unterabfragen die Berechnung von gleitenden Durchschnitten. Ich sehe, dass sie bestimmte Zeitfenster in verschachtelten Abfragen definieren, was es einfacher macht, Daten zu glätten und Trends zu erkennen.

• Ausreißer mithilfe von Standardabweichungen erkennen: Das Erkennen von Ausreißern ist für viele Dinge wichtig, zum Beispiel für die Aufdeckung von Betrug. Unterabfragen machen es einfach, berechnete Metriken wie Standardabweichungen in verschachtelten Abfragen zu berechnen.

• Konzepte der Mengenlehre anwenden: Ich finde es interessant, wie Unterabfragen Operationen der Mengenlehre wie UNION und INTERSECT widerspiegeln. Diese Fähigkeit ist perfekt für Aufgaben wie die Analyse der Kundenbindung, bei der das Verständnis von Überschneidungen und Unterschieden zwischen Kundengruppen zu intelligenteren Marketingstrategien führen kann.

Fazit

Die Beherrschung von SQL-Unterabfragen kann deine Fähigkeit, Daten effizient zu verwalten und zu analysieren, erheblich verbessern. Wenn du ihre Struktur, Anwendungen und Best Practices verstehst, kannst du deine SQL-Abfragen für eine bessere Leistung optimieren. Außerdem möchte ich sagen, dass das Beherrschen von Unterabfragen das Schreiben von SQL einfacher macht, es lohnt sich also, es zu lernen.

Wenn du ein erfahrener Datenanalyst werden möchtest, schau dir unseren Lernpfad zum Associate Data Analyst in SQL an, um die notwendigen Fähigkeiten zu erlernen. Der Kurs Reporting in SQL ist auch geeignet, wenn du lernen willst, wie man professionelle Dashboards mit SQL erstellt. Schließlich empfehle ich dir, die SQL Associate-Zertifizierung zu erlangen, um zu zeigen, dass du SQL für die Datenanalyse beherrschst und dich von anderen Datenexperten abhebst.