Zusammenfassung und Beispiele zum SQL-SELECT

SQL-Anfragen mit komplexer Selektion, Subqueries, Gruppierung und Aggregationsfunktionen

Wichtigste Schlüsselworte in SQL im Bereich Subqueries (Unteranfragen), Gruppierung und Aggregationsfunktionen sind:

IN, ANY, ALL, EXISTS
GROUP BY, HAVING
COUNT, MIN, MAX, AVG, SUM
DISTINCT

Die Beispiele in diesem Material beziehen sich auf die Tabelle customers aus der W3C SQL School (Google: w3c sql school). Alle Anfragen lassen sich in der W3C SQL School unter SQL-Try-It ausführen. Durch die Eingabe von leicht veränderten Versionen der Anfragen kann man den Einfluss der verschiedenen Konstrukte nachvollziehen. Z.B. lässt sich

die SELECT-Klausel durch Hinzunehmen oder Entfernen von Spalten,
die FROM-Klausel durch Hinzunehmen oder Entfernen von Tupelvariablen (Alias-Angaben) für die Tabelle,
die WHERE- und HAVING-Klausel durch Modifikation der Formel (z.B. Austauschen von Konstanten, Austauschen von Vergleichoperatoren, Vertauschen von AND und OR, Vertauschen von ANY und ALL),
die GROUP BY-Klausel durch Hinzunehmen oder Entfernen von Spalten und
die ORDER BY-Klausel durch Vertauschen der Spaltenreihenfolge und Hinzunehmen oder Entfernen von ASC oder DESC

verändern und das neue Ergebnis mit dem ursprünglichen Ergebnis vergleichen.

SELECT, FROM

SELECT *
FROM customers

CustomerID	CompanyName	ContactName	Address	City	PostalCode	Country
ALFKI	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
BERGS	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden
CENTC	Centro comercial Moctezuma	Francisco Chang	Sierras de Granada 9993	México D.F.	05022	Mexico
ERNSH	Ernst Handel	Roland Mendel	Kirchgasse 6	Graz	8010	Austria
FISSA	FISSA Fabrica Inter. Salchichas S.A.	Diego Roel	C/ Moralzarzal, 86	Madrid	28034	Spain
GALED	Galería del gastrónomo	Eduardo Saavedra	Rambla de Cataluña, 23	Barcelona	08022	Spain
ISLAT	Island Trading	Helen Bennett	Garden House Crowther Way	Cowes	PO31 7PJ	UK
KOENE	Königlich Essen	Philip Cramer	Maubelstr. 90	Brandenburg	14776	Germany
LAUGB	Laughing Bacchus Wine Cellars	Yoshi Tannamuri	1900 Oak St.	Vancouver	V3F 2K1	Canada
MAGAA	Magazzini Alimentari Riuniti	Giovanni Rovelli	Via Ludovico il Moro 22	Bergamo	24100	Italy
NORTS	North/South	Simon Crowther	South House 300 Queensbridge	London	SW7 1RZ	UK
PARIS	Paris spécialités	Marie Bertrand	265, boulevard Charonne	Paris	75012	France
RATTC	Rattlesnake Canyon Grocery	Paula Wilson	2817 Milton Dr.	Albuquerque	87110	USA
SIMOB	Simons bistro	Jytte Petersen	Vinbæltet 34	København	1734	Denmark
THEBI	The Big Cheese	Liz Nixon	89 Jefferson Way Suite 2	Portland	97201	USA
VAFFE	Vaffeljernet	Palle Ibsen	Smagsløget 45	Århus	8200	Denmark
WOLZA	Wolski Zajazd	Zbyszek Piestrzeniewicz	ul. Filtrowa 68	Warszawa	01-012	Poland

WHERE, ORDER BY

SELECT Country
FROM customers
WHERE Country<='Italy'
ORDER BY Country

Country
Austria
Canada
Denmark
Denmark
France
Germany
Germany
Italy

SELECT DISTINCT

SELECT DISTINCT Country
FROM customers
WHERE Country<='Italy'
ORDER BY Country

Country
Austria
Canada
Denmark
France
Germany
Italy

AND, OR, NOT (Konjunktion, Disjunktion, Negation)

SELECT CompanyName, Country, PostalCode
FROM customers
WHERE Country IN ('Germany','Austria','Switzerland')

SELECT CompanyName, Country, PostalCode
FROM customers
WHERE Country='Germany' OR Country='Austria' OR Country='Switzerland'

CompanyName	Country	PostalCode
Alfreds Futterkiste	Germany	12209
Ernst Handel	Austria	8010
Königlich Essen	Germany	14776

SELECT CompanyName, Country, PostalCode
FROM customers
WHERE LEN(PostalCode)=4

SELECT CompanyName, Country, PostalCode
FROM customers
WHERE PostalCode LIKE '____'

'____' = 4 x Unterstrich

CompanyName	Country	PostalCode
Ernst Handel	Austria	8010
Simons bistro	Denmark	1734
Vaffeljernet	Denmark	8200

SELECT CompanyName, Country, PostalCode
FROM customers
WHERE Country IN ('Germany','Austria','Switzerland')
AND
LEN(PostalCode)=4

