Kapitel 4. Deine Daten lesen

Diese Arbeit wurde mithilfe von KI übersetzt. Wir freuen uns über dein Feedback und deine Kommentare: translation-feedback@oreilly.com

In diesem Kapitel werden wir die wichtigsten SQL-Anweisungen und -Klauseln kennenlernen, die du brauchst, um Informationen aus deiner Datenbank zu extrahieren. Du lernst die grundlegende Anweisung SELECT und die verschiedenen Unterklauseln kennen, die du verwenden kannst, um Daten auszuwählen und zu Zwischensummen zu aggregieren. Am Ende dieses Kapitels wirst du die Konzepte von JOIN Operationen, SET Operationen, Aggregation, Fensteroperationen, allgemeinen Tabellenausdrücken und Unterabfragen verstehen. Du wirst auch wissen, wie du diese effektiv kombinieren kannst, um beliebige Daten in den zentralen Datenbankplattformen abzufragen.

Wie dieses Kapitel zu verwenden ist

Wenn du einen Befehl in diesem Kapitel recherchierst:

  1. Lies "SQL-Plattform-Unterstützung".

  2. Überprüfe Tabelle 4-1.

  3. Schlage die spezifische SQL-Anweisung nach, überprüfe die Syntax und lies die Abschnitte "Schlüsselwörter", "Regeln im Überblick" und "Programmiertipps und -fehler" sowie den Abschnitt über den Standard für SQL-Syntax und -Beschreibung. Tu dies auch, wenn du nach einer bestimmten Plattformimplementierung suchst.

  4. Lies schließlich die plattformspezifischen Implementierungsinformationen.

Du wirst feststellen, dass der Eintrag für eine bestimmte Plattformimplementierung keine Informationen über Klauseln enthält, die sich nicht vom Standard unterscheiden. Es ist also möglich, dass du zwischen den Beschreibungen für eine Hersteller-Variante und dem SQL-Standard hin- und herblättern musst, um alle möglichen Details dieses Befehls abzudecken.

In unseren Diskussionen über MySQL werden wir auch MariaDB, eine Abspaltung von MySQL, einbeziehen. In den meisten Fällen bieten MySQL und MariaDB eine vollständig codekompatible Syntax. In diesen Fällen werden wir sie gemeinsam als MySQL bezeichnen. Wir werden MariaDB nur dann explizit erwähnen, wenn sie sich in wichtigen Punkten von MySQL unterscheidet.

Unterstützung der SQL-Plattform

Tabelle 4-1 enthält eine Auflistung der in diesem Kapitel behandelten SQL-Anweisungen, die Plattformen, die sie unterstützen, und den Grad ihrer Unterstützung. Die folgende Liste enthält nützliche Tipps zum Lesen von Tabelle 4-1 sowie eine Erklärung, wofür die einzelnen Abkürzungen stehen:

  1. Die erste Spalte enthält die SQL-Befehle in alphabetischer Reihenfolge.

  2. Die SQL-Anweisungsklasse für jeden Befehl ist in der zweiten Spalte angegeben.

  3. In den folgenden Spalten ist der Grad der Unterstützung für jeden Anbieter aufgeführt:

    Unterstützt (S)
    Die Plattform unterstützt den SQL-Standard für den jeweiligen Befehl.
    Unterstützt, mit Abweichungen (SWV)
    Die Plattform unterstützt den SQL-Standard für den jeweiligen Befehl, wobei herstellerspezifischer Code oder Syntax verwendet wird.
    Unterstützt, mit Einschränkungen (SWL)
    Die Plattform unterstützt einige, aber nicht alle Funktionen, die der SQL-Standard für den jeweiligen Befehl vorsieht.
    Nicht unterstützt (NS)
    Die Plattform unterstützt den jeweiligen Befehl gemäß dem SQL-Standard nicht.

Die Abschnitte, die der Tabelle folgen, beschreiben die Befehle im Detail.

Erinnere dich daran, dass selbst wenn ein bestimmter SQL-Befehl in der Tabelle als "nicht unterstützt" aufgeführt ist, die Plattform in der Regel über eine alternative Kodierung oder Syntax verfügt, um denselben Befehl oder dieselbe Funktion auszuführen. Lies deshalb unbedingt die Diskussion und die Beispiele für jeden Befehl weiter unten in diesem Kapitel.

Tabelle 4-1. Alphabetische SQL-Befehlsschnellreferenz
SQL-Befehl SQL-Klasse MySQL/MariaDB Oracle PostgreSQL SQL Server
ALL/ANY/SOME SQL-Daten S SWV SWV SWV
BETWEEN SQL-Daten S S S S
EXCEPT SQL-Daten NS/SWL SWL SWL SWL
EXISTS SQL-Daten S S S S
FILTER SQL-Daten NS NS S NS
GROUP BY SQL-Daten SWV SWV SWV SWV
IN SQL-Daten S S S S
INTERSECT SQL-Daten NS/SWL SWL SWL SWL
IS SQL-Daten S S S S
JOIN SQL-Daten SWV SWV SWV SWL
LIKE SQL-Daten S S SWV SWV
ORDER BY SQL-Daten SWL SWV SWV SWL
OVER SQL-Daten SWL SWV SWV SWL
SELECT SQL-Daten SWV SWV SWV SWV
SUBQUERY SQL-Daten SWL S S S
UNION SQL-Daten S SWL SWL SWL
VALUES SQL-Daten SWL NS S SWL
WHERE SQL-Daten S S S S
WITH SQL-Daten S SWV SWV SWV
WITH ORDINALITY SQL-Daten NS NS S NS

SQL-Befehlsreferenz

ALL/ANY/SOME Operatoren

Der ALL Operator führt einen booleschen Test einer Unterabfrage auf das Vorhandensein eines Wertes in allen Zeilen durch. Der Operator ANY und sein Synonym SOME führen einen booleschen Test einer Unterabfrage auf das Vorhandensein eines Wertes in einer der getesteten Zeilen durch.

Du wirst auch das Schlüsselwort ALL in Verbindung mit UNION finden. Dies wird in "UNION Set Operator" behandelt .

Plattform Befehl
MySQL Unterstützt
Oracle Unterstützt mit Variationen
PostgreSQL Unterstützt mit Variationen
SQL Server Unterstützt mit Variationen

SQL-Standardsyntax

SELECT ...
WHERE expression comparison {ALL | ANY | SOME} ( subquery )

Schlüsselwörter

WHERE expression
Prüft einen skalaren Ausdruck (z. B. eine Spalte) gegen jeden Wert in der subquery für ALL und mit jedem Wert, bis eine Übereinstimmung für ANY und SOME gefunden wird. Alle Zeilen müssen mit dem Ausdruck übereinstimmen, um einen booleschen TRUE Wert für den ALL Operator zurückzugeben, während eine oder mehrere Zeilen mit dem Ausdruck übereinstimmen müssen, um einen booleschen TRUE Wert für die ANY und SOME Operatoren zurückzugeben.
comparison
verg verg vergleicht die expression mit dem subquery. Der comparison muss ein Standardvergleichsoperator wie =, <>, !=, >, >=, < oder <= sein.

Die Regeln auf einen Blick

Der Operator ALL gibt einen booleschen Wert TRUE zurück, wenn einer von zwei Fällen eintritt: Entweder liefert die Unterabfrage eine leere Menge (d. h., es stimmen keine Datensätze überein), oder jeder Datensatz in der Menge entspricht dem Vergleich. ALL gibt FALSE zurück, wenn ein Datensatz in der Menge nicht dem Wertevergleich entspricht. Die Operatoren ANY und SOME geben einen booleschen Wert TRUE zurück, wenn mindestens ein Datensatz in der Unterabfrage mit dem Vergleich übereinstimmt, und FALSE, wenn kein Datensatz mit dem Vergleich übereinstimmt (oder wenn eine Unterabfrage eine leere Ergebnismenge liefert). Wenn auch nur ein Rückgabewert der Subquery NULL ist, wird die Operation als NULL und nicht als TRUE ausgewertet.

Füge keine speziellen Klauseln wie ORDER BY, GROUP BY, CUBE, ROLLUP, WITH, etc. in deine Subquery ein.

Diese Abfrage liefert zum Beispiel Autoren, die derzeit keine Titel haben:

SELECT au_id
FROM authors
WHERE au_id <> ALL(SELECT titleauthor.au_id FROM titleauthor);

Du kannst ANY oder SOME verwenden, um verschiedene Arten von Filterprüfungen durchzuführen. Die folgende Abfrage ruft zum Beispiel alle Datensätze aus der Tabelle " Mitarbeiter" ab, die in der Tabelle " Aufträge" vorhanden sind und bei denen der Mitarbeiter dieselbe job_lvl hat wie die erforderliche Mindeststufe eines Auftrags:

SELECT *
FROM employee
WHERE job_lvl = ANY(SELECT min_lvl FROM jobs);

Programmiertipps und -schwierigkeiten

Die Operatoren ALL und ANY/SOME sind etwas gewöhnungsbedürftig. Die meisten Entwickler finden es einfacher, ähnliche Funktionen wie IN und EXISTS zu verwenden.

EXISTS ist semantisch gleichwertig mit dem ANY/SOME Konstrukt

MySQL

MySQL unterstützt die SQL-Standardversionen von ALL und ANY/SOME.

Oracle

Oracle unterstützt die SQL-Standardversionen von ALL und ANY/SOME mit einer geringfügigen Abweichung, nämlich der, dass du eine Liste von Werten anstelle einer Unterabfrage angeben kannst. Um zum Beispiel alle Mitarbeiter zu finden, die einen job_lvl-Wert von 9 oder 14 haben:

SELECT * FROM employee
WHERE job_lvl = ALL(9, 14);

PostgreSQL

PostgreSQL unterstützt die SQL-Standardversionen von ALL und ANY/SOME. Darüber hinaus unterstützt es deren Verwendung mit Arrays. Um zum Beispiel alle Mitarbeiter zu finden, die einen job_lvl-Wert gleich 9 oder 14 haben:

SELECT * FROM employee
WHERE job_lvl = ANY(ARRAY[9, 14]);

PostgreSQL unterstützt auch die Verwendung dieser Begriffe in Verbindung mit LIKE und der case-insensitive ILIKE. Häufig werden sie als Abkürzung für mehrere LIKE/ILIKE Klauseln verwendet. Zum Beispiel dies:

SELECT * FROM employee
WHERE name_last ILIKE ANY(ARRAY['smith', 'paris%', '%chin%']);

ist gleichbedeutend mit:

SELECT * FROM employee
WHERE name_last ILIKE 'smith' 
  OR name_last ILIKE 'paris%'
  OR name_last ILIKE '%chin%';

SQL Server

SQL Server unterstützt die SQL-Standardversionen von ALL und ANY/SOME. Er unterstützt auch einige zusätzliche Vergleichsoperatoren: nicht größer als (!>) und nicht kleiner als (!<).

Siehe auch

  • BETWEEN

  • EXISTS

  • IN

  • LIKE

  • SELECT

  • UNION

  • WHERE

ZWISCHEN BETRIEB

Der BETWEEN Operator führt einen booleschen Test eines Wertes gegen einen Wertebereich durch. Er gibt TRUE zurück, wenn der Wert in dem Bereich enthalten ist, und FALSE, wenn der Wert außerhalb des Bereichs liegt. Das Ergebnis ist NULL (unbekannt), wenn einer der Werte im Bereich NULL ist.

Plattform Befehl
MySQL Unterstützt
Oracle Unterstützt
PostgreSQL Unterstützt
SQL Server Unterstützt

SQL-Standardsyntax

SELECT ...
WHERE expression [NOT] BETWEEN lower_range AND upper_range

Schlüsselwörter

WHERE expression
Vergleicht einen skalaren Ausdruck, z. B. eine Spalte, mit dem Wertebereich, der durch upper_range und lower_range.
[NOT] BETWEEN lower_range AND upper_range
verg verg vergleicht die expression mit dem lower_range und upper_range. Der Vergleich ist inklusiv, das heißt, er ist gleichbedeutend mit der Aussage "wenn expression ist [nicht] größer als oder gleich lower_range und kleiner als oder gleich upper_range."

Die Regeln auf einen Blick

Der BETWEEN Operator wird verwendet, um einen Ausdruck gegen einen Wertebereich zu testen. Er kann mit jedem Datentyp außer BLOB, CLOB, NCLOB, REF oder ARRAY verwendet werden.

Diese Abfrage gibt zum Beispiel Titel_idszurück, die seit Jahresbeginn einen Umsatz zwischen 10.000 und 20.000 haben:

SELECT title_id
FROM titles
WHERE ytd_sales BETWEEN 10000 AND 20000

BETWEEN schließt den Bereich der aufgelisteten Werte ein, d.h. die Werte 10.000 und 20.000 werden in die Suche einbezogen. Wenn du eine exklusive Suche möchtest, musst du die Symbole größer als (>) und kleiner als (<) verwenden:

SELECT title_id
FROM titles
WHERE ytd_sales > 10000
  AND ytd_sales < 20000

Mit dem Operator NOT kannst du nach Werten außerhalb des Bereichs BETWEEN suchen. So kannst du zum Beispiel alle title_idsfinden, die im Jahr 2021 nicht veröffentlicht wurden:

SELECT title_id
FROM titles
WHERE pub_date NOT BETWEEN '01-JAN-2021'
  AND '31-DEC-2021'

Programmiertipps und -schwierigkeiten

Einige Programmierer legen großen Wert darauf, wie das Schlüsselwort AND in WHERE Klauseln verwendet wird. Um zu verhindern, dass ein zufälliger Prüfer denkt, dass das AND, das in einer BETWEEN Operation verwendet wird, ein logischer AND Operator ist, solltest du die gesamte BETWEEN Klausel in Klammern einschließen:

SELECT title_id
FROM titles
WHERE (ytd_sales BETWEEN 10000 AND 20000)
  AND pubdate >= '2021-06-12 00:00:00.000'

PostgreSQL unterstützt auch @> (den Enthält-Operator) und && (den Überschneidungs-Operator), die denselben Zweck erfüllen wie BETWEEN, allerdings für Array-Typen, Range-Typen und Multi-Range-Typen.

Siehe auch

  • ALL/ANY/SOME

  • EXISTS

  • SELECT

  • WHERE

EXCEPT Set Operator

Der EXCEPT set Operator ruft die Ergebnismengen von zwei oder mehr Abfragen ab, einschließlich aller Datensätze, die von der ersten Abfrage abgerufen werden und nicht auch in den nachfolgenden Abfragen gefunden werden. Während JOIN Klauseln verwendet werden, um die gemeinsamen Zeilen von zwei oder mehr Abfragen zurückzugeben, wird EXCEPT verwendet, um die Datensätze herauszufiltern, die nur in einer von mehreren ähnlichen Tabellen vorhanden sind.

EXCEPT gehört zu einer Klasse von Schlüsselwörtern namens set operators. Andere Set-Operatoren sind INTERSECT und UNION. (MINUS ist ein Synonym für das Schlüsselwort EXCEPT; EXCEPT ist der SQL-Standard.) Alle Set-Operatoren werden verwendet, um die Ergebnismengen von zwei oder mehr Abfragen gleichzeitig zu manipulieren, daher der Begriff "Set-Operatoren".

Plattform Befehl
MySQL Nicht unterstützt
MariaDB Unterstützt, mit Einschränkungen
Oracle Unterstützt, mit Einschränkungen (wie MINUS)
PostgreSQL Unterstützt, mit Einschränkungen
SQL Server Unterstützt, mit Einschränkungen

SQL-Standardsyntax

Es gibt technisch gesehen keine Begrenzung für die Anzahl der Abfragen, die du mit dem EXCEPT Operator kombinieren kannst. Die allgemeine Syntax lautet:

{SELECT statement1 | VALUES (expr1[, ...])}
EXCEPT [ALL | DISTINCT]
[CORRESPONDING [BY (column1, column2, ...)]]
{SELECT statement2 | VALUES (expr2[, ...])}
EXCEPT [ALL | DISTINCT]
[CORRESPONDING [BY (column1, column2, ...)]]
...

Schlüsselwörter

VALUES (expr1[, ... ])
Erzeugt eine abgeleitete Ergebnismenge mit explizit deklarierten Werten als expr1, expr2usw. - es handelt sich im Grunde um eine Ergebnismenge der Anweisung SELECT ohne die SELECT ... FROM-Syntax. Dies wird als Zeilenkonstruktor bezeichnet, da die Zeilen der Ergebnismenge manuell erstellt werden. Nach dem SQL-Standard müssen mehrere handcodierte Zeilen in einem Zeilenkonstruktor in Klammern gesetzt und durch Kommas getrennt werden.
EXCEPT
Legt fest, welche Zeilen aus der endgültigen Ergebnismenge ausgeschlossen werden sollen.
ALL | DISTINCT
ALL berücksichtigt doppelte Zeilen aus allen Ergebnismengen beim EXCEPT Vergleich. DISTINCT lässt doppelte Zeilen aus allen Ergebnismengen vor dem EXCEPT Vergleich fallen. Alle Spalten, die einen NULL-Wert enthalten, werden als Duplikate betrachtet. (Wenn weder ALL noch DISTINCT verwendet wird, ist das Verhalten von DISTINCT der Standard).
CORRESPONDING
Legt fest, dass nur Spalten mit demselben Namen in beiden Abfragen zurückgegeben werden, auch wenn beide Abfragen das Kürzel Sternchen(*) verwenden.
BY (column1, column2, ... )
Legt fest, dass nur die benannten Spalten zurückgegeben werden, auch wenn in den Abfragen weitere Spalten mit entsprechenden Namen existieren. Muss mit dem CORRESPONDING Schlüsselwort verwendet werden.

Die Regeln auf einen Blick

Es gibt nur eine wichtige Regel, an die du dich bei der Verwendung von EXCEPT erinnern musst: Die Anzahl und die Reihenfolge der Spalten sollten in allen Abfragen gleich sein, und die Datentypen sollten der gleichen Kategorie angehören.

Die Datentypen müssen nicht identisch sein, aber sie müssen kompatibel sein. Zum Beispiel sind CHAR und VARCHAR kompatible Datentypen. Standardmäßig wird die Ergebnismenge auf die größte Datentypgröße jeder Spalte in jeder Ordinalposition eingestellt. Eine Abfrage, die zum Beispiel Zeilen aus den Spalten VARCHAR(10) und VARCHAR(15) abruft, verwendet den Datentyp und die Größe VARCHAR(15).

Programmiertipps und -schwierigkeiten

Keine der Plattformen unterstützt die CORRESPONDING [BY (column1, column2, ... )] Klausel.

Nach dem SQL-Standard werden die Set-Operatoren UNION und EXCEPT mit gleichem Vorrang ausgewertet. Der INTERSECT Mengenoperator wird jedoch vor den anderen Mengenoperatoren ausgewertet. Als bewährte Methode empfehlen wir, den Vorrang der Set-Operatoren explizit durch Klammern zu steuern.

Nach dem SQL-Standard ist nur eine ORDER BY Klausel in der gesamten Abfrage erlaubt. Füge sie am Ende der letzten SELECT Anweisung ein. Um Mehrdeutigkeiten zwischen Spalten und Tabellen zu vermeiden, musst du jeder Spalte für jede Tabelle denselben Alias zuweisen. Zum Beispiel:

SELECT au_lname AS lastname, au_fname AS firstname
FROM authors
EXCEPT
SELECT emp_lname AS lastname, emp_fname AS firstname
FROM employees
ORDER BY lastname, firstname

Während jede der Spaltenlisten Spalten mit entsprechend kompatiblen Datentypen auflistet, kann es zwischen den DBMS-Plattformen Unterschiede im Verhalten in Bezug auf die Länge der Spalten geben. Wenn zum Beispiel die Spalte au_lname in der ersten Abfrage des vorherigen Beispiels deutlich länger ist als die Spalte emp_lname in der zweiten Abfrage, können die Plattformen unterschiedliche Regeln anwenden, welche Länge für das Endergebnis verwendet wird. Im Allgemeinen wählen die Plattformen jedoch die längere (und weniger restriktive) Spaltengröße für die Verwendung in der Ergebnismenge.

Jedes DBMS kann seine eigenen Regeln anwenden, um zu bestimmen, welcher Spaltenname verwendet wird, wenn die Namen in den Spaltenlisten unterschiedlich sind. Im Allgemeinen werden die Spaltennamen der ersten Abfrage verwendet.

Auf Plattformen, die EXCEPT nicht unterstützen, kannst du LEFT JOIN, NOT IN oder NOT EXISTS. ersetzen. Die folgenden Abfragen sind Beispiele dafür, wie du die Funktionalität von EXCEPT mit NOT EXISTS und NOT IN erreichen kannst:

SELECT DISTINCT a.city
FROM authors AS a
WHERE NOT EXISTS
   (SELECT *
    FROM publishers AS p
    WHERE a.city = p.city)

SELECT DISTINCT a.city
FROM authors AS a
WHERE a.city NOT IN
   (SELECT p.city
    FROM pubs.publishers AS p
    WHERE p.city IS NOT NULL)

Im Allgemeinen ist NOT EXISTS schneller als NOT IN. Darüber hinaus gibt es ein subtiles Problem mit NULLen, das die Operatoren IN und NOT IN von den Set-Operatoren EXISTS und NOT EXISTS unterscheidet. Um diese unterschiedliche Behandlung von NULLs zu umgehen, fügst du einfach die IS NOT NULL Klausel zur WHERE Klausel hinzu, wie im vorangegangenen Beispiel gezeigt.

Das folgende Beispiel veranschaulicht die Verwendung von LEFT JOIN:

SELECT DISTINCT a.city
FROM authors AS a 
 LEFT JOIN (SELECT city 
  FROM publishers 
  WHERE city IS NOT NULL) AS p ON a.city = p.city
WHERE p.city IS NULL;

MySQL und MariaDB

MySQL unterstützt EXCEPT nicht. MariaDB ab Version 10.3 unterstützt die Set-Operatoren EXCEPT, EXCEPT ALL und EXCEPT DISTINCT mit der SQL-Standardsyntax. Für MySQL kannst du die Operationen NOT IN oder NOT EXISTS als Alternative zu EXCEPT verwenden, wie im vorherigen Abschnitt beschrieben.

Oracle

Oracle-Versionen unter Oracle 21c unterstützen den Set-Operator EXCEPT nicht. Sie verfügen jedoch über einen alternativen Set-Operator, MINUS, mit identischer Funktionalität wie EXCEPT. Seine Syntax lautet wie folgt:

<SELECT statement1>
MINUS
<SELECT statement2>
MINUS
...

MINUS ist das funktionale Äquivalent von MINUS DISTINCT; die ALL Klausel ist nicht implementiert.

Oracle unterstützt MINUS nicht bei Abfragen, die:

  • Spalten, deren Datentypen LONG, BLOB, CLOB, BFILE, oder VARRAY

  • Eine FOR UPDATE Klausel

  • TABLE Sammlungsausdrücke

Wenn die erste Abfrage in einer Mengenoperation Ausdrücke in der Artikelliste SELECTenthält, musst du AS Klauseln einfügen, um diesen Ausdrücken Aliasnamen zuzuordnen. Außerdem darf nur die letzte Abfrage in der Mengenoperation eine ORDER BY Klausel enthalten.

Du könntest zum Beispiel eine Liste aller Filial-IDs erstellen, die keine Datensätze in der Verkaufstabelle haben, wie folgt:

SELECT stor_id FROM stores
MINUS
SELECT stor_id FROM sales

Der Befehl MINUS ist funktional ähnlich wie eine NOT IN Abfrage. Diese Abfrage liefert die gleichen Ergebnisse:

SELECT stor_id FROM stores
WHERE stor_id NOT IN
   (SELECT stor_id FROM sales)

PostgreSQL

PostgreSQL unterstützt die Set-Operatoren EXCEPT und EXCEPT ALL unter Verwendung der grundlegenden SQL-Standardsyntax:

<SELECT statement1>
EXCEPT [ALL]
<SELECT statement2>
EXCEPT [ALL]
...

EXCEPT DISTINCT wird nicht unterstützt, aber EXCEPT ist das funktionale Äquivalent. EXCEPT oder EXCEPT ALL werden bei Abfragen mit einer FOR UPDATE Klausel nicht unterstützt.

Die erste Abfrage in der Mengenoperation darf keine ORDER BY Klausel oder LIMIT Klausel enthalten, obwohl du eine Unterabfrage für die SELECT Anweisung definieren kannst, die diese enthält. Nachfolgende Abfragen in der Mengenoperation EXCEPT oder EXCEPT ALL können diese Klauseln enthalten, müssen aber in Klammern gesetzt werden. Andernfalls wird das letzte Vorkommen von ORDER BY oder LIMIT auf die gesamte Mengenoperation angewendet.

PostgreSQL wertet die SELECT Anweisungen in einer MehrfachanweisungEXCEPT von oben nach unten aus, es sei denn, du verwendest Klammern, um die Auswertungshierarchie der Anweisungen zu ändern.

Normalerweise werden doppelte Zeilen aus den beiden Ergebnismengen entfernt, es sei denn, du fügst das Schlüsselwort ALL hinzu. Mit dieser Abfrage kannst du zum Beispiel alle Titel in der Autorentabelle finden, die keine Einträge in der Verkaufstabelle haben:

SELECT title_id
FROM   authors
EXCEPT ALL
SELECT title_id
FROM   sales;

SQL Server

SQL Server unterstützt EXCEPT, aber keine seiner Unterklauseln. Zu Vergleichszwecken betrachtet SQL Server NULL-Werte als gleich, wenn er eine EXCEPT Ergebnismenge auswertet. Bei Verwendung der Anweisung SELECT ... INTO darf nur die erste Abfrage die Klausel INTO enthalten. ORDER BY ist nur am Ende der Anweisung und nicht bei jeder einzelnen Abfrage erlaubt. Umgekehrt können die Klauseln GROUP BY und HAVING nur innerhalb einzelner Abfragen verwendet werden und dürfen die endgültige Ergebnismenge nicht beeinflussen. Die Klausel FOR BROWSE darf nicht mit Anweisungen verwendet werden, die EXCEPT enthalten.

Siehe auch

  • INTERSECT

  • SELECT

  • UNION

EXISTS Operator

Der EXISTS Operator prüft eine Unterabfrage auf die Existenz von Zeilen. Alle Plattformen unterstützen die SQL-Standardsyntax.

Plattform Befehl
MySQL Unterstützt
Oracle Unterstützt
PostgreSQL Unterstützt
SQL Server Unterstützt

SQL-Standardsyntax

SELECT ...
WHERE [NOT] EXISTS (subquery)

Schlüsselwörter

WHERE [NOT] EXISTS
Prüft die Unterabfrage auf das Vorhandensein von einer oder mehreren Zeilen. Wenn auch nur eine Zeile die Subquery-Klausel erfüllt, wird der Wert Boolean TRUE zurückgegeben. Das optionale Schlüsselwort NOT gibt einen booleschen Wert TRUE zurück, wenn die Unterabfrage keine passenden Zeilen liefert.
subquery
Ruft eine Ergebnismenge ab, die auf einer vollständig gebildeten Unterabfrage basiert.

Die Regeln auf einen Blick

Der EXISTS Operator prüft eine Unterabfrage auf das Vorhandensein eines oder mehrerer Datensätze im Vergleich zu den Datensätzen der übergeordneten Abfrage.

Wenn wir zum Beispiel sehen wollen, ob es Aufträge gibt, bei denen kein Mitarbeiter die Stelle besetzt:

SELECT *
FROM jobs
WHERE NOT EXISTS
   (SELECT * FROM employee
    WHERE jobs.job_id = employee.job_id)

In diesem Beispiel wird mit dem optionalen Schlüsselwort NOT geprüft, ob es in der Unterabfrage Datensätze gibt. Das nächste Beispiel sucht nach bestimmten Datensätzen in der Unterabfrage, um die Hauptergebnismenge abzurufen:

SELECT au_lname
FROM authors
WHERE EXISTS
   (SELECT *
    FROM publishers
    WHERE authors.city = publishers.city)

Diese Abfrage gibt die Nachnamen von Autoren zurück, die in der gleichen Stadt leben wie ihre Verleger. Beachte, dass das Sternchen in der Unterabfrage akzeptabel ist, da die Unterabfrage nur einen einzigen Datensatz zurückgeben muss, um einen booleschen TRUE Wert zu liefern. Spalten sind in diesen Fällen irrelevant.

Programmiertipps und -schwierigkeiten

EXISTSbewirkt in vielen Abfragen dasselbe wie ANY (tatsächlich ist er semantisch äquivalent zum ANY Operator). EXISTS ist normalerweise bei korrelierten Unterabfragen am effektivsten.

Die Unterabfrage EXISTS sucht normalerweise nur nach einem von zwei Dingen. Die erste Möglichkeit ist die Verwendung des Sternchen-Platzhalters (z. B. SELECT * FROM ... ), damit du keine bestimmte Spalte oder keinen bestimmten Wert abrufst. In diesem Fall bedeutet das Sternchen "jede Spalte". Die zweite Möglichkeit ist, nur eine einzige Spalte in der Unterabfrage auszuwählen (z.B., SELECT au_id FROM ... ). Die dritte Möglichkeit ist, eine Konstante auszuwählen, z. B. (SELECT 1 FROM ... ).

EXISTS kann mit einer IN Klausel umgeschrieben werden. Hier ist ein Beispiel, das mit EXISTS geschrieben wurde:

SELECT au_id
FROM authors
WHERE EXISTS(SELECT au_id 
  FROM titleauthor AS ta WHERE ta.au_id = authors.au_id)

und seine Entsprechung mit IN:

SELECT au_id
FROM authors
WHERE au_id IN(SELECT ta.au_id 
  FROM titleauthor AS ta 
  WHERE ta.au_id = authors.au_id)

Siehe auch

  • ALL/ANY/SOME

  • IN

  • SELECT

  • WHERE

FILTER-Klausel

Die FILTER Klausel wird in Verbindung mit Aggregatfunktionen verwendet, es sei denn, sie werden als Fensteraggregate verwendet. PostgreSQL unterstützt die SQL-Standardsyntax für diese Klausel; die anderen Plattformen unterstützen sie nicht.

Plattform Befehl
MySQL Nicht unterstützt
Oracle Nicht unterstützt
PostgreSQL Unterstützt
SQL Server Nicht unterstützt

SQL-Standardsyntax

Die FILTER Klausel ist Teil einer SELECT Anweisung und qualifiziert einen Aggregatfunktionsaufruf:

