Unterabfragen in SQL  (MySQL )

Skalare Abfragen

Ein Skalar (wie eine Zahl oder Zeichenfolgen) ist hier für uns dasselbe wie eine Tabelle mit genau einer Zelle.

Eine Abfrage liefert einen Skalar, wenn sie eine Tabelle mit genau einer Zeile und einer Spalte liefert.

Konsole

SELECT 1;

+---+
| 1 |
+---+
| 1 |
+---+

Abfragen als Ausdrücke

Man kann eine SELECT-Abfrage als Ausdruck  verwenden, indem man die Abfrage (ohne ein eventuelles Semikolon »;« am Ende) in runden Klammern einschließt.

Solch eine Abfrage wird Unterabfrage  genannt.

Wenn die Unterabfrage eine Tabelle mit genau einer Zeile und einer Spalte ergibt, so nennen wir sie eine skalare Unterabfrage. In einem Kontext, in dem ein Skalar erwartet wird, steht solch eine Unterabfrage dann für den Skalar in ihrer einzigen Zelle. In diesem Text beschäftigen wir uns zunächst mit skalaren Unterabfragen.

So wird aus der Abfrage »SELECT 1;« die Unterabfrage »( SELECT 1 )«.

Der Wert dieses Ausdrucks ist dann das Ergebnis jener Abfrage, hier also effektiv die Zahl 1. Die Unterabfrage »( SELECT 1 )« kann also in der Regel wie der Wert »1« verwendet werden.

Solch eine Unterabfrage kann überall verwendet werden, wo auch ein anderer Ausdruck mit diesem Wert verwendet werden kann (jedenfalls hinter »SELECT«, »FROM« oder »WHERE«).

Konsole

SELECT ( SELECT 1 );

+--------------+
| ( SELECT 1 ) |
+--------------+
|            1 |
+--------------+

SELECT 2 *( SELECT 1 );

+-----------------+
| 2 *( SELECT 1 ) |
+-----------------+
|               2 |
+-----------------+

Statistikfunktionen in Unterabfragen

DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE PLANET
( NAME   CHAR( 8 ) NOT NULL,
  MASSE  DOUBLE    NOT NULL COMMENT 'Einheit: kg' )
COMMENT = 'Der Planet NAME hat die Masse MASSE.';
INSERT INTO PLANET ( NAME,      MASSE    )
            VALUES ( 'Merkur',  3.302E23 ),
                   ( 'Venus',   4.896E24 ),
                   ( 'Erde',    5.974E24 ),
                   ( 'Mars',    6.419E23 ),
                   ( 'Jupiter', 1.899E27 ),
                   ( 'Saturn',  5.686E26 ),
                   ( 'Uranus',  8.698E25 ),
                   ( 'Neptun',  1.024E26 ),
                   ( 'Pluto',   1.250E22 );
SELECT * FROM PLANET;

Welcher Planet hat die größte Masse? Genauer: Wie lautet der Name des Planeten mit der größten Masse?

SELECT NAME FROM PLANET WHERE MASSE =( SELECT MAX( MASSE ) FROM PLANET );
+---------+
| NAME    |
+---------+
| Jupiter |
+---------+

Um weiterhin alle Zeilen zu sehen, kann eine Unterabfrage verwendet werden.

mysql> SELECT NAME, MASSE,( SELECT SUM( MASSE ) FROM PLANET ) AS 'SUMME' FROM PLANET;

+---------+----------+-----------------------+
| NAME    | MASSE    | SUMME                 |
+---------+----------+-----------------------+
| Merkur  | 3.302e23 | 2.6688346000000003e27 |
| Venus   | 4.896e24 | 2.6688346000000003e27 |
| Erde    | 5.974e24 | 2.6688346000000003e27 |
| Mars    | 6.419e23 | 2.6688346000000003e27 |
| Jupiter | 1.899e27 | 2.6688346000000003e27 |
| Saturn  | 5.686e26 | 2.6688346000000003e27 |
| Uranus  | 8.698e25 | 2.6688346000000003e27 |
| Neptun  | 1.024e26 | 2.6688346000000003e27 |
| Pluto   |  1.25e22 | 2.6688346000000003e27 |
+---------+----------+-----------------------+

/   Prozentualer Anteil *

Die folgende Tabelle gibt an, wie viele Stimmen eine Partei bei Wahlen erhielt. Schreiben Sie eine Abfrage, welche diese Tabelle ausgibt, wobei eine Spalte hinzugefügt werden soll, die den prozentualen Anteil der Stimmen der jeweiligen Partei angibt.

DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE WAHLERGEBNIS
( NAME    CHAR( 3 )     NOT NULL,
  STIMMEN DECIMAL( 15 ) NOT NULL )
COMMENT = 'Die Partei NAME erhielt STIMMEN Stimmen.';
INSERT INTO WAHLERGEBNIS ( NAME,  STIMMEN )
                  VALUES ( 'ABC', 504780  ),
                         ( 'DEF', 328141  ),
                         ( 'GHI', 891792  );
SELECT * FROM WAHLERGEBNIS;
+------+---------+
| NAME | STIMMEN |
+------+---------+
| ABC  |  504780 |
| DEF  |  328141 |
| GHI  |  891792 |
+------+---------+

Unterabfragen und Views

Man kann Unterabfragen mit Views vergleichen.

Ein View ist praktisch eine unter einem Namen abgespeicherte Unterabfrage.

Wir können die Unterabfrage »( SELECT 1 )« auch als View »EINS« speichern und dann verwenden.

Da man den Namen eines Views aber nicht direkt als Ausdruck verwenden kann, ist es in diese Fall nötig noch einmal umständlich »SELECT * FROM« davorzuschreiben.

Konsole

WARNINGS; SET sql_mode = 'ANSI,TRADITIONAL';
DROP SCHEMA IF EXISTS S; CREATE SCHEMA S; USE S;
CREATE VIEW EINS AS SELECT 1;
SELECT ( SELECT * FROM EINS );

+------------------------+
| ( SELECT * FROM EINS ) |
+------------------------+
|                      1 |
+------------------------+

Unterabfragen in der FROM-Klausel

Konsole

SELECT * FROM ( SELECT 1 );

ERROR 1248 (42000): Every derived table must have its own alias

SELECT * FROM ( SELECT 1 ) AS U;

+---+
| 1 |
+---+
| 1 |
+---+

Views dürfen in MySQL keine Unterabfragen in der FROM-Klausel enthalten!

Konsole

WARNINGS; SET sql_mode = 'ANSI,TRADITIONAL';
DROP SCHEMA IF EXISTS S; CREATE SCHEMA S; USE S;
CREATE VIEW U1 AS SELECT * FROM ( SELECT 1 ) AS U;
ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause

Man kann aber in vielen Fällen Unterabfragen als extra View auslagern oder Abfragen mit Unterabfragen als Join formulieren.

Unterabfragen in der FROM-Klausel (Beispiel)

Skript (MySQL )

WARNINGS; SET sql_mode = 'ANSI,TRADITIONAL';
DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE POSTEN ( NAME VARCHAR( 255 ), ANZAHL INTEGER, WERT INTEGER, PRIMARY KEY( NAME ));
INSERT INTO POSTEN ( NAME, ANZAHL, WERT ) VALUES ( 'Tisch', 1, 2800 );
INSERT INTO POSTEN ( NAME, ANZAHL, WERT ) VALUES ( 'Stuhl', 6, 720 );
SELECT * FROM POSTEN;

Die Tabelle »POSTEN« soll um den Gesamtwert, die Mehrwertsteuer und den Bruttobetrag für jeden Posten erweitert werden.

Konsole

SELECT *, ANZAHL * WERT AS GESAMT FROM POSTEN;

+-------+--------+------+--------+
| NAME  | ANZAHL | WERT | GESAMT |
+-------+--------+------+--------+
| Stuhl |      6 |  720 |   4320 |
| Tisch |      1 | 2800 |   2800 |
+-------+--------+------+--------+

Ein Problem bei der obigen Abfrage bestand darin, daß man in derselben Auswahlliste nicht auf den Wert der berechneten Spalte »GESAMT« zugreifen kann und daher den Ausdruck »ANZAHL * WERT« bei Bedarf stets wiederholen muß.

Konsole

SELECT *, ANZAHL * WERT AS GESAMT, ANZAHL * WERT * .11 AS MWST, ANZAHL * WERT * 1.11 AS BRUTTO FROM POSTEN;

+-------+--------+------+--------+--------+---------+
| NAME  | ANZAHL | WERT | GESAMT | MWST   | BRUTTO  |
+-------+--------+------+--------+--------+---------+
| Stuhl |      6 |  720 |   4320 | 475.20 | 4795.20 |
| Tisch |      1 | 2800 |   2800 | 308.00 | 3108.00 |
+-------+--------+------+--------+--------+---------+

In einer äußeren Abfrage kann man aber nun auf den Wert aller Spalten der inneren Abfrage über ihren Namen zugreifen, auch auf die berechneten Spalten. (Dies wäre auch ähnlich mit einem View möglich.)

Konsole

SELECT *, GESAMT * .11 AS MWST FROM 
( SELECT *, ANZAHL * WERT AS GESAMT FROM POSTEN ) U;

+-------+--------+------+--------+--------+
| NAME  | ANZAHL | WERT | GESAMT | MWST   |
+-------+--------+------+--------+--------+
| Stuhl |      6 |  720 |   4320 | 475.20 |
| Tisch |      1 | 2800 |   2800 | 308.00 |
+-------+--------+------+--------+--------+

Wir können oben in der äußeren Abfrage über ihren Namen »GESAMT« auf den Wert der berechneten Spalte der inneren Abfrage zugreifen.

Dies entspricht der Definition einer Konstanten »GESAMT« und ihrer späteren Verwendung in einer prozeduralen Programmiersprache.

Die in der äußeren Abfrage definierte neue Spalte »MWST« kann nun ihrerseits wiederum in einer Abfrage verwendet werden, die sich noch weiter außen befindet.

Konsole

SELECT *, GESAMT + MWST AS BRUTTO FROM
( SELECT *, GESAMT * .11 AS MWST FROM
  ( SELECT *, ANZAHL * WERT AS GESAMT FROM POSTEN ) U ) V;

+-------+--------+------+--------+--------+---------+
| NAME  | ANZAHL | WERT | GESAMT | MWST   | BRUTTO  |
+-------+--------+------+--------+--------+---------+
| Stuhl |      6 |  720 |   4320 | 475.20 | 4795.20 |
| Tisch |      1 | 2800 |   2800 | 308.00 | 3108.00 |
+-------+--------+------+--------+--------+---------+

Alles ist genau in der anderen Reihenfolge geschrieben worden als in einer prozeduralen Sprache, in welcher man sinngemäß folgendes schreiben würde:

Prozedurale Sprache

GESAMT := ANZAHL * WERT;
MWST   := GESAMT * .11;
BRUTTO := GESAMT + MWST;
PRINT *, GESAMT, MWST, BRUTTO;

Unterabfragen in der FROM-Klausel (Beispiel)

Skript (MySQL )

WARNINGS; SET sql_mode = 'ANSI,TRADITIONAL';
DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE NOTEN ( NAME VARCHAR( 255 ), ARBEIT INTEGER, NOTE INTEGER );
INSERT INTO NOTEN ( NAME, ARBEIT, NOTE ) VALUES ( 'Hans',  1, 2 );
INSERT INTO NOTEN ( NAME, ARBEIT, NOTE ) VALUES ( 'Hans',  2, 4 );
INSERT INTO NOTEN ( NAME, ARBEIT, NOTE ) VALUES ( 'Hans',  3, 4 );
INSERT INTO NOTEN ( NAME, ARBEIT, NOTE ) VALUES ( 'Lotte', 1, 3 );
INSERT INTO NOTEN ( NAME, ARBEIT, NOTE ) VALUES ( 'Lotte', 2, 3 );
INSERT INTO NOTEN ( NAME, ARBEIT, NOTE ) VALUES ( 'Lotte', 3, 3 );
SELECT * FROM NOTEN;
+-------+--------+------+
| NAME  | ARBEIT | NOTE |
+-------+--------+------+
| Hans  |      1 |    2 |
| Hans  |      2 |    4 |
| Hans  |      3 |    4 |
| Lotte |      1 |    3 |
| Lotte |      2 |    3 |
| Lotte |      3 |    3 |
+-------+--------+------+

Was ist der beste Notendurchschnitt?

SELECT NAME, AVG( NOTE ) AS MITTELWERT FROM NOTEN GROUP BY NAME;

+-------+-------------+
| NAME  | AVG( NOTE ) |
+-------+-------------+
| Hans  |      3.3333 |
| Lotte |      3.0000 |
+-------+-------------+

SELECT MIN( MITTELWERT ) FROM ( SELECT NAME, AVG( NOTE ) AS MITTELWERT FROM NOTEN GROUP BY NAME ) U;

+-------------------+
| MIN( MITTELWERT ) |
+-------------------+
|            3.0000 |
+-------------------+

Welcher Schüler hat den besten Notendurchschnitt?

SELECT NAME FROM 
( SELECT NAME, AVG( NOTE ) AS MITTELWERT FROM NOTEN GROUP BY NAME ) P
WHERE
P.MITTELWERT = 
( SELECT MIN( MITTELWERT ) FROM ( SELECT NAME, AVG( NOTE ) AS MITTELWERT FROM NOTEN GROUP BY NAME ) I );

+-------+
| NAME  |
+-------+
| Lotte |
+-------+

Hier wird ein Teil wiederholt, was man .verhindern kann, indem man diesen als View definiert.

Unterabfragen in der FROM-Klausel (Beispiel)

Die Berechnung des Osterdatums nach Gauß, auch ein Beispiel für den Einsatz von »DIV« und »%«.

DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE YEAR ( YEAR INTEGER );
INSERT INTO YEAR ( YEAR ) VALUES 
( '1980' ), ( '1981' ), ( '1982' ), ( '1983' ), ( '1984' ), 
( '1985' ), ( '1986' ), ( '1987' ), ( '1988' ), ( '1989' ),
( '1990' ), ( '1991' ), ( '1992' ), ( '1993' ), ( '1994' ), 
( '1995' ), ( '1996' ), ( '1997' ), ( '1998' ), ( '1999' ),
( '2000' ), ( '2001' ), ( '2002' ), ( '2003' ), ( '2004' ), 
( '2005' ), ( '2006' ), ( '2007' ), ( '2008' ), ( '2009' ),
( '2010' ), ( '2011' ), ( '2012' ), ( '2013' ), ( '2014' ), 
( '2015' ), ( '2016' ), ( '2017' ), ( '2018' ), ( '2019' ),
( '2020' ), ( '2021' ), ( '2022' ), ( '2023' ), ( '2024' ), 
( '2025' ), ( '2026' ), ( '2027' ), ( '2028' ), ( '2029' );
SELECT YEAR, N AS MONTH,( H - M + R + N + 19 )% 32 AS DAY FROM ( 
SELECT YEAR,( H - M + R + 90 )DIV 25 AS N, H, M, R FROM ( 
SELECT YEAR,( 2 * E + 2 * J - K - H + M + 32)% 7 AS R, H, M FROM ( 
SELECT YEAR,( A + 11 * H )DIV 319 AS M, E, J, K, H FROM ( 
SELECT YEAR, A, C, E,( 19 * a + b - d - g + 15 )% 30 AS H, C DIV 4 AS J, C % 4 AS K FROM ( 
SELECT YEAR, A, B, C, B DIV 4 AS D, B % 4 AS E,( 8 * B + 13 )DIV 25 AS G FROM ( 
SELECT YEAR, YEAR % 19 AS A, YEAR DIV 100 AS B, YEAR % 100 AS C FROM YEAR )AS T1 )AS T2 )AS T3 )AS T4 )AS T5 )AS T6;

Die Pflicht zur Angabe der eigentlich nicht benötigten Tabellennamenaliasse macht sich hier störend bemerkbar, zumal die AS-Klauseln zu weit enfernten SELECT-Klauseln gehören.

Auf Einrückung nach Klammern wurde hier bewußt verzichtet, da die Verschachtelung hier nur linear ist.

Unterabfragen in der WHERE-Klausel

Konsole

WARNINGS; SET sql_mode = 'ANSI,TRADITIONAL';
DROP SCHEMA IF EXISTS S; CREATE SCHEMA S; USE S;
CREATE TABLE PERSON ( VORNAME VARCHAR ( 255 ), NACHNAME VARCHAR ( 255 ), NOTE INT );
INSERT INTO PERSON ( VORNAME, NACHNAME, NOTE ) VALUES ( 'Emma',      'Schneider', 1 );
INSERT INTO PERSON ( VORNAME, NACHNAME, NOTE ) VALUES ( 'Finn',      'Weber',     2 );
INSERT INTO PERSON ( VORNAME, NACHNAME, NOTE ) VALUES ( 'Charlotte', 'Wagner',    2 );
SELECT * FROM PERSON;

+-----------+-----------+------+
| VORNAME   | NACHNAME  | NOTE |
+-----------+-----------+------+
| Emma      | Schneider |    1 |
| Finn      | Weber     |    2 |
| Charlotte | Wagner    |    2 |
+-----------+-----------+------+

SELECT * FROM PERSON WHERE NOTE = ( SELECT 1 );
+---------+-----------+------+
| VORNAME | NACHNAME  | NOTE |
+---------+-----------+------+
| Emma    | Schneider |    1 |
+---------+-----------+------+

Unterabfragen in anderen Klausel

Eine Unterabfrage kann nicht unbedingt in anderen Klausel  als in den oben genannten verwendet werden.

Verwendet man sie beispielsweise in einer ORDER-BY-Klausel, erhält man zwar keine Diagnose-Meldung, aber es wird nicht so sortiert wie bei Verwendung des Numerales »1« an Stelle der Unterabfrage.

Konsole

WARNINGS; SET sql_mode = 'ANSI,TRADITIONAL';
DROP SCHEMA IF EXISTS S; CREATE SCHEMA S; USE S;
CREATE TABLE PERSON ( VORNAME VARCHAR ( 255 ), NACHNAME VARCHAR ( 255 ), NOTE INT );
INSERT INTO PERSON ( VORNAME, NACHNAME, NOTE ) VALUES ( 'Emma',      'Schneider', 1 );
INSERT INTO PERSON ( VORNAME, NACHNAME, NOTE ) VALUES ( 'Finn',      'Weber',     2 );
INSERT INTO PERSON ( VORNAME, NACHNAME, NOTE ) VALUES ( 'Charlotte', 'Wagner',    2 );
SELECT * FROM PERSON ORDER BY 1;

+-----------+-----------+------+
| VORNAME   | NACHNAME  | NOTE |
+-----------+-----------+------+
| Charlotte | Wagner    |    2 |
| Emma      | Schneider |    1 |
| Finn      | Weber     |    2 |
+-----------+-----------+------+

SELECT * FROM PERSON ORDER BY ( SELECT 1 );

+-----------+-----------+------+
| VORNAME   | NACHNAME  | NOTE |
+-----------+-----------+------+
| Emma      | Schneider |    1 |
| Finn      | Weber     |    2 |
| Charlotte | Wagner    |    2 |
+-----------+-----------+------+

Sortieren in Unterabfragen

Eine ORDER-BY-Klausel in einer Unterabfrage ist in der Regel nicht sinnvoll. Sie wird von vielen Datenbanksystemen ignoriert oder nicht gestattet.

Konstante und Variable Ausdrücke

Ein konstanter Ausdruck hat in jeder Zeile der Ergebnistabelle denselben Wert.

Konsole

WARNINGS; SET sql_mode = 'ANSI,TRADITIONAL';
DROP SCHEMA IF EXISTS S; CREATE SCHEMA S; USE S;
CREATE TABLE PERSON ( VORNAME VARCHAR ( 255 ), NACHNAME VARCHAR ( 255 ), NOTE INT );
INSERT INTO PERSON ( VORNAME, NACHNAME, NOTE ) VALUES ( 'Emma',      'Schneider', 1 );
INSERT INTO PERSON ( VORNAME, NACHNAME, NOTE ) VALUES ( 'Finn',      'Weber',     2 );
INSERT INTO PERSON ( VORNAME, NACHNAME, NOTE ) VALUES ( 'Charlotte', 'Wagner',    2 );
SELECT 1 FROM PERSON;

+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
+---+

Ein variabler Ausdruck kann in jeder Zeile der Ergebnistabelle einen anderen Wert haben.

Konsole

SELECT NOTE FROM PERSON;

+------+
| NOTE |
+------+
|    1 |
|    2 |
|    2 |
+------+

Konstante und Variable Unterabfagen

Wenn eine Unterabfrage nur konstante Ausdrücke enthält (also solche Ausdrücke, welche nicht von der Zeile einer sie enthaltenden Abfrage abhängen), dann ist es eine konstante Unterabfrage, deren Wert im Grund einmal vor der Auswertung der Hauptabfrage berechnet werden kann.

Konsole

WARNINGS; SET sql_mode = 'ANSI,TRADITIONAL';
DROP SCHEMA IF EXISTS S; CREATE SCHEMA S; USE S;
CREATE TABLE PERSON ( VORNAME VARCHAR ( 255 ), NACHNAME VARCHAR ( 255 ), NOTE INT );
INSERT INTO PERSON ( VORNAME, NACHNAME, NOTE ) VALUES ( 'Emma',      'Schneider', 1 );
INSERT INTO PERSON ( VORNAME, NACHNAME, NOTE ) VALUES ( 'Finn',      'Weber',     2 );
INSERT INTO PERSON ( VORNAME, NACHNAME, NOTE ) VALUES ( 'Charlotte', 'Wagner',    2 );
SELECT ( SELECT 1 ) FROM PERSON;

+--------------+
| ( SELECT 1 ) |
+--------------+
|            1 |
|            1 |
|            1 |
+--------------+

Wenn eine Unterabfrage einen variablen Ausdruck enthält (also einen Ausdrucke, welcher von der Zeile einer sie enthaltenden Abfrage abhängen könnte), dann ist es eine variable Unterabfrage, deren Wert im allgemeinen für jede Zeile einer sie enthaltenden Tabelle erneut berechnet werden muß.

Das folgende Beispiel zeigt eine variable Unterabfrage.

Konsole

SELECT ( SELECT NOTE ) FROM PERSON;

+-----------------+
| ( SELECT NOTE ) |
+-----------------+
|               1 |
|               2 |
|               2 |
+-----------------+

Man nennt die konstanten Unterabfragen auch nicht-korrelierte Unterabfragen  und die variablen Unterabfragen auch korrelierte Unterabfragen.

Beispiel einer nicht-korrelierten Unterabfrage

Ermittle die Kennzahl des Planeten, dessen Name alphabetisch am weitesten hinten steht.

Dieses Beispiel zeigt, daß man den Namen des Planeten zunächst mit einer Abfrage ermitteln kann. Das Ergebnis »Venus« kann alsdann manuell in eine Abfrage eingesetzt werden, welche die Kennzahl jenes Namens ergibt.

Stattdessen kann man aber auch die erste Abfrage direkt in die zweite Abfrage einsetzen und spart so den manuellen Übertrag des Ergebnisses.

Konsole

DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE ZAHL ( I VARCHAR ( 255 ), DE VARCHAR ( 255 ), EN VARCHAR ( 255 )); 
INSERT INTO ZAHL ( I, DE, EN ) VALUES ( 0, 'NULL', 'ZERO'  );
INSERT INTO ZAHL ( I, DE, EN ) VALUES ( 1, 'EINS', 'ONE'   );
INSERT INTO ZAHL ( I, DE, EN ) VALUES ( 2, 'ZWEI', 'TWO'   );
INSERT INTO ZAHL ( I, DE, EN ) VALUES ( 3, 'DREI', 'THREE' );
CREATE TABLE PLANET ( PLANET VARCHAR( 255 ), NAME VARCHAR( 255 ));
INSERT INTO PLANET ( PLANET, NAME ) VALUES ( '1', 'Merkur' );
INSERT INTO PLANET ( PLANET, NAME ) VALUES ( '2', 'Venus'  );
INSERT INTO PLANET ( PLANET, NAME ) VALUES ( '3', 'Erde'   );
SELECT * FROM ZAHL;
SELECT * FROM PLANET;
SELECT MAX( NAME ) FROM PLANET;

+-------------+
| MAX( NAME ) |
+-------------+
| Venus       |
+-------------+

SELECT PLANET FROM PLANET WHERE NAME = 'Venus';

+--------+
| PLANET |
+--------+
| 2      |
+--------+

SELECT PLANET FROM PLANET WHERE NAME = ( SELECT MAX( NAME ) FROM PLANET );

+--------+
| PLANET |
+--------+
| 2      |
+--------+

EXPLAIN SELECT PLANET FROM PLANET WHERE NAME = ( SELECT MAX( NAME ) FROM PLANET );

+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY     | PLANET | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
|  2 | SUBQUERY    | PLANET | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL        |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

Nicht-korrelierte Unterabfragen kann man vor dem Einsetzen in die Oberabfrage isoliert ausprobieren.

Beispiel einer korrelierten Unterabfrage

SELECT ( SELECT DE FROM ZAHL WHERE I = PLANET ), NAME FROM PLANET;

+------------------------------------------+--------+
| ( SELECT DE FROM ZAHL WHERE I = PLANET ) | NAME   |
+------------------------------------------+--------+
| EINS                                     | Merkur |
| ZWEI                                     | Venus  |
| NULL                                     | Erde   |
+------------------------------------------+--------+

EXPLAIN SELECT ( SELECT DE FROM ZAHL WHERE I = PLANET ), NAME FROM PLANET;

+----+--------------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | PLANET | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL        |
|  2 | DEPENDENT SUBQUERY | ZAHL   | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+--------------------+--------+------+---------------+------+---------+------+------+-------------+

Korrelierte Unterabfragen kann man vor dem Einsetzen in die Oberabfrage nicht isoliert ausprobieren, da sie Namen enthalten, welche sich auf die Oberabfrage beziehen und daher ohne die Oberabfrage nicht einzeln verwendbar sind.

Beispiel

DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE TEMPERATUR
( ORT VARCHAR ( 255 ) NOT NULL,
  MON CHAR ( 3 )      NOT NULL,
  MIN INT ( 3 )       NOT NULL,
  MAX INT ( 3 )       NOT NULL)
COMMENT = "Am Orte ORT ist MIN die Minimaltemperatur und MAX die Maximaltemperatur im Monat MON.";
INSERT INTO TEMPERATUR ( ORT, MON, MIN, MAX ) 
                VALUES ( 'Gardasee', 'Jan', -1,  5 ),
                       ( 'Gardasee', 'Feb',  0,  8 ),
                       ( 'Gardasee', 'Mar',  4, 13 ),
                       ( 'Gardasee', 'Apr',  8, 17 ),
                       ( 'Gardasee', 'Mai', 11, 22 ),
                       ( 'Gardasee', 'Jun', 15, 26 ),
                       ( 'Gardasee', 'Jul', 17, 29 ),
                       ( 'Gardasee', 'Aug', 16, 26 ),
                       ( 'Gardasee', 'Sep', 14, 25 ),
                       ( 'Gardasee', 'Okt', 10, 18 ),
                       ( 'Gardasee', 'Nov',  5, 11 ),
                       ( 'Gardasee', 'Dez',  2,  7 ),
                       ( 'Korsika',  'Jan',  3, 13 ),
                       ( 'Korsika',  'Feb',  5, 14 ),
                       ( 'Korsika',  'Mar',  6, 15 ),
                       ( 'Korsika',  'Apr',  6, 19 ),
                       ( 'Korsika',  'Mai',  9, 22 ),
                       ( 'Korsika',  'Jun', 14, 23 ),
                       ( 'Korsika',  'Jul', 15, 27 ),
                       ( 'Korsika',  'Aug', 17, 28 ),
                       ( 'Korsika',  'Sep', 14, 25 ),
                       ( 'Korsika',  'Okt', 10, 21 ),
                       ( 'Korsika',  'Nov',  7, 17 ),
                       ( 'Korsika',  'Dez',  4, 14 );
SELECT * FROM TEMPERATUR;

+----------+-----+-----+-----+
| ORT      | MON | MIN | MAX |
+----------+-----+-----+-----+
| Gardasee | Jan |  -1 |   5 |
| Gardasee | Feb |   0 |   8 |
| Gardasee | Mar |   4 |  13 |
| Gardasee | Apr |   8 |  17 |
| Gardasee | Mai |  11 |  22 |
| Gardasee | Jun |  15 |  26 |
| Gardasee | Jul |  17 |  29 |
| Gardasee | Aug |  16 |  26 |
| Gardasee | Sep |  14 |  25 |
| Gardasee | Okt |  10 |  18 |
| Gardasee | Nov |   5 |  11 |
| Gardasee | Dez |   2 |   7 |
| Korsika  | Jan |   3 |  13 |
| Korsika  | Feb |   5 |  14 |
| Korsika  | Mar |   6 |  15 |
| Korsika  | Apr |   6 |  19 |
| Korsika  | Mai |   9 |  22 |
| Korsika  | Jun |  14 |  23 |
| Korsika  | Jul |  15 |  27 |
| Korsika  | Aug |  17 |  28 |
| Korsika  | Sep |  14 |  25 |
| Korsika  | Okt |  10 |  21 |
| Korsika  | Nov |   7 |  17 |
| Korsika  | Dez |   4 |  14 |
+----------+-----+-----+-----+

SELECT     ORT, AVG(( MIN + MAX )/ 2 ) 
FROM       TEMPERATUR 
GROUP BY   ORT;

+----------+------------------------+
| ORT      | AVG(( MIN + MAX )/ 2 ) |
+----------+------------------------+
| Gardasee |            12.83333333 |
| Korsika  |            14.50000000 |
+----------+------------------------+

SELECT     ORT, AVG(( MIN + MAX )/ 2 ) 
FROM       TEMPERATUR 
WHERE      MON IN ( 'Dez', 'Jan' )
GROUP BY   ORT;

+----------+------------------------+
| ORT      | AVG(( MIN + MAX )/ 2 ) |
+----------+------------------------+
| Gardasee |             3.25000000 |
| Korsika  |             8.50000000 |
+----------+------------------------+

SELECT     ORT, AVG(( MIN + MAX )/ 2 ) 
FROM       TEMPERATUR 
WHERE      MON IN ( 'Dez', 'Jan' ) 
GROUP BY   ORT 
HAVING     AVG(( MIN + MAX )/ 2 ) > 5;

+---------+------------------------+
| ORT     | AVG(( MIN + MAX )/ 2 ) |
+---------+------------------------+
| Korsika |             8.50000000 |
+---------+------------------------+

SELECT     ORT, AVG(( MIN + MAX )/ 2 ) AS AVG
FROM       TEMPERATUR 
WHERE      MON IN ( 'Dez', 'Jan' ) 
GROUP BY   ORT 
HAVING     AVG > 5;

+---------+------------+
| ORT     | AVG        |
+---------+------------+
| Korsika | 8.50000000 |
+---------+------------+

SELECT * FROM
( SELECT     ORT, AVG(( MIN + MAX )/ 2 ) AS AVG
  FROM       TEMPERATUR 
  WHERE      MON IN ( 'Dez', 'Jan' ) 
  GROUP BY   ORT ) AS SUB
WHERE AVG > 5;

+---------+------------+
| ORT     | AVG        |
+---------+------------+
| Korsika | 8.50000000 |
+---------+------------+

HAVING wurde wohl zu einer Zeit zu SQL hinzugefügt, zu der solche Unterabfragen noch nicht möglich waren. Heute würde man es vielleicht nicht mehr zu SQL hinzufügen, da man heute Unterabfragen verwenden kann.

Übungsaufgabe

/   Unterabfrage mit Equi-Join

Formulieren Sie die Abfrage des letzten Beispiels mit einem Equi-Join und ohne Unterabfrage, so daß sich (bis auf die Reihenfolge der Zeilen), die selben drei zweispaltigen Zeilen ergeben.

Listen

Bisher haben wir uns mit skalaren Ausdrücken beschäftigt, zB »123« oder »'abc'«

Eine Spalte ist für uns dasselbe wie ein Tabelle mit genau einer Spalte. Eine Tabelle mit genau einer Spalte ist eigentlich eine Skalarmenge, und wird auch Liste  genannt.

Damit kann eine Unterabfrage auch für eine Menge  von Werten stehen.

Um Mengen in Ausdrücken verwenden zu können, ist es nötig, spezielle Mengenoperatoren zu verwenden.

IN

Alle Zahlen zeigen, die für einen Planeten verwendet werden.

Konsole

DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE ZAHL ( I VARCHAR ( 255 ), DE VARCHAR ( 255 ), EN VARCHAR ( 255 )); 
INSERT INTO ZAHL ( I, DE, EN ) VALUES ( 0, 'NULL', 'ZERO'  );
INSERT INTO ZAHL ( I, DE, EN ) VALUES ( 1, 'EINS', 'ONE'   );
INSERT INTO ZAHL ( I, DE, EN ) VALUES ( 2, 'ZWEI', 'TWO'   );
INSERT INTO ZAHL ( I, DE, EN ) VALUES ( 3, 'DREI', 'THREE' );
CREATE TABLE PLANET ( PLANET VARCHAR( 255 ), NAME VARCHAR( 255 ));
INSERT INTO PLANET ( PLANET, NAME ) VALUES ( '1', 'Merkur' );
INSERT INTO PLANET ( PLANET, NAME ) VALUES ( '2', 'Venus'  );
INSERT INTO PLANET ( PLANET, NAME ) VALUES ( '3', 'Erde'   );
SELECT * FROM ZAHL;

+------+------+-------+
| I    | DE   | EN    |
+------+------+-------+
| 0    | NULL | ZERO  |
| 1    | EINS | ONE   |
| 2    | ZWEI | TWO   |
| 3    | DREI | THREE |
+------+------+-------+

SELECT * FROM PLANET;

+--------+--------+
| PLANET | NAME   |
+--------+--------+
| 1      | Merkur |
| 2      | Venus  |
| 3      | Erde   |
+--------+--------+

SELECT PLANET FROM PLANET;

+--------+
| PLANET |
+--------+
| 1      |
| 2      |
| 3      |
+--------+

SELECT DE FROM ZAHL WHERE I IN ( SELECT PLANET FROM PLANET );

+------+
| DE   |
+------+
| EINS |
| ZWEI |
| DREI |
+------+

NOT IN

Alle Zahlen zeigen, die nicht für einen Planeten verwendet werden.

Konsole

SELECT DE FROM ZAHL WHERE I NOT IN ( SELECT PLANET FROM PLANET );
+------+
| DE   |
+------+
| NULL |
+------+

ALL

Hier mit kann ermittelt werden, ob ein Vergleich für alle Werte einer Menge zutrifft.

Zeige jeden Planeten, dessen Name im Alphabet vor allen Planetennamen kommt.

Konsole

SELECT NAME FROM PLANET WHERE NAME <= ALL ( SELECT NAME FROM PLANET );

+------+
| NAME |
+------+
| Erde |
+------+

EXISTS

Dieses Prädikat ermittelt, ob die als Argument angegebene Menge nicht leer ist und sollte daher eigentlich »NOTEMPTY« heißen.

Zeige jede Zahl, zu der es einen Planeten gibt.

Konsole

SELECT DE FROM ZAHL WHERE EXISTS ( SELECT PLANET FROM PLANET WHERE PLANET = I );

+------+
| DE   |
+------+
| EINS |
| ZWEI |
| DREI |
+------+

NOT EXISTS

Dieses Prädikat ermittelt, ob eine Menge leer ist.

Zeige jede Zahl, zu der es keinen Planeten gibt.

Konsole

SELECT DE FROM ZAHL WHERE NOT EXISTS ( SELECT PLANET FROM PLANET WHERE PLANET = I );

+------+
| DE   |
+------+
| NULL |
+------+

Konsole

WARNINGS; SET sql_mode = 'ANSI,TRADITIONAL';
DROP SCHEMA IF EXISTS S; CREATE SCHEMA S; USE S;
CREATE TABLE STUDENT ( STUDENT INT UNSIGNED, NAME VARCHAR ( 255 )); 
INSERT INTO STUDENT ( STUDENT, NAME ) VALUES ( 0, 'Stefanie Euler' );
INSERT INTO STUDENT ( STUDENT, NAME ) VALUES ( 1, 'Nina Eickelmann' );
INSERT INTO STUDENT ( STUDENT, NAME ) VALUES ( 2, 'Andreas Gernetzki' );
INSERT INTO STUDENT ( STUDENT, NAME ) VALUES ( 3, 'Siegfried Gutte' );
CREATE TABLE HAUSAUFGABE ( HAUSAUFGABE INT UNSIGNED, STUDENT INT UNSIGNED, NOTE INT );
INSERT INTO HAUSAUFGABE ( HAUSAUFGABE, STUDENT, NOTE ) VALUES ( 0, 0, 3 );
INSERT INTO HAUSAUFGABE ( HAUSAUFGABE, STUDENT, NOTE ) VALUES ( 0, 1, 2 );
INSERT INTO HAUSAUFGABE ( HAUSAUFGABE, STUDENT, NOTE ) VALUES ( 0, 2, 5 );
INSERT INTO HAUSAUFGABE ( HAUSAUFGABE, STUDENT, NOTE ) VALUES ( 1, 1, 5 );
INSERT INTO HAUSAUFGABE ( HAUSAUFGABE, STUDENT, NOTE ) VALUES ( 1, 2, 2 );
INSERT INTO HAUSAUFGABE ( HAUSAUFGABE, STUDENT, NOTE ) VALUES ( 2, 1, 2 );

Wir nehmen im folgenden an, daß es nur drei Hausaufgaben (0, 1 und 2) gab. In einer gut gestalteten Datenbank wäre noch eine separate Tabelle für die Hausaufgaben ohne Noten notwendig.

Welcher Student hat mindestens eine Hausaufgabe eingereicht?

Konsole

SELECT NAME FROM STUDENT AS ST WHERE EXISTS 
( SELECT HAUSAUFGABE FROM HAUSAUFGABE WHERE ST.STUDENT = STUDENT );
+-------------------+
| NAME              |
+-------------------+
| Stefanie Euler    |
| Nina Eickelmann   |
| Andreas Gernetzki |
+-------------------+

Welcher Student hat keine Hausaufgaben eingereicht?

Konsole

SELECT NAME FROM STUDENT AS ST WHERE NOT EXISTS 
( SELECT HAUSAUFGABE FROM HAUSAUFGABE WHERE ST.STUDENT = STUDENT );
+-----------------+
| NAME            |
+-----------------+
| Siegfried Gutte |
+-----------------+

Bei welchen Aufgaben haben alle eingereichten Lösungen mindestens die Note 3?

Konsole

SELECT HAUSAUFGABE FROM HAUSAUFGABE H WHERE NOT EXISTS
( SELECT * FROM HAUSAUFGABE WHERE HAUSAUFGABE = H.HAUSAUFGABE AND NOTE > 3 );
+-------------+
| HAUSAUFGABE |
+-------------+
|           2 |
+-------------+

Welcher Student hat die beste Note bei Aufgabe 0?

Konsole

SELECT NAME, NOTE FROM STUDENT S, HAUSAUFGABE H
WHERE S.STUDENT = H.STUDENT AND H.HAUSAUFGABE = 0
AND NOT EXISTS
( SELECT * FROM HAUSAUFGABE HH
  WHERE HH.HAUSAUFGABE = 0 AND HH.NOTE < H.NOTE );

+-----------------+------+
| NAME            | NOTE |
+-----------------+------+
| Nina Eickelmann |    2 |
+-----------------+------+

Welcher Student hat alle Hausaufgaben eingereicht?

Also: Für welchen Studenten gibt es keine Hausaufgabe, die er nicht eingereicht hat?

Konsole

SELECT NAME FROM STUDENT AS ST WHERE NOT EXISTS 
( SELECT * FROM HAUSAUFGABE AS H WHERE NOT EXISTS
  ( SELECT * FROM HAUSAUFGABE AS HH 
    WHERE ST.STUDENT = STUDENT AND H.HAUSAUFGABE = HAUSAUFGABE  ));
+-----------------+
| NAME            |
+-----------------+
| Nina Eickelmann |
+-----------------+

Es ist nicht ganz einfach die obige Abfrage zu verstehen. Aber das ist in einem Grundkurs auch nicht unbedingt nötig.