Bemerkung zum Layout von Anfragen: Alle Anfragen sollen aus diesem Material kopiert werden und in der W3C SQL School ausprobiert werden können. Daher muss der HTML-Text der Anfragen einfach sein und kann teilweise leider, zumindest was das Layout betrifft, nicht optimal gestaltet werden.

CompanyName	Country	PostalCode
Ernst Handel	Austria	8010

SELECT CompanyName, Country, PostalCode
FROM customers
WHERE Country IN ('Germany','Austria','Switzlerland)
OR
LEN(PostalCode)=4

OR entspricht der Mengenvereinigung von Anfrageergebnissen, AND entspricht dem Mengendurchschnitt.

CompanyName	Country	PostalCode
Alfreds Futterkiste	Germany	12209
Ernst Handel	Austria	8010
Königlich Essen	Germany	14776
Simons bistro	Denmark	1734
Vaffeljernet	Denmark	8200

Obacht bei der Ersetzung des IN-Terms durch den OR-Term in der AND-Anfrage. Diese Anfrage liefert 3 Tupel, während die ursprüngliche AND-Anfrage 1 Tupel lieferte. Die Klammersetzung muss beachtet werden. In der Anfrage bindet das AND stärker als das OR.

SELECT CompanyName, Country, PostalCode
FROM customers
WHERE Country='Germany' OR Country='Austria' OR Country='Switzerland'
AND
LEN(PostalCode)=4

CompanyName	Country	PostalCode
Alfreds Futterkiste	Germany	12209
Ernst Handel	Austria	8010
Königlich Essen	Germany	14776

SELECT CompanyName, Country, PostalCode
FROM customers
WHERE Country='Germany' OR Country='Austria' OR (Country='Switzerland'
                AND
                LEN(PostalCode)=4)

SELECT CompanyName, Country, PostalCode
FROM customers
WHERE (Country='Germany' OR Country='Austria' OR Country='Switzerland')
                AND
                LEN(PostalCode)=4

Verwendung von NOT:

SELECT CompanyName, Country, PostalCode
FROM customers
WHERE NOT ( Country IN ('Germany','Austria','Switzerland') )

SELECT CompanyName, Country, PostalCode
FROM customers
WHERE Country NOT IN ('Germany','Austria','Switzerland')

CompanyName	Country	PostalCode
Berglunds snabbköp	Sweden	S-958 22
Centro comercial Moctezuma	Mexico	05022
FISSA Fabrica Inter. Salchichas S.A.	Spain	28034
Galería del gastrónomo	Spain	08022
Island Trading	UK	PO31 7PJ
Laughing Bacchus Wine Cellars	Canada	V3F 2K1
Magazzini Alimentari Riuniti	Italy	24100
North/South	UK	SW7 1RZ
Paris spécialités	France	75012
Rattlesnake Canyon Grocery	USA	87110
Simons bistro	Denmark	1734
The Big Cheese	USA	97201
Vaffeljernet	Denmark	8200
Wolski Zajazd	Poland	01-012

Beobachtung: 'Switzerland' taucht weder im Datenbankzustand unter Country noch im Ergebnis der Anfrage auf.

NOT entspricht der Komplementbildung von Anfrageergebnissen bzgl. der gespeicherten Tabelle.

Gesetze der Logik

Kommutativität

F AND G = G AND F
F OR G = G OR F

Assoziativität

(F AND G) AND H = F AND (G AND H) = F AND G AND H
(F OR G) OR H = F OR (G OR H) = F OR G OR H

Distributivität

F AND (G OR H) = (F AND G) OR (F AND H) = F AND G OR F AND H
F OR (G AND H) = (F OR G) AND (F OR H)

DeMorgan

NOT (F AND G) = (NOT F) OR (NOT G)
NOT (F OR G) = (NOT F) AND (NOT G)

Weitere Gesetze siehe http://de.wikipedia.org/wiki/Boolesche_Algebra.

Diese Gesetze sind in SQL anwendbar. Idealerweise berücksichtigt ein Anfrageauswerter und -optimierer diese Gesetze. Dies ist jedoch nicht immer der Fall. Somit können diese Gesetze auch zur Umformulierung dienen und damit möglicherweise zu einer schnelleren Anfragebearbeitung führen.

Disjunktive und Konjunktive Normalform (Logik)

Bei der disjunktiven Normalform (DNF) handelt es sich um einen logischen Ausdruck, der aus ODER-Verknüpfungen besteht. Der logische Ausdruck besteht in der obersten Ebene ausschließlich aus ODER-Verknüpfungen. Dabei können die einzelnen Elemente der ODER-Verknüpfung kompliziertere Ausdrücke sein, die dann auch eine UND-Verknüpfung enthalten können. (http://de.wikipedia.org/wiki/Disjunktive_Normalform)

Bei der konjunktiven Normalform (KNF) handelt es sich um einen logischen Ausdruck, der aus UND-Verknüpfungen besteht. Der logische Ausdruck besteht in der obersten Ebene ausschließlich aus UND-Verknüpfungen. Dabei können die einzelnen Elemente der UND-Verknüpfung kompliziertere Ausdrücke sein, die dann auch eine ODER-Verknüpfung enthalten können.

1. Anfrage in DNF

SELECT Country, City
FROM customers
WHERE ('I'<=Country AND Country<='P') OR
                ('I'<=City AND City<='P')
ORDER BY Country, City

Äquivalente Anfrage in KNF

SELECT Country, City
FROM customers
WHERE ('I'<=Country OR 'I'<=City) AND
                ('I'<=Country OR City<='P') AND
                (Country<='P' OR 'I'<=City) AND
                (Country<='P' OR City<='P')
ORDER BY Country, City

2. Anfrage in KNF (nicht äquivalent zur 1. Anfrage)

SELECT Country, City
FROM customers
WHERE ('I'<=Country OR Country<='P') AND
                ('I'<=City OR City<='P')
ORDER BY Country, City

Äquivalente Anfrage in DNF

SELECT Country, City
FROM customers
WHERE ('I'<=Country AND 'I'<=City) OR
                ('I'<=Country AND City<='P') OR
                (Country<='P' AND 'I'<=City) OR
                (Country<='P' AND City<='P')
ORDER BY Country, City

Obacht: Diese Anfrage ist durch Vertauschen von AND und OR aus der 1. Anfrage hervorgegangen. Man beachte den Einfluss der WHERE-Klausel auf die Resultatsmenge.

3. Anfrage in KNF (nicht äquivalent zur 1. und 2. Anfrage)

SELECT Country, City
FROM customers
WHERE (Country<='I' OR 'P'<=Country) AND
                (City<='I' OR 'P'<=City)
ORDER BY Country, City

Äquivalente Anfrage in DNF

SELECT Country, City
FROM customers
WHERE (Country<='I' AND City<='I') OR
                (Country<='I' AND 'P'<=City) OR
                ('P'<=Country AND City<='I') OR
                ('P'<=Country AND 'P'<=City)
ORDER BY Country, City

BETWEEN

SELECT CompanyName, Country
FROM customers
WHERE Country BETWEEN 'France' AND 'Poland'
ORDER BY Country

SELECT CompanyName, Country
FROM customers
WHERE 'France'<=Country AND Country<='Poland'
ORDER BY Country

CompanyName	Country
Paris spécialités	France
Königlich Essen	Germany
Alfreds Futterkiste	Germany
Magazzini Alimentari Riuniti	Italy
Centro comercial Moctezuma	Mexico
Wolski Zajazd	Poland

LEFT, INSTR, RIGHT, LEN, AS

Obacht: LEFT, INSTR, RIGHT und LEN sind im Standard teilweise anders benannt.

SELECT LEFT(ContactName,INSTR(ContactName,' ')-1) AS FirstName,
RIGHT(ContactName,LEN(ContactName)-INSTR(ContactName,' ')) AS LastName
FROM customers
WHERE Country<='Italy'
ORDER BY 2, 1

FirstName	LastName
Maria	Anders
Marie	Bertrand
Philip	Cramer
Palle	Ibsen
Roland	Mendel
Jytte	Petersen
Giovanni	Rovelli
Yoshi	Tannamuri

LIKE

SELECT ContactName, CompanyName
FROM customers
WHERE LEFT(ContactName,INSTR(ContactName,' ')-1) LIKE '%a'
ORDER BY 1

ContactName	CompanyName
Christina Berglund	Berglunds snabbköp
Maria Anders	Alfreds Futterkiste
Paula Wilson	Rattlesnake Canyon Grocery

SELECT CompanyName, City
FROM customers
WHERE City LIKE '%burg'
ORDER BY 1

CompanyName	City
Königlich Essen	Brandenburg

SELECT CompanyName, PostalCode
FROM customers
WHERE Country='Germany' AND PostalCode LIKE '1____'
ORDER BY 1

'____' = 4 x Unterstrich

CompanyName	PostalCode
Alfreds Futterkiste	12209
Königlich Essen	14776

Im allgemeinen nicht äquivalent zur obigen Anfrage, hier jedoch das gleiche Ergebnis liefernd, ist:

SELECT CompanyName, PostalCode
FROM customers
WHERE Country='Germany' AND PostalCode LIKE '1%'
ORDER BY 1

'1____' testet auf eine '1' am Anfang und 4 folgende Zeichen; '1%' testet auf eine '1' am Anfang und beliebig viele folgende Zeichen; z.B., ergibt '1%' angewendet auf '1' den Wert TRUE und '1____' angewendet auf '1' liefert FALSE.

Exotische Vornamen (Vornamen, die die Buchstaben X, Y oder Z enthalten)

SELECT LEFT(ContactName,INSTR(ContactName,' ')-1) AS FirstName
FROM customers
WHERE LEFT(ContactName,INSTR(ContactName,' ')-1) LIKE '%X%' OR
                LEFT(ContactName,INSTR(ContactName,' ')-1) LIKE '%x%' OR
                LEFT(ContactName,INSTR(ContactName,' ')-1) LIKE '%Y%' OR
                LEFT(ContactName,INSTR(ContactName,' ')-1) LIKE '%y%' OR
                LEFT(ContactName,INSTR(ContactName,' ')-1) LIKE '%Z%' OR
                LEFT(ContactName,INSTR(ContactName,' ')-1) LIKE '%z%'
ORDER BY 1

FirstName
Jytte
Liz
Yoshi
Zbyszek

<=All-Subquery

Lexikographisch kleinster PostalCode

SELECT Country, PostalCode
FROM customers
WHERE PostalCode <=ALL (SELECT PostalCode
FROM customers)

Country	PostalCode
Poland	01-012

MIN-Abfrage mit Ein-Spalten-Selektion

SELECT Country, PostalCode
FROM customers
WHERE PostalCode=(SELECT MIN(PostalCode)
FROM customers)

Die Konstruktion einer Unteranfrage mit ALL bietet im allgemeinen Fall mehr Möglichkeiten. Nicht alle mit ALL formulierten Unteranfragen lassen sich auf Aggregationsfunktionen zurückführen.

<>All-Subquery

SELECT Country, PostalCode
FROM customers
WHERE PostalCode <>ALL (SELECT c2.PostalCode
FROM customers c1, customers c2
WHERE c1.PostalCode<c2.PostalCode)

Die Unteranfrage berechnet die Menge der Postleitzahlen c2.PostalCode, zu denen es eine Postleitzahl c1.PostalCode gibt, die lexikographisch vor der Ergebnis-Postleitzahl c2.PostalCode liegt. Die Unteranfrage berechnet die Menge der 'lexikographisch grösseren' Postleitzahlen.

Kartesisches Produkt (Kreuzprodukt) von Tabellen

SELECT *
FROM customers c1, customers c2

Werden mehr als eine Tabelle in der FROM-Klausel genannt und die Tabellen inklusive möglicher Alias-Angaben (Tupel- oder Satz-Variablen) durch Kommata getrennt, wird das Kreuzprodukt der angegebenen Tabellen berechnet. D.h., jeder Satz aus der ersten Tabelle wird mit jedem Satz aus der zweiten Tabelle kombiniert. Im Beispiel ergibt sich dann eine Resultatsmenge mit 7+7=14 Spalten und 17*17=289 Sätzen (Zeilen,Tupeln).

SELECT *
FROM customers c1, customers c2
WHERE c1.CompanyName<c2.CompanyName AND
                c1.ContactName<c2.ContactName AND
                c1.Address<c2.Address AND
                c1.City<c2.City AND
                c1.PostalCode<c2.PostalCode AND
                c1.Country<c2.Country

c1.CustomerID	c1.CompanyName	c2.CustomerID	c2.CompanyName
ALFKI	Alfreds Futterkiste	NORTS	North/South
ERNSH	Ernst Handel	NORTS	North/South
GALED	Galería del gastrónomo	NORTS	North/South
KOENE	Königlich Essen	NORTS	North/South

SELECT c1.CompanyName, c2.CompanyName
FROM customers c1, customers c2
WHERE c1.CompanyName<c2.CompanyName AND
                c1.ContactName<c2.ContactName AND
                c1.Address<c2.Address AND
                c1.City<c2.City AND
                c1.PostalCode<c2.PostalCode AND
                c1.Country<c2.Country

c1.CompanyName	c2.CompanyName
Alfreds Futterkiste	North/South
Ernst Handel	North/South
Galería del gastrónomo	North/South
Königlich Essen	North/South

=ANY-Subquery

SELECT Country, PostalCode
FROM customers
WHERE NOT ( PostalCode =ANY (SELECT c2.PostalCode
FROM customers c1, customers c2
WHERE c1.PostalCode<c2.PostalCode) )

IN-Subquery

SELECT Country, PostalCode
FROM customers
WHERE NOT ( PostalCode IN (SELECT c2.PostalCode
FROM customers c1, customers c2
WHERE c1.PostalCode<c2.PostalCode) )

EXISTS-Subquery

SELECT Country, PostalCode
FROM customers c
WHERE NOT ( EXISTS (SELECT c2.PostalCode
                                            FROM customers c1, customers c2
                                            WHERE c.PostalCode=c2.PostalCode AND
                                                            c1.PostalCode<c2.PostalCode) )

SELECT Country, PostalCode
FROM customers c
WHERE NOT ( EXISTS (SELECT *
                                            FROM customers c1, customers c2
                                            WHERE c.PostalCode=c2.PostalCode AND
                                                            c1.PostalCode<c2.PostalCode) )

SELECT Country, PostalCode
FROM customers c
WHERE NOT ( EXISTS (SELECT 42
                                            FROM customers c1, customers c2
                                            WHERE c.PostalCode=c2.PostalCode AND
                                                            c1.PostalCode<c2.PostalCode) )

Statt der Konstanten 42 kann eine beliebige andere Konstante angegeben werden, z.B. 4711, 3.14, 'ABBA', 'Zappa', FALSE oder TRUE.

Es sind nun mittlerweile 8 (in Worten acht) verschiedene äquivalente Formulierungen einer Anfrage angegeben worden. Weitere unterschiedliche Anfragetexte ergeben sich z.B. durch Anwendung des Kommutativgesetzes für AND.

Diese Unteranfragen im EXISTS können im Gegensatz zu den bisherigen Unteranfragen nicht direkt als Anfragen gestellt werden.

<=ALL-Subquery mit Korrelation

Lexikographisch kleinster PostalCode pro Land.

SELECT Country, PostalCode
FROM customers c
WHERE PostalCode <=ALL (SELECT PostalCode
                                                    FROM customers
                                                    WHERE c.Country=Country) AND
                Country<='Italy'
ORDER BY Country, PostalCode

Verwendung von Variablen nur an den Vorkommen wo die Variablen benötigt werden.

Country	PostalCode
Austria	8010
Canada	V3F 2K1
Denmark	1734
France	75012
Germany	12209
Italy	24100

Die erste obige Unteranfrage (SELECT PostalCode FROM customers) könnte auch direkt als Anfrage gestellt werden. Diese Unteranfrage hier könnte nicht als Anfrage gestellt werden, da die Variable c und damit der Ausdruck c.Country nicht bekannt sind.

Bezeichnung: Dies ist eine korrelierte Unteranfrage (Unteranfrage mit Korrelation versus Unteranfrage ohne Korrelation).

SELECT c_o.Country, c_o.PostalCode
FROM customers c_o
WHERE c_o.PostalCode <=ALL (SELECT c_i.PostalCode
                                                           FROM customers c_i
                                                           WHERE c_o.Country=c_i.Country) AND
                Country<='Italy'
ORDER BY Country, PostalCode

Verwendung von Variablen an allen Vorkommen der Variablen (c_o outer, c_i inner).

Analogie:

Verwendung von Variablen in Anfragen: Variablen in blockorientierten Programmiersprachen
{ integer v_o; ... { integer v_i; ... } ... }
SELECT ... FROM table v_o WHERE ... (SELECT ... FROM table v_i WHERE ... ) ...
Gültigkeitsbereich von v_o ist jeweils auch der innere Teil des Programms bzw. der Anfrage.

Falls keine Tupelvariablen deklariert werden, stehen die Namen der Tabellen als implizite Variablen zur Verfügung.

<ALL-Subquery

Obacht: Ersetzung von '<=All' durch '<ALL' in der obigen Anfrage ergibt die leere Resultatsmenge.

SELECT Country, PostalCode
FROM customers c
WHERE PostalCode <ALL (SELECT PostalCode
FROM customers c_inner
WHERE c.Country=c_inner.Country)
ORDER BY Country, PostalCode

Country	PostalCode

NOT EXISTS-Subquery

SELECT c_o.Country, c_o.PostalCode
FROM customers c_o
WHERE NOT EXISTS (SELECT *
                                         FROM customers c_i
                                         WHERE c_o.Country=c_i.Country AND
                                                         c_o.PostalCode>c_i.PostalCode)
ORDER BY Country, PostalCode

MIN-Abfrage mit Ein-Spalten-Selektion

SELECT Country, PostalCode
FROM customers c
WHERE PostalCode=(SELECT MIN(PostalCode)
FROM customers
WHERE Country=c.Country)
ORDER BY Country, PostalCode

IN-Subquery

Länder, die mindestens zwei Sätze in der Tabelle customer besitzen, zusammen mit ihren Postleitzahlen.

SELECT Country, PostalCode
FROM customers
WHERE Country IN (SELECT Country
                                    FROM customers
                                    GROUP BY Country
                                    HAVING COUNT(*)>=2)
ORDER BY Country, PostalCode

Country	PostalCode
Denmark	1734
Denmark	8200
Germany	12209
Germany	14776
Spain	08022
Spain	28034
UK	PO31 7PJ
UK	SW7 1RZ
USA	87110
USA	97201

ANY-Subquery

SELECT Country, PostalCode
FROM customers
WHERE Country =ANY (SELECT Country
                                            FROM customers
                                            GROUP BY Country
                                            HAVING COUNT(*)>=2)
ORDER BY Country, PostalCode

EXISTS-Subquery

SELECT Country, PostalCode
FROM customers c
WHERE EXISTS (SELECT *
                                FROM customers c1, customers c2
                                WHERE c.Country=c1.Country AND c.Country=c2.Country AND
                                                c1.PostalCode<>c2.PostalCode)
ORDER BY Country, PostalCode

Beobachtung: Die Anfrage könnte auch mit GROUP BY/HAVING formuliert werden. Die Verwendung von GROUP BY/HAVING impliziert also nicht die zwingende Notwendigkeit der Verwendung dieser Sprachmittel.

ALL-Subquery

SELECT Country, PostalCode
FROM customers c
WHERE LEN(PostalCode) =ALL (SELECT LEN(PostalCode)
FROM customers c_inner
WHERE c.Country=c_inner.Country)
ORDER BY Country, PostalCode

Country	PostalCode
Austria	8010
Canada	V3F 2K1
Denmark	1734
Denmark	8200
France	75012
Germany	12209
Germany	14776
Italy	24100
Mexico	05022
Poland	01-012
Spain	08022
Spain	28034
Sweden	S-958 22
USA	87110
USA	97201

Beobachtung: 'UK' nicht enthalten!

<>ANY-Subquery

SELECT Country, PostalCode
FROM customers c
WHERE LEN(PostalCode) <>ANY (SELECT LEN(PostalCode)
FROM customers c_inner
WHERE c.Country=c_inner.Country)
ORDER BY Country, PostalCode

Country	PostalCode
UK	PO31 7PJ
UK	SW7 1RZ

FORALL als NOT EXISTS NOT

SQL92 kennt zwar eine EXISTS-Subquery und damit einen Existenzquantor für Tupel. Aber es gibt in SQL92 (und damit in den meisten existierenden Systemen) keinen entsprechenden Allquantor. Der Allquantor kann jedoch mit Hilfe einer NOT EXISTS-Subquery ausgedrückt werden.

Erinnerung

[ für alle x gilt p(x) ] = es gilt nicht [ es existiert x mit ( es gilt nicht p(x) ) ]
[ es existiert x mit p(x) ] = es gilt nicht [ für alle x gilt ( es gilt nicht p(x) ) ]

In SQL

FORALL (SELECT * FROM T WHERE F)
=
NOT EXISTS (SELECT * FROM T WHERE NOT F)

Beispiel zu einer möglichen, teilweise hypothetischen Umformung einer SQL-Anfrage

SELECT Country, PostalCode
FROM customers c1
WHERE NOT EXISTS (SELECT *
                                         FROM customers c2
                                         WHERE c1.Country=c2.Country AND
                                                         c1.PostalCode>c2.PostalCode)

SELECT Country, PostalCode
FROM customers c1
WHERE NOT EXISTS (SELECT *
                                         FROM customers c2
                                         WHERE NOT ( NOT ( c1.Country=c2.Country AND
                                                                               c1.PostalCode>c2.PostalCode ) ) )

SELECT Country, PostalCode
FROM customers c1
WHERE NOT EXISTS (SELECT *
                                         FROM customers c2
                                         WHERE NOT ( c1.Country<>c2.Country OR
                                                                    c1.PostalCode<=c2.PostalCode ) )

SELECT Country, PostalCode -- KEIN GÜLTIGES SQL!
FROM customers c1
WHERE FORALL (SELECT *
                                  FROM customers c2
                                  WHERE c1.Country<>c2.Country OR
                                                c1.PostalCode<=c2.PostalCode )

SELECT Country, PostalCode -- KEIN GÜLTIGES SQL!
FROM customers c1
WHERE FORALL (SELECT *
                                  FROM customers c2
                                  WHERE c1.Country=c2.Country IMPLIES
                                                c1.PostalCode<=c2.PostalCode )

FEHLEN von IMPLIES und XOR in SQL

IMPLIES und XOR fehlen in SQL, können aber ausgedrückt werden.
F IMPLIES G = NOT(F) OR G
F XOR G = (NOT(F) AND G) OR (F AND NOT(G))
Die in SQL zu verwendenden Formulierungen sind aber häufig schlechter verständlich als eine Formulierung direkt mit IMPLIES oder XOR, insbesondere für XOR, da hier Teilterme dupliziert werden müssen.

GROUP BY, COUNT(*)

SELECT Country, COUNT(*) AS CountCountry
FROM customers
GROUP BY Country
ORDER BY 2,1

Country	CountCountry
Austria	1
Canada	1
France	1
Italy	1
Mexico	1
Poland	1
Sweden	1
Denmark	2
Germany	2
Spain	2
UK	2
USA	2

HAVING

SELECT Country, COUNT(*) AS CountCountry
FROM customers
GROUP BY Country
HAVING COUNT(*)>=2
ORDER BY 2,1

Country	CountCountry
Denmark	2
Germany	2
Spain	2
UK	2
USA	2

WHERE, HAVING

SELECT Country, COUNT(*) AS CountCountry
FROM customers
WHERE LEN(PostalCode)=5
GROUP BY Country
HAVING COUNT(*)>=2
ORDER BY 2,1

Country	CountCountry
Germany	2
Spain	2
USA	2

Integritätsbedingungen

Realisierung durch Auffinden von Datensätzen, die der Integrität widersprechen.
Das Ergebnis der entsprechenden Anfrage muss die leere Menge sein.

Beispiele

IB: Alle CustomerIDs sind fünfstellig.
Es gibt keine fünfstelligen CustomerID.

SELECT *
FROM customers
WHERE LEN(CustomerID)<>5

CustomerID	CompanyName	ContactName	Address	City	PostalCode	Country

IB: Ein ContactName hat maximal ein Leerzeichen (ContactName hat Format: FirstName Leerzeichen LastName).
Es gibt kein ContactName mit 2 Leerzeichen.

SELECT *
FROM customers
WHERE ContactName LIKE '% % %'

CustomerID	CompanyName	ContactName	Address	City	PostalCode	Country

IB: PostalCode enthält mindestens eine Ziffer.
Es gibt keinen PostalCode der keine Ziffer enthält.

SELECT *
FROM customers
WHERE PostalCode NOT LIKE '%0%' AND PostalCode NOT LIKE '%1%' AND
                PostalCode NOT LIKE '%2%' AND PostalCode NOT LIKE '%3%' AND
                PostalCode NOT LIKE '%4%' AND PostalCode NOT LIKE '%5%' AND
                PostalCode NOT LIKE '%6%' AND PostalCode NOT LIKE '%7%' AND
                PostalCode NOT LIKE '%8%' AND PostalCode NOT LIKE '%9%'

SELECT *
FROM customers
WHERE NOT (PostalCode LIKE '%0%' OR PostalCode LIKE '%1%' OR
                        PostalCode LIKE '%2%' OR PostalCode LIKE '%3%' OR
                        PostalCode LIKE '%4%' OR PostalCode LIKE '%5%' OR
                          PostalCode LIKE '%6%' OR PostalCode LIKE '%7%' OR
                          PostalCode LIKE '%8%' OR PostalCode LIKE '%9%')

CustomerID	CompanyName	ContactName	Address	City	PostalCode	Country

IB: Ein deutscher PostalCode besteht aus genau 5 Ziffern.
Es gibt keinen deutschen PostalCode, der nicht aus genau 5 Ziffern besteht.

SELECT PostalCode,
                 LEFT(RIGHT(LEFT(PostalCode,5),5),1) AS PC1,
               LEFT(RIGHT(LEFT(PostalCode,5),4),1) AS PC2,
               LEFT(RIGHT(LEFT(PostalCode,5),3),1) AS PC3,
               LEFT(RIGHT(LEFT(PostalCode,5),2),1) AS PC4,
                 LEFT(RIGHT(LEFT(PostalCode,5),1),1) AS PC5
FROM customers
WHERE Country='Germany' AND
                LEFT(RIGHT(LEFT(PostalCode,5),5),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,5),4),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,5),3),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,5),2),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,5),1),1) IN ('0','1','2','3','4','5','6','7','8','9')

PostalCode	PC1	PC2	PC3	PC4	PC5
12209	1	2	2	0	9
14776	1	4	7	7	6

SELECT *
FROM customers
WHERE Country='Germany' AND
                LEFT(RIGHT(LEFT(PostalCode,5),5),1) NOT IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,5),4),1) NOT IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,5),3),1) NOT IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,5),2),1) NOT IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,5),1),1) NOT IN ('0','1','2','3','4','5','6','7','8','9')

CustomerID	CompanyName	ContactName	Address	City	PostalCode	Country

IB: CustomerID ist Primärschlüssel. Zwei unterschiedliche Zeilen besitzen unterschiedliche CustomerID-Werte.
Es gibt keinen weiteren Satz, der den gleichen CustomerID hat, aber unterschiedliche Werte für CompanyName, ContactName, Address, City, PostalCode oder Country.

SELECT *
FROM customers c1
WHERE EXISTS (SELECT *
                                FROM customers c2
                                WHERE c1.CustomerId=c2.CustomerID AND
                                                (c1.CompanyName<>c2.CompanyName OR
                                                   c1.ContactName<>c2.ContactName OR
                                               c1.Address<>c2.Address OR
                                                 c1.City<>c2.City OR
                                               c1.PostalCode<>c2.PostalCode OR
                                                   c1.Country<>c2.Country))

CustomerID	CompanyName	ContactName	Address	City	PostalCode	Country

Erzeugung eines 'virtuellen' Attributs 'Sales'

SELECT Country, CompanyName, LEFT(PostalCode,4) AS Sales
FROM customers
WHERE LEFT(RIGHT(LEFT(PostalCode,4),4),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),3),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),2),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),1),1) IN ('0','1','2','3','4','5','6','7','8','9')
ORDER BY Country, CompanyName

Country	CompanyName	Sales
Austria	Ernst Handel	8010
Denmark	Simons bistro	1734
Denmark	Vaffeljernet	8200
France	Paris spécialités	7501
Germany	Alfreds Futterkiste	1220
Germany	Königlich Essen	1477
Italy	Magazzini Alimentari Riuniti	2410
Mexico	Centro comercial Moctezuma	0502
Spain	FISSA Fabrica Inter. Salchichas S.A.	2803
Spain	Galería del gastrónomo	0802
USA	Rattlesnake Canyon Grocery	8711
USA	The Big Cheese	9720

MIN, AVG, MAX

SELECT Country, MIN(LEFT(PostalCode,4)) AS MinSales,
                                AVG(LEFT(PostalCode,4)) AS AvgSales,
                                MAX(LEFT(PostalCode,4)) AS MaxSales
FROM customers
WHERE LEFT(RIGHT(LEFT(PostalCode,4),4),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),3),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),2),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),1),1) IN ('0','1','2','3','4','5','6','7','8','9')
GROUP BY Country
ORDER BY Country

Country	MinSales	AvgSales	MaxSales
Austria	8010	8010	8010
Denmark	1734	4967	8200
France	7501	7501	7501
Germany	1220	1348.5	1477
Italy	2410	2410	2410
Mexico	0502	502	0502
Spain	0802	1802.5	2803
USA	8711	9215.5	9720

Country	MinSales	AvgSales	MaxSales
USA	8711	9215.5	9720
Austria	8010	8010	8010
France	7501	7501	7501
Denmark	1734	4967	8200
Italy	2410	2410	2410
Spain	0802	1802.5	2803
Germany	1220	1348.5	1477
Mexico	0502	502	0502

HAVING inklusive MIN- und AVG-Selektion

Länder mit kleiner Umsatzspanne (Umsatz=Sales), d.h., Länder, bei denen der minimale Geschäftsumsatz nur 20 Prozent unter dem durchschnittlichen Geschäftsumsatz liegt.

SELECT Country, MIN(LEFT(PostalCode,4)) AS MinSales,
                                MIN(LEFT(PostalCode,4))*1.20 AS MinSalesTimes1Dot20,
                                AVG(LEFT(PostalCode,4)) AS AvgSales,
                                MAX(LEFT(PostalCode,4)) AS MaxSales