[aggregate_function(input_args) FILTER
   (WHERE search_condition)
input_args := value[,..]

In Datenbanken, in denen die FILTER Klausel nicht unterstützt wird, kann die Funktionalität für Aggregate, die NULLs ignorieren, mit einer CASE Anweisung wie folgt simuliert werden:

[aggregate_function(input_args)
input_args := CASE WHEN search_condition THEN value 
   ELSE NULL END[,...]

Schlüsselwörter

aggregate_function
Eine Aggregatfunktion wie AVG, COUNT, COUNT DISTINCT, MAX, MIN, oder SUM.
WHERE search_condition
Jede Bedingung, die in einer WHERE Klausel erlaubt ist, ist erlaubt.

Die Regeln auf einen Blick

Die FILTER Klausel ist nur in Abfragen erlaubt, die Aggregatfunktionen verwenden. Hier ist ein Beispiel, das die FILTER Klausel verwendet, um Bücher nach Preis in PostgreSQL zu zählen:

SELECT SUM(ytd_sales) AS total_sales
   , SUM(ytd_sales) FILTER(WHERE price < '$20.00') 
   AS sales_book_lt_20
FROM titles;

Und hier ist eine Alternative mit CASE:

SELECT SUM(ytd_sales) AS total_sales
   , SUM(CASE WHEN price < '$20.00' THEN ytd_sales 
   ELSE NULL END) AS sales_book_lt_20
FROM titles;

Die Ergebnisse sind:

total_sales  sales_book_lt_20
-----------  -----------------------
97446        83821

Siehe auch

GROUP BY-Klausel

Die GROUP BY Klausel wird zum Aggregieren oder Deduplizieren von Daten verwendet. Sie wird oft zusammen mit Aggregatfunktionen und der HAVING Klausel verwendet.

Plattform Befehl
MySQL Unterstützt, mit Variationen
Oracle Unterstützt, mit Variationen
PostgreSQL Unterstützt, mit Variationen
SQL Server Unterstützt, mit Variationen

SQL-Standardsyntax

[GROUP BY group_by_expression
   [HAVING search_condition]]

group_by_expression ::= { (grouping_column[, ...]) | 
   ROLLUP (grouping_column[, ...]) | 
   CUBE (grouping_column[, ...]) | 
   GROUPING SETS (grouping_set_list) | () | 
   grouping_set, grouping_set_list }

Schlüsselwörter

GROUP BY group_by_expression
Gruppiert Ergebnismengen in die Kategorien, die in der group_by_expression. Wird in Abfragen verwendet, die Aggregatfunktionen wie AVG, COUNT, COUNT DISTINCT, MAX, MIN und SUM verwenden. Die group_by_expression der GROUP BY Klausel hat eine eigene, ausgefeilte Syntax; Beispiele und weitere Informationen zu ROLLUP, CUBE und GROUPING SETS findest du im folgenden Abschnitt.
HAVING search_condition
Fügt ähnlich wie die WHERE Klausel Suchbedingungen zu den Ergebnissen der GROUP BY Klausel hinzu. HAVING hat keinen Einfluss auf die Zeilen, die zur Berechnung der Aggregate verwendet werden. HAVING Klauseln können Unterabfragen enthalten.

Die Regeln auf einen Blick

Die GROUP BY Klausel wird nur in Abfragen benötigt, die Aggregatfunktionen verwenden. Die HAVING Klausel wird fast immer von einer GROUP BY Klausel begleitet, aber eine GROUP BY Klausel wird oft ohne eine HAVING Klausel verwendet.

Die GROUP BY-Klausel

Die GROUP BY Klausel wird verwendet, um einen aggregierten Wert für eine oder mehrere Zeilen zu melden, die von einer SELECT Anweisung zurückgegeben werden, die auf einer oder mehreren nicht aggregierten Spalten, den sogenannten Gruppierungsspalten, basiert. Hier ist zum Beispiel eine Abfrage, die auflistet, wie viele Personen jedes Jahr in den Jahren 2016 bis 2021 eingestellt wurden:

SELECT hire_year, COUNT(emp_id) AS nbr_emps
FROM employee
WHERE status = 'ACTIVE'
  AND hire_year BETWEEN 2016 AND 2021
GROUP BY hire_year;

Die Ergebnisse sind:

hire_year nbr_emps
--------- --------
2016      27
2017      17
2018      13
2019      19
2020      20
2021      32

Abfragen mit Aggregatfunktionen liefern viele Arten von zusammenfassenden Informationen. Zu den gängigsten Aggregatfunktionen gehören:

AVG
Gibt den Durchschnitt aller Nicht-NULL-Werte in der/den angegebenen Spalte(n) zurück
AVG DISTINCT
Gibt den Durchschnitt aller eindeutigen Nicht-NULL-Werte in der/den angegebenen Spalte(n) zurück.
COUNT
Zählt die Vorkommen aller Nicht-NULL-Werte in der/den angegebenen Spalte(n)
COUNT DISTINCT
Zählt die Vorkommen aller eindeutigen Nicht-NULL-Werte in der/den angegebenen Spalte(n)
COUNT(*)
Zählt jeden Datensatz in der Tabelle
MAX
Gibt den höchsten Nicht-NULL-Wert in der/den angegebenen Spalte(n) zurück
MIN
Gibt den niedrigsten Nicht-NULL-Wert in der/den angegebenen Spalte(n) zurück
SUM
Summiert alle Nicht-NULL-Werte in der/den angegebenen Spalte(n)
SUM DISTINCT
Summiert alle eindeutigen Nicht-NULL-Werte in der/den angegebenen Spalte(n)

Einige Abfragen, die Aggregate verwenden, geben eine einzelne Zeile zurück und werden zur Aggregation der gesamten Tabelle verwendet. Einwertige Aggregate werden als skalare Aggregate bezeichnet. Skalare Aggregate brauchen keine GROUP BY Klausel. Ein Beispiel:

-- Query
SELECT AVG(price)
FROM titles

-- Results
14.77

Abfragen, bei denen alle zurückgegebenen Spalten Aggregate sind, brauchen auch keine GROUP BY Klausel.

Abfragen, die sowohl reguläre Spaltenwerte als auch Werte von Aggregatfunktionen zurückgeben, werden üblicherweise Vektoraggregate genannt. Vektoraggregate verwenden die GROUP BY Klausel und geben eine oder mehrere Zeilen zurück.

Bei der Verwendung von GROUP BY gibt es einige Regeln zu beachten:

  • Platziere GROUP BY in der richtigen Reihenfolge der Klauseln - nach der WHERE Klausel und vor der ORDER BY Klausel.

  • Nimm alle nicht aggregierten Spalten in die GROUP BY Klausel auf.

  • Verwende keine Spaltenaliase in der GROUP BY Klausel (obwohl Tabellenaliase zulässig sind).

Nehmen wir zum Beispiel an, dass wir den Gesamtbetrag mehrerer Käufe aus einer Tabelle Order_Details abrufen müssen, die wie folgt aussieht:

OrderID      ProductID    UnitPrice            Quantity
-----------  -----------  -------------------  --------
10248        11           14.0000              12
10248        42           9.8000               10
10248        72           34.8000              5
10249        14           18.6000              9
10249        51           42.4000              40
10250        41           7.7000               10
10250        51           42.4000              35
10250        65           16.8000              15
...

Wir können dies mit einer Abfrage wie der folgenden tun:

SELECT OrderID, SUM(UnitPrice * Quantity) AS Order_Amt
FROM order_details
WHERE orderid IN (10248, 10249, 10250)
GROUP BY OrderID

Die Ergebnisse sind:

OrderID      Order_Amt
-----------  ----------------
10248        440.0000
10249        1863.4000
10250        1813.0000

Wir können die Aggregationen weiter verfeinern, indem wir mehr als eine Gruppierungsspalte verwenden. Betrachte die folgende Abfrage, die den Durchschnittspreis unserer Produkte abfragt, zuerst gruppiert nach Name und dann nach Größe:

SELECT name, size, AVG(unit_price) AS avg
FROM product
GROUP BY name, size

Die Ergebnisse sind:

name                 size    avg
------------         ------  -----------------------
Flux Capacitor       small   900
P32 Space Modulator  small   1400
Transmogrifier      medium  1400
Acme Rocket          large   600
Land Speeder         large   6500

Darüber hinaus unterstützt die GROUP BY Klausel einige sehr wichtige Unterklauseln:

{ROLLUP | CUBE} ([grouping_column[, ... ]])[, grouping_set_list]
Gruppiert die aggregierten Werte der Ergebnismenge nach einer oder mehreren Gruppierungsspalten. (Ohne ROLLUP oder CUBE ist die GROUP BY (grouping_column[, ... ]) Klausel die einfachste und häufigste Form der GROUP BY Klausel).
ROLLUP
Erzeugt Zwischensummen für jeden Satz von Gruppierungsspalten als hierarchischen Ergebnissatz, wobei Zwischensummen- und Gesamtsummenzeilen hierarchisch in den Ergebnissatz eingefügt werden. ROLLUP Operationen geben eine Zeile pro Gruppierungsspalte zurück, wobei in der Gruppierungsspalte NULL erscheint, um den Zwischensummen- oder Gesamtsummenwert anzuzeigen.
CUBE
Erzeugt Zwischensummen und Kreuztabellensummen für alle Gruppierungsspalten. Mit der CUBE Klausel kannst du gewissermaßen schnell und ohne viel Programmieraufwand mehrdimensionale Ergebnismengen aus relationalen Standardtabellen zurückgeben. CUBE ist besonders nützlich, wenn du mit großen Datenmengen arbeitest. Wie ROLLUP liefert CUBE Zwischensummen für die Gruppierungsspalten, enthält aber auch Zwischensummenzeilen für alle möglichen Kombinationen der in der Abfrage angegebenen Gruppierungsspalten.
GROUPING SETS [{ROLLUP | CUBE}] ([grouping_column[, ... ]])[, grouping_set_list]
Ermöglicht aggregierte Gruppen auf mehreren verschiedenen Gruppen von Spalten innerhalb derselben Abfrage. Dies ist besonders nützlich, wenn du nur einen Teil einer aggregierten Ergebnismenge zurückgeben möchtest. Mit der Klausel GROUPING SETS kannst du außerdem auswählen, welche Gruppierungsspalten verglichen werden sollen, während CUBE alle Gruppierungsspalten zurückgibt und ROLLUP eine hierarchische Teilmenge der Gruppierungsspalten zurückgibt. Wie die Syntax zeigt, erlaubt der SQL-Standard auch, dass GROUPING SETS mit ROLLUP oder CUBE gepaart wird.

Tabelle 4-2 veranschaulicht die Unterschiede zwischen den Ergebnismengen, die von GROUP BY allein und mit jeder dieser Unterklauseln zurückgegeben werden.

Tabelle 4-2. GROUP BY Syntaxvarianten
GROUP BY-Syntax Gibt die folgenden Sets zurück 
 GROUP BY (col_A, col_B, col_C)
 GROUP BY ROLLUP (col_A, col_B, col_C)



 GROUP BY CUBE (col_A, col_B, col_C)







 (col_A, col_B, col_C)
 (col_A, col_B, col_C)
    (col_A, col_B)
    (col_A)
    ()
 (col_A, col_B, col_C)
    (col_A, col_B)
    (col_A)
    (col_B, col_C)
    (col_B)
    (col_C)
    ()

Jeder Typ der GROUP BY Klausel gibt einen anderen Satz von aggregierten Werten und, im Fall von ROLLUP und CUBE, Summen und Zwischensummen zurück.

Die Konzepte von ROLLUP, CUBE und GROUPING SETS sind viel intuitiver, wenn sie anhand eines Beispiels erklärt werden. Im folgenden Beispiel fragen wir Daten ab, die die Anzahl der Kundenaufträge nach Auftragsjahr und Auftragsquartal zusammenfassen:

SELECT order_year AS year, order_quarter AS quarter,
    COUNT (*) AS orders
FROM order_details
WHERE order_year IN (2020, 2021)
GROUP BY ROLLUP (order_year, order_quarter)
ORDER BY order_year, order_quarter;

Die Ergebnisse sind:

year quarter orders
---- ------- ------
NULL NULL    648     -- grand total
2020 NULL    380     -- total for year 2020
2020 1       87
2020 2       77
2020 3       91
2020 4       125
2021 NULL    268     -- total for year 2021
2021 1       139
2021 2       119
2021 3       10

Wenn du der Abfrage Gruppierungsspalten hinzufügst, erhältst du mehr Details (und mehr Zwischensummen) in der Ergebnismenge. Ändern wir nun das vorherige Beispiel, indem wir der Abfrage eine Region hinzufügen (da die Anzahl der Zeilen jedoch steigt, betrachten wir nur das erste und zweite Quartal):

SELECT order_year AS year, order_quarter AS quarter, region,
   COUNT (*) AS orders
FROM order_details
WHERE order_year IN (2020, 2021)
  AND order_quarter IN (1,2)
  AND region IN ('USA', 'CANADA')
GROUP BY ROLLUP (order_year, order_quarter,region)
ORDER BY order_year, order_quarter, region;

Die Ergebnisse sind:

year quarter region  orders
---- ------- ------  ------
NULL NULL    NULL    183     -- grand total
2020 NULL    NULL    68      -- subtotal for year 2020
2020 1       NULL    36      -- subtotal for all regions in q1 of 2020
2020 1       CANADA  3
2020 1       USA     33
2020 2       NULL    32      -- subtotal for all regions in q2 of 2021
2020 2       CANADA  3
2020 2       USA     29
2021 NULL    NULL    115     -- subtotal for year 2021
2021 1       NULL    57      -- subtotal for all regions in q1 of 2021
2021 1       CANADA  11
2021 1       USA     46
2021 2       NULL    58      -- subtotal for all regions in q2 of 2021
2021 2       CANADA  4
2021 2       USA     54

Die GROUP BY CUBE Klausel ist nützlich, um multidimensionale Analysen auf aggregierten Daten durchzuführen. Wie GROUP BY ROLLUP liefert sie Zwischensummen, aber im Gegensatz zu GROUP BY ROLLUP liefert sie Zwischensummen, die alle in der Abfrage genannten Gruppierungsspalten kombinieren. (Wie du sehen wirst, hat sie auch das Potenzial, die Anzahl der Zeilen in der Ergebnismenge zu erhöhen.)

Im folgenden Beispiel suchen wir nach Daten, die die Anzahl der Kundenaufträge nach Auftragsjahr und Auftragsquartal zusammenfassen:

SELECT order_year AS year, order_quarter AS quarter,
     COUNT (*) AS orders
FROM order_details
WHERE order_year IN (2020, 2021)
GROUP BY CUBE (order_year, order_quarter)
ORDER BY order_year, order_quarter;

Die Ergebnisse sind:

year quarter orders
---- ------- ------
NULL NULL    648     -- grand total
NULL 1       226     -- subtotal for q1 of both years
NULL 2       196     -- subtotal for q2 of both years
NULL 3       101     -- subtotal for q3 of both years
NULL 4       125     -- subtotal for q4 of both years
2020 NULL    380     -- total for year 2020
2020 1       87
2020 2       77
2020 3       91
2020 4       125
2021 NULL    268     -- total for year 2021
2021 1       139
2021 2       119
2021 3       10

Mit der GROUP BY GROUPING SETS Klausel kannst du in einer einzigen Abfrage über mehr als eine Gruppe aggregieren. Für jede Gruppe gibt die Abfrage Zwischensummen zurück, wobei die Gruppierungsspalte als NULL markiert ist. Während die Klauseln CUBE und ROLLUP vordefinierte Zwischensummen in die Ergebnismenge einfügen, kannst du mit der Klausel GROUPING SETS festlegen, welche Zwischensummen der Abfrage hinzugefügt werden sollen. Die GROUPING SETS Klausel gibt auch eine Gesamtsumme zurück, wenn du ein Set ohne Spalten einfügst, wie z.B. ().

Wir verwenden eine ähnliche Beispielabfrage wie die mit ROLLUP und CUBE, dieses Mal summieren wir nach Jahr und Quartal und getrennt nach Jahr:

SELECT order_year AS year, order_quarter AS quarter, COUNT (*)
AS orders
FROM order_details
WHERE order_year IN (2020, 2021)
GROUP BY GROUPING SETS ((order_year, order_quarter), (order_year))
ORDER BY order_year, order_quarter;

Die Ergebnisse sind:

year quarter orders
---- ------- ------
2020 NULL    380     -- total for year 2020
2020 1       87
2020 2       77
2020 3       91
2020 4       125
2021 NULL    268     -- total for year 2021
2021 1       139
2021 2       119
2021 3       10

Eine andere Möglichkeit, sich GROUPING SETS vorzustellen, ist, es als eine UNION ALL von mehr als einer GROUP BY Abfrage zu betrachten, die sich auf verschiedene Teile derselben Daten bezieht. Du kannst der Datenbank mitteilen, dass sie Zwischensummen zu einer GROUPING SET hinzufügen soll, indem du einfach die ROLLUP oder CUBE Klausel hinzufügst, je nachdem, wie du die Zwischensummen bilden möchtest.

GROUPING SETS können auch verkettet werden, um große Kombinationen von Gruppierungen zu erzeugen. Verkettete GROUPING SETS ergeben das Kreuzprodukt der Gruppierungen aus jeder der Mengen in einer GROUPING SETS Liste. Verkettete GROUPING SETS sind mit CUBE und ROLLUP kompatibel, aber da sie ein Kreuzprodukt aller GROUPING SETS bilden, erzeugen sie selbst aus einer kleinen Anzahl von verketteten Gruppierungen eine sehr große Anzahl von endgültigen Gruppierungen. Dies wird in dem Beispiel in Tabelle 4-3 deutlich.

Tabelle 4-3. GROUP BY GOUPING SETS syntax
GROUP BY Syntax Gibt die folgenden Sets zurück 
 GROUP BY (col_A, col_B, col_C)
 
 (col_A, col_B, col_C)
 
 ...
 
 ...
 
 GROUP BY GROUPING SETS (col_A, col_B)
    (col_Y, col_Z)
 
 (col_A, col_Y)
    (col_A, col_Z)
    (col_B, col_Y)
    (col_B, col_Z)

Du kannst dir vorstellen, wie groß die Ergebnismenge sein würde, wenn die verkettete GROUPING SETS eine große Anzahl von Gruppierungen enthalten würde! Die zurückgegebenen Informationen können jedoch sehr wertvoll und schwer zu reproduzieren sein.

Die HAVING-Klausel

Die HAVING Klausel fügt Suchbedingungen für das Ergebnis der GROUP BY Klausel hinzu. Die HAVING Klausel funktioniert ähnlich wie die WHERE Klausel, bezieht sich aber auf die GROUP BY Klausel. Die HAVING -Klausel unterstützt dieselben Suchbedingungen wie die WHERE -Klausel (siehe oben). Nehmen wir zum Beispiel an, dass wir mit der gleichen Abfrage wie zu Beginn des vorherigen Abschnitts nur die Aufträge finden wollen, die von mehr als drei Personen ausgeführt werden:

-- Query
SELECT   j.job_desc "Job Description",
         COUNT(e.job_id) "Nbr in Job"
FROM     employee e
JOIN     jobs j ON e.job_id = j.job_id
GROUP BY j.job_desc
HAVING   COUNT(e.job_id) > 3

-- Results
Job Description                                    Nbr in Job
-------------------------------------------------- -----------
Acquisitions Manager                               4
Managing Editor                                    4
Marketing Manager                                  4
Operations Manager                                 4
Productions Manager                                4
Public Relations Manager                           4
Publisher                                          7

Beachte, dass der SQL-Standard nicht vorschreibt, dass eine explizite GROUP BY -Klausel mit einer HAVING -Klausel erscheinen muss. Die folgende Abfrage für die Tabelle " Mitarbeiter" ist zum Beispiel gültig, weil sie eine implizite GROUP BY Klausel enthält:

SELECT COUNT(dept_nbr)
FROM employee
HAVING COUNT(dept_nbr) > 30;

Diese Anwendung der HAVING Klausel ist zwar gültig, aber eher selten.

IN Bediener

Der IN Operator ermöglicht es, eine Liste von Werten abzugrenzen, die entweder explizit aufgeführt sind oder aus einer Unterabfrage stammen, und einen Wert mit dieser Liste in einer WHERE oder HAVING Klausel zu vergleichen. Mit anderen Worten, er gibt dir die Möglichkeit zu sagen: "Ist Wert A in dieser Liste von Werten?" Alle Plattformen unterstützen die SQL-Standardsyntax.

Plattform Befehl
MySQL Unterstützt
Oracle Unterstützt
PostgreSQL Unterstützt
SQL Server Unterstützt

SQL-Standardsyntax

{WHERE | HAVING | {AND | OR}}
   value [NOT] IN ({comp_value1, comp_value2[, ...] | subquery})

Schlüsselwörter

{WHERE | HAVING | {AND | OR}} value
IN ist entweder in der WHERE oder in der HAVING Klausel erlaubt. Der IN Vergleich kann auch Teil einer AND oder OR Klausel in einer WHERE oder HAVING Klausel mit mehreren Bedingungen sein. value kann von beliebigem Datentyp sein, ist aber normalerweise der Name einer Spalte der Tabelle, auf die die Transaktion verweist, oder vielleicht eine Host-Variable, wenn sie programmatisch verwendet wird.
NOT
Weist die Datenbank optional an, nach einer Ergebnismenge zu suchen, die Werte enthält, die nicht in der Liste enthalten sind.
IN ({comp_value1, comp_value2[, ... ] | subquery})
Definiert die Liste der Vergleichswerte (daher, comp_value), mit denen verglichen werden soll. Jeder comp_value muss vom gleichen oder einem kompatiblen Datentyp sein wie der ursprüngliche value. Außerdem gelten für sie die Standard-Datentypregeln. So müssen beispielsweise String-Werte durch Anführungszeichen getrennt werden, während Integer-Werte keine Begrenzungszeichen benötigen. Alternativ zur Auflistung bestimmter Werte kannst du eine Unterabfrage, die einen oder mehrere Werte eines kompatiblen Datentyps zurückgibt, in Klammern einschließen.

Die Regeln auf einen Blick

Im folgenden Beispiel, das auf SQL Server erstellt wurde, suchen wir in der Tabelle " Mitarbeiter" der Personaldatenbank nach allen Mitarbeitern, deren Heimatstaat Georgia, Tennessee, Alabama oder Kentucky ist:

SELECT *
FROM hr..employee
WHERE home_state IN ('AL','GA','TN','KY')

Genauso können wir in der Personaldatenbank nach allen Mitarbeiternsuchen, die Autoren in der Publikationsdatenbank sind:

SELECT *
FROM hr..employee
WHERE emp_id IN (SELECT au_id FROM pubs..authors)

Wir können auch das Schlüsselwort NOT verwenden, um eine Ergebnismenge zurückzugeben, wenn kein Wert vorhanden ist. Im folgenden Fall befindet sich der Hauptsitz des Unternehmens in New York, und viele Arbeitnehmer pendeln aus den benachbarten Staaten ein. Wir wollen alle diese Arbeitnehmer sehen:

SELECT *
FROM hr..employee
WHERE home_state
   NOT IN ('NY','NJ','MA','CT','RI','DE','NH')

Beachte, dass Oracle zwar die SQL-Standardfunktionalität vollständig unterstützt, aber die Funktionalität des IN -Operators erweitert, indem es Übereinstimmungen mit mehreren Argumenten zulässt. Zum Beispiel ist die folgende SELECT ... WHERE ... IN Anweisung unter Oracle zulässig:

SELECT *
FROM hr..employee e
WHERE (e.emp_id, e.emp_dept)
   IN ( (242, 'sales'), (442, 'mfg'), (747, 'mkt) )

Siehe auch

  • ALL/ANY/SOME

  • BETWEEN

  • EXISTS

  • LIKE

  • SELECT

INTERSECT Set Operator

Der INTERSECT set-Operator ruft die Ergebnismengen von zwei oder mehr Abfragen ab, wobei er nur die Datensätze enthält, die von der ersten Abfrage abgerufen wurden und auch in allen nachfolgenden Abfragen gefunden werden (d.h. er enthält nur die Zeilen, die in allen Ergebnismengen vorkommen). In mancher Hinsicht ähnelt INTERSECT einer INNER JOIN Operation (siehe "JOIN Subclause" für weitere Informationen).

INTERSECT gehört zu einer Klasse von Schlüsselwörtern, die Mengenoperatoren genannt werden. Andere Mengenoperatoren sind EXCEPT und UNION. Alle Mengenoperatoren werden verwendet, um die Ergebnismengen von zwei oder mehr Abfragen gleichzeitig zu manipulieren; daher der Begriff "Mengenoperatoren".

Plattform Befehl
MySQL Nicht unterstützt
MariaDB Unterstützt, mit Einschränkungen
Oracle Unterstützt, mit Einschränkungen
PostgreSQL Unterstützt, mit Einschränkungen
SQL Server Unterstützt, mit Einschränkungen

SQL-Standardsyntax

Es gibt technisch gesehen keine Begrenzung für die Anzahl der Abfragen, die du mit dem INTERSECT set-Operator kombinieren kannst. Die allgemeine Syntax lautet:

<SELECT statement1>
INTERSECT [ALL | DISTINCT]
[CORRESPONDING [BY (column1, column2, ...)]]
<SELECT statement2>
INTERSECT [ALL | DISTINCT]
[CORRESPONDING [BY (column1, column2, ...)]]
...

Schlüsselwörter

INTERSECT
Legt fest, welche Zeilen in die endgültige Einzelergebnismenge aufgenommen werden.
ALL | DISTINCT
ALL schließt doppelte Zeilen aus allen Ergebnismengen in den INTERSECT Vergleich ein. DISTINCT lässt doppelte Zeilen aus allen Ergebnismengen vor dem INTERSECT Vergleich fallen. Alle Spalten, die einen NULL-Wert enthalten, werden als Duplikate betrachtet. (Wenn weder ALL noch DISTINCT verwendet wird, ist das Verhalten von DISTINCT der Standard).
CORRESPONDING
Legt fest, dass nur Spalten mit demselben Namen in beiden Abfragen zurückgegeben werden, auch wenn beide Abfragen das Kürzel Sternchen(*) verwenden.
BY (column1, column2, ... )
Legt fest, dass nur die benannten Spalten zurückgegeben werden, auch wenn in den Abfragen weitere Spalten mit entsprechenden Namen existieren. Muss mit dem CORRESPONDING Schlüsselwort verwendet werden.

Die Regeln auf einen Blick

Es gibt nur eine wichtige Regel, an die du dich bei der Verwendung von INTERSECT erinnern musst: Die Reihenfolge und Anzahl der Spalten muss in allen Abfragen gleich sein.

Auch wenn die Datentypen der entsprechenden Spalten nicht identisch sein müssen, müssen sie kompatibel sein (zum Beispiel sind CHAR und VARCHAR kompatible Datentypen). Standardmäßig wird die Ergebnismenge auf die größte der Spalten in jeder Ordnungsposition gesetzt.

Programmiertipps und -schwierigkeiten

Keine der Plattformen unterstützt die SQL-Standard CORRESPONDING [BY (column1, column2, ... )] Klausel.

Der SQL-Standard bewertet INTERSECT mit höherer Priorität als andere Set-Operatoren, aber nicht alle Plattformen bewerten den Vorrang von Set-Operatoren auf die gleiche Weise. Du kannst den Vorrang von Mengenoperatoren explizit mit Klammern steuern. Andernfalls wertet das DBMS die Ausdrücke entweder von ganz links nach ganz rechts oder vom ersten zum letzten aus.

Nach dem Standard ist nur eine ORDER BY Klausel in der gesamten Abfrage erlaubt. Sie sollte am Ende der letzten SELECT Anweisung eingefügt werden. Um Mehrdeutigkeiten zwischen Spalten und Tabellen zu vermeiden, musst du jeder Spalte jeder Tabelle den gleichen Alias geben. Zum Beispiel:

SELECT a.au_lname AS last_name, a.au_fname AS first_name
FROM authors AS a
INTERSECT
SELECT e.emp_lname AS last_name, e.emp_fname AS last_name
FROM employees AS e
ORDER BY last_name, first_name

Beachte auch, dass deine Spaltendatentypen zwar in allen Abfragen auf INTERSECT kompatibel sind, dass es aber auf den verschiedenen DBMS-Plattformen Unterschiede in Bezug auf die Länge der Spalten geben kann. Wenn zum Beispiel die Spalte au_lname in der ersten Abfrage deutlich länger ist als die Spalte emp_lname in der zweiten Abfrage, können die verschiedenen Plattformen unterschiedliche Regeln anwenden, welche Länge für das Endergebnis verwendet wird. In der Regel wählen die Plattformen jedoch die längere (und weniger einschränkende) Spaltengröße für die Verwendung in der Ergebnismenge.

Jedes DBMS kann seine eigenen Regeln anwenden, um zu bestimmen, welcher Spaltenname verwendet wird, wenn die Spalten in den Tabellen unterschiedliche Namen haben. Im Allgemeinen werden die Spaltennamen aus der ersten Abfrage verwendet.

Auf Plattformen, die INTERSECT nicht unterstützen, ersetze eine Abfrage durch INNER JOIN.

Hier ist das frühere Beispiel, das als INNER JOIN umgeschrieben wurde:

SELECT DISTINCT a.au_lname AS last_name, a.au_fname AS first_name
FROM authors AS a 
   INNER JOIN employees AS e 
   ON (a.au_lname = e.emp_lname AND a.au_fname = e.emp_fname)
ORDER BY last_name, first_name

MySQL und MariaDB

MySQL unterstützt INTERSECT nicht. MariaDB 10.3 und höher unterstützen die INTERSECT, INTERSECT ALL und INTERSECT DISTINCT unter Verwendung der SQL-Standardsyntax.

Oracle

Oracle unterstützt die Set-Operatoren INTERSECT und INTERSECT ALL mit der grundlegenden SQL-Standardsyntax. INTERSECT DISTINCT wird nicht unterstützt, aber INTERSECT ist das funktionale Äquivalent.

Mit dieser Abfrage kannst du zum Beispiel alle Laden-IDs finden, die auch Verkäufe haben:

SELECT stor_id FROM stores
INTERSECT
SELECT stor_id FROM sales

Oracle unterstützt INTERSECT nicht für die folgenden Arten von Abfragen:

  • Abfragen, die Spalten mit den Datentypen LONG, BLOB, CLOB, BFILE, oder VARRAY enthalten

  • Abfragen, die eine FOR UPDATE Klausel oder einen TABLE Sammelausdruck enthalten

Wenn die erste Abfrage in der Set-Operation Ausdrücke in der Elementliste SELECT enthält, solltest du das Schlüsselwort AS einfügen, um der Spalte, die aus dem Ausdruck resultiert, einen Alias zuzuordnen. Außerdem darf nur die erste Abfrage in der Set-Operation eine ORDER BY Klausel enthalten.

PostgreSQL

PostgreSQL unterstützt die Set-Operatoren INTERSECT und INTERSECT ALL mit der grundlegenden SQL-Standardsyntax, aber nicht bei Abfragen mit einer FOR UPDATE -Klausel. INTERSECT DISTINCT wird nicht unterstützt, aber INTERSECT ist das funktionale Äquivalent.

So kannst du zum Beispiel alle Autoren finden, die auch Angestellte sind und deren Nachnamen mit "P" beginnen:

SELECT a.au_lname
FROM   authors AS a
WHERE  a.au_lname LIKE 'P%'
INTERSECT
SELECT e.lname
FROM   employee AS e
WHERE  e.lname LIKE 'W%';

Die erste Abfrage in der Mengenoperation darf keine ORDER BY Klausel oder LIMIT Klausel enthalten. Nachfolgende Abfragen in der Mengenoperation INTERSECT [ALL] können diese Klauseln enthalten, müssen aber in Klammern gesetzt werden. Andernfalls wird davon ausgegangen, dass das äußerste rechte Vorkommen von ORDER BY oder LIMIT für die gesamte Mengenoperation gilt.

SQL Server

SQL Server unterstützt INTERSECT, aber nicht seine Unterklauseln. Die Spaltennamen der Ergebnismenge sind die, die von der ersten Abfrage zurückgegeben werden. Alle Spaltennamen oder Aliasnamen, auf die in einer ORDER BY Klausel verwiesen wird, müssen in der ersten Abfrage erscheinen. Wenn du INTERSECT (oder EXCEPT) verwendest, um mehr als zwei Ergebnismengen zu vergleichen, wird jedes Paar von Ergebnismengen (d.h. jedes Paar von Abfragen) verglichen, bevor zum nächsten Paar übergegangen wird, und zwar in der Reihenfolge: Ausdrücke in Klammern zuerst, INTERSECT Mengenoperatoren an zweiter Stelle und EXCEPT und UNION an letzter Stelle.

Beachte auch, dass du die Operationen NOT IN oder NOT EXISTS in Verbindung mit einer korrelierten Unterabfrage als Alternative verwenden kannst. Beispiele findest du in den Abschnitten IN und EXISTS.

Siehe auch

  • EXCEPT

  • EXISTS

  • IN

  • SELECT

  • UNION

IS-Operator

Der IS Operator bestimmt, ob ein Wert NULL ist oder nicht. Alle Plattformen unterstützen die SQL-Standardsyntax.

Plattform Befehl
MySQL Unterstützt
Oracle Unterstützt
PostgreSQL Unterstützt
SQL Server Unterstützt

SQL-Standardsyntax

{WHERE | {AND | OR}} expression IS [NOT] NULL

Schlüsselwörter

{WHERE | {AND | OR}} expression IS NULL
Gibt einen booleschen Wert von TRUE zurück, wenn die expression NULL ist, und FALSE, wenn die expression nicht NULL ist. Der expression für NULL ausgewertet wird, kann das Schlüsselwort WHERE oder die Schlüsselwörter AND oder OR vorangestellt werden.
NOT
Kehrt das Prädikat um: Die Anweisung gibt stattdessen einen Boolean TRUE zurück, wenn der Wert von expression nicht NULL ist, und FALSE, wenn der Wert von expression NULL ist.

Die Regeln auf einen Blick

Da der Wert von NULL unbekannt ist, kannst du keine Vergleichsausdrücke verwenden, um festzustellen, ob ein Wert NULL ist. Zum Beispiel können die Ausdrücke X = NULL und X <> NULL nicht aufgelöst werden, weil kein Wert einer Unbekannten gleich oder ungleich sein kann.

Stattdessen musst du den IS NULL Operator verwenden. Achte darauf, dass du das Wort NULL nicht in Anführungszeichen setzt, denn wenn du das tust, interpretiert das DBMS den Wert als das Wort "NULL" und nicht als den speziellen Wert NULL.

Programmiertipps und -schwierigkeiten

Einige Plattformen unterstützen die Verwendung eines Vergleichsoperators, um festzustellen, ob ein Ausdruck NULL ist. Alle Plattformen, die in diesem Buch behandelt werden, unterstützen jetzt jedoch die SQL-Standardsyntax IS [NOT] NULL.

Manchmal macht die Prüfung auf NULL deine WHERE Klausel nur geringfügig komplexer. Anstelle eines einfachen Prädikats, das den Wert von stor_id prüft, wie hier gezeigt:

SELECT stor_id, ord_date
FROM sales
WHERE stor_id IN (6630, 7708)

kannst du ein zweites Prädikat hinzufügen, um die Möglichkeit zu berücksichtigen, dass stor_id NULL sein könnte:

SELECT stor_id, ord_date
FROM sales
WHERE stor_id IN (6630, 7708)
   OR stor_id IS NULL

Siehe auch

  • SELECT

  • WHERE

JOIN Unterklausel

Die Subclause JOIN ermöglicht es dir, Zeilen aus zwei oder mehr logisch zusammenhängenden Tabellen abzurufen. Du kannst viele verschiedene Join-Bedingungen und Arten von Joins definieren, wobei die von den verschiedenen Plattformen unterstützten Arten von Joins sehr unterschiedlich sind.

Plattform Befehl
MySQL Unterstützt, mit Variationen
Oracle Unterstützt, mit Variationen
PostgreSQL Unterstützt, mit Variationen
SQL Server Unterstützt, mit Einschränkungen

SQL-Standardsyntax

FROM table [AS alias] { [join_type] JOIN [LATERAL] joined_table
[[AS] alias]
   { ON join_condition1 [{AND | OR} join_condition2] [...] |
   USING (column1[, ...]) }} |
 [ PARTITION BY (column1[, ...])]
[...]

Schlüsselwörter

FROM table
Definiert die erste Tabelle oder Ansicht in der Verknüpfung.
[join_type] JOIN [LATERAL] joined_table
Gibt den Typ von JOIN und die zweite(n) Tabelle(n) im Join an. Du kannst auch eine alias für jede der joined_tables. Die Join-Typen sind:
CROSS JOIN

Gibt das vollständige Kreuzprodukt von zwei Tabellen an. Für jeden Datensatz in der ersten Tabelle werden alle Datensätze in der zweiten Tabelle verknüpft, wodurch eine große Ergebnismenge entsteht. Dieser Befehl hat die gleiche Wirkung wie das Weglassen der Verknüpfungsbedingung, und die Ergebnismenge wird auch als kartesisches Produkt bezeichnet.

Cross-Joins sind nicht ratsam oder empfohlen.

[INNER] JOIN
Legt fest, dass nicht übereinstimmende Zeilen in beiden Tabellen des Joins verworfen werden sollen. Wenn kein Join-Typ explizit definiert ist, ist dies die Standardeinstellung.
LEFT [OUTER] JOIN
Legt fest, dass alle Datensätze aus der Tabelle auf der linken Seite der Join-Anweisung zurückgegeben werden. Wenn ein Datensatz, der aus der linken Tabelle zurückgegeben wird, keinen passenden Datensatz in der Tabelle auf der rechten Seite des Joins hat, wird er trotzdem zurückgegeben. Die Spalten der rechten Tabelle geben NULL-Werte zurück, wenn es keine übereinstimmende Zeile gibt. Aus Gründen der Konsistenz ist es ratsam, alle äußeren Joins als linke äußere Joins zu konfigurieren (anstatt linke und rechte äußere Joins zu mischen), wo immer dies möglich ist.
RIGHT [OUTER] JOIN
Legt fest, dass alle Datensätze aus der Tabelle auf der rechten Seite der Join-Anweisung zurückgegeben werden, auch wenn die Tabelle auf der linken Seite keinen passenden Datensatz enthält. Spalten aus der linken Tabelle geben NULL-Werte zurück, wenn es keine übereinstimmende Zeile gibt.
FULL [OUTER] JOIN
Legt fest, dass alle Zeilen aus beiden Tabellen zurückgegeben werden, unabhängig davon, ob eine Zeile aus einer Tabelle mit einer Zeile in der anderen Tabelle übereinstimmt. Alle Spalten, die in der entsprechenden Join-Tabelle keinen Wert haben, erhalten einen NULL-Wert.
NATURAL
Legt fest, dass der Join (entweder inner oder outer) für alle gleichnamigen Spalten in den beiden Tabellen durchgeführt werden soll. Daher solltest du keine Join-Bedingungen mit den Klauseln ON oder USING angeben. Die Abfrage wird fehlschlagen, wenn du sie auf zwei Tabellen stellst, die keine Spalten mit demselben Namen enthalten.
LATERAL
Das Schlüsselwort LATERAL kann mit LEFT JOIN oder CROSS JOIN verwendet werden. Es bezeichnet eine korrelierte Subquery oder einen Funktionsaufruf, bei dem Elemente aus zuvor angegebenen Tabellen in der Subquery oder als Argumente für die Funktion verwendet werden. Die verwendete Funktion kann mehr als eine Zeile zurückgeben.
[AS] alias
Gibt einen Alias oder ein Kürzel für die verbundene Tabelle an. Das Schlüsselwort AS ist optional, wenn du einen Alias angibst.
ON join_condition
Verbindet die Zeilen der Tabelle, die in der FROM Klausel angezeigt wird, mit den Zeilen der Tabelle, die in der JOIN Klausel deklariert wird. Du kannst mehrere JOIN Anweisungen haben, die alle auf einem gemeinsamen Satz von Werten basieren. Diese Werte sind in der Regel in Spalten mit demselben Namen und Datentyp enthalten, die in beiden zu verbindenden Tabellen vorkommen. Diese Spalten oder möglicherweise eine einzelne Spalte aus jeder Tabelle werden als Join-Schlüssel oder gemeinsamer Schlüssel bezeichnet. In den meisten (aber nicht allen) Fällen ist der Join-Schlüssel der Primärschlüssel der einen Tabelle und ein Fremdschlüssel in der anderen Tabelle. Solange die Werte in den Spalten übereinstimmen, kann der Join durchgeführt werden.

join_conditions werden syntaktisch in der folgenden Form dargestellt (beachte, dass Join-Typen in diesem Beispiel absichtlich ausgeschlossen werden):

FROM table_name1
JOIN table_name2
   ON table_name1.column1 = table_name2.column2
      [{AND|OR} table_name1.column3 = table_name2.column4]
      [...]
JOIN table_name3
   ON table_name1.columnA = table_name3.columnA
      [{AND|OR} table_name1.column3 = table_name2.column4]
      [...]
[JOIN...]

Verwende den AND Operator und den OR Operator, um eine JOIN mit mehreren Bedingungen zu erstellen. Es ist auch eine gute Idee, Klammern um jedes Paar verbundener Tabellen zu verwenden, wenn mehr als zwei Tabellen beteiligt sind, da dies das Lesen der Abfrage viel einfacher macht.

USING (column[, ... ])
Setzt eine Gleichheitsbedingung für einen oder mehrere benannte columns, die in beiden Tabellen vorkommen. Die Spalte(n) muss (müssen) wie angegeben in beiden Tabellen vorhanden sein. Das Schreiben einer USING Klausel ist etwas schneller als das Schreiben von ... ON table1.columnA = table2.columnAzu schreiben, aber die Ergebnisse sind funktional gleichwertig.
PARTITION BY (column1[, ... ])
Nützlich zum Füllen von Lücken in Ergebnismengen. Nur Oracle unterstützt diese Klausel. Ein Beispiel findest du im Abschnitt zu Oracle.

Die Regeln auf einen Blick

Mit Joins kannst du Datensätze aus zwei (oder mehr) logisch verbundenen Tabellen in einer einzigen Ergebnismenge abrufen. Für diesen Vorgang kannst du den SQL-Standard JOIN (siehe hier) oder einen sogenannten Theta-Join verwenden. Theta-Joins, die eine WHERE -Klausel zur Festlegung der Filterkriterien verwenden, sind die "alte" Art, Joins durchzuführen.

Du könntest zum Beispiel eine Tabelle mit dem Namen employee haben, die Informationen über alle Mitarbeiter deines Unternehmens enthält. Die Mitarbeitertabelle enthält jedoch keine ausführlichen Informationen über die Stelle, die ein Mitarbeiter innehat, sondern nur job_ids. Alle Informationen über die Stelle, wie z. B. die Beschreibung und der Titel, werden in einer Tabelle namens job gespeichert. Mit einer Verknüpfung kannst du ganz einfach Spalten aus beiden Tabellen in einem einzigen Satz von Datensätzen zurückgeben. Die folgenden Beispielabfragen veranschaulichen den Unterschied zwischen einem Theta-Join und einem SQL-Standard JOIN:

/* Theta join */
SELECT emp_lname, emp_fname, job_title
FROM employee, jobs
WHERE employee.job_id = jobs.job_id;

/* SQL standard join */
SELECT emp_lname, emp_fname, job_title
FROM employee
JOIN jobs ON employee.job_id = jobs.job_id;

Wenn du mehrere Spalten in einer einzigen Abfrage referenzierst, müssen die Spalten eindeutig sein. Mit anderen Worten: Die Spalten müssen entweder in jeder Tabelle eindeutig sein oder mit einem Tabellenbezeichner referenziert werden, wie es bei der Spalte job_id im vorangegangenen Beispiel der Fall ist (alle Spalten in der Abfrage, die nicht in beiden Tabellen vorhanden sind, müssen nicht durch Tabellenbezeichner qualifiziert werden). Abfragen wie diese sind jedoch oft schwer zu lesen. Die folgende Abwandlung des vorherigen SQL-Standards JOIN ist in besserer Form, weil sie die kurzen, leicht zu lesenden Aliasnamen e und j verwendet, um auf die Tabellen Mitarbeiter und Aufträge zu verweisen:

SELECT e.emp_lname, e.emp_fname, j.job_title
FROM employee AS e
JOIN jobs AS j ON e.job_id = j.job_id;

Die vorherigen Beispiele beschränkten sich auf Gleichheits-Joins oder Joins, die auf Gleichheit basieren und ein Gleichheitszeichen(=) verwenden. Die meisten anderen Vergleichsoperatoren sind jedoch auch erlaubt: Du kannst Joins mit >, <, >=, <=, <> usw. durchführen.

Du kannst keine binären Large-Object-Datentypen (z.B. BLOB) oder andere Large-Object-Datentypen (z.B. CLOB, NLOB, etc.) verknüpfen. Andere Datentypen sind in einem Join-Vergleich normalerweise erlaubt.

Im Folgenden findest du Beispiele für jede Art von Verbindung:

CROSS JOIN

Hier sind einige Beispiele für Cross-Joins. Die erste ist ein Theta-Join, bei dem die Join-Bedingungen einfach weggelassen werden, die zweite ist mit der CROSS JOIN Klausel geschrieben und die letzte Abfrage ist vom Konzept her ähnlich wie die erste, mit einer JOIN Klausel, die die Join-Bedingungen weglässt:

SELECT *
FROM employee, jobs;

SELECT *
FROM employee
CROSS JOIN jobs;

SELECT *
FROM employee
JOIN jobs;

Wie bereits erwähnt, sind Cross-Joins - also Verknüpfungen zwischen zwei oder mehr Tabellen, die alle Daten für alle Zeilen in allen möglichen Varianten zurückgeben (d.h. das kartesische Produkt der Tabellen) - eine wirklich schlechte Idee. Sieh dir diese Beispiele genau an, damit du weißt, wie sie aussehen, und vermeide sie dann!

INNER JOIN

Es folgt ein Inner Join, der mit der SQL-Standardsyntax geschrieben wurde:

SELECT a.au_lname AS 'last name',
   a.au_fname AS 'first name',
   p.pub_name AS 'publisher'
FROM authors AS a
INNER JOIN publishers AS p ON a.city = p.city
ORDER BY a.au_lname DESC

Es gibt viele Autoren in der Autorentabelle, aber nur sehr wenige von ihnen haben Städte, die mit den Städten ihrer Verleger in der Verlagstabelle übereinstimmen. Die vorhergehende Abfrage, die in der Pubs-Datenbank auf SQL Server ausgeführt wird, liefert zum Beispiel folgende Ergebnisse:

last name      first name             publisher
---------------  --------------------  ------------------
Carson          Cheryl                Algodata Infosystems
Bennet          Abraham               Algodata Infosystems

Die Verknüpfung wird als Inner Join bezeichnet, weil nur die Datensätze, die die Verknüpfungsbedingung in beiden Tabellen erfüllen, als "innerhalb" der Verknüpfung gelten. Auf Plattformen, die dies unterstützen, kannst du dieselbe Abfrage auch stellen, indem du die USING Klausel durch die ON Klausel ersetzt:

SELECT a.au_lname AS 'last name',
   a.au_fname AS 'first name',
   p.pub_name AS 'publisher'
FROM authors AS a
INNER JOIN publishers AS p USING (city)
ORDER BY a.au_lname DESC
Die Ergebnisse für diese Abfrage wären die gleichen.
LEFT [OUTER] JOIN

Es folgt ein Beispiel für eine linke äußere Verknüpfung, bei der wir für jeden Autor nach dem Verlag fragen (wir könnten auch die USING Klausel durch die ON Klausel ersetzen, wie im vorherigen Beispiel für die innere Verknüpfung):

SELECT a.au_lname AS "last name",
   a.au_fname AS "first name",
   p.pub_name AS "publisher"
FROM authors AS a
LEFT OUTER JOIN publishers AS p ON a.city = p.city
ORDER BY a.au_lname DESC

In diesem Beispiel wird jeder Autor aus der linken Tabelle(Autoren) zurückgegeben, zusammen mit dem Namen des Verlags, wenn es eine Übereinstimmung gibt, oder ein NULL-Wert, wenn es keine Übereinstimmung gibt. In der SQL Server Pubs-Datenbank gibt die Abfrage zum Beispiel zurück:

last name     first name            publisher
--------------- -------------------- ----------------
Yokomoto       Akiko                NULL
White          Johnson              NULL
Stringer       Dirk                 NULL
Straight       Dean                 NULL
...
Wie bereits erwähnt, ist es am besten, linke und rechte äußere Joins nicht zu mischen, um die Konsistenz zu erhöhen. Linke Verknüpfungen sind die häufigere Wahl.
RIGHT [OUTER] JOIN

Eine rechte äußere Verknüpfung ist im Grunde dasselbe wie eine linke äußere Verknüpfung, nur dass sie alle Datensätze aus der Tabelle auf der rechten Seite der Abfrage zurückgibt. Ein Beispiel: Die folgende Abfrage wird in der Pubs-Datenbank auf SQL Server ausgeführt:

SELECT a.au_lname AS "last name",
   a.au_fname AS "first name",
   p.pub_name AS "publisher"
FROM authors AS a
RIGHT OUTER JOIN publishers AS p ON a.city = p.city
ORDER BY a.au_lname DESC

gibt die folgende Ergebnismenge zurück:

last name       first name        publisher
---------------- ---------------- ------------------------
Carson           Cheryl           Algodata Infosystems
Bennet           Abraham          Algodata Infosystems
NULL             NULL             New Moon Books
NULL             NULL             Binnet & Hardley
...
Jeder Verlag aus der rechten Tabelle(Verlage) wird zurückgegeben, zusammen mit den Namen der Autoren, wenn es eine Übereinstimmung gibt, oder ein NULL-Wert, wenn es keine Übereinstimmung gibt. Auch hier ist es am besten, rechte und linke Outer-Joins nicht zu vermischen.
NATURAL [INNER | {LEFT | RIGHT} [OUTER]] JOIN

Natürliche Verknüpfungen sind ein Ersatz für die Klausel ON oder USING, also verwende NATURAL nicht mit diesen Klauseln. Zum Beispiel:

SELECT a.au_lname AS "first name",
   a.au_fname AS "last name",
   p.pub_name AS "publisher"
FROM authors AS a
NATURAL RIGHT OUTER JOIN publishers AS p
ORDER BY a.au_lname DESC

Die vorstehende Abfrage funktioniert genauso wie die früheren Beispiele, allerdings nur, wenn beide Tabellen eine Spalte namens Stadt haben und dies die einzige gemeinsame Spalte ist. Auf ähnliche Weise kannst du auch alle anderen Arten von Joins (INNER, FULL, OUTER) mit dem Präfix NATURAL durchführen.

Wir empfehlen dir, natürliche Verknüpfungen zu vermeiden. Sie ersparen dir zwar ein paar Tastenanschläge, aber das geht auf Kosten eines möglichen Bruchs deines Codes in der Zukunft. Angenommen, du hast eine date_add-Spalte in deiner Autorentabelle, aber nicht in deiner Verlagstabelle, und du entscheidest dich später, diese Spalte zur Verlagstabelle hinzuzufügen. Natürliche Join-Abfragen, die du vorher geschrieben hast, werden plötzlich sehr unerwartete Ergebnisse liefern, weil sie jetzt auch nach date_add verknüpft sind.

FULL [OUTER] JOIN

Wenn wir unsere vorherige Beispielabfrage als FULL JOIN darstellen, sieht sie wie folgt aus (beachte, dass das Schlüsselwort OUTER optional ist):

SELECT a.au_lname AS "last name",
   a.au_fname AS "first name",
   p.pub_name AS "publisher"
FROM authors AS a
FULL JOIN publishers AS p ON a.city = p.city
ORDER BY a.au_lname DESC;

Die Ergebnismenge, die von der Abfrage zurückgegeben wird, ist eigentlich die Summe der Ergebnismengen der separaten LEFT und RIGHT Join-Abfragen (einige Datensätze wurden aus Gründen der Kürze ausgeschlossen):

last name           first name            publisher
-------------------- -------------------- --------------------
Yokomoto             Akiko                NULL
White                Johnson              NULL
Stringer             Dirk                 NULL
...
Dull                 Ann                  NULL
del Castillo         Innes                NULL
DeFrance             Michel               NULL
Carson               Cheryl               Algodata Infosystems
Blotchet-Halls       Reginald             NULL
Bennet               Abraham              Algodata Infosystems
NULL                 NULL                 Binnet & Hardley
NULL                 NULL                 Five Lakes Publishing
NULL                 NULL                 New Moon Books
...
NULL                 NULL                 Scootney Books
NULL                 NULL                 Ramona Publishers
NULL                 NULL                 GGG&G
Wie du siehst, erhältst du mit FULL JOIN einige Datensätze mit allen Daten (LEFT und RIGHT JOINs), einige mit den NULLen auf der rechten Seite und Daten auf der linken Seite (LEFT JOIN) und einige mit NULLen auf der linken Seite und Daten auf der rechten Seite (RIGHT JOIN).
LATERAL {query | function}

In diesem Beispiel wird die SQL-Standardsyntax verwendet, um die drei teuersten Bücher jedes Autors zurückzugeben, und es werden nur Autoren berücksichtigt, die mindestens einen Titel veröffentlicht haben. Du kannst die gleichen Ergebnisse in MySQL und älteren Versionen von PostgreSQL erzielen, indem du FETCH FIRST 3 ROWS ONLY durch LIMIT 3 ersetzt:

SELECT a.au_lname AS "first name",
   a.au_fname AS "last name",
   topt.title, topt.pubdate
FROM authors AS a
  CROSS JOIN LATERAL
  (SELECT t.title, t.pubdate
  FROM titles AS t
       INNER JOIN titleauthor AS ta ON
            t.title_id = ta.title_id
  WHERE ta.au_id = a.au_id
  ORDER BY t.pubdate DESC
  FETCH FIRST 3 ROWS ONLY
  ) AS topt
ORDER BY a.au_lname ASC, topt.pubdate DESC;

Wenn du alle Autoren auflisten möchtest, auch wenn sie keine veröffentlichten Titel haben, würdest du eine linke Verknüpfung wie folgt verwenden:

SELECT a.au_lname AS "first name",
   a.au_fname AS "last name",
   topt.title, topt.pubdate
FROM authors AS a
  LEFT JOIN LATERAL
  (SELECT t.title, t.pubdate
  FROM titles AS t
       INNER JOIN titleauthor AS ta ON
            t.title_id = ta.title_id
  WHERE ta.au_id = a.au_id
  ORDER BY t.pubdate DESC
  LIMIT 3
  ) AS topt ON (1=1)
ORDER BY a.au_lname ASC, topt.pubdate DESC;

Das Schlüsselwort LATERAL kann nur mit CROSS JOIN oder LEFT JOIN verwendet werden.

Programmiertipps und -schwierigkeiten

Wie im vorangegangenen Abschnitt beschrieben, gibt es viele Arten von Verknüpfungen, die jeweils ihre eigenen Regeln und Verhaltensweisen haben. Wenn eine explizite join_type weggelassen wird, wird eine INNER JOIN angenommen.

Im Allgemeinen solltest du die JOIN Klausel der WHERE Klausel vorziehen, um Join-Ausdrücke zu beschreiben. Dadurch bleibt dein Code nicht nur sauberer, weil du Join-Bedingungen leicht von Suchbedingungen unterscheiden kannst, sondern du vermeidest auch mögliche Fehler, die sich aus plattformspezifischen Implementierungen von Outer Joins ergeben, die mit der WHERE Klausel angegeben werden.

Im Allgemeinen raten wir von der Verwendung arbeitssparender Schlüsselwörter wie NATURAL ab, da sich die Subclause nicht automatisch aktualisiert, wenn sich die Strukturen der zugrunde liegenden Tabellen ändern. Folglich können Anweisungen, die diese Konstrukte verwenden, fehlschlagen, wenn eine Tabellenänderung eingeführt wird, ohne dass auch die Abfrage geändert wird.

Nicht alle Join-Typen werden von allen Plattformen unterstützt, daher findest du in den folgenden Abschnitten alle Details zur plattformspezifischen Join-Unterstützung.

Joins, die mehr als zwei Tabellen umfassen, können schwierig sein. Wenn Joins drei oder mehr Tabellen umfassen, ist es eine gute Idee, die Abfrage als eine Reihe von zwei Tabellen-Joins zu betrachten.

MySQL

MySQL unterstützt die meisten SQL-Standardsyntaxen, mit der Ausnahme, dass natürliche Joins nur bei äußeren Joins unterstützt werden, nicht aber bei inneren Joins. MySQL unterstützt auch nicht die PARTITION BY Klausel. Die JOIN Syntax lautet:

FROM table [AS alias]
{[STRAIGHT_JOIN joined_table] |
{ {[INNER] | [CROSS] |
    [NATURAL] [ {LEFT | RIGHT | FULL} [OUTER] ]}
   JOIN [LATERAL] joined_table [AS alias]
   { ON join_condition1 [{AND|OR} join_condition2] [...] } |
     USING (column1[, ...]) }}
[...]

wo:

STRAIGHT_JOIN

Erzwingt, dass der Optimierer Tabellen genau in der Reihenfolge verknüpft, in der sie in der FROM Klausel stehen. Das Schlüsselwort STRAIGHT_JOIN ist funktional gleichwertig mit JOIN, außer dass es die Join-Reihenfolge von links nach rechts erzwingt. Diese Option wurde angegeben, weil MySQL in seltenen Fällen die Tabellen in der falschen Reihenfolge verknüpfen könnte.

Beispiele findest du unter "Regeln auf einen Blick".

MySQL ist sehr flexibel in der Art, wie es Joins unterstützt. Du kannst verschiedene Syntaxen verwenden, um einen Join durchzuführen. Du kannst zum Beispiel einen Join in einer Abfrage explizit mit der JOIN Klausel deklarieren, aber dann die Join-Bedingung in der WHERE Klausel anzeigen. Die anderen Plattformen zwingen dich, dich für die eine oder andere Methode zu entscheiden, und erlauben es dir nicht, beide in einer einzigen Abfrage zu kombinieren. Wir halten es jedoch für eine schlechte Praxis, Methoden zu mischen, daher verwenden unsere Beispiele die SQL-Standardsyntax JOIN.

Oracle

Oracle unterstützt die SQL-Standardsyntax JOIN vollständig. Es gibt auch eine ältere Syntax für äußere Theta-Joins, bei der "(+)" an die Spaltennamen auf der entgegengesetzten Seite der Join-Richtung angehängt wird (das kommt daher, dass der Tabelle, die die NULL-Wert-Zeilen liefert, tatsächlich NULL-Wert-Zeilen hinzugefügt werden). Diese Syntax sollte jedoch vermieden werden, da sie einige Funktionen der SQL-Standard-Joins nicht unterstützt, wie z. B. FULL JOIN. Oracle unterstützt auch die Klauseln CROSS APPLY und OUTER APPLY (die es auch in SQL Server gibt), die der LATERAL Klausel des SQL-Standards entsprechen.

Die folgende Abfrage führt zum Beispiel eine RIGHT OUTER JOIN für die Tabellen Autoren und Verlage durch:

SELECT a.au_lname AS 'first name',
   a.au_fname AS 'last name',
   p.pub_name AS 'publisher'
FROM authors AS a
RIGHT OUTER JOIN publishers AS p ON a.city = p.city
ORDER BY a.au_lname DESC

Die alte Oracle-Syntax sieht wie folgt aus:

SELECT a.au_lname AS 'first name',
   a.au_fname AS 'last name',
   p.pub_name AS 'publisher'
FROM authors a, publishers p
WHERE a.city(+) = p.city
ORDER BY a.au_lname DESC

Weitere Beispiele findest du unter "Regeln auf einen Blick" JOIN.

Oracle bietet als einziges Unternehmen partitionierte Outer-Joins an, die nützlich sind, um Lücken in Ergebnismengen zu füllen, die durch die spärliche Speicherung von Daten entstehen. Nehmen wir zum Beispiel an, wir speichern Produktionsdatensätze in einer Fertigungstabelle, die nach Tag und Produkt-ID verschlüsselt ist. Die Tabelle enthält eine Zeile mit der Menge jedes Produkts, das an einem bestimmten Tag hergestellt wurde, aber es gibt keine Zeilen für die Tage, an denen es nicht hergestellt wurde. Diese Daten werden als spärlich angesehen, da eine Liste aller Zeilen nicht jeden Tag für jedes Produkt aufzeigt. Für Berechnungen und Berichte ist es sehr nützlich, Ergebnismengen zu erstellen, in denen jedes Produkt eine Zeile für jeden Tag hat, unabhängig davon, ob es an diesem Tag hergestellt wurde oder nicht. Mit einer partitionierten äußeren Verknüpfung ist das ganz einfach, denn du kannst eine logische Partition definieren und eine äußere Verknüpfung auf jeden Partitionswert anwenden. Im folgenden Beispiel wird ein partitionierter Outer-Join mit einer Zeittabelle durchgeführt, um sicherzustellen, dass jede product_id alle Daten in einem bestimmten Zeitbereich enthält:

SELECT times.time_id AS time, product_id AS id, quantity AS qty
FROM manufacturing
PARTITION BY (product_id)
RIGHT OUTER JOIN times
ON (manufacturing.time_id = times.time_id)
WHERE manufacturing.time_id
BETWEEN TO_DATE('01/10/05', 'DD/MM/YY')
    AND TO_DATE('06/10/05', 'DD/MM/YY')
ORDER BY 2, 1;

Hier ist die Ausgabe dieser Abfrage:

time          id        qty
--------- ------        ---
01-OCT-05    101         10
02-OCT-05    101
03-OCT-05    101
04-OCT-05    101         17
05-OCT-05    101         23
06-OCT-05    101
01-OCT-05    102
02-OCT-05    102
03-OCT-05    102         43
04-OCT-05    102         99
05-OCT-05    102
06-OCT-05    102         87

Um diese Ergebnisse ohne eine partitionierte äußere Verknüpfung zu erhalten, wäre ein viel komplexeres und weniger effizientes SQL erforderlich.

PostgreSQL

PostgreSQL unterstützt die SQL-Standardsyntax vollständig, mit Ausnahme der PARTITION BY Klausel. Beispiele findest du unter "Regeln auf einen Blick". Wenn du Funktionen in einem LATERAL Konstrukt verwendest, ist das LATERAL Schlüsselwort optional.

Wenn du zum Beispiel für jeden Titel eine Reihe von Daten vom Erscheinungsdatum bis zum aktuellen Datum erstellen möchtest, könntest du deine LATERAL Abfrage wie folgt schreiben:

SELECT title_id, i AS cal_date
FROM titles CROSS JOIN 
   generate_series(titles.pubdate, CURRENT_DATE, interval '1 day') 
   AS i
ORDER BY title_id, cal_date

Oder mit dem Schlüsselwort LATERAL:

SELECT title_id, i AS cal_date
FROM titles CROSS JOIN
   LATERAL generate_series(titles.pubdate, CURRENT_DATE, 
      interval '1 day') 
   AS i
ORDER BY title_id, cal_date

SQL Server

SQL Server unterstützt INNER, OUTER und CROSS Joins unter Verwendung der ON Klausel. Er unterstützt weder die NATURAL Join-Syntax, noch PARTITION BY oder die USING Klausel. SQL Server unterstützt auch nicht die LATERAL Klausel, wohl aber CROSS APPLY, was CROSS JOIN LATERAL entspricht, und OUTER APPLY, was LEFT JOIN LATERAL entspricht. Die JOIN Syntax von SQL Server ist:

FROM table [AS alias]
{ {[INNER] | [CROSS] | [ {LEFT | RIGHT | FULL} [OUTER] ]}
   [JOIN | APPLY]  joined_table [AS alias]
      { ON join_condition1 [{AND|OR}
       join_condition2] [...] } }
[...]

Beispiele findest du unter "Regeln auf einen Blick".

Die entsprechende SQL Server-Abfrage für das Beispiel LATERAL lautet:

SELECT a.au_lname AS "first name",
   a.au_fname AS "last name",
   topt.title, topt.pubdate
FROM authors AS a
  CROSS APPLY
  (SELECT TOP 3 t.title, t.pubdate
  FROM titles AS t
       INNER JOIN titleauthor AS ta ON
            t.title_id = ta.title_id
  WHERE ta.au_id = a.au_id
  ORDER BY t.pubdate DESC
  ) AS topt
ORDER BY a.au_lname ASC, topt.pubdate DESC;

Wenn du sicherstellen möchtest, dass alle Autoren aufgelistet werden, auch wenn sie keine veröffentlichten Titel haben, würdest du die OUTER APPLY Klausel wie folgt verwenden:

SELECT a.au_lname AS "first name",
   a.au_fname AS "last name",
   topt.title, topt.pubdate
FROM authors AS a
  OUTER APPLY
  (SELECT TOP 3 t.title, t.pubdate
  FROM titles AS t
       INNER JOIN titleauthor AS ta ON
            t.title_id = ta.title_id
  WHERE ta.au_id = a.au_id
  ORDER BY t.pubdate DESC
  ) AS topt
ORDER BY a.au_lname ASC, topt.pubdate DESC;

Siehe auch

  • SELECT

  • ORDER BY

  • WHERE

LIKE Operator

Mit dem Operator LIKE können bestimmte Stringmuster in den Anweisungen SELECT, INSERT, UPDATE und DELETE abgeglichen werden, insbesondere in der Klausel WHERE. Ein angegebenes Muster kann spezielle Platzhalterzeichen enthalten. Die unterstützten Platzhalter variieren von Plattform zu Plattform.

Plattform Befehl
MySQL Unterstützt
Oracle Unterstützt
PostgreSQL Unterstützt, mit Variationen
SQL Server Unterstützt, mit Variationen

SQL-Standardsyntax

WHERE expression [NOT] LIKE string_pattern
   [ESCAPE escape_sequence]

Schlüsselwörter

WHERE expression [NOT] LIKE
Gibt einen Booleschen Wert TRUE zurück, wenn der Wert von expression mit dem string​_pat⁠tern. Der Ausdruck kann eine Spalte, eine Konstante, eine Host-Variable, eine skalare Funktion oder eine Verkettung aus diesen sein. Er sollte weder ein benutzerdefinierter Typ noch ein bestimmter LOB Typ sein.
NOT
Kehrt das Prädikat um, so dass die Anweisung einen Booleschen Wert TRUE zurückgibt, wenn der Wert von expression nicht die string_pattern enthält und gibt FALSE zurück, wenn der Wert von expression den enthält string_pattern.
ESCAPE escape_sequence
Ermöglicht es dir, nach dem Vorhandensein von Zeichen zu suchen, die normalerweise als Wildcards interpretiert werden würden.

Die Regeln auf einen Blick

Das Abgleichen von String-Mustern ist mit LIKE ganz einfach, aber es gibt ein paar Regeln, an die du dich erinnern musst:

  • Alle Zeichen, einschließlich der Leerzeichen am Anfang und am Ende, sind wichtig.

  • Unterschiedliche Datentypen können mit LIKE verglichen werden, aber sie speichern String-Muster unterschiedlich. Achte vor allem auf die Unterschiede zwischen den Datentypen CHAR, VARCHAR und DATE.

  • Die Verwendung von LIKE kann Indizes negieren oder das DBMS dazu zwingen, alternative, weniger optimale Indizes zu verwenden als bei einem reinen Vergleichsvorgang.

Der SQL-Standard unterstützt derzeit zwei Wildcard-Operatoren, die von allen in diesem Buch behandelten Plattformen unterstützt werden:

%
Passt auf eine beliebige Zeichenkette
_ (Unterstrich)
Passt auf jedes einzelne Zeichen

Die erste Abfrage im folgenden Beispiel findet jeden Datensatz einer Stadt mit "ville" im Namen. Die zweite Abfrage liefert Autoren mit einem Vornamen , der nicht Sheryl oder Cheryl (oder Aheryl, Bheryl, Dheryl, 2heryl usw.) lautet:

SELECT * FROM authors
WHERE city LIKE '%ville%';
SELECT * FROM authors
WHERE au_fname NOT LIKE '_heryl';

Einige Plattformen unterstützen zusätzliche Platzhaltersymbole. Diese werden in den folgenden plattformspezifischen Abschnitten beschrieben.

Mit der ESCAPE Klausel kannst du nach Platzhalterzeichen in den in deiner Datenbank gespeicherten Zeichenketten suchen. Mit diesem Mechanismus bestimmst du ein Zeichen - in der Regel ein Zeichen, das sonst nicht in der Musterzeichenkette vorkommt - als Fluchtzeichen. Du könntest zum Beispiel die Tilde(~) bestimmen, weil du weißt, dass sie nie in der Musterzeichenfolge vorkommt. Jedes Platzhalterzeichen, dem die Escape-Sequenz vorausgeht, wird dann nicht als Platzhalter, sondern als das Zeichen selbst behandelt. Mit dieser Abfrage können wir zum Beispiel in der Kommentarspalte der Tabelle sales_detail (auf SQL Server) nachsehen, ob Kunden einen neu eingeführten Rabatt erwähnt haben:

SELECT ord_id, comment
FROM sales_detail
WHERE comment LIKE '%~%%' ESCAPE '~'

In diesem Fall werden das erste und das letzte %-Zeichenals Platzhalter interpretiert, aber das zweite %-Zeichen wird als genau das interpretiert (ein %-Zeichen ), da ihm die angegebene Escape-Sequenz vorausgeht.

Programmiertipps und -schwierigkeiten

Die Nützlichkeit von LIKE beruht auf den Platzhalteroperatoren, die es unterstützt. LIKE gibt einen booleschen Wert TRUE zurück, wenn der Vergleich einen oder mehrere übereinstimmende Werte findet.

Die voreingestellte Groß- und Kleinschreibung des DBMS ist sehr wichtig für das Verhalten von LIKE. SQL Server zum Beispiel unterscheidet standardmäßig nicht zwischen Groß- und Kleinschreibung (obwohl es so konfiguriert werden kann), so dass die Zeichenketten 'DAD' und 'dad' als gleichwertig betrachtet werden. MySQL unterscheidet ebenfalls standardmäßig nicht zwischen Groß- und Kleinschreibung, hat aber einen LIKE BINARY Operator, um die Groß- und Kleinschreibung zu erzwingen. Oracle und PostgreSQL hingegen unterscheiden zwischen Groß- und Kleinschreibung. Daher würde ein Vergleich von 'DAD' und 'dad' auf diesen Plattformen ergeben, dass sie ungleich sind. PostgreSQL (aber nicht Oracle) verfügt über einen ILIKE Operator, der die Groß- und Kleinschreibung nicht berücksichtigt. Hier ist eine Beispielabfrage, um diesen Punkt zu verdeutlichen:

SELECT *
FROM authors
WHERE lname LIKE 'LARS%'

Diese Abfrage auf MySQL und SQL Server würde Autoren finden, deren Nachnamen als 'Larson' oder 'Lars' gespeichert sind, auch wenn die Suche nach dem Großbuchstaben 'LARS%' erfolgte. Oracle und PostgreSQL hingegen würden 'Larson' oder 'Lars' nicht finden, da sie standardmäßig Vergleiche unter Berücksichtigung der Groß- und Kleinschreibung durchführen.

MySQL

MySQL unterstützt die SQL-Standardsyntax für LIKE. Zusätzlich unterstützt es die speziellen Funktionen REGEXP, RLIKE, NOT REGEXP und NOT RLIKE für die Auswertung von regulären Ausdrücken.

Oracle

Oracle unterstützt die SQL-Standardsyntax für LIKE. Die Syntax von LIKE lautet wie folgt:

WHERE expression [NOT] {LIKE | LIKEC | LIKE2 |
   LIKE4} string_pattern
[ESCAPE escape_sequence]

Die Oracle-spezifischen Syntaxelemente haben die folgenden Bedeutungen:

LIKEC
Verwendet vollständige Unicode-Zeichen
LIKE2
Verwendet Unicode USC2-Codepunkte
LIKE4
Verwendet Unicode UCS4-Codepunkte

Da Oracle zwischen Groß- und Kleinschreibung unterscheidet, sollten Sie die expression, die string​_pat⁠ternoder beide mit der Funktion UPPER einschließen. Auf diese Weise kannst du immer Äpfel mit Äpfeln vergleichen.

PostgreSQL

PostgreSQL unterstützt die SQL-Standardsyntax für LIKE. Standardmäßig wird zwischen Groß- und Kleinschreibung unterschieden, aber es gibt das Schlüsselwort ILIKE für den Mustervergleich ohne Berücksichtigung der Groß- und Kleinschreibung. Du kannst auch die Operatoren ~~ als Äquivalent zu LIKE, ~~* für ILIKE und !~~ und !~~* für NOT LIKE bzw. NOT ILIKE verwenden. Dies sind alles Erweiterungen der SQL-Standardsyntax.

Die folgenden Abfragen sind zum Beispiel funktional gleich:

SELECT * FROM authors
WHERE city LIKE '%ville';
SELECT * FROM authors
WHERE city ~~ '%ville';

Da diese Abfragen in Kleinbuchstaben gestellt werden, kann es zu einem Problem mit der Groß-/Kleinschreibung kommen. Das heißt, die Abfragen suchen nach einem Kleinbuchstaben '%ville', aber die Tabelle kann Großbuchstaben (und ungleiche Werte) wie 'BROWNSVILLE', 'NASHVILLE' und 'HUNTSVILLE' enthalten. Das kannst du wie folgt umgehen:

-- Convert the values to uppercase
SELECT * FROM authors
WHERE city LIKE UPPER('%ville');

-- Perform the pattern match using case insensitivity
SELECT * FROM authors
WHERE city ~~* '%ville';
SELECT * FROM authors
WHERE city ILIKE '%ville';

Obwohl dies den Rahmen dieses Textes sprengen würde, solltest du wissen, dass PostgreSQL auch reguläre POSIX-Ausdrücke unterstützt. Einzelheiten dazu findest du in der Plattformdokumentation.

SQL Server

SQL Server unterstützt die SQL-Standardsyntax für LIKE und die folgenden zusätzlichen Wildcard-Operatoren:

[ ]
Passt auf jeden Wert in der angegebenen Menge, wie in [abc], oder im Bereich, wie in [k-n]
[^ ]
Passt auf alle Zeichen, die nicht in der angegebenen Menge oder dem angegebenen Bereich sind

Mit den zusätzlichen Platzhalteroperatoren von SQL Server hast du einige zusätzliche Möglichkeiten. So kannst du zum Beispiel jeden Autor mit einem Nachnamen wie Carson, Carsen, Karson oder Karsen abfragen:

SELECT * FROM authors
WHERE au_lname LIKE '[CK]ars[eo]n'

oder du kannst jeden Autor abrufen, dessen Nachname auf "arson" oder "arsen" endet , aber nicht Larsen oder Larson ist:

SELECT * FROM authors
WHERE au_lname LIKE '[A-Z^L]ars[eo]n'

Erinnere dich daran, dass bei String-Vergleichen mit LIKE alle Zeichen in der Musterzeichenkette von Bedeutung sind, einschließlich aller führenden und nachfolgenden Leerzeichen.

Siehe auch

ORDER BY-Klausel

Die ORDER BY Klausel legt die Sortierreihenfolge der Ergebnismenge fest, die von einer SELECT Anweisung abgerufen wird.

Plattform Befehl
MySQL Unterstützt, mit Einschränkungen
Oracle Unterstützt, mit Variationen
PostgreSQL Unterstützt, mit Variationen
SQL Server Unterstützt, mit Einschränkungen

SQL-Standardsyntax

ORDER BY { sort_expression [COLLATE collation_name]
   [ASC | DESC] [NULLS {FIRST | LAST}] }[, ...]
[ OFFSET int {ROW | ROWS} ]
[ FETCH {FIRST | NEXT} numeric {ROW | ROWS | PERCENT}
   {ONLY | WITH TIES} ]

Schlüsselwörter

ORDER BY
Legt die Reihenfolge fest, in der die Zeilen von einer Abfrage zurückgegeben werden sollen. Du solltest keine bestimmte Reihenfolge vorwegnehmen, wenn du die ORDER BY Klausel ausschließt, auch wenn du eine GROUP BY Klausel angibst und es so aussieht, als ob eine Sortierung durchgeführt worden wäre.
sort_expression
Gibt ein Element in der Abfrage an, mit dem die Reihenfolge der Ergebnismenge bestimmt wird. Du kannst mehrere Sortierausdrücke haben. In der Regel handelt es sich dabei um Spaltennamen oder Spaltenaliase aus der Abfrage; es können aber auch Ausdrücke wie (salary * 1.02) sein.
COLLATE collation_name
Überschreibt die Standardsortierung der sort_expression und wendet die collation_name auf den Ausdruck für die Zwecke der ORDER BY Klausel an.
ASC | DESC
Gibt an, dass die Ergebnismenge entweder in aufsteigender Reihenfolge (ASC) oder in absteigender Reihenfolge (DESC) zurückgegeben werden soll.
NULLS {FIRST | LAST}
NULLS FIRST und NULLS LAST geben an, dass die Datensätze, die NULLs enthalten, entweder an erster oder letzter Stelle erscheinen sollen. Standardmäßig werden NULLs bei Oracle und PostgreSQL bei aufsteigender Sortierung als letztes und bei absteigender Sortierung als erstes angezeigt.
OFFSET int {ROW | ROWS}
Gibt die Anzahl der Zeilen an, die ab dem Beginn des ORDER BY Sets übersprungen werden sollen.
FETCH {FIRST | NEXT} numeric {ROW | ROWS | PERCENT}
Gibt die erste oder nächste (ab OFFSET) numeric Zeilen oder PERCENT von Datensätzen zurück. Wenn ROW oder ROWS verwendet wird, muss der Wert eine ganze Zahl sein. Bei der Verwendung von FIRST sollte keine OFFSET Klausel verwendet werden. PERCENT ist eine beliebige Zahl zwischen 0 und 100 und kann auch Bruchzahlen enthalten.
ONLY | WITH TIES
ONLY gibt höchstens die angegebene Anzahl oder den angegebenen Prozentsatz an Zeilen zurück. WITH TIES gibt zusätzliche Zeilen zurück, wenn aufgrund der ORDER BY Klausel Datensätze innerhalb dieser Anzahl gebunden sind. Mit anderen Worten: Anstatt einen oder mehrere der gebundenen Datensätze nach dem Zufallsprinzip zurückzugeben, werden bis zur numeric werden alle gebundenen Datensätze innerhalb der Anzahl zurückgegeben, auch wenn die Gesamtzahl die numeric angegeben.

Die Regeln auf einen Blick

Die ORDER BY Klausel sollte die Spalten so referenzieren, wie sie in der Artikelliste der SELECT Anweisung erscheinen, vorzugsweise unter Verwendung ihrer Aliasnamen (falls Aliasnamen existieren). Zum Beispiel:

SELECT au_fname AS first_name, au_lname AS last_name
FROM authors
ORDER BY first_name, last_name

Die ORDER BY Klausel verwendet eine Major-to-Minor-Sortierung. Das bedeutet, dass die Ergebnismenge nach der ersten Spalte sortiert wird, auf die verwiesen wird; gleiche Werte in der ersten Spalte werden dann nach der zweiten Spalte sortiert, gleiche Werte in der zweiten Spalte nach der dritten Spalte und so weiter.

Die einzelnen Aspekte der Reihenfolge einer Spalte -COLLATE und ASC/DESC- sind unabhängig von den anderen Spalten in der ORDER BY Klausel. Du könntest also eine Ergebnismenge nach einer Spalte in aufsteigender Reihenfolge ordnen und dann die nächste Spalte umdrehen und in absteigender Reihenfolge ordnen:

SELECT au_fname AS first_name, au_lname AS last_name
FROM authors
ORDER BY au_lname ASC, au_fname DESC

NULLs werden für die Sortierung immer in Gruppen zusammengefasst (d.h. als gleichwertig betrachtet). Je nach Plattform werden die NULLs am Anfang oder am Ende der Ergebnismenge gruppiert. Die folgende Abfrage auf SQL Server:

SELECT title, price
FROM titles
ORDER BY price, title

liefert diese Ergebnismenge:

title                                                price
---------------------------------------------------- -------
Net Etiquette                                        NULL
The Psychology of Computer Cooking                   NULL
The Gourmet Microwave                                2.9900
You Can Combat Computer Stress!                      2.9900
Life Without Fear                                    7.0000
Onions, Leeks, and Garlic: Cooking Secrets of the Me 20.9500
Computer Phobic AND Non-Phobic Individuals: Behavior 21.5900
But Is It User Friendly?                             22.9500
...

Du kannst mit ASC oder DESC erzwingen, dass NULLs am Anfang oder am Ende der Ergebnismenge erscheinen. Natürlich werden auch alle Nicht-NULL-Zeilen der Ergebnismenge in auf- oder absteigender Reihenfolge sortiert.

Einige Plattformen unterstützen die Angabe der NULL-Sortierung. In Oracle und PostgreSQL werden NULLs standardmäßig an das Ende sortiert, aber du kannst das Verhalten wie folgt ändern, um das vorherige Ergebnis zu erhalten, indem du dies tust:

SELECT title, price
FROM titles
ORDER BY price NULLS FIRST, title NULLS LAST

Der SQL-Standard unterstützt auch für die sort_expression die Verwendung von Spalten, die nicht in der Artikelliste SELECT referenziert sind. Die folgende Abfrage ist zum Beispiel gültig:

SELECT title, price
FROM titles
ORDER BY title_id

Wenn du dir dieses Beispiel ansiehst, siehst du, dass die Abfrage zwar nicht title_id auswählt, diese Spalte aber die Primärspalte ist sort_expression. Die Ergebnismenge wird in der Reihenfolge title_id zurückgegeben, auch wenn diese Spalte nicht ausgewählt ist.

Du kannst die Anzahl der zurückgegebenen Datensätze mit den [OFFSET] FETCH {FIRST | NEXT} Unterklauseln begrenzen. Die folgende Abfrage überspringt zum Beispiel die ersten 10 Titel und gibt die nächsten 10 zurück:

SELECT title, price
FROM titles
ORDER BY title
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY

Programmiertipps und -schwierigkeiten

Bei der Verwendung von Mengenoperatoren (EXCEPT, INTERSECT, UNION) darf nur die letzte Abfrage eine ORDER BY Klausel enthalten.

Eine Reihe von Verhaltensweisen, die in SQL92 unterstützt wurden, sind in neueren SQL-Standards verpönt. Du solltest diese Verwendungen vermeiden, obwohl alle hier behandelten Datenbanken sie noch unterstützen:

Referenzen zu Tabellenaliasen
Zum Beispiel sollte ORDER BY e.emp_id in ORDER BY emp_id geändert werden. Wenn es einen mehrdeutigen Spaltennamen gibt, verwende einen Alias, um das auszugleichen.
Verweise auf die ordinale Position
Verwende explizit definierte Spaltenaliase, um dies auszugleichen.

Du kannst nicht nur nach Spalten sortieren, sondern auch nach Ausdrücken, die Spalten enthalten, oder sogar nach Literalen:

SELECT SUBSTRING(title,1,55) AS title, (price * 1.15) as price
FROM titles
WHERE price BETWEEN 2 and 19
ORDER BY price, title

Wenn du nach Ausdrücken aus der Artikelliste SELECT sortierst, solltest du Aliasnamen verwenden, damit die ORDER BY sort_expression Spaltenverweise leichter zu lesen.

MySQL

MySQL unterstützt den SQL-Standard mit Ausnahme der Optionen COLLATE, FETCH PERCENT und NULLS {FIRST | LAST}. Zusätzlich zum SQL-Standard OFFSET FETCH unterstützt es auch eine LIMIT OFFSET Subklausel, die vom Zweck her gleichwertig ist und der Subklausel des SQL-Standards vorausgeht. Die Syntax von MySQL ORDER BY lautet:

ORDER BY {sort_expression [ASC | DESC]}[, ...]
[ OFFSET int {ROW | ROWS} ]
[ FETCH {FIRST | NEXT} numeric {ROW | ROWS} {ONLY | WITH TIES} ]
[ LIMIT int OFFSET int ]

wo:

LIMIT int
Gibt die maximale Anzahl der zurückzugebenden Datensätze an
OFFSET int
Gibt an, wie viele Datensätze übersprungen werden sollen

Du kannst das LIMIT OFFSET Konstrukt nicht in Verbindung mit dem OFFSET FETCH Konstrukt verwenden.

Du solltest nicht versuchen, ORDER BY Spalten des Datentyps BLOB zu sortieren, da nur die ersten Bytes, die durch die Einstellung MAX_SORT_LENGTH definiert sind, für die Sortierung verwendet werden. Standardmäßig sortiert MySQL die NULL-Werte am niedrigsten (zuerst) für ASC und am höchsten (zuletzt) für DESC.

Oracle

Oracle unterstützt den SQL-Standard, mit Ausnahme der Option COLLATE. Es unterstützt auch die Option SIBLINGS. Die ORDER BY Syntax von Oracle lautet:

ORDER [SIBLINGS] BY {sort_expression
   [ASC | DESC] [NULLS {FIRST | LAST}]}[, ...]
[ OFFSET int {ROW | ROWS} ]
[ FETCH {FIRST | NEXT} numeric {ROW | ROWS | PERCENT} 
   {ONLY | WITH TIES} ]

wo:

ORDER [SIBLINGS] BY sort_expression
Sortiert die Ergebnismenge der Abfrage in der Reihenfolge der sort_expression(s). A sort_expression kann ein Spaltenname, ein Alias, eine ganze Zahl, die die Ordnungsposition einer Spalte angibt, oder ein anderer Ausdruck sein (z. B. Gehalt * 1,02). Die ORDER SIBLINGS BY Klausel weist Oracle an, die durch eine hierarchische Abfrageklausel (CONNECT BY) festgelegte Reihenfolge beizubehalten und die Reihenfolge des Sortierausdrucks für die Reihenfolge der Geschwister in der Hierarchie zu verwenden.

Du kannst das Verhalten der Option COLLATE für eine einzelne Sitzung emulieren, indem du die Funktion NLSSORT mit dem Parameter NLS_SORT verwendest. Du kannst auch das Verhalten der Option COLLATE für alle Sitzungen auf dem Server emulieren, entweder explizit mit dem Initialisierungsparameter NLS_SORT oder implizit mit dem Initialisierungsparameter NLS_LANGUAGE.

Du solltest eine ORDER BY nicht auf einer LOB Spalte, einer verschachtelten Tabelle oder VARRAY ausführen.

PostgreSQL

PostgreSQL unterstützt den SQL-Standard, mit Ausnahme der Optionen COLLATE und FETCH PERCENT. Außerdem unterstützt es eine LIMIT OFFSET Subclause, ähnlich der von MySQL, und eine USING Subclause. Die Unterstützung für OFFSET FETCH wurde in PostgreSQL 13 eingeführt. Die ORDER BY Syntax von PostgreSQL lautet:

ORDER BY {sort_expression [ASC | DESC] [USING operator]
   [NULLS {FIRST | LAST}]}[, ...]
[ OFFSET int {ROW | ROWS} ]
[ FETCH {FIRST | NEXT} int {ROW | ROWS} {ONLY | WITH TIES} ]
[ LIMIT int OFFSET int ]

wo:

USING operator
Gibt einen bestimmten Vergleichsoperator an. So kannst du z.B. nach >, <, =, >=, <= und so weiter sortieren. Aufsteigende Reihenfolge ist dasselbe wie die Angabe von USING <, während absteigende Reihenfolge dasselbe ist wie USING >.
LIMIT int
Gibt die maximale Anzahl von Datensätzen an, die zurückgegeben werden sollen.
OFFSET int
Gibt an, wie viele Datensätze übersprungen werden sollen.

ASC und DESC sind SQL-Standards. Wenn nicht angegeben, ist ASC der Standard. PostgreSQL sortiert NULL-Werte standardmäßig höher als alle anderen Werte, wodurch NULL-Werte am Ende von ASC und am Anfang von DESC erscheinen. Du kannst die NULLS {FIRST | LAST} Klausel verwenden, um dieses Verhalten zu ändern. Ein Beispiel:

SELECT stor_id, ord_date, qty AS quantity
FROM sales
ORDER BY stor_id, ord_date DESC, qty ASC NULLS FIRST;

Du kannst nicht sowohl OFFSET FETCH als auch LIMIT OFFSET verwenden, da sie die gleichen Ziele erreichen. Obwohl LIMIT OFFSET schneller zu tippen ist, entspricht das OFFSET FETCH Konstrukt dem SQL-Standard und ermöglicht es, festzulegen, wie Gleichheiten in der Ergebnismenge behandelt werden. Ein Beispiel:

SELECT stor_id, ord_date, qty
FROM sales
ORDER BY qty
FETCH FIRST 3 ROWS WITH TIES;
stor_id |        ord_date       | qty
--------+------------------------+-----
 6380    | 1994-09-13 00:00:00-04 |   3
 6380    | 1994-09-14 00:00:00-04 |   5
 7896    | 1993-12-12 00:00:00-05 |  10
 7067    | 1994-09-14 00:00:00-04 |  10
 8042    | 1994-09-14 00:00:00-04 |  10
(5 rows)

Beachte, dass die Ausgabe fünf statt drei Zeilen hat, weil die letzten drei um den dritten Platz kämpfen.

In SELECT Anweisungen, die einzelne Tabellen betreffen oder JOINs, aber keine Mengenoperationen verwenden (z.B.UNION), kannst du auch nach Spalten der Tabelle ordnen, die nicht in der select_item Liste erscheinen. Zum Beispiel:

SELECT stor_name
FROM sales
ORDER BY stor_id, qty;

SQL Server

SQL Server unterstützt den SQL-Standard, mit Ausnahme der Optionen FETCH PERCENT und NULLS {FIRST | LAST}. PERCENT kann mit TOP verwendet werden, um FETCH PERCENT zu simulieren. Die folgende Abfrage ruft zum Beispiel die Vornamen der Autoren aus der Tabelle authors in der Sortierung SQL_Latin1 ab:

SELECT au_fname
FROM authors
ORDER BY au_fname
COLLATE SQL_Latin1_general_cp1_ci_as

SQL Server sortiert NULL-Werte standardmäßig höher als alle anderen Werte.

SQL Server erlaubt eine Vielzahl von Sortierreihenfolgen, die die Auswertung der Ergebnismenge beeinflussen können. So können "SMITH" und "smith" unter bestimmten Kollationen unterschiedlich ausgewertet und sortiert werden. Du solltest die Spalten TEXT, IMAGE und NTEXT nicht als sort_expressions auf SQL Server verwenden.

Siehe auch

  • SELECT

OVER-Klausel

Die OVER Klausel erscheint in der SELECT Klausel als Funktionsqualifizierer für auf Fensterfunktionen basierende Spalten und wenn Aggregate in einem Fensterkonstrukt verwendet werden. Die WINDOW Klausel begleitet oft eine oder mehrere OVER Klauseln und erscheint nach der gesamten SELECT Anweisung. Sie wird verwendet, um eine Fensterspezifikation zu benennen, die dann in der/den OVER Klausel(n) namentlich verwendet wird.

Plattform Befehl
MySQL Unterstützt, mit Einschränkungen
Oracle Unterstützt, mit Variationen
PostgreSQL Unterstützt, mit Variationen
SQL Server Unterstützt, mit Einschränkungen

SQL-Standardsyntax

FUNCTION_NAME(expr) OVER {window_name | (window_specification)}
window_specification ::= [window_name] [partitioning] 
      [ordering] [framing]
   partitioning ::= PARTITION BY value[, value...] 
         [COLLATE collation_name]
   ordering ::= ORDER [SIBLINGS] BY rule[, rule...]
      rule ::= {value | position | alias} [ASC | DESC] 
         [NULLS {FIRST | LAST}]
   framing ::= {ROWS | RANGE | GROUPS} {start | between} 
         [exclusion]
      start ::= {UNBOUNDED PRECEDING | unsigned-integer 
            PRECEDING | CURRENT ROW}
      between ::= BETWEEN bound AND bound
         bound ::= {start | UNBOUNDED FOLLOWING | unsigned-integer 
            FOLLOWING}
      exclusion ::= {EXCLUDE CURRENT ROW | EXCLUDE GROUP | 
         EXCLUDE TIES | EXCLUDE NO OTHERS}

Zusätzlich zur OVER Klausel kannst du eine WINDOW Klausel vor der ORDER BY Klausel und nach der WHERE Klausel in einer SELECT Anweisung haben. Ihr folgen eine oder mehrere Definitionen von benannten Fenstern. Die Syntax lautet wie folgt:

WINDOW {window_name AS (window_specification)[, ...]}

Schlüsselwörter

OVER {window_name | (window_specification)}
Die OVER Klausel kann auf ein vordefiniertes Fenster verweisen (definiert in einer WINDOW Klausel), das über mehrere Spalten hinweg verwendet werden kann, oder eine Fensterspezifikation bestehend aus ORDER BY und PARTITION BY Klauseln liefern. Ein Datenfenster besteht aus einer Teilmenge von Zeilen relativ zur aktuellen Zeile.
partitioning
Definiert die Gruppe von Reihen, zu der eine Reihe gehört. Die values sind Spaltennamen oder Ausdrücke, die Spaltennamen enthalten. Alle Datensätze, die diese Ausdrücke gemeinsam haben, gehören zur selben Partition. Wenn es keine Partitionierungsklausel gibt, gehören alle Zeilen zur selben Partition.
ordering
Legt die Reihenfolge fest, in der die Zeilen im Fenster sortiert werden sollen.
framing

Gibt an, welche Zeilen als im Fenster befindlich betrachtet werden (eine Teilmenge von Zeilen relativ zur aktuellen Zeile). Die Syntax der framing Unterklausel lautet wie folgt:

{ROWS | RANGE} {start | between} [exclusion]
start ::= {UNBOUNDED PRECEDING | unsigned-integer PRECEDING | 
   CURRENT ROW}
between ::= BETWEEN bound AND bound
bound ::= {start | UNBOUNDED FOLLOWING | unsigned-integer 
   FOLLOWING}
exclusion ::= {EXCLUDE CURRENT ROW | EXCLUDE GROUP |                
   EXCLUDE TIES | EXCLUDE NO OTHERS}
ASC | DESC
Gibt an, dass die Ergebnismenge entweder in aufsteigender Reihenfolge (ASC) oder in absteigender Reihenfolge (DESC) zurückgegeben werden soll, basierend auf dem value | position.
NULLS {FIRST | LAST}
Legt fest, dass die Datensätze, die NULLs enthalten, entweder an erster oder letzter Stelle in der Ergebnismenge erscheinen sollen.
WINDOW {window_name AS (window_specification)[, ... ]}
Definiert ein oder mehrere benannte Fenster und gibt für jedes eine Spezifikation an.

Die Regeln auf einen Blick

Jede SELECT Anweisung kann null oder mehr OVER Klauseln haben. Hier sind ein paar Beispiele. Die folgende Abfrage ordnet jeden Titel über alle Titel(dr) und dann innerhalb der Gruppe, die den gleichen Verlag hat:

SELECT   t.title_id,
   DENSE_RANK() OVER(ORDER BY price) AS dr,
   DENSE_RANK() OVER(PARTITION BY pub_id ORDER BY price) AS dr_pub
FROM     titles AS t
ORDER BY title_id;

Die nächste Abfrage verwendet die Aggregatfunktion SUM, um eine laufende Summe und eine Gesamtsumme für den Preis zu erstellen. Beachte, dass die ORDER BY Klausel, die in einer OVER Klausel verwendet wird, nicht mit der SELECT ... ORDER BY Klausel übereinstimmen muss, aber es erleichtert die Fehlersuche:

SELECT   t.title_id,
   SUM(price) OVER() AS overall_total,
   SUM(price) OVER(ORDER BY price) AS running_total
FROM     titles AS t
ORDER BY title_id;

Hier ist ein Beispiel für eine Abfrage, die benannte Fenster verwendet:

SELECT   t.title_id,
   DENSE_RANK() OVER wprice AS dr,
   SUM(price) OVER(wpub) AS wpub_total
FROM     titles AS t
WINDOW wprice AS (ORDER BY price), 
   wpub AS (PARTITION BY pub_id)
ORDER BY title_id;

MySQL

MySQL unterstützt eine Teilmenge der SQL-Standardsyntax für die OVER und window​_spec⁠ification Klauseln, wie hier gezeigt:

FUNCTION_NAME(expr) OVER {window_name | (window_specification)}
window_specification ::= [window_name] [partitioning] 
      [ordering] [framing]
   partitioning ::= PARTITION BY value[, value...] 
   ordering ::= ORDER BY rule[, rule...]
      rule ::= {value | position | alias} [ASC | DESC] ]
   framing ::= {ROWS | RANGE} {start | between} [exclusion]
      start ::= {UNBOUNDED PRECEDING | unsigned-integer 
            PRECEDING | CURRENT ROW}
      between ::= BETWEEN bound AND bound
         bound ::= {start | UNBOUNDED FOLLOWING | unsigned-integer
                             FOLLOWING}

Sie unterstützt auch die WINDOW Klausel und hat die gleichen Optionen für die window​_specifi⁠cation Klausel.

Oracle

Oracle unterstützt vollständig den SQL-Standard OVER und window_specification Klauseln, mit Ausnahme der COLLATE Klausel. Ihre Syntax lautet wie folgt:

FUNCTION_NAME(expr) OVER {window_name | (window_specification)}
window_specification ::= [window_name] [partitioning]
      [ordering] [framing]
   partitioning ::= PARTITION BY value[, value...] 
   ordering ::= ORDER [SIBLINGS] BY rule[, rule...]
      rule ::= {value | position | alias} [ASC | DESC] 
            [NULLS {FIRST | LAST}]
   framing ::= {ROWS | RANGE | GROUPS} {start | between} 
         [exclusion]
      start ::= {UNBOUNDED PRECEDING | unsigned-integer 
            PRECEDING | CURRENT ROW}
      between ::= BETWEEN bound AND bound
         bound ::= {start | UNBOUNDED FOLLOWING | 
               unsigned-integer FOLLOWING}
      exclusion ::= {EXCLUDE CURRENT ROW | EXCLUDE GROUP | 
            EXCLUDE TIES | EXCLUDE NO OTHERS}

Die WINDOW Klausel wurde in Oracle 21c eingeführt. Sie erlaubt alles, was auch in der window_specification der OVER Klausel erlaubt ist.

PostgreSQL

PostgreSQL unterstützt die gesamte SQL-Standardsyntax für die OVER und window​_specifi⁠cation Klauseln, mit Ausnahme des Schlüsselworts SIBLINGS und der Klausel COLLATE. Es können alle Aggregatfunktionen, einschließlich der benutzerdefinierten, als Fensteraggregate verwendet werden. Aggregate können in fast jeder von PostgreSQL unterstützten Sprache erstellt werden, aber die eingebauten Sprachen PL/pgSQL und SQL unterstützen die Erstellung von Fensterfunktionen nicht (Fensterfunktionen können in C, PL/V8 und PL/R erstellt werden).

Die Syntax von PostgreSQL OVER lautet wie folgt:

FUNCTION_NAME(expr) OVER {window_name | (window_specification)}
window_specification ::= [window_name] [partitioning] 
      [ordering] [framing]
   partitioning ::= PARTITION BY value[, value...] 
   ordering ::= ORDER BY rule[, rule...]
      rule ::= {value | position | alias} [ASC | DESC] 
            [NULLS {FIRST | LAST}]
   framing ::= {ROWS | RANGE | GROUPS} {start | between} 
         [exclusion]
      start ::= {UNBOUNDED PRECEDING | unsigned-integer 
            PRECEDING | CURRENT ROW}
      between ::= BETWEEN bound AND bound
         bound ::= {start | UNBOUNDED FOLLOWING | 
               unsigned-integer FOLLOWING}
      exclusion ::= {EXCLUDE CURRENT ROW | EXCLUDE GROUP | 
            EXCLUDE TIES | EXCLUDE NO OTHERS}

Es unterstützt auch die WINDOW Klausel, mit den gleichen Optionen für die window​_specifica⁠tion Klausel.

SQL Server

SQL Server unterstützt eine Teilmenge der SQL-Standardsyntax für die OVER -Klausel. Er unterstützt keine benannten Fenster oder die WINDOW Klausel. Die OVER -Syntax von SQL Server lautet wie folgt:

FUNCTION_NAME(expr) OVER {(window_specification)}
window_specification ::= [partitioning] [ordering] [framing]
   partitioning ::= PARTITION BY value[, value...] 
         [COLLATE collation_name]
   ordering ::= ORDER BY rule[, rule...]
      rule ::= {value | position | alias} [ASC | DESC]
   framing ::= {ROWS | RANGE} {start | between}
      start ::= {UNBOUNDED PRECEDING | unsigned-integer 
            PRECEDING | CURRENT ROW}
      between ::= BETWEEN bound AND bound
         bound ::= {start | UNBOUNDED FOLLOWING | 
               unsigned-integer FOLLOWING}

Siehe auch

SELECT-Anweisung

Die Anweisung SELECT ruft Zeilen, Spalten und abgeleitete Werte aus einer oder mehreren Tabellen einer Datenbank ab.

Plattform Befehl
MySQL Unterstützt, mit Variationen
Oracle Unterstützt, mit Variationen
PostgreSQL Unterstützt, mit Variationen
SQL Server Unterstützt, mit Variationen

SQL-Standardsyntax

Die vollständige Syntax der SELECT Anweisung ist mächtig und komplex, aber sie lässt sich in die folgenden Hauptklauseln unterteilen:

SELECT [{ALL | DISTINCT}] select_item [AS alias][, ...]
FROM [ONLY | OUTER]
   {table_name [[AS] alias] | view_name [[AS] alias]}[, ...]
[ [join_type] JOIN join_condition ]
[WHERE search_condition] [ {AND | OR | NOT} search_condition[...] ]

group_by_clause

order_by_clause

Stichwörter

[{ALL | DISTINCT}] select_item

Ruft die Werte ab, aus denen die Ergebnismenge der Abfrage besteht. Jeder select_item kann ein Literal, eine Aggregat- oder Skalarfunktion, eine mathematische Berechnung, ein Parameter oder eine Variable oder eine Subquery sein, aber ein select_item ist jedoch meistens eine Spalte aus einer Tabelle oder einem View. Jedes Element in einer Liste solcher Elemente muss durch ein Komma getrennt werden.

Der Name des Schemas oder des Eigentümers sollte dem Namen einer Spalte vorangestellt werden, wenn sie aus einem anderen Kontext als dem des aktuellen Benutzers extrahiert wird. Wenn ein anderer Benutzer Eigentümer der Tabelle ist, muss dieser Benutzer in der Spaltenreferenz enthalten sein. Wenn zum Beispiel der Benutzer jake auf die Daten im Schema katie zugreifen muss, würde er verwenden:

SELECT emp_id
FROM katie.employee;

Du kannst die Abkürzung Sternchen(* ) verwenden, um alle Spalten in jeder Tabelle oder View abzurufen, die in der FROM Klausel aufgeführt sind. Es ist ratsam, diese Abkürzung nur bei Abfragen für einzelne Tabellen zu verwenden.

ALL DISTINCT weist die Datenbank an, alle doppelten Datensätze herauszufiltern, so dass nur ein Exemplar von vielen identischen Datensätzen gefunden wird.

AS alias
Ersetzt eine Spaltenüberschrift (wenn in der select_item Klausel) oder einen Tabellen- oder View-Namen (in der FROM Klausel) durch eine kürzere Überschrift oder einen kürzeren Namen. Diese Klausel ist besonders nützlich, wenn es darum geht, kryptische oder lange Namen durch kurze, leicht verständliche Namen oder Mnemonics zu ersetzen und wenn die Spalte nur abgeleitete Daten enthält, damit du nicht am Ende eine Spalte mit dem Namen ORA000189x7/0.02 hast. Sie ist auch sehr nützlich bei Self-Joins und korrelierten Unterabfragen, bei denen eine einzige Abfrage mehr als einmal auf dieselbe Tabelle verweist. Wenn mehrere Elemente in der select_item Klausel oder der FROM Klausel vorkommen, musst du die Kommas nach den AS alias Klauseln. Achte außerdem darauf, dass du einen Alias immer einheitlich verwendest, sobald du ihn in die Abfrage einfügst.
FROM [ONLY | OUTER] {table_name | view_name}[, ... ]
Listet alle Tabellen und/oder Views auf, aus denen die Abfrage Daten abruft. Trenne die Tabellen- und View-Namen durch Kommas. Mit der FROM Klausel kannst du auch langen Tabellen-/View-Namen oder Unterabfragen Aliasnamen zuweisen, indem du die AS Klausel verwendest. Die Verwendung kürzerer Aliasnamen anstelle längerer Tabellen- oder View-Namen vereinfacht die Codierung. (Natürlich kann dies die sorgfältig geplanten Namenskonventionen des DBAs durchkreuzen, aber der Alias gilt nur für die Dauer der Abfrage. Weitere Informationen zu Aliasen findest du unter " Regeln auf einen Blick".) Eine FROM Klausel kann eine Subquery enthalten (siehe "SUBQUERY Substatement" für weitere Informationen).
ONLY
Gibt an, dass nur die Zeilen der benannten Tabelle oder View (und keine Zeilen in Untertabellen oder Subviews) in der Ergebnismenge abgerufen werden. Wenn du ONLY verwendest, musst du darauf achten, dass du die table_name oder view_name in Klammern setzen. ONLY wird ignoriert, wenn die Tabelle oder Ansicht keine Untertabellen oder Unteransichten hat.
OUTER
Gibt an, dass die Zeilen der benannten Tabelle oder Ansicht sowie die Zeilen und Spalten aller Untertabellen oder Unteransichten in der Ergebnismenge abgerufen werden. Die Spalten der Untertabellen (oder Unteransichten) werden rechts angehängt, in der Reihenfolge der Untertabellenhierarchie entsprechend der Tiefe der Untertabelle. In umfangreichen Hierarchien werden Untertabellen mit gemeinsamen Eltern in der Reihenfolge der Erstellung ihrer Typen angehängt. Wenn du OUTER verwendest, achte darauf, dass du die table_name oder view_name in Klammern setzen. OUTER wird ignoriert, wenn die Tabelle oder Ansicht keine Untertabellen oder Unteransichten hat.
[join_type] JOIN join_condition

Verbindet die Ergebnismenge der in der FROM -Klausel angegebenen Tabelle mit einer anderen Tabelle, die eine sinnvolle Beziehung auf der Grundlage einer gemeinsamen Menge von Werten aufweist. Diese Werte sind in der Regel in Spalten mit demselben Namen und Datentyp enthalten, die in beiden zu verbindenden Tabellen vorkommen. Diese Spalten oder möglicherweise eine einzelne Spalte aus jeder Tabelle werden als Join-Schlüssel oder gemeinsamer Schlüssel bezeichnet. Meistens - aber nicht immer - ist der Join-Schlüssel der Primärschlüssel der einen Tabelle und ein Fremdschlüssel in der anderen Tabelle. Solange die Daten in den Spalten übereinstimmen, kann der Join durchgeführt werden. (Beachte, dass Joins auch mit der WHERE Klausel durchgeführt werden können. Diese Technik wird manchmal als Theta-Join bezeichnet).

Siehe "JOIN Subclause" für Details zu den verschiedenen Arten von Joins.

Die Fügebedingungen werden am häufigsten in der Form abgebildet:

JOIN table_name2 ON table_name1.column1 comparison_operator
   table_name2.column1
JOIN table_name3 ON table_name1.columnA comparison_operator
   table_name3.columnA
[...]

Wenn das comparison_operator das Gleichheitszeichen(=) ist, wird eine Verknüpfung als Gleichheitsverknüpfung bezeichnet. Der Vergleichsoperator kann aber auch <, >, <=, >= oder sogar <> sein .

Verwende den AND Operator, um eine JOIN mit mehreren Bedingungen zu erstellen. Du kannst auch den OR Operator verwenden, um alternative Join-Bedingungen anzugeben.

Wenn eine explizite join_type weggelassen wird, wird eine INNER JOIN angenommen. Beachte, dass es viele Arten von Joins gibt, die jeweils ihre eigenen Regeln und Verhaltensweisen haben. Beachte auch, dass es einen alternativen Ansatz für die Join-Bedingung gibt, nämlich die USING Klausel:

USING (column_name[, ... ])

Dient als Alternative zur ON Klausel. Mit dieser Klausel beschreibst du nicht die Bedingungen für den Join, sondern gibst einfach einen oder mehrere Spaltennamen (durch Kommas getrennt) an, die in beiden Tabellen vorkommen. Die Datenbank wertet den Join dann anhand dieser Spalten aus (die Spaltennamen müssen in beiden Tabellen identisch sein). Im folgenden Beispiel liefern die beiden Abfragen identische Ergebnisse:

   SELECT emp_id
   FROM employee
   LEFT JOIN sales USING (emp_id, region_id);

   SELECT emp_id
   FROM employee AS e
   LEFT JOIN sales AS s
      ON e.emp_id     = s.emp_id
      AND e.region_id = s.region_id;
WHERE search_condition

Filtert unerwünschte Daten aus der Ergebnismenge der Abfrage und gibt nur die Datensätze zurück, die die Suchbedingungen erfüllen. Eine schlecht geschriebene WHERE Klausel kann die Leistung einer ansonsten nützlichen SELECT Anweisung ruinieren, daher ist die Beherrschung der Feinheiten der WHERE Klausel von größter Bedeutung. Suchbedingungen werden syntaktisch in folgender Form dargestellt WHERE [schema.[table_name.]]column operator value.

WHERE Klauseln vergleichen normalerweise die Werte, die in einer column der Tabelle. Die Werte der Spalte werden mit einer operator eines bestimmten Typs verglichen (weitere Informationen findest du in Kapitel 2 ). Eine Spalte kann zum Beispiel gleich(=) einer bestimmten valuesein, größer sein als (>) ein bestimmter valuesein, oder BETWEEN einen Bereich von values.

WHERE Klauseln können viele Suchbedingungen enthalten, die mit den booleschen Operatoren AND oder OR aneinandergereiht werden, und Klammern können verwendet werden, um die Rangfolge der Suchbedingungen zu beeinflussen. WHERE Klauseln können auch Unterabfragen enthalten (siehe "WHERE-Klausel" für weitere Informationen).

group_by_clause
Weitere Informationen findest du unter "GROUP BY-Klausel".
order_by_clause
Weitere Informationen findest du unter "ORDER BY-Klausel".

Die Regeln auf einen Blick

Jede Klausel der SELECT Anweisung hat einen bestimmten Zweck. Man kann also einzeln von der FROM -Klausel, der WHERE -Klausel, der GROUP BY -Klausel und so weiter sprechen. Weitere Details und Beispiele zu den SELECT Anweisungen findest du in den Einträgen zu den einzelnen Klauseln der Anweisung an anderer Stelle in diesem Kapitel. Nicht jede Abfrage braucht jede Klausel, aber eine Abfrage braucht mindestens eine select_item Liste.

Weil die SELECT Klausel so wichtig ist und so viele Möglichkeiten bietet, haben wir diesen Abschnitt "Regeln auf einen Blick" in die folgenden detaillierten Unterabschnitte unterteilt:

  • Aliase und WHERE Klausel Joins

  • Die JOIN Klausel

  • Die WHERE Klausel

  • Die ORDER BY Klausel

Alle diese Klauseln sowie die Klausel GROUP BY werden in den jeweiligen Abschnitten dieses Kapitels ausführlicher behandelt.

Aliase und WHERE-Klausel-Joins

Den Spaltennamen müssen unter Umständen ihre Datenbank-, Schema- und Tabellennamen vorangestellt werden, insbesondere wenn derselbe Spaltenname in mehr als einer Tabelle in der Abfrage vorkommt. In einer Oracle-Datenbank können zum Beispiel sowohl die Tabelle jobs als auch die Tabelle scott's employee die Spalte job_id enthalten. Im folgenden Beispiel werden die Tabellen " Mitarbeiter" und " Aufträge" mit der Klausel WHERE verknüpft. Dies ist eine Join-Syntax im alten Stil:

SELECT   scott.employee.emp_id,
         scott.employee.fname,
         scott.employee.lname,
         jobs.job_desc

FROM     scott.employee,
         jobs
WHERE    scott.employee.job_id = jobs.job_id
ORDER BY scott.employee.fname,
         scott.employee.lname

Du kannst auch Aliasnamen verwenden, um eine solche Abfrage einfacher und übersichtlicher zu schreiben:

SELECT   e.emp_id,
         e.fname,
         e.lname,
         j.job_desc
FROM     scott.employee AS e,
         jobs AS j
WHERE    e.job_id = j.job_id
ORDER BY e.fname,
         e.lname

Diese beiden Abfragen veranschaulichen die folgenden wichtigen Regeln für WHERE clause joins:

  • Verwenden Sie Kommas zur Trennung mehrerer Elemente in der select_item Liste, Tabellen in der FROM Klausel und Elemente in der order_expression.

  • Verwende die AS Klausel, um Aliasnamen zu definieren.

  • Verwende Aliase konsequent in der gesamten SELECT Anweisung, sobald du sie definiert hast.

Im Allgemeinen solltest du die JOIN Klausel (wird im Folgenden erklärt) der WHERE Klausel für die Beschreibung von Join-Ausdrücken vorziehen. Dadurch bleibt dein Code nicht nur sauberer, weil du die Join-Bedingungen leicht von den Suchbedingungen unterscheiden kannst, sondern du vermeidest auch das kontraintuitive Verhalten, das in manchen Implementierungen aus der Verwendung der WHERE Klausel für äußere Joins resultiert.

Die JOIN-Klausel

Um die gleiche Abfrage wie im vorherigen Beispiel mit einem SQL-Standard-Join durchzuführen, gibst du die erste Tabelle und das Schlüsselwort JOIN an, gefolgt vom Namen der Tabelle, die gejoint werden soll, dem Schlüsselwort ON und der Join-Bedingung, die in der Abfrage im alten Stil verwendet worden wäre. Das nächste Beispiel zeigt die vorangegangene Abfrage unter Verwendung der SQL-Standardklausel JOIN:

SELECT   e.emp_id, e.fname, e.lname, j.job_desc
FROM     scott.employee AS e
JOIN     jobs AS j ON e.job_id = j.job_id
ORDER BY e.fname, e.lname;

Alternativ kannst du auch die USING Klausel verwenden. Anstatt die Bedingungen des Joins zu beschreiben, gibst du einfach ein oder mehrere column_names (durch Kommas getrennt) an, die in den beiden verbundenen Tabellen vorkommen. Die Datenbank wertet den Join dann anhand dieser Spalten aus (die Spaltennamen müssen in beiden Tabellen identisch sein). Im folgenden Beispiel liefern die beiden Abfragen (eine mit der ON Klausel und eine mit der USING Klausel) identische Ergebnisse:

SELECT emp_id
FROM employee LEFT JOIN sales USING (emp_id, region_id);

SELECT emp_id
FROM employee   AS e
LEFT JOIN sales AS s
   ON  e.emp_id    = s.emp_id
   AND e.region_id = s.region_id;

Siehe "JOIN Subclause" für Details zu den verschiedenen Arten von Joins.

Die WHERE-Klausel

Eine schlecht geschriebene WHERE Klausel kann eine ansonsten schöne SELECT Anweisung ruinieren. Deshalb ist es wichtig, dass du die Feinheiten der WHERE Klausel beherrschst (auf die wir später in diesem Kapitel näher eingehen). Hier ist ein Beispiel für eine typische Abfrage mit einer mehrteiligen WHERE Klausel:

SELECT   a.au_lname,
         a.au_fname,
         t2.title,
         t2.pubdate
FROM     authors a
JOIN     titleauthor t1 ON a.au_id = t1.au_id
JOIN     titles t2 ON t1.title_id = t2.title_id
WHERE    (t2.type = 'business' OR t2.type = 'popular_comp')
  AND    t2.advance > 5500
ORDER BY t2.title

Wenn du diese Abfrage untersuchst, beachte, dass die Klammern die Reihenfolge der Verarbeitung der Suchbedingungen beeinflussen. Du kannst Klammern verwenden, um Suchbedingungen in der Rangfolge nach oben oder unten zu verschieben, genau wie bei einer Algebra-Gleichung.

Auf einigen Plattformen wirkt sich die Standardsortierung der Datenbank (auch als Sortierreihenfolge bekannt) darauf aus, wie die WHERE Klausel die Ergebnisse einer Abfrage filtert. SQL Server ist zum Beispiel (standardmäßig) nach Wörterbüchern sortiert und unterscheidet nicht zwischen Groß- und Kleinschreibung und macht keinen Unterschied zwischen "Smith", "smith" und "SMITH". Oracle hingegen unterscheidet zwischen Wörterbuchreihenfolge und Groß-/Kleinschreibung und stellt fest, dass die Werte "Smith", "smith" und "SMITH" ungleich sind.

Die WHERE Klausel bietet viel mehr spezifische Möglichkeiten, als das vorangegangene Beispiel verdeutlicht. Die folgende Liste verweist auf einige der gängigsten Möglichkeiten der WHERE Klausel:

NOT
Invertiert eine Vergleichsoperation mit der Syntax WHERE NOT expression. Du könntest also WHERE NOT LIKE ... oder WHERE NOT IN ... in einer Abfrage verwenden.
Vergleichsoperatoren

Vergleicht eine beliebige Menge von Werten mit den Operationen <, >, <>, >=, <= und =. Zum Beispiel:

WHERE emp_id = '54123'
IS NULL oder IS NOT NULL Bedingungen
Suche nach allen NULL bzw. NOT NULL Werten mit der Syntax WHERE expression IS [NOT] NULL.
AND

Führt mehrere Bedingungen zusammen und gibt nur die Datensätze zurück, die alle Bedingungen erfüllen. Die maximale Anzahl von Mehrfachbedingungen ist plattformabhängig. Zum Beispiel:

WHERE job_id = '12' AND job_status = 'active'
OR

Führt alternative Bedingungen zusammen und gibt Datensätze zurück, die eine der Bedingungen erfüllen. Zum Beispiel:

WHERE job_id = '13' OR job_status = 'active'
LIKE

Weist die Abfrage an, eine in Anführungszeichen stehende Zeichenkette zur Mustererkennung zu verwenden. Die von den einzelnen Plattformen unterstützten Platzhaltersymbole sind in den jeweiligen Abschnitten aufgeführt. Alle Plattformen unterstützen das Prozentzeichen(%) als Platzhaltersymbol. Beispiel: Um alle Telefonnummern zu finden, die mit der Vorwahl 415 beginnen:

WHERE phone LIKE '415%'
EXISTS

EXISTS wird nur bei Unterabfragen verwendet und prüft, ob die Daten der Unterabfrage existieren. Sie ist normalerweise viel schneller als eine WHERE IN Unterabfrage. Die folgende Abfrage findet zum Beispiel alle Autoren, die auch Angestellte sind:

SELECT au_lname FROM authors WHERE EXISTS
   (SELECT last_name FROM employees)
BETWEEN

Führt eine Bereichsprüfung durch, um festzustellen, ob ein Wert zwischen zwei Werten liegt (einschließlich dieser beiden Werte). Zum Beispiel:

WHERE ytd_sales BETWEEN 4000 AND 9000.
IN

Führt einen Test durch, um festzustellen, ob ein Ausdruck auf einen beliebigen Wert aus einer Liste von Werten passt. Die Liste kann ein Literal sein, wie in WHERE state IN ('or', 'il', 'tn', 'ak'), oder sie kann mit einer Unterabfrage abgeleitet werden:

WHERE state IN (SELECT state_abbr FROM territories)
SOME | ANY

Funktioniert genauso wie die Operation EXISTS, allerdings mit einer etwas anderen Syntax. Die folgende Abfrage findet zum Beispiel alle Autoren, die auch Angestellte sind:

SELECT au_lname FROM authors WHERE
   au_lname = SOME(SELECT last_name FROM employees)
ALL

Überprüft, ob alle Datensätze in der Unterabfrage den Bewertungskriterien entsprechen, und gibt TRUE zurück, wenn die Unterabfrage null Zeilen liefert. Zum Beispiel:

WHERE city = ALL
   (SELECT city FROM employees WHERE emp_id = 54123)

Siehe "WHERE-Klausel" für weitere Details.

Die ORDER BY-Klausel

Eine Ergebnismenge kann durch die ORDER BY Klausel entsprechend der Sortierreihenfolge der Datenbank sortiert werden. Jede Spalte der Ergebnismenge kann entweder in aufsteigender (ASC) oder absteigender (DESC) Reihenfolge sortiert werden. (Aufsteigende Reihenfolge ist die Standardeinstellung.) Wenn keine ORDER BY Klausel angegeben wird, geben die meisten Implementierungen die Daten entweder in der physischen Reihenfolge der Daten in der Tabelle oder in der Reihenfolge eines von der Abfrage verwendeten Indexes zurück. Wenn jedoch keine ORDER BY Klausel angegeben ist, gibt es keine Garantie für die Reihenfolge der Ergebnismenge. Es folgt ein Beispiel für eine SELECT Anweisung mit einer ORDER BY Klausel auf SQL Server:

SELECT   e.emp_id "Emp ID",
         e.fname "First",
         e.lname "Last",
         j.job_desc "Job Desc"
FROM     employee e,
         jobs j

WHERE    e.job_id = j.job_id
  AND    j.job_desc = 'Acquisitions Manager'
ORDER BY e.fname DESC,
         e.lname ASC

Die Ergebnisse sind:

Emp ID    First           Last            Job Desc
--------- --------------- --------------- --------------------
MIR38834F Margaret        Rancé          Acquisitions Manager
MAS70474F Margaret        Smith          Acquisitions Manager
KJJ92907F Karla           Jablonski      Acquisitions Manager
GHT50241M Gary            Thomas         Acquisitions Manager

Nachdem die Ergebnismenge so reduziert wurde, dass sie den Suchbedingungen entspricht, wird sie nach den Vornamen der Autoren in absteigender Reihenfolge sortiert. Wenn die Vornamen der Autoren gleich sind, werden die Ergebnisse in aufsteigender Reihenfolge nach dem Nachnamen sortiert. Weitere Informationen findest du unter "ORDER BY-Klausel".

Du kannst eine ORDER BY Klausel schreiben, die Spalten in der Tabelle verwendet, die nicht in der select_item Liste erscheinen. Du könntest z.B. alle emp_idsaus der Tabelle employee abfragen, aber ORDER BY die Vor- und Nachnamen der Mitarbeiter.

Programmiertipps und -schwierigkeiten

Wenn du einer Tabelle oder View in der FROM Klausel einen Alias zugewiesen hast, verwende ihn ausschließlich für alle anderen Verweise auf diese Tabelle oder View innerhalb der Abfrage (z. B. in der WHERE Klausel). Mische keine Verweise auf den vollständigen Tabellennamen und den Alias in einer einzigen Abfrage. Du solltest gemischte Verweise aus mehreren Gründen vermeiden. Erstens ist es einfach inkonsistent und erschwert die Wartung des Codes. Zweitens geben einige Datenbankplattformen bei SELECT Anweisungen mit gemischten Verweisen Fehler zurück. (Siehe "SUBQUERY Substatement" für spezielle Anweisungen zum Alias innerhalb einer Subquery).

MySQL, PostgreSQL und SQL Server unterstützen bestimmte Arten von Abfragen, die keine FROM Klausel benötigen. Verwenden Sie diese Abfragetypen mit Vorsicht, da der SQL-Standard eine FROM -Klausel erfordert. Abfragen ohne FROM Klausel müssen manuell entweder in die SQL-Standardform oder in eine Form migriert werden, die auch auf der Zieldatenbank funktioniert. Siehe den Eintrag für jede Klausel, um den unterschiedlichen Grad der Unterstützung, den die verschiedenen Datenbankanbieter für die verschiedenen Optionen des SELECT Befehls bieten, genau zu untersuchen.

MySQL

Die MySQL-Implementierung von SELECT unterstützt JOIN (mit einigen Variationen, wie in "JOIN Subclause" beschrieben ), die INTO Klausel, die LIMIT Klausel und die PROCEDURE Klausel. Die Syntax lautet wie folgt:

SELECT [DISTINCT | DISTINCTROW | ALL] [HIGH_PRIORITY] [STRAIGHT_JOIN]
   [ {SQL_SMALL_RESULT | SQL_BIG_RESULT} ] [SQL_BUFFER_RESULT] 
   [SQL_CALC_FOUND_ROWS] select_item[, ...]
[INTO {OUTFILE 'filename' options | DUMPFILE 'filename' | 
   variable[, ...]}]
[FROM table_name[, ...]

join_clause

[WHERE search_condition]

group_by_clause

order_by_clause

[PROCEDURE procedure_name (param[, ...])]
[FOR {UPDATE | SHARE} [OF table_name[, ...]] [NOWAIT | SKIP LOCKED] | 
   LOCK IN SHARE MODE]

wo:

DISTINCT | DISTINCTROW | ALL
DISTINCTROW ist ein Synonym für DISTINCT. ALL wird angenommen, wenn DISTINCT oder DISTINCTROW nicht angegeben ist.
HIGH_PRIORITY
Gibt der Abfrage eine höhere Priorität als Anweisungen, die Daten innerhalb der Tabelle ändern. Dies sollte nur für spezielle Abfragen mit hoher Geschwindigkeit verwendet werden.
STRAIGHT_JOIN
Ähnlich wie JOIN, mit dem Unterschied, dass die linke Tabelle immer vor der rechten Tabelle gelesen wird. Dies ist eine Funktion zur Optimierung von Verknüpfungen. Sie sollte vermieden werden, es sei denn, du erhältst schlechte Abfragepläne mit der Standard-Syntax JOIN.
SQL_SMALL_RESULT | SQL_BIG_RESULT
Weist den Optimierer an, eine kleine bzw. große Ergebnismenge für eine GROUP BY oder DISTINCT Klausel zu erwarten. MySQL erstellt eine temporäre Tabelle, wenn eine Abfrage eine DISTINCT oder GROUP BY Klausel enthält, und diese optionalen Schlüsselwörter teilen MySQL mit, ob eine schnelle temporäre Tabelle im Speicher (für SQL_SMALL_RESULT) oder eine langsamere, plattenbasierte temporäre Tabelle (für SQL_BIG_RESULT) erstellt werden soll, um die Arbeitstabelle zu verarbeiten.
SQL_BUFFER_RESULT
Zwingt die Ergebnismenge in eine temporäre Tabelle, so dass MySQL die Tabellensperren früher freigeben und die Ergebnismenge dem Kunden schneller zur Verfügung stellen kann.
SQL_CALC_FOUND_ROWS
Berechnet, wie viele Zeilen sich in der Ergebnismenge befinden (unabhängig von einer LIMIT Klausel), die dann mit SELECT FOUND_ROWS() abgerufen werden können.
select_item
Ruft die aufgeführten Ausdrücke oder Spalten ab. Die Spalten können in folgendem Format aufgelistet werden [database_name.[table_name.]]column_name. Wenn die Datenbank- und/oder Tabellennamen weggelassen werden, nimmt MySQL die aktuelle Datenbank und Tabelle an.
INTO {OUTFILE 'filename' options | DUMPFILE 'filename' | variable[, ... ]}
Schreibt die Ergebnismenge der Abfrage in eine Datei namens 'filename' auf dem Host-Dateisystem mit der Option OUTFILE. Die benannte Datei darf nicht bereits auf dem Dateisystem existieren. Die Option DUMPFILE schreibt eine einzelne fortlaufende Datenzeile ohne Spalten- und Zeilenabschlüsse oder Escape-Zeichen. Diese Option wird hauptsächlich für BLOB Dateien verwendet. Die genauen Regeln für die Verwendung dieser Klausel werden im Anschluss an diese Liste erläutert. Die INTO variable Klausel ermöglicht es dir, eine oder mehrere Variablen aufzulisten (eine für jede zurückgegebene Spalte). Wenn du die Klausel INTO variableverwendest, gibst du nicht auch noch einen Dateinamen an.
FROM ...
Gibt die Tabelle an, aus der die Zeilen abgerufen werden sollen. Die Tabelle kann wie folgt beschrieben werden [database_name.]table_name. MySQL behandelt die Abfrage als Join, wenn mehr als eine Tabelle in der FROM Klausel erscheint.
PROCEDURE procedure_name (param[, ... ])
Benennt eine Prozedur, die die Daten in der Ergebnismenge verarbeitet. Die Prozedur ist eine externe Prozedur (normalerweise C++), keine datenbankinterne Stored Procedure.
FOR {UPDATE | SHARE} [OF table_name[, ... ]] [NOWAIT | SKIP LOCKED] | LOCK IN SHARE MODE
Erteilt eine Schreibsperre für die von der Abfrage zurückgegebenen Zeilen (UPDATE) zur ausschließlichen Verwendung (vorausgesetzt, die Tabelle ist vom Typ InnoDB oder BDB), oder gibt Lesesperren für die von der Abfrage zurückgegebenen Zeilen (SHARE und LOCK IN SHARE MODE) aus, so dass andere Benutzer die Zeilen zwar sehen, aber nicht ändern können. NOWAIT und SKIP LOCKED führen dazu, dass eine FOR UPDATE - oder FOR SHARE -Abfrage sofort ausgeführt wird. NOWAIT gibt einen Fehler zurück, wenn keine Zeilensperre erlangt werden kann, während SKIP LOCKED Zeilen aus der Ergebnismenge ausschließt, die durch eine andere Transaktion gesperrt sind.

Bei der Verwendung der INTO Klausel sind einige Regeln zu beachten. Erstens darf die Ausgabedatei noch nicht existieren, da die Überschreibfunktion nicht unterstützt wird. Zweitens ist jede Datei, die durch die Abfrage erstellt wird, für jeden lesbar, der sich mit dem Server verbinden kann. (Wenn du SELECT ... INTO OUTFILE verwendest, kannst du anschließend den MySQL-Befehl LOAD DATA INFILE verwenden, um die Daten schnell zu laden).

Du kannst die folgenden Optionen verwenden, um den Inhalt der Ausgabedatei besser zu kontrollieren, wenn du SELECT ... INTO OUTFILE verwendest:

  • ESCAPED BY

  • FIELDS TERMINATED BY

  • LINES TERMINATED BY

  • OPTIONALLY ENCLOSED BY

Das folgende Beispiel veranschaulicht die Verwendung dieser optionalen Befehle anhand einer MySQL-Abfrage, die eine Ergebnismenge in einer kommagetrennten Ausgabedatei zurückgibt:

SELECT job_id, emp_id, lname+fname
INTO OUTFILE "/tmp/employees.text"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\;n"
FROM employee;

MySQL erlaubt auch SELECT Anweisungen ohne eine FROM Klausel, wenn es um einfache Arithmetik geht. Die folgenden Abfragen sind zum Beispiel in MySQL gültig:

SELECT 2 + 2;
SELECT 565 - 200;
SELECT (365 * 2) * 52;

Um die Interoperabilität mit Oracle zu gewährleisten, unterstützt MySQL auch die Auswahl aus einer Pseudotabelle namens Dual:

SELECT 565 - 200 FROM DUAL;

MySQL bietet eine interessante Alternative zum SQL-Standard für die Abfrage von Tabellen - das HANDLER Statement. Die Anweisung HANDLER funktioniert ähnlich wie SELECT, mit dem Unterschied, dass HANDLER sehr schnelle Datenabfragen ermöglicht, die die SQL-Abfrage-Engine von MySQL umgehen. Da die Anweisung HANDLER jedoch keine SQL-Anweisung ist, verweisen wir dich für weitere Informationen auf die MySQL-Dokumentation.

Oracle

Oracle erlaubt eine sehr große Anzahl von Erweiterungen der SQL-Standardanweisung SELECT. Da in Oracle zum Beispiel sowohl verschachtelte Tabellen als auch partitionierte Tabellen erlaubt sind (siehe "CREATE/ALTER TABLE-Anweisung"), erlaubt die SELECT -Anweisung Abfragen auf diese Arten von Strukturen. Die Syntax von Oracle SELECT lautet wie folgt:

SELECT ( {[ALL | DISTINCT]} | [UNIQUE] ) [optimizer_hints]
select_item [AS alias][, ...]
[INTO {variable[, ...] | record}]
FROM {[ONLY] {[schema.][table_name | view_name |
   materialized_view_name]} [@database_link] 
      [AS [OF] {SCN | TIMESTAMP} expression] |
   subquery [WITH {READ ONLY | CHECK OPTION [CONSTRAINT 
      constraint_name]}] |
   [[VERSIONS BETWEEN {SCN | TIMESTAMP} {exp | MINVALUE} AND
      {exp | MAXVALUE}] AS OF {SCN | TIMESTAMP} expression] |
   TABLE (nested_table_column) [(+)]
      {[PARTITION (partition_name) | 
        SUBPARTITION (subpartition_name)]}
   [SAMPLE [BLOCK] [sample_percentage] [SEED (seed_value)]]} 
   [AS alias][, ...]

join_clause
[WHERE search_condition [{AND | OR} search_condition[, ...]]
   [[START WITH value] CONNECT BY [PRIOR] condition]]
group_by_clause

[MODEL model_clause]
order_by_clause

[FOR UPDATE [OF [schema.][table.]column][, ...]
   {[NOWAIT | WAIT (int)]}]

Wenn nicht anders angegeben, folgen die hier gezeigten Klauseln dem SQL-Standard. Ebenso sind die Elemente der Klauseln mit denen des SQL-Standards identisch, sofern nicht anders angegeben. Die Oracle-Klausel GROUP BY ist beispielsweise fast identisch mit dem SQL-Standard, einschließlich ihrer Bestandteile wie ROLLUP, CUBE, GROUPING SETS, verkettet GROUPING SETS und der Klausel HAVING.

Die Parameter sind:

{ALL | DISTINCT} | UNIQUE
UNIQUE ist ein Synonym für DISTINCT. In Oracle können DISTINCT und UNIQUE nicht für LOB Spalten verwendet werden.
optimizer_hints
Setzt das Standardverhalten des Abfrageoptimierers durch benutzerdefinierte Verhaltensweisen außer Kraft. Hinweise können Oracle zum Beispiel zwingen, einen Index zu verwenden, den es sonst nicht verwenden würde, oder einen Index zu vermeiden, den es sonst verwenden würde. Weitere Informationen zu Optimierungshinweisen findest du in der Dokumentation des Herstellers.
select_item
Ruft die aufgeführten Ausdrücke oder Spalten ab. Die Spalten können aus einer benannten Abfrage, einer Tabelle, einer Ansicht oder einer materialisierten Ansicht stammen und im folgenden Format aufgelistet werden [schema.[table_name.]]column_name. Wenn du das Schema weglässt, nimmt Oracle den Kontext des aktuellen Schemas an. Oracle erlaubt auch benannte Abfragen, die ähnlich wie verschachtelte Tabellen-Subabfragen referenziert werden können (siehe "SUBQUERY Substatement"); die Verwendung benannter Abfragen wird als Subquery Factoring bezeichnet. Neben benannten Abfragen unterstützt Oracle auch Unterabfragen und das Sternchen(*), eine Abkürzung für alle Spalten, in der select_item Liste.
INTO {variable[, ... ] | record}
Ruft die Werte der Ergebnismenge in PL/SQL-Variablen oder in einem PL/SQL-Datensatz ab.
FROM [ONLY]
Identifiziert die Tabelle, View, materialisierte View, Partition oder Subquery, aus der die Ergebnismenge abgerufen wird. Das Schlüsselwort ONLY ist optional und gilt nur für Views, die zu einer Hierarchie gehören. Verwenden Sie ONLY, wenn Sie nur Datensätze aus einer benannten Ansicht abrufen möchten und nicht aus einer ihrer Unteransichten.
AS [OF] {SCN | TIMESTAMP} expression
Implementiert ein SQL-gesteuertes Flashback, bei dem Systemänderungsnummern (SCNs) oder Zeitstempel auf jedes Objekt in der Liste angewendet werden. select_item Liste angewendet werden. Die Abfrage ruft nur die Datensätze ab, die zum angegebenen SCN oder Zeitpunkt existierten. (Diese Funktion kann auch auf Sitzungsebene mit dem integrierten Paket DBMS​_FLASH⁠BACK implementiert werden). SCN expression muss gleich einer Zahl sein, während TIMESTAMP expression einem Zeitstempelwert entsprechen muss. Flashback-Abfragen können nicht auf verknüpften Servern verwendet werden.
subquery [WITH {READ ONLY | CHECK OPTION [CONSTRAINT constraint_name]}]
Sie werden separat erwähnt, weil Oracle zusätzliche Möglichkeiten zur Steuerung einer Subquery bietet. WITH READ ONLY gibt an, dass das Ziel der Subquery nicht aktualisiert werden kann. WITH CHECK OPTION gibt an, dass jede Aktualisierung des Ziels der Subquery Zeilen erzeugen muss, die in der Subquery enthalten sind. WITH CONSTRAINT erstellt eine CHECK OPTION Einschränkung von constraint_name auf die Tabelle. Beachte, dass WITH CHECK OPTION und WITH CONSTRAINT normalerweise in INSERT ... SELECT Anweisungen verwendet werden.
[VERSIONS BETWEEN {SCN | TIMESTAMP} {exp | MINVALUE} AND {exp | MAXVALUE}] AS OF {SCN | TIMESTAMP} expression
Gibt eine spezielle Art der Abfrage an, um den Verlauf der Änderungen an Daten aus einer Tabelle, Ansicht oder materialisierten Ansicht abzurufen. Die Pseudospalte VERSIONS_XID zeigt den Bezeichner der Transaktion an, die die Änderung vorgenommen hat. Diese Art der Abfrage wird als Flashback-Abfrage bezeichnet; sie erfordert, dass du für jedes Objekt in der Liste einen SCN oder TIMESTAMP Wert angibst. select_item Liste. (Mit dem Oracle-Paket DBMS_FLASHBACK kannst du ein SQL-gesteuertes Flashback auf Sitzungsebene implementieren).

Die optionale Unterklausel VERSIONS BETWEEN wird verwendet, um mehrere Versionen der angegebenen Daten abzurufen, entweder mit einer oberen und unteren Grenze eines SCN (eine Zahl) oder TIMESTAMP (ein Zeitstempelwert), oder mit den Schlüsselwörtern MINVALUE und MAXVALUE. Ohne diese Klausel wird nur eine vergangene Version der Daten zurückgegeben. (Oracle bietet auch mehrere Pseudospalten für die Versionsabfrage an, um zusätzliche Versionsinformationen zu erhalten).

Die AS OF Klausel, die weiter oben in dieser Liste besprochen wurde, bestimmt die SCN oder den Zeitpunkt, von dem aus die Datenbank die Abfrage stellt, wenn sie mit der VERSIONS Klausel verwendet wird.

Du kannst Flashback-Abfragen mit der VERSIONS Klausel nicht gegen temporäre Tabellen, externe Tabellen, Tabellen in einem Cluster oder Views verwenden.

TABLE
Erforderlich bei der Abfrage einer hierarchisch deklarierten verschachtelten Tabelle.
PARTITION | SUBPARTITION
Schränkt eine Abfrage auf die angegebene Partition oder Unterpartition der Tabelle ein. Die Zeilen werden nur aus der angegebenen Partition oder Unterpartition abgerufen, nicht aus der gesamten Tabelle, um die E/A zu reduzieren.
SAMPLE [BLOCK] [sampling_percentage] [SEED (seed_value)]
Weist Oracle an, Datensätze aus einer zufälligen Stichprobe von Zeilen innerhalb der Ergebnismenge auszuwählen, als Prozentsatz von Zeilen oder Blöcken, anstatt aus der gesamten Tabelle. BLOCK weist Oracle an, Blockstichproben statt Zeilenstichproben zu verwenden. Die sampling​_per⁠centagegibt Oracle den Gesamtprozentsatz der Blöcke oder Zeilen an, die in die Stichprobe einbezogen werden sollen, und kann zwischen .000001 und 99 liegen. Die optionale Klausel SEED wird verwendet, um eine begrenzte Wiederholbarkeit zu gewährleisten. Wenn du einen Seed-Wert angibst, versucht Oracle, von einer Ausführung der Abfrage zur nächsten dieselbe Stichprobe zu liefern. Der Seed-Wert kann zwischen 0 und 4.294.967.295 liegen. Wenn du SEED nicht angibst, ändert sich die resultierende Stichprobe von einer Ausführung der Abfrage zur nächsten. Sampling kann nur bei Abfragen mit einer Tabelle verwendet werden.
join_clause
Führt die Ergebnismengen von zwei oder mehr Tabellen in einer einzigen Abfrage zusammen. Weitere Informationen findest du in der Beschreibung nach dieser Liste.
WHERE ... [[START WITH value] CONNECT BY [PRIOR] condition]

Filtert Datensätze, die in der Ergebnismenge zurückgegeben werden. Oracle erlaubt die Verwendung von hierarchischen Informationen innerhalb von Tabellen, deren Filterung mit der START WITH Klausel gesteuert werden kann. START WITH identifiziert die Zeilen, die als Elternzeilen in der Ergebnismenge dienen. CONNECT BY identifiziert die Beziehung condition zwischen den Elternzeilen und ihren Kindzeilen. Das Schlüsselwort PRIOR wird verwendet, um die Elternzeilen anstelle der Kindzeilen zu identifizieren.

Hierarchische Abfragen verwenden die Pseudospalte LEVEL, um (1) den Stammknoten, (2) die Kindknoten, (3) die Enkelknoten usw. zu identifizieren. Andere Pseudospalten, die in hierarchischen Abfragen zur Verfügung stehen, sind CONNECT_BY_ISCYCLE und CONNECT_BY_ISLEAF. Hierarchische Abfragen schließen die Klauseln ORDER BY und GROUP BY gegenseitig aus. Verwende diese Klauseln nicht in einer Abfrage, die START WITH oder CONNECT BY enthält. Du kannst Datensätze von Geschwistern derselben übergeordneten Tabelle mit der Klausel ORDER SIBLINGS BY ordnen.

MODEL model_clause
Ermöglicht es dir, ein mehrdimensionales Array aus Abfrageergebnissen zu erstellen und dann Formeln (sogenannte Regeln) auf dieses Array anzuwenden, um neue Werte zu berechnen. Siehe "Die MODEL-Klausel" für weitere Informationen.
FOR UPDATE [OF [schema.][table.]column[, ... ] {[NOWAIT | WAIT (int)]}
Sperrt die Zeilen der Ergebnismenge, so dass andere Benutzer sie nicht sperren oder aktualisieren können, bis du deine Transaktion abgeschlossen hast. FOR UPDATE kann nicht in einer Unterabfrage, in Abfragen mit DISTINCT oder GROUP BY oder in Abfragen mit Mengenoperatoren oder Aggregatfunktionen verwendet werden. Untergeordnete Zeilen in einer hierarchischen Tabelle werden nicht gesperrt, wenn diese Klausel auf die übergeordneten Zeilen angewandt wird. Das Schlüsselwort OF wird verwendet, um nur die ausgewählte Tabelle oder Ansicht zu sperren. Andernfalls sperrt Oracle alle Tabellen oder Views, auf die in der FROM Klausel verwiesen wird. Bei der Verwendung von OF spielen die Spalten keine Rolle, allerdings müssen echte Spaltennamen (keine Aliasnamen) verwendet werden. Die Schlüsselwörter NOWAIT und WAIT weisen Oracle an, die Kontrolle entweder sofort zurückzugeben, wenn bereits eine Sperre besteht, oder zu warten int Sekunden warten, bevor es die Kontrolle an dich zurückgibt. Wenn weder NOWAIT noch WAIT angegeben wird, wartet Oracle, bis die Zeilen verfügbar sind.

Im Gegensatz zu einigen anderen Datenbankplattformen lässt Oracle keine SELECT Anweisung ohne FROM Klausel zu. Die folgende Abfrage ist zum Beispiel ungültig:

SELECT 2 + 2;

Als Abhilfe hat Oracle eine Spezialtabelle namens DUAL bereitgestellt. Immer wenn du eine Abfrage schreiben willst, die keine Daten aus einer vom Benutzer erstellten Tabelle abruft, z. B. um eine Berechnung durchzuführen, verwende FROM DUAL. Beide der folgenden Abfragen sind gültig:

SELECT 2 + 2
FROM DUAL;
SELECT (((52-4) * 5) * 8)
FROM DUAL;

Oracles Implementierung von SELECT ist recht einfach, wenn du Daten aus einer Tabelle abrufen willst. Wie bereits erwähnt, erlaubt Oracle die Verwendung von benannten Abfragen. Eine benannte Abfrage ist gewissermaßen ein Alias für eine ganze Abfrage, mit der du Zeit sparen kannst, wenn du eine komplexe Anweisung mit mehreren Unterabfragen SELECT schreibst. Ein Beispiel:

WITH pub_costs AS
   (SELECT pub_id, SUM(job_lvl) dept_total
    FROM employees e
    GROUP BY pub_id),
avg_costs AS
   (SELECT SUM(dept_total)/COUNT(*) avg
    FROM employee)
SELECT * FROM pub_costs
WHERE dept_total > (SELECT avg FROM avg_cost)
ORDER BY department_name;

Hier erstellen wir zwei benannte Unterabfragen - public_costs und avg_costs - diespäter in der Hauptabfrage referenziert werden. Die benannten Abfragen sind im Grunde das Gleiche wie Unterabfragen. Allerdings müssen Unterabfragen bei jeder Verwendung vollständig ausgeschrieben werden, während dies bei benannten Abfragen nicht erforderlich ist.

Oracle ermöglicht es dir, mit der PARTITION Klausel Zeilen aus einer einzelnen Partition einer partitionierten Tabelle auszuwählen oder mit SAMPLE nur eine statistische Stichprobe der Zeilen (als Prozentsatz der Zeilen oder Blöcke) einer Ergebnismenge abzurufen. Ein Beispiel:

SELECT *
FROM sales PARTITION (sales_2021_q3) sales
WHERE sales.qty > 1000;
SELECT *
FROM sales SAMPLE (12);

Flashback-Abfragen sind eine Funktion von Oracle, die das Abrufen von Ergebnismengen zu einem bestimmten Zeitpunkt ermöglicht. So kannst du zum Beispiel herausfinden, wie hoch das Gehalt aller Mitarbeiter gestern war, bevor eine große Änderung in der Datenbank vorgenommen wurde:

SELECT job_lvl, lname, fname
FROM employee
   AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);

Eine weitere interessante Oracle-Erweiterung des Standardabfrageformats ist die hierarchische Abfrage. Hierarchische Abfragen geben die Ergebnisse von Abfragen gegen hierarchisch aufgebaute Tabellen in der von dir festgelegten Reihenfolge zurück. Die folgende Abfrage gibt zum Beispiel die Namen der Mitarbeiter und ihre Positionen in der Hierarchie (dargestellt durch die Position in der Spalte org_char ), die Mitarbeiter-IDs, die Manager-IDs und die Auftrags-IDs zurück:

-- Query
SELECT LPAD(' ',2*(LEVEL-1)) || lname AS org_chart,
   emp_id, mgr_id, job_id
FROM employee
START WITH job_id = 'Chief Executive Officer'
CONNECT BY PRIOR emp_id = mgr_id;

-- Results
ORG_CHART      EMPLOYEE_ID MANAGER_ID JOB_ID
-------------- ----------- ---------- ------------------------
Cramer             101        100 Chief Executive Officer
Devon              108        101 Business Operations Mgr
Thomas             109        108 Acquisitions Manager
Koskitalo          110        108 Productions Manager
Tonini             111        108 Operations Manager
Whalen             200        101 Admin Assistant
Chang              203        101 Chief Financial Officer
Gietz              206        203 Comptroller
Buchanan           102        101 VP Sales
Callahan           103        102 Marketing Manager

In der vorherigen Abfrage definiert die CONNECT BY Klausel die hierarchische Beziehung des emp_id Wertes als übergeordnete Zeile, die dem mgr_id Wert in der untergeordneten Zeile entspricht, während die START WITH Klausel angibt, wo in der Hierarchie die Ergebnismenge beginnen soll.

Oracle unterstützt die folgenden Arten der JOIN Syntax (siehe "JOIN Subclause" für weitere Details):

FROM table1 {
   CROSS JOIN table2 |
   INNER JOIN table2 [ {ON join_condition |
      USING (column_list)} ] |
   NATURAL [LEFT [OUTER]] JOIN table2 |
   LEFT [OUTER] JOIN table2 [ {ON join_condition
    | USING (column_list)} ] |
   RIGHT [OUTER] JOIN table2 [ {ON join_condition
    | USING (column_list)} ]|
   NATURAL [RIGHT [OUTER]] JOIN table2
   FULL [OUTER] JOIN table2 }
[CROSS] JOIN
Ruft alle Datensätze von sowohl table1 und table2. Dies ist syntaktisch dasselbe wie FROM table1, table2 ohne Join-Bedingungen in der WHERE Klausel.
INNER JOIN
Ruft jene Datensätze ab, die sowohl table1 und table2 ab, in denen es gemäß der Join-Bedingung übereinstimmende Werte in beiden Tabellen gibt. Beachte, dass die Syntax FROM table1, table2 mit Join-Bedingungen in der WHERE Klausel semantisch gleichbedeutend mit einem Inner Join ist.
NATURAL
Erspart die Deklaration einer Join-Bedingung, indem eine USING Klausel angenommen wird, die alle Spalten enthält, die zwischen den beiden verbundenen Tabellen gemeinsam sind. (Vorsicht, wenn Spalten zwar denselben Namen, aber nicht denselben Datentyp oder dieselbe Art von Werten haben!) LOB Spalten können in einem Natural Join nicht referenziert werden. Der Verweis auf eine LOB oder Collection-Spalte in einer NATURAL JOIN Klausel führt zu einem Fehler.
LEFT [OUTER] JOIN

Ruft alle Datensätze in der Tabelle ganz links (d. h., table1) und übereinstimmende Datensätze in der Tabelle ganz rechts (d. h., table2). Wenn es keinen übereinstimmenden Datensatz in table2gibt, werden die Spalten dieser Tabelle durch NULL-Werte ersetzt. Mit dieser Art der Verknüpfung kannst du alle Datensätze in einer Tabelle abrufen, auch wenn es in der verknüpften Tabelle keine Entsprechungen gibt. Ein Beispiel:

SELECT j.job_id, e.lname
FROM jobs j
LEFT OUTER JOIN employee e ON j.job_id = e.job_id
ORDER BY d.job_id
RIGHT [OUTER] JOIN
Ruft alle Datensätze in der Tabelle ganz rechts ab, unabhängig davon, ob es einen passenden Datensatz in der Tabelle ganz links gibt. Ein rechter Join ist dasselbe wie ein linker Join, nur dass die optionale Tabelle links steht.
FULL [OUTER] JOIN
Gibt an, dass alle Zeilen aus beiden Tabellen zurückgegeben werden, unabhängig davon, ob eine Zeile aus einer Tabelle mit einer Zeile in der anderen Tabelle übereinstimmt. Alle Spalten, die in der entsprechenden verbundenen Tabelle keinen Wert haben, erhalten den Wert NULL.
ON join_condition
Deklariert die Bedingung(en), die die Ergebnismengen von zwei Tabellen miteinander verbinden. Dies geschieht in Form der Deklaration der Spalten in table1 und table2 die der Join-Bedingung entsprechen müssen. Wenn mehrere Spalten verglichen werden müssen, verwendest du die AND Klausel.
USING (column_list)

Dient als Alternative zur ON Klausel. Anstatt die Bedingungen des Joins zu beschreiben, gibst du einfach einen Spaltennamen an (oder Spalten, die durch Kommas getrennt sind), der in beiden Tabellen vorkommt. Der/die Spaltenname(n) müssen in beiden Tabellen identisch sein und dürfen keinen Tabellennamen oder Alias vorangestellt bekommen. USING kann nicht für LOB Spalten beliebigen Typs verwendet werden. Die folgenden zwei Abfragen führen zu identischen Ergebnissen. Die eine ist mit einer USING Klausel geschrieben und die andere spezifiziert Join-Bedingungen mit der SQL-Standardsyntax:

SELECT column1
FROM foo
LEFT JOIN poo USING (column1, column2);

SELECT column1
FROM foo
LEFT JOIN poo ON foo.column1 = poo.column1
AND foo.column2 = poo.column2;

Partitionierte äußere Joins

Oracle unterstützt die PARTITION BY Subclause der JOIN Klausel des SQL-Standards, die eine spezielle Art von Abfrage definiert, die als partitionierter Outer Join bezeichnet wird und die herkömmliche Outer Join-Syntax erweitert, indem ein rechter oder linker Outer Join auf eine Partition von einer oder mehreren Zeilen angewendet wird. Dies ist besonders nützlich, um spärliche Daten entlang einer bestimmten Dimension abzufragen und so Zeilen zurückzugeben, die sonst in der Ergebnismenge ausgelassen würden. Die PARTITION BY Klausel kann auf beiden Seiten eines Outer Joins verwendet werden und führt zu einer UNION der Outer Joins jeder der Partitionen in der partitionierten Ergebnismenge und der Tabelle auf der anderen Seite des Joins. (Wenn diese Klausel weggelassen wird, behandelt Oracle die gesamte Ergebnismenge als eine einzige Partition.) PARTITION BY ist mit FULL OUTER JOIN nicht zulässig.

Unsere Produkttabelle enthält zum Beispiel alle Produkte, die wir herstellen, während die Fertigungstabelle anzeigt, wann wir sie herstellen. Da wir nicht ständig jedes Produkt herstellen, kann es vorkommen, dass die Daten zwischen den beiden Tabellen nur spärlich vorhanden sind. Daher die folgende Abfrage:

SELECT manufacturing.time_id AS time, product_name AS name,
     quantity AS qty
FROM product
PARTITION BY (product_name)
RIGHT OUTER JOIN times ON (manufacturing.time_id =
     product.time_id)
WHERE manufacturing.time_id
   BETWEEN TO_DATE('01/10/05', 'DD/MM/YY')
       AND TO_DATE('06/10/05', 'DD/MM/YY')
ORDER BY 2, 1;

gibt dieses Ergebnis zurück:

time      name            qty

--------- ----------      ----------

01-OCT-05 flux capacitor  10
02-OCT-05 flux capacitor

03-OCT-05 flux capacitor
04-OCT-05 flux capacitor
05-OCT-05 flux capacitor
06-OCT-05 flux capacitor  10
06-OCT-05 flux capacitor  8
01-OCT-05 transmogrifier 10
01-OCT-05 transmogrifier 15
02-OCT-05 transmogrifier
03-OCT-05 transmogrifier
04-OCT-05 transmogrifier 10
04-OCT-05 transmogrifier 11
05-OCT-05 transmogrifier
06-OCT-05 transmogrifier

Rückblende Abfragen

Oracle unterstützt auch Flashback-Abfragen, bei denen die vorherigen Werte der Ergebnisse einer SELECT Anweisung gespeichert werden. Im folgenden Beispielcode werden wir eine reguläre Abfrage auf eine Tabelle stellen, die Werte in der Tabelle mit einer UPDATE Anweisung ändern und dann die Flashback-Version der Daten abfragen. Zuerst die reguläre Abfrage:

SELECT salary FROM employees
WHERE last_name = 'McCreary';

Die Ergebnisse sind:

SALARY
----------
3800

Jetzt ändern wir den Wert in der Tabelle Mitarbeiter und fragen die Tabelle ab, um den aktuellen Wert zu bestätigen:

UPDATE employees SET salary = 4000
WHERE last_name = 'McCreary ';
SELECT salary FROM employees
WHERE last_name = 'McCreary ';

Die Ergebnisse sind:

SALARY
----------
4000

Zum Schluss führen wir eine Flashback-Abfrage durch, um zu sehen, wie hoch das Gehalt in der Vergangenheit war:

SELECT salary FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)
WHERE last_name = 'McCreary';

Die Ergebnisse sind:

SALARY
----------
3800

Wenn wir uns mehr Mühe geben wollen, können wir alle Gehaltswerte für einen bestimmten Zeitraum herausfinden, z. B. für die letzten zwei Tage:

SELECT salary FROM employees
VERSIONS BETWEEN TIMESTAMP
   SYSTIMESTAMP - INTERVAL '1' MINUTE AND
   SYSTIMESTAMP - INTERVAL '2' DAY
WHERE last_name = 'McCreary';

Die Ergebnisse sind:

SALARY
----------
4000
3800

Die MODEL-Klausel

Oracle unterstützt eine MODEL Klausel, mit der aus einer SELECT Anweisung kalkulationsähnliche Ergebnismengen zurückgegeben werden können. Die MODEL Klausel soll es Entwicklern erleichtern, Daten aus der Datenbank zu extrahieren und sie zur weiteren Bearbeitung in eine Tabellenkalkulation wie Microsoft Excel zu übertragen. Sie erstellt ein mehrdimensionales Array, in dem die Zellen durch Dimensionswerte referenziert werden können. Du könntest zum Beispiel ein Array nach Produkt und Zeit dimensionieren und die Spaltenwerte angeben, auf die du über Kombinationen dieser beiden Dimensionen zugreifen möchtest. Du kannst dann Regeln schreiben, die ähnlich wie Tabellenkalkulationsformeln funktionieren und die ausgeführt werden, um Werte in deinem Modell zu ändern oder neue Werte und vielleicht sogar neue Zeilen in deinem Modell zu erstellen.

Syntaktisch gesehen steht die MODEL Klausel nach der GROUP BY Klausel und vor der ORDER BY Klausel. Das frühere Syntaxdiagramm für die Anweisung SELECT von Oracle zeigt die Position der Klausel, und die Syntaxdetails werden hier dargestellt:

MODEL
   [{IGNORE | KEEP} NAV] 
      [UNIQUE {DIMENSION | SINGLE REFERENCE}]
   [ RETURN {UPDATED | ALL} ROWS ]
   [REFERENCE reference_model_name ON (subquery)
      [PARTITION BY (column [AS alias][, ...])]
         DIMENSION BY (column [AS alias][, ...])
         MEASURES (column [AS alias][, ...])
      [{IGNORE | KEEP} NAV] 
      [UNIQUE {DIMENSION | SINGLE REFERENCE}]]
[MAIN main_model_name]
   [PARTITION BY (column [AS alias][, ...])]
      DIMENSION BY (column [AS alias][, ...])
      MEASURES (column [AS alias][, ...])
   [{IGNORE | KEEP} NAV] 
      [UNIQUE {DIMENSION | SINGLE REFERENCE}]]
   model_rules_clause
[RULES [UPSERT [ALL] | UPDATE]
      [{AUTOMATIC | SEQUENTIAL} ORDER]]
   [ITERATE (int) [UNTIL (ending_condition)]]
   ( [ {UPSERT [ALL] | UPDATE } ]   measure [...]
   [FOR { dimension | ( dimension[, ...] ) }
   { [IN ({subquery | literal[, ...]})] |
      [LIKE pattern] FROM start_literal TO end_literal
         {INCREMENT | DECREMENT} diff_literal }[, ...]
   [ORDER [SIBLINGS] BY (order_column [ASC | DESC]
      [NULLS FIRST | NULLS LAST][, ...])]]
   = expr[, ...] )

Die Parameter der MODEL Klausel sind wie folgt:

{IGNORE | KEEP} NAV
Legt fest, ob NULL- oder fehlende Werte (NAV) als NULLs (KEEP) beibehalten werden oder ob sie durch geeignete Standardwerte (IGNORE) ersetzt werden: Null für numerische Typen, 1-Jan-2000 für Datumstypen, eine leere Zeichenkette für Zeichentypen und NULL für alles andere.
UNIQUE {DIMENSION | SINGLE REFERENCE}
Legt den Bereich fest, in dem die Datenbank sicherstellt, dass ein bestimmter Zellbezug auf einen eindeutigen Datenwert verweist. Verwende DIMENSION, um sicherzustellen, dass jede mögliche Zellreferenz, egal ob auf der linken oder rechten Seite einer Regel, einen einzigen Wert darstellt. Verwende SINGLE REFERENCE, um diese Prüfung nur für die Zellbezüge durchzuführen, die auf der rechten Seite einer Regel erscheinen.
RETURN {UPDATED | ALL} ROWS
Gibt an, ob alle Zeilen aus der Modellverarbeitung zurückgegeben werden oder ob nur aktualisierte Zeilen zurückgegeben werden.
reference_model_name ON (subquery)
Gibt den Namen und die Zeilenquelle für ein Referenzmodell an. Dies ist ein Modell, auf dem du keine Berechnungen durchführen kannst, das aber Werte enthält, auf die du in deiner Hauptabfrage verweisen kannst.
PARTITION BY (column[, ... ])
Teilt ein Modell anhand der angegebenen Spalten in unabhängige Partitionen auf. Du kannst Referenzmodelle nicht partitionieren.
DIMENSION BY (column[, ... ])
Gibt die Dimensionen für ein Modell an. Die Werte in diesen Spalten stellen die Indexwerte dar, die zur Identifizierung der Zellen im mehrdimensionalen Adressraum verwendet werden.
MEASURES (column[, ... ])
Gibt die Werte an, die mit jeder einzelnen Kombination von Dimensionen verbunden sind (z. B. mit jeder Zelle des Modells).
alias
Gibt einen Alias für eine Spalte an.
MAIN model_name
Beginnt die Definition des Hauptmodells und gibt diesem Modell einen Namen. Das Hauptmodell ist das Modell, an dem du arbeitest. Die Zeilen aus deinem SELECT werden in dieses Modell eingespeist, Regeln werden angewendet und die resultierenden Zeilen werden zurückgegeben.
RULES [UPSERT [ALL] | UPDATE]
Legt fest, ob Regeln sowohl neue Zellen erstellen als auch bestehende Zellen aktualisieren dürfen (UPSERT), oder ob sie nur bestehende Zellen aktualisieren dürfen (UPDATE). Wenn dein Modell in der Lage sein soll, neue Zeilen in deiner Ergebnismenge zu erstellen, gib UPSERT an. Die Standardeinstellung ist UPSERT. Du kannst dieses Verhalten auch regelbasiert steuern; siehe rule in der Syntax.
{AUTOMATIC | SEQUENTIAL} ORDER
Gibt an, ob der Optimierer die Reihenfolge bestimmt, in der die Regeln ausgewertet werden (AUTOMATIC), oder ob die Regeln in der Reihenfolge ausgewertet werden, in der du sie auflistest (SEQUENTIAL). Die Vorgabe ist SEQUENTIAL.
ITERATE (int)
Verlangt, dass ein ganzer Satz von Regeln wiederholt ausgewertet wird, int mal. Standardmäßig wird der Satz von Regeln nur einmal ausgewertet.
UNTIL(ending_condition)
Gibt eine Bedingung an, die, wenn sie erfüllt ist, die Iteration beendet. Du musst noch eine intangeben, das als Schutz vor Endlosschleifen dient.
measure[ ... ]
Ein Verweis auf eine der Maßnahmen, die in der MEASURES Klausel aufgeführt sind. Wenn du eine Kennzahl referenzierst, sind die eckigen Klammern Teil der Syntax. Du musst alle Dimensionen angeben, entweder über eine Unterabfrage oder indem du sie auflistest, und der spezifische Wert der Kennzahl, die mit diesen Dimensionen verbunden ist, wird zurückgegeben oder referenziert.
FOR ...
Eine FOR Schleife, die über eine oder mehrere Dimensionen iteriert. Die mehrfach iterierende FOR Schleife ist ähnlich wie eine Unterabfrage, bei der jede Zeile der Ergebnismenge eine bestimmte Kombination von Dimensionen darstellt.
{ dimension | ( dimension[, ... ]) }
Eine Liste von Werten, entweder aus Spalten oder Ausdrücken, die zusammen eine eindeutige Zelle im Modell identifizieren.
IN ({subquery | literal[, ... ]})
Die Quelle der Werte für eine FOR Schleife kann eine Unterabfrage oder eine bestimmte Liste von Literalwerten sein.
LIKE pattern
Ermöglicht es dir, Bemaßungswerte in ein Muster einzufügen. Verwende ein Prozentzeichen, um die Stelle zu markieren, an der die Bemaßungswerte eingefügt werden sollen. Verwende zum Beispiel FOR x LIKE 'A%B' FROM 1 TO 3 INCREMENT 1, um Werte wie 'A1B', 'A2B', 'A3B' zu erzeugen.
FROM start_literal TO end_literal {INCREMENT | DECREMENT} diff_literal
Legt die Anfangs- und Endwerte der FOR Schleife fest, sowie die Differenz zwischen den einzelnen nachfolgenden Werten, wenn die Schleife von Anfang bis Ende durchläuft.
ORDER [SIBLINGS] BY (order_column [ASC | DESC] [NULLS FIRST | NULLS LAST][, ... ])
Legt die Reihenfolge der Auswertung in Bezug auf die Zellen fest, auf die von der linken Seite einer Regel verwiesen wird. Verwende diese Klausel, wenn du möchtest, dass eine Regel in der richtigen Reihenfolge auf die Zellen angewendet wird. Andernfalls gibt es keine Garantie dafür, in welcher Reihenfolge die Regel auf die betroffenen Zellen angewendet wird. Du kannst Datensätze aus Geschwistern derselben übergeordneten Tabelle mit der ORDER SIBLINGS BY Klausel ordnen.

Es folgt eine Liste von Funktionen, die speziell für die Verwendung in der MODEL Klausel entwickelt wurden:

CV() or CV(dimension_column)
Gibt den aktuellen Wert einer Dimensionsspalte zurück. Darf nur auf der rechten Seite eines Ausdrucks in einer Regel verwendet werden. Wenn die Form CV() verwendet wird, wird die Dimensionsspalte implizit anhand der Position des Funktionsaufrufs in einer Liste von Dimensionswerten bestimmt.
PRESENTNNV(measure[dimension[, ... ], not_null, was_null)
Gibt entweder not_null oder zurück. was_nullzurück, je nachdem, ob das angegebene Maß zu Beginn der Modellverarbeitung NULL war. Diese Funktion kann nur auf der rechten Seite eines Regelausdrucks verwendet werden.
PRESENTV(measure[dimension[, ... ], did_exist, didnt_exist)
Gibt entweder did_exist oder zurück. didnt_existzurück, je nachdem, ob die angegebene Maßnahme zu Beginn der Modellverarbeitung existierte. Diese Funktion kann nur auf der rechten Seite eines Regelausdrucks verwendet werden. Beachte, dass die Frage, ob eine Kennzahl existierte, etwas völlig anderes ist als die Frage, ob diese Kennzahl NULL war.
ITERATION_NUMBER
Gibt bei der ersten Iteration durch die Regeln 0 zurück, bei der zweiten Iteration 1 und so weiter. Dies ist nützlich, wenn du die Berechnungen der Regeln auf die Anzahl der Iterationen stützen willst.

Das folgende Beispiel zeigt, dass die MODEL Klausel einer normalen SELECT Anweisung die Möglichkeit gibt, ein mehrdimensionales Array als Ergebnismenge zu konstruieren und Werte zwischen den Zeilen und zwischen den Arrays unabhängig voneinander zu berechnen. Die neu berechneten Werte werden als Teil der Ergebnismenge der SELECT Anweisung zurückgegeben:

SELECT SUBSTR(region,1,20) country, SUBSTR(product,1,15)
   product, year, sales
FROM sales_view
WHERE region IN ('USA','UK')
MODEL RETURN UPDATED ROWS
   PARTITION BY (region)
   DIMENSION BY (product, year)
   MEASURES (sale sales)
   RULES (
      sales['Bounce',2006] = sales['Bounce',2005] 
         + sales['Bounce',2004],
      sales['Y Box', 2006] = sales['Y Box', 2005],
      sales['2_Products',2006] = sales['Bounce',2006]
         + sales['Y Box',2006] )
ORDER BY region, product, year;

In diesem Beispiel gibt eine Abfrage gegen die materialisierte Ansicht sales_view die Summe der Verkäufe im Laufe einiger Jahre für die Regionen 'USA' und 'UK' zurück. Die Klausel MODEL liegt dann zwischen der Klausel WHERE und der Klausel ORDER BY. Da sales_view derzeit Daten für die Jahre 2004 und 2005 enthält, versehen wir sie mit Regeln zur Berechnung der Zahlen für das Jahr 2006.

Die Subklausel RETURN UPDATED ROWS begrenzt die Ergebnismenge auf die Zeilen, die durch die Abfrage erstellt oder aktualisiert wurden. Als Nächstes definiert das Beispiel die logischen Unterteilungen der Daten mit Hilfe von Datenelementen aus der materialisierten Ansicht und unter Verwendung der Subklauseln PARTITION BY, DIMENSION BY und MEASURES. Die Subklausel RULES verweist dann auf einzelne Kennzahlen des Modells, indem sie auf Kombinationen verschiedener Dimensionswerte verweist, ähnlich wie ein Tabellenkalkulationsmakro Arbeitsblattzellen mit spezifischen Lookups und Verweisen auf Wertebereiche referenziert.

Oracle (und SQL Server, der eine etwas andere Technik verwendet) unterstützt einen Nicht-SQL-Standard-Abfragetyp, der als Pivot-Abfrage bekannt ist. Obwohl du in der Dokumentation des Anbieters nachlesen solltest, wie du eine Pivot-Abfrage (oder Unpivot-Abfrage) genau schreibst, soll dir das folgende Beispiel helfen, die Vorteile dieser nützlichen Technik zu nutzen. Eine Pivot-Abfrage dreht die Ergebnismenge auf die Seite und ermöglicht es dir, mehr Wert aus den Daten zu ziehen. In Oracle musst du zuerst deine Pivot-Tabelle erstellen. Mit einer Pivot-Tabelle kannst du dann das Ergebnis "auf die Seite drehen", so dass die Spalte order_type zu den Spaltenüberschriften wird:

CREATE TABLE pivot_table AS
SELECT * FROM (SELECT year, order_type, amt FROM sales)
PIVOT SUM(amt) FOR order_type IN ('retail', 'web');

SELECT * FROM pivot_table ORDER BY YEAR;

Die Ergebnisse sind:

YEAR     RETAIL    WEB
---- ----------- ------
2004     7014.54
2005     9745.12
2006    16717.88 10056.6
2007    28833.34 39334.9
2008    66165.77 127109.4

PostgreSQL

PostgreSQL unterstützt eine unkomplizierte Implementierung der SELECT Anweisung. Es unterstützt JOIN und Subquery-Anwendungen. PostgreSQL ermöglicht auch die Erstellung neuer temporärer oder permanenter Tabellen mit der SELECT ... INTO Syntax oder dem CREATE TABLE AS SELECT Konstrukt. Die SELECT Syntax lautet wie folgt:

SELECT [ALL | DISTINCT [ON (select_item[, ...])]]
[AS alias [(alias_list)]][, ...]
[INTO [LOGGED | UNLOGGED] [[TEMP]ORARY] [TABLE] new_table]
[FROM [ONLY] table1[.*] [AS alias][, ...]]
[[join_type] JOIN table2 {[ON join_condition] | 
   [USING (column_list)]}]
[WHERE search_condition]
[group_by_clause]
[order_by_clause]
[for_update_clause]

for_update_clause ::= [FOR {UPDATE | NO KEY UPDATE | SHARE | KEY SHARE}
   [OF table_name[, ...]] [NOWAIT | SKIP LOCKED] [...]]

wo:

ALL | DISTINCT [ON (select_item[, ... ]]
Unterstützt die Schlüsselwörter ALL und DISTINCT des SQL-Standards, wobei ALL (die Standardeinstellung) alle Zeilen (einschließlich Duplikaten) zurückgibt und DISTINCT doppelte Zeilen eliminiert. Darüber hinaus eliminiert DISTINCT ON Duplikate nur in den angegebenen select_items, nicht auf allen select_items in der Abfrage (es folgt ein Beispiel).
select_item
Enthält die Standardelemente einer select_item Liste, die vom SQL-Standard unterstützt werden. Zusätzlich zur Abkürzung mit Sternchen (*), um alle Zeilen abzurufen, kannst du mit table_name.* verwenden, um alle Zeilen aus einer einzelnen Tabelle abzurufen.
AS alias [(alias_list)]
Erzeugt einen Alias oder eine Liste von Aliasen für eine oder mehrere Spalten (oder Tabellen in der FROM Klausel). AS ist erforderlich für select_item Aliasen erforderlich, nicht aber für FROM Tabellenaliase. (Einige andere Datenbankplattformen behandeln AS bei der Deklaration eines Alias als Option).
INTO [UNLOGGED | LOGGED] [[TEMP]ORARY] [TABLE] new_table
Erzeugt eine neue Tabelle aus der Ergebnismenge der Abfrage. Sowohl TEMP als auch TEMPORARY sind akzeptable Verwendungen, um eine temporäre Tabelle zu erstellen, die am Ende der Sitzung automatisch gelöscht wird. Andernfalls erstellt der Befehl eine permanente Tabelle. Permanente Tabellen, die mit dieser Anweisung erstellt werden, müssen neue, eindeutige Namen haben, aber temporäre Tabellen können denselben Namen wie eine bestehende Tabelle haben. Wenn du eine temporäre Tabelle mit demselben Namen wie eine bestehende permanente Tabelle erstellst, wird die temporäre Tabelle verwendet, um alle Operationen mit diesem Tabellennamen aufzulösen, solange du dich in derselben Sitzung befindest, in der du sie erstellt hast. Andere Sitzungen sehen weiterhin die bestehende permanente Tabelle. Die Tabelle UNLOGGED erstellt eine Tabelle, bei der nur die Erstellung der Tabellenstruktur in die Transaktionsprotokolle geschrieben wird. Die Erstellung von unprotokollierten Tabellen ist in der Regel schneller als die von protokollierten Tabellen und kann bis zu fünfmal schneller sein. Da die Daten der Tabelle UNLOGGED jedoch nicht protokolliert werden, können die eingefügten Daten nicht repliziert werden. Außerdem werden sie bei einem Neustart der Datenbank oder einem Absturz abgeschnitten. Deshalb solltest du unprotokollierte Tabellen nur für Daten verwenden, die du nicht auf Replikaten lesen musst, und für Daten, die sich leichter wiederherstellen lassen. Wenn die Logging-Option nicht angegeben wird, wird eine LOGGED Tabelle erstellt.
FROM [ONLY]table1[, ... ]

Gibt eine oder mehrere Quelltabellen an, in denen sich die Daten befinden. (Achte darauf, eine Join-Bedingung oder eine Theta WHERE Klausel anzugeben, damit du kein kartesisches Produkt aller Datensätze in allen Tabellen erhältst). PostgreSQL erlaubt die Vererbung in Kindtabellen von deklarierten Elterntabellen. Das ONLY-Schlüsselwort wird für partitionierte Tabellen nicht unterstützt, da die Elterntabelle nie Daten hat. Verwende das Schlüsselwort ONLY, um Zeilen aus den Kindtabellen deiner Quelltabelle zu unterdrücken. (Du kannst diese Standardvererbung mit dem Befehl SET SQL_Inheritance TO OFF global ausschalten.) PostgreSQL unterstützt auch verschachtelte Tabellenunterabfragen (siehe den Abschnitt über SUBQUERY weiter unten in diesem Kapitel). Die FROM Klausel wird nicht benötigt, wenn sie für Berechnungen verwendet wird:

SELECT 8 * 40;

PostgreSQL fügt auch eine implizite FROM in SELECT Anweisungen ein, die schema-identifizierte Spalten enthalten. Die folgende Abfrage ist zum Beispiel akzeptabel (wenn auch nicht empfohlen):

SELECT sales.stor_id WHERE sales.stor_id = '6380';

for_update_clause

Wenn FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE oder FOR KEY SHARE angegeben wird, sperrt die Anweisung SELECT die ausgewählten Zeilen gegen gleichzeitige Aktualisierungen. SKIP LOCKS ermöglicht, dass bereits gesperrte Datensätze nicht aktualisiert werden. Dies eignet sich, wenn du Datensätze in Stapeln aktualisierst und Datensätze, die in einem zweiten Stapel nicht aktualisiert wurden, jederzeit wieder aufrufen kannst.

PostgreSQL unterstützt eine praktische Variante der DISTINCT Klausel, DISTINCT ON (select_item[, ... ] ). Mit dieser Variante kannst du genau die Spalten auswählen, die für die Eliminierung von Duplikaten berücksichtigt werden sollen. PostgreSQL wählt die Ergebnismenge auf ähnliche Weise aus wie bei ORDER BY. Du solltest eine ORDER BY Klausel einfügen, damit nicht unvorhersehbar ist, welcher Datensatz zurückgegeben wird. Diese Abfrage ruft zum Beispiel den neuesten Verkaufsbericht für jede Filiale auf der Grundlage des letzten Bestelldatums ab:

SELECT DISTINCT ON (stor_id), ord_date, qty
FROM sales
ORDER BY stor_id, ord_date DESC;

Es gibt jedoch keine Möglichkeit, vorherzusagen, welcher einzelne Datensatz ohne die ORDER BY Klausel zurückgegeben worden wäre.

PostgreSQL ermöglicht es auch, eine ganze Zeile als Spalte abzurufen, und zwar wie folgt:

SELECT DISTINCT ON (stor_id) stor_id, s AS sale_row
FROM sales AS s
ORDER BY stor_id, ord_date DESC;

Diese Abfrage ruft den letzten Verkaufsbericht für jede Filiale auf der Grundlage des letzten Bestelldatums ab, aber anstatt einzelne Spalten zurückzugeben, gibt sie die gesamte Zeile als Spaltenwert zurück. Dies geschieht einfach durch die Angabe des Tabellennamens oder eines Tabellenalias. Wenn ein Tabellenalias angegeben wird, kann der Tabellenname nicht verwendet werden; du musst den Alias verwenden. Die Ausgabe der vorangegangenen Abfrage sieht wie folgt aus:

6380  (6380,6871,"1994-09-14 00:00:00-04",5,"Net 60",BU1032)
7066  (7066,QA7442.3,"1994-09-13 00:00:00-04",75,"ON invoice",PS2091)
7067  (7067,D4482,"1994-09-14 00:00:00-04",10,"Net 60",PS2091)
7131  (7131,N914008,"1994-09-14 00:00:00-04",20,"Net 30",PS2091)
7896  (7896,TQ456,"1993-12-12 00:00:00-05",10,"Net 60",MC2222)
8042  (8042,423LL922,"1994-09-14 00:00:00-04",15,"ON invoice",MC3021)

Diese Funktion ist besonders nützlich für die Ausgabe von Daten an Anwendungen, indem du sie wie folgt mit einer Funktion wie jsonb_agg oder json_agg kombinierst und die ORDER BY Aggregations-Syntax verwendest (eine Funktion, die von allen PostgreSQL-Aggregatfunktionen unterstützt wird, auch von benutzerdefinierten):

SELECT json_agg(s ORDER BY stor_id, ord_date) AS sale_rows
FROM sales AS s;

Informationen zu den unterstützten Join-Typen findest du im Abschnitt "JOIN Subclause".

SQL Server

SQL Server unterstützt die meisten grundlegenden Elemente der SQL-Standardanweisung SELECT, einschließlich aller verschiedenen Join-Typen. Außerdem bietet er mehrere Variationen der SELECT Anweisung, darunter Optimierungshinweise, die INTO Klausel, die TOP Klausel, GROUP BY Variationen, COMPUTE und WITH OPTIONS. Die SQL Server SELECT Syntax lautet:

SELECT {[ALL | DISTINCT] | [TOP number [PERCENT] [WITH TIES]]}
   select_item [AS alias]
[INTO new_table_name]
[FROM {[rowset_function | table1[, ...]]} [AS alias]]
[[join_type] JOIN table2 [ON join_condition]]
[WHERE search_condition]
group_by_clause
order_by_clause

[COMPUTE {aggregation (expression)}[, ...]
   [BY expression[, ...]]]
[FOR {BROWSE | XML | JSON}]
[OPTION (hint[, ...])]

wo:

TOP number [PERCENT] [WITH TIES]
Gibt an, dass nur die angegebene number Zeilen in der Ergebnismenge der Abfrage abgerufen werden sollen. Wenn PERCENT angegeben wird, werden nur die ersten number Prozent der Zeilen abgerufen. WITH TIES wird nur für Abfragen mit einer ORDER BY Klausel verwendet. Diese Variante gibt an, dass zusätzliche Zeilen aus der Basis-Ergebnismenge unter Verwendung des gleichen Werts in der ORDER BY -Klausel zurückgegeben werden, die als letzte der TOP -Zeilen erscheinen.
INTO new_table_name
Erzeugt eine neue Tabelle aus der Ergebnismenge der Abfrage. Du kannst diesen Befehl verwenden, um temporäre oder permanente Tabellen zu erstellen. (Siehe die Regeln von SQL Server zum Erstellen von temporären oder permanenten Tabellen unter "CREATE/ALTER TABLE-Anweisung") . Der Befehl SELECT ... INTO kopiert die abgefragten Zeilen und Spalten aus anderen Tabellen schnell in eine neue Tabelle, indem er einen nicht protokollierten Vorgang verwendet. Da er nicht protokolliert wird, haben die Anweisungen COMMIT und ROLLBACK keine Auswirkungen auf ihn.
FROM {[rowset_function | table1[, ... ]]}

Unterstützt das Standardverhalten der SQL-Standardklausel FROM, einschließlich verschachtelter Tabellenunterabfragen. Darüber hinaus unterstützt SQL Server eine Reihe von Erweiterungen, die sogenannten Rowset-Funktionen. Mit den Rowset-Funktionen kann SQL Server Daten aus speziellen oder externen Datenquellen wie XML-Streams, Dateistrukturen für die Volltextsuche (eine spezielle Struktur in SQL Server, in der z. B. MS Word-Dokumente und MS PowerPoint-Diashows in der Datenbank gespeichert werden) oder externen Datenquellen (z. B. MS Excel-Tabellen) beziehen.

In der SQL Server-Dokumentation findest du eine vollständige Beschreibung der verfügbaren FROM {[rowset_function | table1[, ... ]]} Optionen. Unter den vielen Möglichkeiten unterstützt SQL Server derzeit die folgenden Funktionen:

CONTAINSTABLE
Gibt eine Tabelle zurück, die von einer angegebenen Tabelle abgeleitet ist und mindestens einen Volltextindex TEXT oder NTEXT Spalte enthält. Die abgeleiteten Datensätze basieren entweder auf einer präzisen, unscharfen, gewichteten Suche oder einer Suche mit Annäherung. Die abgeleitete Tabelle wird dann wie jede andere FROM Datenquelle behandelt.
FREETEXTTABLE
Ähnlich wie CONTAINSTABLE, mit dem Unterschied, dass die Datensätze auf der Grundlage einer Bedeutungssuche von 'freetext_string'FREETEXTTABLE ist nützlich für Ad-hoc-Abfragen gegen Volltexttabellen, aber weniger genau als CONTAINSTABLE.
OPENDATASOURCE
Bietet eine Möglichkeit, Daten von außerhalb des SQL Servers über OLEDB zu beziehen, ohne einen verknüpften Server zu deklarieren, wie z. B. eine MS Excel-Tabelle oder eine Sybase Adaptive Server-Datenbanktabelle. Dies ist für gelegentliche Ad-hoc-Abfragen gedacht; wenn du häufig Ergebnismengen aus externen Datenquellen abrufst, solltest du einen verknüpften Server deklarieren.
OPENQUERY
Führt eine Passthrough-Abfrage gegen einen verknüpften Server aus. Dies ist ein effektives Mittel, um eine verschachtelte Tabellenunterabfrage gegen eine Datenquelle auszuführen, die sich außerhalb von SQL Server befindet. Die Datenquelle muss zunächst als verknüpfter Server deklariert werden.
OPENROWSET
Führt eine Passthrough-Abfrage gegen eine externe Datenquelle aus. Dies ist ähnlich wie OPENDATASOURCE, mit dem Unterschied, dass OPENDATASOURCE nur die Datenquelle öffnet und keine SELECT Anweisung durchreicht. OPENROWSET ist nur für die gelegentliche Ad-hoc-Nutzung gedacht.
OPENXML
Bietet eine abfragbare, tabellenähnliche Ansicht eines XML-Dokuments unter Verwendung eines Dateihandles. Dies wird in "SQL Server XML-Schlüsselwörter, -Funktionen, -Prozeduren und -Methoden" behandelt .
COMPUTE {aggregation (expression)}[, ... ] [BY expression[, ... ]]

Erzeugt zusätzliche Aggregationen - in der Regel Gesamtsummen -, die am Ende der Ergebnismenge erscheinen. BY expression fügt der Ergebnismenge Zwischensummen und Kontrollumbrüche hinzu. COMPUTE und COMPUTE BY können gleichzeitig in derselben Abfrage verwendet werden. COMPUTE BY muss mit einer ORDER BY Klausel gekoppelt werden, obwohl die expression die von COMPUTE BY verwendet wird, kann eine Teilmenge der order_by_expression. Die Aggregation kann mit einem der folgenden Funktionsaufrufe erfolgen: AVG, COUNT, MAX, MIN, STDEV, STDEVP, VAR, VARP oder SUM. Beispiele werden später in diesem Abschnitt gezeigt.

COMPUTEfunktioniert in keiner Form mit dem Schlüsselwort DISTINCT oder mit den Datentypen TEXT, NTEXT oder IMAGE.

FOR {BROWSE | XML | JSON}

FOR BROWSE wird verwendet, um Aktualisierungen von Daten zu ermöglichen, die mit einem DB-Library-Cursor im Browse-Modus abgerufen wurden. (DB-Library ist die ursprüngliche Zugriffsmethode für SQL Server und wurde inzwischen in den meisten Anwendungen von OLE DB verdrängt.) FOR BROWSE kann nur für Tabellen mit einem eindeutigen Index und einer Spalte mit dem Datentyp TIMESTAMP verwendet werden; sie kann nicht in UNION Anweisungen oder bei aktivem HOLDLOCK Hinweis verwendet werden.

FOR XML und FOR JSON werden verwendet, um die Ergebnismenge als XML-Dokument bzw. als JSON zu extrahieren. In Kapitel 10 erfährst du mehr über die Arbeit mit diesen Formaten.

OPTION (hint[, ... ])
Ersetzt die Elemente des Standard-Abfrageplans durch deinen eigenen. Da der Optimierer in der Regel den besten Abfrageplan für jede Abfrage auswählt, wird dringend davon abgeraten, Optimierer-Hinweise in deine Abfragen einzufügen. Weitere Informationen zu Hints findest du in der SQL Server-Dokumentation.

Hier ist ein Beispiel für die Fähigkeit von SQL Server SELECT ... INTO. In diesem Beispiel wird mit SELECT ... INTO eine Tabelle namens non_mgr_employees erstellt. Die Tabelle enthält die emp_id, den Vornamen und den Nachnamen der einzelnen Nicht-Manager aus der Mitarbeitertabelle, zusammen mit den Stellenbeschreibungen (aus der Tabelle Aufträge ):

SELECT   e.emp_id, e.fname, e.lname,
   SUBSTRING(j.job_desc,1,30) AS job_desc
INTO non_mgr_employee
FROM     employee e
JOIN     jobs AS j ON e.job_id = j.job_id
WHERE    j.job_desc NOT LIKE '%MANAG%'
ORDER BY 2,3,1

Die neu erstellte und geladene Tabelle non_mgr_employee kann jetzt wie jede andere Tabelle abgefragt werden.

SELECT ... INTO wird nicht protokolliert oder kann nicht wiederhergestellt werden und sollte daher nur für Vorgänge verwendet werden, die von Anfang an neu gestartet werden können.

COMPUTE hat eine Reihe von Permutationen, die sich auf die Ergebnismenge auswirken können, die von der Abfrage abgerufen wird. Das folgende Beispiel zeigt die Summe der Buchpreise, aufgeschlüsselt nach Buchtyp und sortiert nach Typ und dann nach Preis:

-- Query
SELECT type, price
FROM titles
WHERE type IN ('business','psychology')
  AND price > 10
ORDER BY type, price
COMPUTE SUM(price) BY type

-- Results
type         price
------------ ---------------------
business     11.9500
business     19.9900
business     19.9900
             sum
             =====================
             51.9300
type         price
------------ ---------------------
psychology   10.9500
psychology   19.9900
psychology   21.5900
             sum
             =====================
             52.5300

Die Klausel COMPUTE verhält sich anders, wenn du BY nicht einschließt. Die folgende Abfrage ermittelt die Gesamtsumme der Preise und Vorschüsse für Bücher mit Preisen über 16,00 $:

-- Query
SELECT type, price, advance
FROM titles
WHERE price > $16
COMPUTE SUM(price), SUM(advance)

-- Results
type         price                 advance
------------ --------------------- ---------------------
business     19.9900               5000.0000
business     19.9900               5000.0000
mod_cook     19.9900               .0000
popular_comp 22.9500               7000.0000
popular_comp 20.0000               8000.0000
psychology   21.5900               7000.0000
psychology   19.9900               2000.0000
trad_cook    20.9500               7000.0000
             sum
             =====================
             165.4500
                                   sum
                                   =====================
                                   41000.0000

Du kannst sogar COMPUTE BY und COMPUTE in der gleichen Abfrage verwenden, um Zwischensummen und Gesamtsummen zu bilden. (Der Kürze halber zeigen wir eine Beispielabfrage, aber nicht die Ergebnismenge). In diesem Beispiel finden wir die Summe der Preise und Vorschüsse nach Typ für Wirtschafts- und Psychologiebücher, die mehr als 16,00 $ kosten:

SELECT type, price, advance
FROM titles
WHERE price > $16
  AND type IN ('business','psychology')
ORDER BY type, price
COMPUTE SUM(price), SUM(advance) BY type
COMPUTE SUM(price), SUM(advance)

Vergiss nicht, dass du die ORDER BY Klausel mit einer COMPUTE BY Klausel einschließen musst! (Du brauchst keine ORDER BY Klausel mit einer einfachen COMPUTE Klausel ohne das BY Schlüsselwort.) Es gibt viele Permutationen, die du in einer einzigen Abfrage durchführen kannst - mehrere COMPUTE und COMPUTE BY Klauseln, GROUP BY mit einer COMPUTE Klausel und sogar COMPUTE mit einer ORDER BY Anweisung. Es macht wirklich Spaß, die verschiedenen Möglichkeiten auszuprobieren, wie du Abfragen mit COMPUTE und COMPUTE BY erstellen kannst. Es ist zwar kein Vergnügungspark, aber was soll's? Dies ist ein Programmierbuch!

SQL Server (und Oracle, das eine etwas andere Technik verwendet) unterstützt auch eine Nicht-SQL-Standardabfrage, die sogenannte Pivot-Abfrage. Obwohl du in der Dokumentation des Herstellers nachlesen solltest, wie du eine Pivot-Abfrage (oder Unpivot-Abfrage) genau schreibst, soll dir das folgende Beispiel helfen, diese nützliche Technik zu nutzen. Eine Pivot-Abfrage stellt die Ergebnismenge auf den Kopf und ermöglicht es dir, mehr Wert aus den Daten zu ziehen. Die folgende Abfrage erzeugt zum Beispiel eine zweispaltige, vierzeilige Ergebnismenge:

-- Query
SELECT days_to_make, AVG(manufacturing_cost) AS Avg_Cost
FROM manufacturing.products
GROUP BY days_to_make;

-- Results
days_to_make  Avg_Cost
0             5
1             225
2             350
4             950

Mit einer Pivot-Abfrage kannst du das Ergebnis dann "auf die Seite drehen", so dass die Werte der days_to_make-Spalte zu den Spaltenüberschriften werden und die Abfrage eine Zeile mit fünf Spalten liefert:

-- Query
SELECT 'Avg_Cost' As Cost_by_Days, [0], [1], [2], [3], [4]
FROM 
   (SELECT days_to_make, manufacturing_cost 
    FROM manufacturing.products)
AS source
PIVOT
   (AVG(manufacturing_cost) 
    FOR days_to_make IN ([0], [1], [2], [3], [4]))
 AS pivottable;

-- Results
Cost_by_Days  0    1    2    3    4
Avg_Cost      5    225  350  NULL 950

Siehe auch

  • JOIN

  • GROUP BY

  • ORDER BY

  • WHERE

  • WITH

SUBQUERY Substatement

Eine Subquery ist eine verschachtelte Abfrage. Subqueries können an verschiedenen Stellen innerhalb einer SQL-Anweisung erscheinen.

Plattform Befehl
MySQL Unterstützt, mit Einschränkungen
Oracle Unterstützt
PostgreSQL Unterstützt
SQL Server Unterstützt

SQL-Standardsyntax

Die verschiedenen Arten von Unterabfragen, die der SQL-Standard unterstützt, werden unter "Regeln auf einen Blick" beschrieben . Skalar-, Tabellen- und verschachtelte Tabellenunterabfragen werden durch die folgende verallgemeinerte Syntax dargestellt:

SELECT column1, column2, ... (scalar_subquery)
FROM table1, ... (nested_table_subquery)
   AS subquery_table_name]
WHERE foo = (scalar_subquery)
   OR foo IN (table_subquery)

Korrelierte Unterabfragen sind komplexer, weil die Werte solcher Unterabfragen von Werten abhängen, die in den Hauptabfragen abgefragt werden. Zum Beispiel:

SELECT column1
FROM   table1 AS t1
WHERE  foo IN
   (SELECT value1
    FROM table2 AS t2
    WHERE t2.pk_identifier = t1.fk_identifier)

Beachte, dass die IN Klausel nur als Beispiel dient. Es kann jeder beliebige Vergleichsoperator verwendet werden.

Schlüsselwörter

scalar_subquery
Nimmt eine skalare Subquery in die SELECT Elementliste oder in die WHERE oder HAVING Klausel einer Abfrage auf.
table_subquery
Beinhaltet eine Tabellen-Subquery nur in der WHERE Klausel, mit Operatoren wie IN, ANY, SOME, EXISTS oder ALL, die auf mehrere Werte wirken. Tabellenunterabfragen geben eine oder mehrere Zeilen zurück, die jeweils einen einzelnen Wert enthalten.
nested_table_subquery
Nimmt eine verschachtelte Tabellenunterabfrage nur in der FROM Klausel in Verbindung mit der AS Klausel auf.

Die Regeln auf einen Blick

Mit Unterabfragen kannst du einen oder mehrere Werte zurückgeben und sie innerhalb einer SELECT, INSERT, UPDATE oder DELETE Anweisung oder innerhalb einer anderen Unterabfrage verschachteln. Unterabfragen können überall dort verwendet werden, wo Ausdrücke erlaubt sind. Unterabfragen können auch oft durch eine JOIN Anweisung ersetzt werden. Je nach DBMS können Subqueries weniger schnell als Joins sein.

Unterabfragen werden immer in Klammern gesetzt.

SQL unterstützt die folgenden Arten von Subqueries:

Skalare Unterabfragen
Subqueries, die einen einzelnen Wert abfragen. Dies ist die von den verschiedenen Datenbankplattformen am häufigsten unterstützte Art von Unterabfragen.
Vektorielle Unterabfragen
Unterabfragen, die eine einzelne Zeile abrufen, die mehr als eine Spalte hat.
Tabellenunterabfragen
Unterabfragen, die mehr als einen Wert oder eine Reihe von Werten abrufen.

Skalare und vektorielle Unterabfragen können auf einigen Plattformen als Teil des Ausdrucks in einer SELECT Liste von Elementen, einer WHERE Klausel oder einer HAVING Klausel erscheinen. Verschachtelte Tabellenunterabfragen erscheinen in der Regel in den FROM Klauseln von SELECT Anweisungen.

Eine korrelierte Subquery ist eine Subquery, die von einem Wert in einer äußeren Abfrage abhängig ist. Folglich wird die innere Abfrage für jeden in der äußeren Abfrage abgerufenen Datensatz einmal ausgeführt. Da Unterabfragen viele Ebenen tief verschachtelt sein können, kann eine korrelierte Unterabfrage auf jede Ebene in der Hauptabfrage verweisen, die höher als ihre eigene Ebene ist.

Je nachdem, in welcher Klausel eine Subquery vorkommt, gelten unterschiedliche Regeln für ihr Verhalten. Auch der Grad der Unterstützung zwischen den Datenbankplattformen variiert: Einige Plattformen unterstützen Unterabfragen in allen oben genannten Klauseln (SELECT, FROM, WHERE und HAVING), während andere nur in einer oder zwei der Klauseln Unterabfragen unterstützen.

Unterabfragen sind normalerweise mit der Anweisung SELECT verbunden. Da Unterabfragen in der WHERE Klausel erscheinen können, können sie in jeder SQL-Anweisung verwendet werden, die eine WHERE Klausel unterstützt, einschließlich SELECT, INSERT ... SELECT, DELETE und UPDATE Anweisungen.

Bestimmte Operatoren in einer WHERE Klausel, wie =, <, >, >=, <= und <> (oder !=), erwarten nur einen Wert. Wenn eine Unterabfrage mehr als einen Wert zurückgibt, der Operator aber nur einen Wert erwartet, schlägt die gesamte Abfrage fehl. In diesen Fällen sollten skalare Subqueries verwendet werden, da sie nur einen einzigen Wert zurückgeben können. Tabellenunterabfragen hingegen können mehrere Werte zurückgeben, aber sie sind nur mit mehrwertigen Ausdrücken wie [NOT] IN, ANY, ALL, SOME oder [NOT] EXISTS verwendbar.

Tabellenunterabfragen können in der FROM Klausel erscheinen und sollten durch die AS Klausel verlinkt werden. Die Ergebnismenge, die von einer Tabellenunterabfrage zurückgegeben wird, wird manchmal auch als abgeleitete Tabelle bezeichnet und bietet ähnliche Funktionen wie ein View (siehe "CREATE/ALTER VIEW-Anweisung" für weitere Informationen zu Views). Jede Spalte, die in der abgeleiteten Tabelle zurückgegeben wird, muss nicht in der Abfrage verwendet werden, obwohl sie alle von der äußeren Abfrage verarbeitet werden können.

Korrelierte Unterabfragen erscheinen normalerweise als Bestandteil einer WHERE - oder HAVING -Klausel in der äußeren Abfrage (und seltener in der SELECT -Elementliste) und werden durch die WHERE -Klausel der inneren Abfrage (d.h. der Unterabfrage) korreliert. Korrelierte Subqueries können auch als Tabellen-Subqueries verwendet werden, obwohl dies weniger üblich ist. Achte darauf, dass eine solche Unterabfrage eine WHERE Klausel enthält, die auf der Grundlage eines korrelierenden Wertes aus der äußeren Abfrage ausgewertet wird; das Beispiel für eine korrelierte Abfrage im früheren SQL-Standardsyntaxdiagramm veranschaulicht diese Anforderung.

Es ist außerdem wichtig, für jede Tabelle, auf die in einer korrelierten Abfrage verwiesen wird, sowohl in der äußeren als auch in der inneren Abfrage einen Tabellenalias, den sogenannten Korrelationsnamen, mit der AS Klausel oder einer anderen Alias-Verknüpfung anzugeben. Korrelationsnamen vermeiden Mehrdeutigkeiten und helfen dem DBMS, die an der Abfrage beteiligten Tabellen schnell aufzulösen.

Alle SQL-Standard-konformen Unterabfragen erfüllen die folgende kurze Liste von Regeln:

  • Eine Unterabfrage kann keine ORDER BY Klausel enthalten.

  • Eine Unterabfrage kann nicht in eine Aggregatfunktion eingeschlossen werden. Die folgende Abfrage ist zum Beispiel ungültig:

SELECT foo FROM table1
WHERE sales >= AVG(SELECT column1 FROM sales_table ...)

Du kannst diese Einschränkung umgehen, indem du die Aggregation in der Subquery und nicht in der äußeren Abfrage durchführst.

Programmiertipps und -schwierigkeiten

Bei den meisten Anbieterplattformen sollten Unterabfragen nicht auf große Objektdatentypen (z. B. CLOB oder BLOB bei Oracle und IMAGE oder TEXT bei SQL Server) oder Array-Datentypen (wie TABLE oder CURSOR bei SQL Server) verweisen.

Die Plattformen unterstützen alle Unterabfragen, aber nicht jeder Anbieter unterstützt alle Arten von Unterabfragen. Tabelle 4-4 fasst die Unterstützung der Anbieter zum Zeitpunkt der Erstellung dieses Artikels zusammen.

Tabelle 4-4. Plattformspezifische Unterstützung von Subqueries
Plattform MySQL Oracle PostgreSQL SQL Server
Skalare Unterabfrage in SELECT item list ✓ ✓ ✓ ✓
Skalare Unterabfrage in WHERE/HAVING Klausel ✓ ✓ ✓ ✓
Vektorielle Unterabfrage in der WHERE/HAVING Klausel ✓ ✓ ✓ ✓
Tabellenunterabfrage in FROM Klausel ✓ ✓ ✓ ✓
Korrelierte Unterabfrage in WHERE/HAVING Klausel ✓ ✓ ✓ ✓

Neben den SELECT Anweisungen können Unterabfragen auch in INSERT, UPDATE und DELETE Anweisungen verwendet werden, die eine WHERE Klausel enthalten. Unterabfragen werden häufig für die folgenden Zwecke verwendet:

  • So identifizieren Sie die Zeilen, die mit einer INSERT ... SELECT -Anweisung, einer CREATE TABLE ... SELECT -Anweisung oder einer SELECT ... INTO -Anweisung in die Zieltabelle eingefügt wurden

  • So identifizieren Sie die Zeilen einer Ansicht oder materialisierten Ansicht in einer CREATE VIEW -Anweisung

  • So identifizieren Sie Werte, die vorhandenen Zeilen zugewiesen wurden, mit einer UPDATE -Anweisung

  • So ermitteln Sie Werte für Bedingungen in den Klauseln WHERE und HAVING der Anweisungen SELECT, UPDATE und DELETE

  • So erstellen Sie eine Ansicht einer Tabelle (d. h. verschachtelte Tabellenunterabfragen)

Beispiele

Dieser Abschnitt zeigt Beispiele für Subqueries, die für MySQL, Oracle, PostgreSQL und SQL Server gleichermaßen gültig sind.

Eine einfache skalare Unterabfrage wird in der SELECT Elementliste der folgenden Abfrage angezeigt:

SELECT job, (SELECT AVG(salary) FROM employee) AS "Avg Sal"
FROM   employee

Tabellenunterabfragen sind funktional gleichwertig mit der Abfrage einer Ansicht. Im Folgenden fragen wir das Bildungsniveau und das Gehalt in einer Tabellen-Subquery ab und führen dann in der äußeren Abfrage Aggregationen an den Werten in der abgeleiteten Tabelle durch:

SELECT AVG(edlevel), AVG(salary)
FROM 
   (SELECT edlevel, salary
    FROM employee) AS emprand
GROUP BY edlevel

Erinnere dich daran, dass diese Abfrage je nach Plattform ohne die AS Klausel fehlschlagen kann, um einen Namen mit der abgeleiteten Tabelle zu verknüpfen.

Die folgende Abfrage zeigt eine Standard-Tabellen-Subquery in der WHERE Klausel Ausdruck. In diesem Fall wollen wir alle Projektnummern für Mitarbeiter in der Abteilung 'A00':

SELECT projno
FROM   emp_act
WHERE  empno IN
   (SELECT empno
    FROM   employee
    WHERE  workdept ='A00')

Die Subquery wird nur einmal für die äußere Abfrage ausgeführt.

Im nächsten Beispiel wollen wir die Namen der Mitarbeiter und ihre Dienstaltersstufe wissen. Diese Ergebnismenge erhalten wir durch eine korrelierte Unterabfrage:

SELECT firstname, lastname,
   (SELECT COUNT(*)
    FROM employee, senior
    WHERE employee.hiredate > senior.hiredate) as senioritype
FROM employee

Im Gegensatz zur vorherigen Subquery wird diese Subquery für jede Zeile, die von der äußeren Abfrage abgerufen wird, einmal ausgeführt. Bei einer solchen Abfrage kann die gesamte Verarbeitungszeit sehr lang sein, da die innere Abfrage möglicherweise viele Male für eine einzige Ergebnismenge ausgeführt wird.

Korrelierte Unterabfragen hängen von Werten ab, die von der äußeren Abfrage abgerufen werden, um die Verarbeitung der inneren Abfrage abzuschließen. Sie sind schwierig zu beherrschen, aber sie bieten einzigartige programmatische Möglichkeiten. Das folgende Beispiel gibt Informationen über Bestellungen zurück, bei denen die verkaufte Menge in jeder Bestellung geringer ist als die durchschnittliche Menge in anderen Verkäufen für diesen Titel:

SELECT s1.ord_num, s1.title_id, s1.qty
FROM sales AS s1
WHERE s1.qty <
   (SELECT AVG(s2.qty)
    FROM sales AS s2
    WHERE s2.title_id = s1.title_id)

In diesem Beispiel kannst du die gleiche Funktion mit einem Self-Join erreichen. Es gibt jedoch Situationen, in denen eine korrelierte Subquery die einzige einfache Möglichkeit ist, das zu erreichen, was du brauchst.

Das nächste Beispiel zeigt, wie eine korrelierte Subquery verwendet werden kann, um Werte in einer Tabelle zu aktualisieren:

UPDATE course SET ends =
   (SELECT min(c.begins) FROM course AS c
    WHERE c.begins BETWEEN course.begins AND course.ends)
WHERE EXISTS
   (SELECT * FROM course AS c
    WHERE c.begins BETWEEN course.begins AND course.ends)

Auf ähnliche Weise kannst du eine Unterabfrage verwenden, um zu bestimmen, welche Zeilen gelöscht werden sollen. In diesem Beispiel wird eine korrelierte Subquery verwendet, um Zeilen aus einer Tabelle anhand von verwandten Zeilen in einer anderen Tabelle zu löschen:

DELETE FROM course
WHERE EXISTS
   (SELECT * FROM course AS c
    WHERE course.id > c.id
    AND (course.begins BETWEEN c.begins
      AND c.ends OR course.ends BETWEEN c.begins AND c.ends))

MySQL

MySQL unterstützt Unterabfragen in der FROM Klausel und in der WHERE Klausel. Er unterstützt skalare Unterabfragen in der SELECT item list.

Oracle

Oracle unterstützt Unterabfragen im SQL-Standard, verwendet aber eine andere Nomenklatur. In Oracle wird eine Tabellen-Subquery, die in der FROM Klausel erscheint, als Inline-View bezeichnet. Das macht Sinn, denn Tabellenunterabfragen sind im Grunde genommen Views, die im laufenden Betrieb erstellt werden. Oracle nennt eine Subquery, die in der WHERE Klausel oder der HAVING Klausel einer Abfrage erscheint, eine verschachtelte Subquery. Es erlaubt korrelierte Unterabfragen in der SELECT Elementliste und in den WHERE und HAVING Klauseln.

PostgreSQL

PostgreSQL unterstützt SQL-Standard-Subqueries in den Klauseln FROM, WHERE und HAVING und erlaubt auch ORDER BY Klauseln zu haben. Unterabfragen, die in einer HAVING Klausel stehen, können jedoch keine ORDER BY, FOR UPDATE oder LIMIT Klauseln enthalten. Sie unterstützt skalare und vektorielle Unterabfragen in der Artikelliste SELECT.

SQL Server

SQL Server unterstützt SQL-Standard-Subqueries. Skalare Unterabfragen können fast überall verwendet werden, wo ein Standardausdruck erlaubt ist. Unterabfragen in SQL Server können nicht die Klauseln COMPUTE oder FOR BROWSE enthalten. Sie können die ORDER BY Klausel enthalten, wenn die TOP Klausel ebenfalls verwendet wird.

Siehe auch

UNION Set Operator

Der UNION set Operator kombiniert die Ergebnismengen von zwei oder mehr Abfragen und zeigt alle Zeilen, die von jeder der Abfragen zurückgegeben wurden, als eine einzige Ergebnismenge an.

UNION gehört zu einer Klasse von Schlüsselwörtern, die als Mengenoperatoren bekannt sind. Andere Mengenoperatoren sind INTERSECT und EXCEPT. Alle Mengenoperatoren werden verwendet, um die Ergebnismengen von zwei oder mehr Abfragen gleichzeitig zu bearbeiten; daher der Begriff "Mengenoperatoren".

Plattform Befehl
MySQL Unterstützt
Oracle Unterstützt, mit Einschränkungen
PostgreSQL Unterstützt, mit Einschränkungen
SQL Server Unterstützt, mit Einschränkungen

SQL-Standardsyntax

Technisch gesehen gibt es keine Beschränkungen für die Anzahl der Abfragen, die du mit der UNION Anweisung kombinieren kannst. Die allgemeine Syntax lautet:

<SELECT statement1>
UNION [ALL | DISTINCT]
<SELECT statement2>
UNION [ALL | DISTINCT]
...

Schlüsselwörter

UNION
Legt fest, welche Ergebnismengen zu einer einzigen Ergebnismenge zusammengefasst werden sollen. Doppelte Zeilen werden standardmäßig ausgeschlossen.
ALL | DISTINCT
Kombiniert doppelte Zeilen aus allen Ergebnismengen (ALL) oder eliminiert doppelte Zeilen aus der endgültigen Ergebnismenge (DISTINCT). Spalten, die einen NULL-Wert enthalten, werden als Duplikate betrachtet. Wenn weder ALL noch DISTINCT verwendet wird, ist das Verhalten von DISTINCT der Standard.

Die Regeln auf einen Blick

Es gibt nur eine wichtige Regel, an die du dich bei der Verwendung von UNION erinnern musst: Die Reihenfolge, Anzahl und Datentypen der Spalten sollten in allen Abfragen gleich sein.

Die Datentypen müssen nicht identisch sein, aber sie sollten kompatibel sein. Zum Beispiel sind CHAR und VARCHAR kompatible Datentypen. Standardmäßig wird die Ergebnismenge auf den größten von zwei (oder mehr) kompatiblen Datentypen eingestellt. Eine Abfrage, die drei CHAR Spalten -CHAR(5), CHAR(10) und CHAR(12)- vereint, zeigt die Ergebnisse im Format CHAR(12) an, wobei die Ergebnisse der kleineren Spalten mit zusätzlichem Leerraum aufgefüllt werden.

Programmiertipps und -schwierigkeiten

Obwohl der SQL-Standard verlangt, dass INTERSECT in einer einzigen Anweisung Vorrang vor anderen Mengenoperatoren hat, werten viele Plattformen alle Mengenoperatoren mit gleichem Vorrang aus. Du kannst den Vorrang von Mengenoperatoren explizit mit Klammern steuern. Andernfalls wertet das DBMS sie wahrscheinlich in der Reihenfolge vom äußersten linken zum äußersten rechten Ausdruck aus.

Je nach Plattform kann die Angabe von DISTINCT zu erheblichen Leistungseinbußen führen, da die Ergebnisse oft ein zweites Mal durchlaufen werden müssen, um doppelte Datensätze auszusortieren. ALL kann in allen Fällen angegeben werden, in denen keine doppelten Datensätze erwartet werden (oder in denen doppelte Datensätze in Ordnung sind), um schnellere Ergebnisse zu erzielen.

Nach dem SQL-Standard ist nur eine ORDER BY Klausel in der gesamten Abfrage erlaubt. Füge sie am Ende der letzten SELECT Anweisung ein. Um die Mehrdeutigkeit von Spalten und Tabellen zu vermeiden, musst du darauf achten, dass die übereinstimmenden Spalten in jeder Tabelle mit denselben Aliasen versehen werden. Für die Spaltenbenennung werden jedoch nur die Aliasnamen in der ersten Abfrage für jede Spalte in der SELECT ... UNION Abfrage verwendet. Zum Beispiel:

SELECT au_lname AS "lastname", au_fname AS "firstname"
FROM authors
UNION
SELECT emp_lname AS "lastname", emp_fname AS "firstname"
FROM employees
ORDER BY lastname, firstname

Auch wenn die Abfragen in deinem UNION kompatible Datentypen haben, kann es Unterschiede im Verhalten der DBMS-Plattformen geben, insbesondere in Bezug auf die Länge der Spalten. Wenn zum Beispiel die Spalte au_lname in der ersten Abfrage deutlich länger ist als die Spalte emp_lname in der zweiten Abfrage, kann es sein, dass verschiedene Plattformen unterschiedliche Regeln anwenden, welche Länge verwendet wird. Im Allgemeinen wählen die Plattformen jedoch die längere (und weniger restriktive) Spaltengröße für die Verwendung in der Ergebnismenge.

Jedes DBMS kann seine eigenen Regeln anwenden, um zu bestimmen, welcher Spaltenname verwendet wird, wenn die Spalten in den Tabellen unterschiedliche Namen haben. Im Allgemeinen werden die Spaltennamen der ersten Abfrage verwendet.

MySQL

MySQL unterstützt die SQL-Standardsyntax vollständig.

Oracle

Oracle unterstützt die Set-Operatoren UNION und UNION ALL mit der grundlegenden SQL-Standardsyntax. UNION DISTINCT wird nicht unterstützt, aber UNION ist das funktionale Äquivalent.

Mit dieser Abfrage kannst du zum Beispiel alle eindeutigen Markt-IDs ohne Duplikate herausfinden:

SELECT stor_id FROM stores
UNION
SELECT stor_id FROM sales;

Oracle unterstützt UNION [ALL] nicht für die folgenden Arten von Abfragen:

  • Abfragen, die Spalten mit den Datentypen LONG, BLOB, CLOB, BFILE, oder VARRAY enthalten

  • Abfragen, die eine FOR UPDATE Klausel oder einen TABLE Sammelausdruck enthalten

Wenn die erste Abfrage in der Set-Operation Ausdrücke in der Elementliste SELECT enthält, füge das Schlüsselwort AS hinzu, um der Spalte, die aus dem Ausdruck resultiert, einen Alias zuzuordnen. Außerdem darf nur die letzte Abfrage in der Set-Operation eine ORDER BY Klausel enthalten.

PostgreSQL

PostgreSQL unterstützt die Set-Operatoren UNION und UNION ALL mit der grundlegenden SQL-Standardsyntax, jedoch nicht bei Abfragen mit einer FOR UPDATE -Klausel. UNION DISTINCT wird nicht unterstützt, aber UNION ist das funktionale Äquivalent.

Die erste Abfrage in der Mengenoperation darf keine ORDER BY Klausel oder LIMIT Klausel enthalten. Nachfolgende Abfragen in der Mengenoperation UNION [ALL] können diese Klauseln enthalten, aber diese Abfragen müssen in Klammern gesetzt werden. Andernfalls wird davon ausgegangen, dass das äußerste rechte Vorkommen von ORDER BY oder LIMIT für die gesamte Mengenoperation gilt.

Mit der folgenden Abfrage könnten wir zum Beispiel alle Autoren und alle Mitarbeiter finden, deren Nachnamen mit "P" beginnen:

SELECT a.au_lname
FROM   authors AS a
WHERE  a.au_lname LIKE 'P%'
UNION
SELECT e.lname
FROM   employee AS e
WHERE  e.lname LIKE 'W%';

SQL Server

SQL Server unterstützt die Set-Operatoren UNION und UNION ALL mit der grundlegenden SQL-Standardsyntax. UNION DISTINCT wird nicht unterstützt, aber UNION ist das funktionale Äquivalent.

Du kannst SELECT ... INTO mit UNION oder UNION ALL verwenden, aber INTO darf nur in der ersten Abfrage der Vereinigung erscheinen. Spezielle Schlüsselwörter wie SELECT TOP und GROUP BY ... WITH CUBE können in allen Abfragen einer Vereinigung verwendet werden, aber wenn du sie in einer Abfrage verwendest, musst du sie in allen Abfragen verwenden. Wenn du SELECT TOP oder GROUP BY ... WITH CUBE in nur einer Abfrage in einer Vereinigung verwendest, schlägt der Vorgang fehl.

Jede Abfrage in einer Vereinigung muss die gleiche Anzahl von Spalten enthalten. Die Datentypen der Spalten müssen nicht identisch sein, aber sie müssen implizit konvertiert werden. Es ist zum Beispiel zulässig, die Spalten VARCHAR und CHAR zu mischen. SQL Server verwendet die größere der beiden Spalten, wenn es um die Größe der Spalten geht, die in der Ergebnismenge zurückgegeben werden. Wenn also eine SELECT ... UNION Anweisung eine CHAR(5) Spalte und eine CHAR(10) Spalte hat, werden die Daten beider Spalten als CHAR(10) Spalte angezeigt. Numerische Spalten werden in den genauesten Datentyp der Vereinigung umgewandelt und angezeigt.

Die folgende Abfrage vereint zum Beispiel die Ergebnisse von zwei unabhängigen Abfragen, die GROUP BY ... WITH CUBE verwenden:

SELECT ta.au_id, COUNT(ta.au_id)
FROM   pubs..titleauthor AS ta
JOIN   pubs..authors     AS a ON a.au_id = ta.au_id
WHERE  ta.au_id >= '722-51-5454'
GROUP  BY ta.au_id WITH CUBE
UNION
SELECT ta.au_id, COUNT(ta.au_id)
FROM   pubs..titleauthor AS ta
JOIN   pubs..authors     AS a ON a.au_id = ta.au_id
WHERE  ta.au_id < '722-51-5454'
GROUP  BY ta.au_id WITH CUBE

Siehe auch

  • EXCEPT

  • INTERSECT

  • SELECT

VALUES-Klausel

Der VALUES Mehrzeilenkonstruktor ist ein Konstruktor, der häufig in INSERT Anweisungen zu finden ist, aber er kann auch in FROM Anweisungen oder überall dort verwendet werden, wo du einen Tabellenausdruck haben kannst, um eine Inline-Tabelle zu erstellen. Wenn er in einer FROM Anweisung verwendet wird, müssen die Spaltennamen und der Tabellenname aliased werden.

Plattform Befehl
MySQL/MariaDB Unterstützt, mit Einschränkungen
Oracle Nicht unterstützt
PostgreSQL Unterstützt
SQL Server Unterstützt, mit Einschränkungen

SQL-Standardsyntax

( VALUES [ROW](<row1 columns>), [ROW](<row2 columns>), ...
   [ROW](<rown columns>) ) 
[AS <table_alias>(column1, column2[,... columnn])]

Stichwörter

(VALUES [ROW](<row1 columns>), [ROW](<row2 columns>), ... [ROW](<rown columns>) )
Definiert eine Reihe von Zeilen. Jede Zeile ist in Klammern eingeschlossen, und die Spaltenwerte werden durch Kommas getrennt. Jede Zeile muss die gleiche Anzahl von Spalten haben.
ROW
Optionales Schlüsselwort, um den Anfang einer Zeile zu kennzeichnen. Einige Datenbanken unterstützen dieses Schlüsselwort nicht, andere verlangen es.
AS <table_alias>(column1, column2[, ... columnn])
Gibt die Namen für die Tabelle und die Spalten an. Wenn nichts angegeben wird, lauten die Standardnamen für Spalten column1, column2, ... column.

Die Regeln auf einen Blick

VALUES Klauseln können allein stehen oder in SELECT Anweisungen, JOIN Klauseln, IN Klauseln, NOT IN Klauseln, DELETE Anweisungen, INSERT ... SELECT Anweisungen, UPDATE Anweisungen und jeder Anweisung enthalten sein, die eine Abfrage oder Unterabfrage haben könnte (wie DECLARE, CREATE TABLE, CREATE VIEW, usw.). Das folgende Beispiel definiert eine virtuelle Tabelle, die aus zwei Zeilen mit zwei benannten Spalten besteht:

SELECT *
FROM
(VALUES ('ABC1', 'Title 1'),
   ('DCF1', 'Title 2')
) AS t(title_id, title);

VALUES kann auch in einem allgemeinen Tabellenausdruck (WITH Klausel) wie folgt verwendet werden:

WITH t(title_id, title) AS (
   VALUES 
      ('ABC1', 'Title 1'),
      ('DCF1', 'Title 2')
)
SELECT * FROM t;

Und sie kann ohne Aliasing verwendet werden, wie folgt:

VALUES ('ABC1', 'Title 1'),
   ('DCF1', 'Title 2');

Das Ergebnis der vorangegangenen Abfragen ist:

ABC1    | Title 1
DCF1    | Title 2

MySQL und MariaDB

MySQL 8 und spätere Versionen unterstützen den VALUES Multirow-Konstruktor, der eigenständig oder innerhalb einer SELECT, INSERT oder UPDATE Anweisung verwendet werden kann. Bei der Verwendung als Tabellenausgabe ist das Schlüsselwort ROW für MySQL nicht optional. Ein Beispiel:

SELECT *
FROM
(VALUES ROW('ABC1', 'Title 1'),
  ROW('DCF1', 'Title 2')
) AS t(title_id, title);

Bei der Verwendung in einer INSERT -Anweisung kann das Schlüsselwort ROW weggelassen werden.

MariaDB unterstützt auch den VALUES Multirow-Konstruktor, der allein, in einer WITH Klausel oder innerhalb einer INSERT Anweisung verwendet werden kann. Das Schlüsselwort ROW wird nicht unterstützt. MariaDB erlaubt Aliasing in einer WITH Klausel, aber nicht, wenn VALUES allein verwendet wird.

Oracle

Oracle unterstützt den VALUES Multirow-Konstruktor nicht.

PostgreSQL

PostgreSQL unterstützt den VALUES Konstruktor vollständig, mit Ausnahme des ROW Schlüsselworts.

SQL Server

SQL Server unterstützt den mehrzeiligen VALUES Konstruktor und erlaubt seine Verwendung in SELECT... FROM und INSERT Anweisungen. Die Verwendung in WITH - oder IN -Klauseln oder im Standalone-Modus wird nicht unterstützt. Auch das optionale Schlüsselwort ROW wird nicht unterstützt. Bei der Verwendung in der FROM Klausel ist eine Umbenennung der Spalten erforderlich.

Siehe auch

  • IN

  • INSERT

  • JOIN

  • SELECT

  • WITH

WHERE-Klausel

Die Klausel WHERE legt die Suchkriterien für eine Operation wie SELECT, UPDATE oder DELETE fest. Alle Datensätze in der/den Zieltabelle(n), die die Suchkriterien nicht erfüllen, werden von der Operation ausgeschlossen. Die Suchbedingungen können viele Variationen enthalten, z. B. Berechnungen, boolesche Operatoren und SQL-Prädikate (z. B. LIKE oder BETWEEN). Alle Plattformen unterstützen die SQL-Standardsyntax.

Plattform Befehl
MySQL Unterstützt
Oracle Unterstützt
PostgreSQL Unterstützt
SQL Server Unterstützt

SQL-Standardsyntax

{ WHERE search_criteria | WHERE CURRENT OF cursor_name }

Schlüsselwörter

WHERE search_criteria
Legt Suchkriterien für die Anweisung fest, um sicherzustellen, dass nur die Zielzeilen betroffen sind.
WHERE CURRENT OF cursor_name
Begrenzt die Operation der Anweisung auf die aktuelle Zeile eines definierten und geöffneten Cursors namens cursor_name.

Die Regeln auf einen Blick

WHERE Klauseln finden sich in SELECT Anweisungen, DELETE Anweisungen, INSERT ... SELECT Anweisungen, UPDATE Anweisungen und jeder Anweisung, die eine Abfrage oder Unterabfrage enthält (wie DECLARE, CREATE TABLE, CREATE VIEW, usw.).

Zu den Suchbedingungen, die alle in eigenen Einträgen an anderer Stelle in diesem Buch beschrieben werden, können gehören:

Alle Datensätze (=ALL, >ALL, <= ALL , SOME/ANY)

Zum Beispiel, um Verleger zu sehen, die in der gleichen Stadt wie ihre Autoren leben:

SELECT pub_name
FROM   publishers
WHERE city = SOME (SELECT city FROM authors);
Kombinationen ( AND, OR und NOT) und Bewertungshierarchie

Zum Beispiel, um alle Autoren zu sehen, die mehr als oder gleich 75 Einheiten verkauft haben, oder Co-Autoren mit einer Tantieme von mehr als oder gleich 60:

SELECT a.au_id
FROM   authors AS a
JOIN titleauthor AS ta ON a.au_id = ta.au_id
WHERE  ta.title_id IN (SELECT title_id FROM sales
                       WHERE qty >= 75)
   OR  (a.au_id IN (SELECT au_id FROM titleauthor
                    WHERE royaltyper >= 60)
  AND  a.au_id IN (SELECT au_id FROM titleauthor
                   WHERE au_ord = 2));
Vergleichsoperatoren (wie =, < >, <, >, <=, und >=)

Zum Beispiel, um die Vor- und Nachnamen von Autoren zu sehen, die keinen Vertrag haben (d.h. Autoren mit einem Vertragswert von 0):

SELECT au_lname, au_fname
FROM   authors
WHERE  contract = 0;
Listen (IN und NOT IN)

Zum Beispiel, um alle Autoren zu sehen, die noch keinen Titel in der Tabelle titleauthor haben:

SELECT au_fname, au_lname
FROM   authors
WHERE au_id NOT IN (SELECT au_id FROM titleauthor);
NULL-Vergleiche (IS NULL und IS NOT NULL)

Zum Beispiel, um Titel zu sehen, die NULL Verkäufe im laufenden Jahr haben:

SELECT title_id, SUBSTRING(title, 1, 25) AS title
FROM   titles
WHERE ytd_sales IS NULL;

Achte darauf, dass du in einer Abfrage nicht = NULL angibst. NULL ist unbekannt und kann nie gleich sein. Die Verwendung von = NULL ist nicht dasselbe wie die Angabe des IS NULL Operators.

Musterübereinstimmungen (LIKE und NOT LIKE)

Zum Beispiel, um Autoren zu sehen, deren Nachnamen mit einem "C" beginnen:

SELECT au_id
FROM   authors
WHERE au_lname LIKE 'C%';
Schießstandbetrieb (BETWEEN und NOT BETWEEN)

Zum Beispiel, um Autoren mit Nachnamen zu sehen, die alphabetisch zwischen "Smith" und "White" liegen:

SELECT au_lname, au_fname
FROM   authors
WHERE  au_lname BETWEEN 'smith' AND 'white';

Programmiertipps und -schwierigkeiten

Die WHERE Klausel kann eine besondere Behandlung erfordern, wenn es um bestimmte Datentypen wie LOBoder bestimmte Zeichensätze, einschließlich Unicode, geht.

Klammern werden verwendet, um die Auswertungshierarchie innerhalb einer WHERE Klausel zu steuern. Wenn du eine Klausel in Klammern einschließt, weist du das DBMS an, diese Klausel vor den anderen auszuwerten. Klammern können verschachtelt werden, um eine Hierarchie von Auswertungen zu erstellen. Die innerste Klammerklausel wird zuerst ausgewertet. Du solltest aus zwei Gründen sehr sorgfältig auf Klammern achten:

  • Du musst immer die gleiche Anzahl von öffnenden und schließenden Klammern haben. Ein Ungleichgewicht in der Anzahl der öffnenden und schließenden Klammern führt zu einem Fehler.

  • Du solltest darauf achten, wo du die Klammern setzt, denn eine falsch gesetzte Klammer kann die Ergebnismenge deiner Abfrage drastisch verändern.

Betrachte zum Beispiel noch einmal die folgende Abfrage, die sechs Zeilen in der Pubs-Datenbank auf der SQL Server-Plattform zurückgibt:

SELECT DISTINCT a.au_id
FROM   authors AS a
JOIN   titleauthor AS ta ON a.au_id = ta.au_id
WHERE  ta.title_id IN (SELECT title_id FROM sales
                       WHERE qty >= 75)
   OR  (a.au_id  IN (SELECT au_id FROM titleauthor
                     WHERE royaltyper >= 60)
  AND   a.au_id  IN (SELECT au_id FROM titleauthor
                     WHERE au_ord = 2))

Die Ausgabe dieser Abfrage sieht folgendermaßen aus:

au_id
-----------
213-46-8915
724-80-9391
899-46-2035
998-72-3567

Wenn du nur einen Satz Klammern änderst, erhältst du andere Ergebnisse:

SELECT DISTINCT a.au_id
FROM   authors AS a
JOIN titleauthor AS ta ON a.au_id = ta.au_id
WHERE  (ta.title_id IN (SELECT title_id FROM sales
                      WHERE qty >= 75)
   OR  a.au_id  IN (SELECT au_id FROM titleauthor
                    WHERE royaltyper >= 60))
 AND  a.au_id  IN (SELECT au_id FROM titleauthor
                   WHERE au_ord = 2)

Diesmal wird die Ausgabe so aussehen:

au_id
-----------
213-46-8915
724-80-9391
899-46-2035

Siehe auch

  • ALL/ANY/SOME

  • BETWEEN

  • DECLARE CURSOR in Kapitel 9
  • DELETE in Kapitel 5

  • EXISTS

  • IN

  • LIKE

  • SELECT

  • UPDATE

MIT-Klausel

Die WITH Klausel definiert eine kurzfristige Ansicht, die für die Dauer einer übergeordneten Abfrage instanziiert wird. Sie kann mit einem Alias verknüpft sein oder auch nicht, um die spätere Referenzierung von der übergeordneten Abfrage (oder Unterabfragen) zu erleichtern. Die temporäre benannte Ergebnismenge, die durch eine WITH Klausel erstellt wird, wird als Common Table Expression (CTE) bezeichnet und auch als Subquery Factoring bezeichnet. CTEs werden nicht wie Standard-Views im Datenbankschema gespeichert, aber sie verhalten sich im Wesentlichen genauso wie diese. Als CTEs in den SQL:1999-Standard aufgenommen wurden, nannte man sie einfach " statement-scoped views". Alle Plattformen unterstützen die SQL-Standardsyntax für die WITH Klausel, einige mit Erweiterungen.

Plattform Befehl
MySQL Unterstützt
Oracle Unterstützt, mit Variationen
PostgreSQL Unterstützt, mit Variationen
SQL Server Unterstützt, mit Variationen

SQL-Standardsyntax

WITH [RECURSIVE] with_query[, ...]
SELECT...

Schlüsselwörter

with_query
Definiert eine Abfrage mit einem Namen, der die Form some_name AS (query​_defini⁠tion) or some_name(col1,col2,col3 ...) AS (query_definition). Es können ein oder mehrere with_querys vor der endgültigen Abfrage stehen. Jedes with_query muss durch ein Komma getrennt werden.
RECURSIVE
Bedeutet, dass der CTE-Stapel Abfragen enthalten kann, die sich selbst aufrufen, in der Regel unter Verwendung der Tail-Rekursion. Dieses Schlüsselwort ist ein syntaktischer Zucker, der dem Abfrageplaner einen Hinweis darauf gibt, dass eine WITH Klausel rekursive Elemente enthält.

Die Regeln auf einen Blick

WITH Klauseln werden verwendet, um komplexe SQL-Abfragen einfacher zu lesen und zu debuggen, indem sie in Teilmengen unterteilt werden. Sie werden auch verwendet, um eine Unterabfrage aufzuteilen, die in mehreren Teilen einer übergeordneten Abfrage wiederverwendet wird (jede CTE ist mit einer verankerten übergeordneten Abfrage verbunden, die wiederum mehrere CTEs haben kann), um rekursive Abfragen zu schreiben und um die Leistung zu verbessern.

Einige Datenbanken ermöglichen beschreibbare CTEs, die Elemente haben, die Daten aktualisieren und die geänderten Daten zurückgeben.

Programmiertipps und -schwierigkeiten

Die WITH Klausel kann sich auf die Abfrageleistung auswirken. Je nach Datenbankanbieter und -version kann eine Abfrage, die mit WITH geschrieben wurde, besser oder schlechter abschneiden als eine ähnliche Abfrage, die verschachtelte SELECT Anweisungen verwendet. Im Zweifelsfall solltest du deine Abfrage mit WITH und ohne WITH schreiben.

Hier ist ein Beispiel für ein nicht-rekursives CTE:

WITH au AS (SELECT au_state AS state, COUNT(*) AS au_count
   FROM authors 
   GROUP BY au_state
   ),
   pu AS (SELECT pub_state AS state, COUNT(*) AS pub_count
      FROM publishers 
   GROUP BY pub_state
   )
SELECT au.state, au.au_count, pu.pub_count
FROM au INNER JOIN pu ON au.state = pu.state;

Eine vollständig SQL-Standard-konforme rekursive CTE, die von 1 bis 20 zählt, könnte so aussehen:

WITH RECURSIVE numbers AS (
   SELECT 1 AS n
   UNION ALL 
   SELECT n + 1
      FROM numbers
   WHERE n+1 <= 20 
)
SELECT *
FROM numbers;

Es gibt keine physische Zahlentabelle; numbers ist ein CTE-Ausdruck, der auf sich selbst aufbaut. Wenn du eine Zahlentabelle in deiner Datenbank hättest, würde trotzdem die CTE-Version verwendet werden, denn bei Namenskonflikten haben CTEs Vorrang.

Rekursive CTEs können nicht DISTINCT enthalten. Einige Datenbanken, wie PostgreSQL, geben einen Fehler aus, wenn du ein Präfix with_query mit RECURSIVE einleitest und sie keine rekursiven Elemente enthält. SQL Server unterstützt zwar rekursive Abfragen, lässt aber das Schlüsselwort RECURSIVE nicht zu.

MySQL und MariaDB

MySQL unterstützt die WITH Klausel seit Version 8.0 vollständig. MariaDB hat die Unterstützung in Version 10.2 eingeführt.

Oracle

Oracle unterstützt den SQL-Standard WITH sowie einen MATERIALIZE Hint, der die Materialisierung einer CTE erzwingt, um die Leistung zu verbessern. Um die Materialisierung zu erzwingen, würdest du etwas schreiben wie:

WITH au AS (SELECT /*+ MATERIALIZE */ au_state, COUNT(*) AS au_count
   FROM authors 
   GROUP BY au_state
   ),
   pu AS (SELECT /*+ MATERIALIZE */ pub_state, COUNT(*) AS pub_count
      FROM publishers 
   GROUP BY pub_state
   )
SELECT au.state, au.au_count, pu.pub_count
FROM au INNER JOIN pu ON au.state = pu.state;

PostgreSQL

PostgreSQL unterstützt vollständig die SQL-Standardklausel WITH und einige Erweiterungen dazu. PostgreSQL erlaubt es, dass CTEs eine oder mehrere INSERT/UPDATE/DELETE Anweisungen enthalten, wenn sie von einer RETURNING Klausel gefolgt werden. Die letzte Abfrage kann auch eine INSERT/UPDATE/DELETE sein, muss aber keine RETURNING Klausel enthalten. Dies ist z. B. nützlich, um gelöschte Datensätze in eine andere Tabelle zu verschieben, wie folgt:

WITH del AS (DELETE
   FROM authors 
   WHERE au_state = 'CA' RETURNING *)
INSERT INTO deleted_authors(au_id)
SELECT del.au_id
FROM del;

PostgreSQL unterstützt auch eine MATERIALIZED/NOT MATERIALIZED Erweiterung des Standards. Um die Materialisierung einer CTE zu erzwingen, wird ihr das Präfix MATERIALIZED vorangestellt, und wenn du die Materialisierung verhindern willst, kannst du ihr das Präfix NOT MATERIALIZED voranstellen:

WITH au AS MATERIALIZED
(SELECT au_state AS state, COUNT(*) AS au_count
   FROM authors 
   GROUP BY au_state
),
pu AS NOT MATERIALIZED 
(SELECT pub_state AS state, COUNT(*) AS pub_count
   FROM publishers 
   GROUP BY pub_state
)
SELECT au.state, au.au_count, pu.pub_count
FROM au INNER JOIN pu ON au.state = pu.state;

WITH RECURSIVE wird auch bei der Definition von Views unterstützt, was das Schreiben von rekursiven Views ermöglicht. PostgreSQL wird sich beschweren, wenn du WITH RECURSIVE verwendest und keine rekursiven Elemente in deinem View hast.

SQL Server

SQL Server unterstützt WITH, lässt aber das Schlüsselwort RECURSIVE nicht zu; er bestimmt intern, ob eine WITH Klausel rekursiv ist oder nicht. SQL Server lässt keine ORDER BY Klauseln in CTEs zu, es sei denn, sie werden in Verbindung mit TOP verwendet. INTO oder OPTION Klauseln mit Query Hints sind ebenfalls nicht zulässig. Im Gegensatz zu anderen Anweisungen, bei denen die ; optional ist, müssen CTEs mit einer ; beginnen, wenn sie Teil eines Satzes von Abfrageanweisungen sind.

Das frühere rekursive CTE würde ohne das Schlüsselwort RECURSIVE wie folgt geschrieben werden:

WITH numbers AS (
   SELECT 1 AS n
   UNION ALL 
   SELECT n + 1
      FROM numbers
   WHERE n+1 <= 20 
)
SELECT *
FROM numbers;

Siehe auch

  • ALTER/CREATE VIEW

  • RETURNING in Kapitel 5

  • SELECT

  • SUBQUERY

MIT ORDINALITÄTSKLAUSEL

Die WITH ORDINALITY Klausel fügt dem Ergebnis einer set-returning Funktion eine inkrementierende Integer-Spalte hinzu. Der Name der Spalte ist gewöhnlich, es sei denn, sie wird umbenannt. Sie wird in der Regel in der FROM oder JOIN Klausel verwendet und wird häufig in Verbindung mit der UNNEST Funktion verwendet, um Array-Daten zu erweitern und zu nummerieren.

Plattform Befehl
MySQL Nicht unterstützt
Oracle Nicht unterstützt
PostgreSQL Unterstützt
SQL Server Nicht unterstützt

SQL-Standardsyntax

set_returning_function_call WITH ORDINALITY [AS ..]

Schlüsselwörter

set_returning_function_call
Definiert einen Funktionsaufruf, zum Beispiel UNNEST(somevalue).

Die Regeln auf einen Blick

WITH ORDINALITY wird verwendet, um die Ergebnisse einer Funktion zu nummerieren, die eine Menge zurückgibt. Bei regulären SELECT Abfragen würdest du stattdessen ROW_NUMBER() OVER() für die Nummerierung verwenden.

Programmiertipps und -schwierigkeiten

Hier ist ein PostgreSQL-Beispiel für die Verwendung von WITH ORDINALITY zur Nummerierung eines Arrays von Werten:

SELECT *
FROM unnest(ARRAY['PC8888','BU1032',
                 'PS7777','PS3333','BU1111']
            ) WITH ORDINALITY AS title_id;

Die Ausgabe dieser Abfrage ist:

title_id | ordinality
----------------------
PC8888   |  1
BU1032   |  2
PS7777   |  3
PS3333   |  4
BU1111   |  5

Du kannst die Ausgabe der Spalten auch mit Hilfe von Aliasen umbenennen, wie folgt:

SELECT *
FROM unnest(ARRAY['PC8888','BU1032',
   'PS7777','PS3333','BU1111']
) WITH ORDINALITY AS title_id(id, ord);

WITH ORDINALITY wird oft in einer JOIN Klausel oder einer LATERAL JOIN Klausel verwendet.

Siehe auch

  • JOIN

  • SELECT

Get SQL in a Nutshell, 4. Auflage now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial