Datenbanken und SQL kennen den undefinierten Feldinhalt NULL. Oftmals wird seine Wirkung auf Abfragen unterschätzt, obwohl er nicht unerhebliche, und manchmal nicht sofort nachvollziehbare, Auswirkungen auf das Abfragergebnis haben kann. So kann ein einziger NULL-Wert dafür sorgen, dass ganze Listen leer bleiben, obwohl man mit einigen Tausend Ergebnissen gerechnet hatte. Welche Rolle NULL-Werte z.B. bei der
Abfrage mit IN, bzw. der Umkehrung NOT IN spielen, habe ich in meinem Blog-Eintrag „Ist NOT IN die Umkehrung von IN?” bereits erläutert.
Genau diese NULL-Werte und ihre Auswirkungen spielen mit eine Rolle für den Unterschied zwischen IN- und EXISTS-Abfragen.
Mal abgesehen von teilweise erheblichen Unterschieden in Bezug auf die Abfragegeschwindigkeit sind die weiteren Unterschiede nicht immer sofort ersichtlich. Oftmals liefern beide exakt die selben Ergebnisse.
Ein entscheidender Unterschied ist jedoch, dass für EXISTS, und ganz besonders für
NOT EXISTS, NULL-Werte keine Auswirkungen auf das Ergebnis haben. NOT EXISTS ist, im Gegensatz zu NOT IN für IN (siehe „Ist NOT IN die Umkehrung von IN?”), exakt und in jedem Fall die Umkehrung zu EXISTS.
EXISTS kann nämlich als Ergebnis nur WAHR oder FALSCH haben, jenachdem ob die
Unterabfrage einen (oder mehrere) oder eben keinen Datensatz zurückliefert.
Damit ist die Umkehrung auch immer nur FALSCH oder WAHR.
Einige Beispiele sollen dies verdeutlichen. Dazu legen wir ein paar Beispieldaten an:
CREATE TABLE zutaten (
id int(8) NOT NULL,
name varchar(64) NOT NULL,
CONSTRAINT PRIMARY KEY pk_zutaten (id)
);
INSERT INTO zutaten (id, name) VALUES
(1, 'Tequila'),
(2, 'Brauner Rum'),
(3, 'Weisser Rum'),
(4, 'Cachaca'),
(5, 'Orangensaft'),
(6, 'Ananassaft'),
(7, 'Cola');
CREATE TABLE cocktails (
id int(8) NOT NULL,
name varchar(64) NOT NULL,
spirituose int(8) NULL,
no_alkohol int(8) NULL,
CONSTRAINT FOREIGN KEY fk_spirituose (spirituose)
REFERENCES zutaten (id),
CONSTRAINT FOREIGN KEY fk_no_alkohol (no_alkohol)
REFERENCES zutaten (id)
);
INSERT INTO cocktails (id, name, spirituose, no_alkohol) VALUES
(1, 'Tequila Sunrise', 1, 5),
(2, 'Caipirinha', 4, NULL),
(3, 'Cuba Libre', 2, 7),
(4, 'Mai Thai', 2, 6),
(5, 'Mojito', 3, NULL);
Aus unserer kleinen Cocktaildatenbank möchten wir jetzt alle Cocktails haben, die als No-Alkohol-Zutat einen Saft haben. Die naheliegendste Lösung wäre sicherlich ein JOIN, da es aber um den Unterschied von IN und EXISTS geht, bleibt diese hier unberücksichtigt.
Lösung mit IN:
SELECT c.name
FROM cocktails c
WHERE c.no_alkohol IN (
SELECT z.id
FROM zutaten z
WHERE z.name LIKE '%saft'
)
Ergebnis:
| name |
|---|
| Tequila Sunrise |
| Mai Thai |
Lösung mit EXISTS:
SELECT c.name
FROM cocktails c
WHERE EXISTS (
SELECT 1
FROM zutaten z
WHERE z.name LIKE '%saft'
AND z.id = c.no_alkohol
)
Ergebnis:
| name |
|---|
| Tequila Sunrise |
| Mai Thai |
Die Ergebnisse sind identisch, es gibt also keinen Unterschied zwischen IN und EXISTS.
Wie sieht es nun mit der negierten Frage aus? Welche Cocktails haben KEINEN Saft als No-Alkohol-Zutat?
Eigentlich sollten das ja alle Anderen sein, also Caipirinha und Mojito, die jeweils gar keine No-Alkohol-Zutat haben, sowie Cuba Libre, der zwar eine solche Zutat hat, die jedoch kein Saft ist.
Lösung mit NOT IN:
SELECT c.name
FROM cocktails c
WHERE c.no_alkohol NOT IN (
SELECT z.id
FROM zutaten z
WHERE z.name LIKE '%saft'
)
Ergebnis:
| name |
|---|
| Cuba Libre |
Lösung mit NOT EXISTS:
SELECT c.name
FROM cocktails c
WHERE NOT EXISTS (
SELECT 1
FROM zutaten z
WHERE z.name LIKE '%saft'
AND z.id = c.no_alkohol
)
Ergebnis:
| name |
|---|
| Caipirinha |
| Cuba Libre |
| Mojito |
Nur die NOT EXISTS-Abfrage liefert das erwartete Ergebnis. Die Datensätze ohne jegliche
No-Alkohol-Zutat werden nicht berücksichtigt. Es gibt also einen Unterschied zwischen NOT IN und NOT EXISTS
Als nächstes wollen wir wissen, welche Zutaten als No-Alkohol-Zutat verwendet werden.
Lösung mit IN:
SELECT z.name
FROM zutaten z
WHERE z.id IN (
SELECT c.no_alkohol
FROM cocktails c
)
Ergebnis:
| name |
|---|
| Orangensaft |
| Ananassaft |
| Cola |
Lösung mit EXISTS:
SELECT z.name
FROM zutaten z
WHERE EXISTS (
SELECT 1
FROM cocktails c
WHERE c.no_alkohol = z.id
)
Ergebnis:
| name |
|---|
| Orangensaft |
| Ananassaft |
| Cola |
Die Ergebnisse sind wieder identisch. Auch hier gibt es keinen Unterschied zwischen
IN und EXISTS.
Wie sieht es nun wiederum mit der Umkehrung aus? Welche Zutaten werden in keinem Cocktail
als No-Alkohol-Zutat verwendet. Das müßten ja jetzt alle Spirituosen, also Tequila,
Brauner und Weisser Rum, sowie Cachaca sein.
Lösung mit NOT IN:
SELECT z.name
FROM zutaten z
WHERE z.id NOT IN (
SELECT c.no_alkohol
FROM cocktails c
)
Diese Abfrage liefert ein leeres Ergebnis!
Lösung mit NOT EXISTS:
SELECT z.name
FROM zutaten z
WHERE NOT EXISTS (
SELECT 1
FROM cocktails c
WHERE c.no_alkohol = z.id
)
Ergebnis:
| name |
|---|
| Tequila |
| Brauner Rum |
| Weisser Rum |
| Cachaca |
Auch hier tun sich wieder erhebliche Unterschiede in den Abfrageergebnissen auf. Wieder
ist es die NOT EXISTS-Abfrage die das erwartete Ergebnis liefert, während das NOT IN diesmal
sogar gänzlich versagt. (Warum das so ist steht in „Ist NOT IN die Umkehrung von IN?”)
Fazit: Der Unterschied (neben möglichen Performanceunterschieden) zwischen IN und EXITS liegt
hauptsächlich in deren Umkehrungen NOT IN bzw. NOT EXISTS und zwar genau dann, wenn NULL-Werte zu
berücksichtigen sind. Wenn man aus diversen Gründen (z.B. in UPDATEs) auf JOINs verzichten und
ausschließende Bedingungen formulieren muß, dann ist - für den Fall das mit NULL-Werten zu rechnen ist -
die Verwendung von NOT EXISTS der von NOT IN vorzuziehen.