FROM customers
WHERE LEFT(RIGHT(LEFT(PostalCode,4),4),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),3),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),2),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),1),1) IN ('0','1','2','3','4','5','6','7','8','9')
GROUP BY Country
HAVING COUNT(*)>1 AND MIN(LEFT(PostalCode,4))*1.20>=AVG(LEFT(PostalCode,4))
ORDER BY 1

Country	MinSales	MinSalesTimes1Dot20	AvgSales	MaxSales
Germany	1220	1464	1348.5	1477
USA	8711	10453.2	9215.5	9720

Wie oben ohne die einschränkende HAVING-Bedingung an MIN/AVG

SELECT Country, MIN(LEFT(PostalCode,4)) AS MinSales,
                                MIN(LEFT(PostalCode,4))*1.20 AS MinSalesTimes1Dot20,
                                AVG(LEFT(PostalCode,4)) AS AvgSales,
                                MAX(LEFT(PostalCode,4)) AS MaxSales
FROM customers
WHERE LEFT(RIGHT(LEFT(PostalCode,4),4),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),3),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),2),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),1),1) IN ('0','1','2','3','4','5','6','7','8','9')
GROUP BY Country
HAVING COUNT(*)>1
ORDER BY 1

Country	MinSales	MinSalesTimes1Dot20	AvgSales	MaxSales
Denmark	1734	2080.8	4967	8200
Germany	1220	1464	1348.5	1477
Spain	0802	962.4	1802.5	2803
USA	8711	10453.2	9215.5	9720

MIN und MAX ohne Aggregationsfunktionen - Konstanten im SELECT - UNION

SELECT Country, LEFT(PostalCode,4) AS Sales, 'Minimum' As Explanation
FROM customers c
WHERE LEFT(RIGHT(LEFT(PostalCode,4),4),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),3),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),2),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),1),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(PostalCode,4) <= ALL (SELECT LEFT(PostalCode,4)
                                                                   FROM customers c_inner
                                                                 WHERE c.Country=c_inner.Country)
UNION
SELECT Country, LEFT(PostalCode,4) AS Sales, 'Maximum' As Explanation
FROM customers c
WHERE LEFT(RIGHT(LEFT(PostalCode,4),4),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),3),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),2),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),1),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(PostalCode,4) >= ALL (SELECT LEFT(PostalCode,4)
                                                                   FROM customers c_inner
                                                                   WHERE c.Country=c_inner.Country)
ORDER BY 1 ASC, 3 DESC

Country	Sales	Explanation
Austria	8010	Minimum
Austria	8010	Maximum
Denmark	1734	Minimum
Denmark	8200	Maximum
France	7501	Minimum
France	7501	Maximum
Germany	1220	Minimum
Germany	1477	Maximum
Italy	2410	Minimum
Italy	2410	Maximum
Mexico	0502	Minimum
Mexico	0502	Maximum
Spain	0802	Minimum
Spain	2803	Maximum
USA	8711	Minimum
USA	9720	Maximum

<=ALL und >=ALL in einer Anfrage

SELECT Country, LEFT(PostalCode,4) AS SalesMinMaxAvgCoincide
FROM customers c
WHERE LEFT(RIGHT(LEFT(PostalCode,4),4),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),3),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),2),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),1),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(PostalCode,4) <= ALL (SELECT LEFT(PostalCode,4)
                                                                   FROM customers c_inner
                                                                 WHERE c.Country=c_inner.Country) AND
                LEFT(PostalCode,4) >= ALL (SELECT LEFT(PostalCode,4)
                                                                   FROM customers c_inner
                                                                 WHERE c.Country=c_inner.Country)

Country	SalesMinMaxAvgCoincide
Mexico	0502
Austria	8010
Italy	2410
France	7501

SUM

SELECT Country, SUM(LEFT(PostalCode,4)) AS SumSales
FROM customers
WHERE LEFT(RIGHT(LEFT(PostalCode,4),4),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),3),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),2),1) IN ('0','1','2','3','4','5','6','7','8','9') AND
                LEFT(RIGHT(LEFT(PostalCode,4),1),1) IN ('0','1','2','3','4','5','6','7','8','9')
GROUP BY Country
ORDER BY 2 DESC

Country	SumSales
USA	18431
Denmark	9934
Austria	8010
France	7501
Spain	3605
Germany	2697
Italy	2410
Mexico	502

Beobachtung: MIN and MAX ermitteln Werte, die in der Tabelle selbst zu finden sind. COUNT, SUM und AVG berechnen Werte, die in der Regel nicht in der Tabelle selbst stehen.

Weitere Themen

Null-Werte in SQL (Wahrheitstabellen)
SQL-Gesetze; z.B. 'IN ~ =ANY', 'NOT (op ALL) ~ opNeg ANY', 'NOT (op ANY) ~ opNeg ALL'

THE END