Frank Kalis

Die Möglichkeit, Unterabfragen zu erstellen und SELECT Statements zu korrelieren ist ein ziemlich mächtiges Feature in SQL und SQL Server unterstützt glücklicherweise die Erstellung von korrelierten und unkorrelierten Unterabfragen. Allerdings gibt es auch hier einige Stolperfallen, die man kennen sollte, um nicht später unangenehm überrascht zu werden. Eine dieser Stolperfallen hängt mit der Namensauflösung von Tabellenspalten in Unterabfragen zusammen. Sie ist Gegenstand dieses Beitrages.

Gegeben ist folgende Ausgangssituation:

2 Tabellen (dbo.t1 und dbo.t2), die über das Spaltenpaar p1 - c1 miteinander verknüpft sind.

IF OBJECT_ID('dbo.t1') IS NOT NULL
    DROP TABLE dbo.t2, dbo.t1;
GO 
CREATE TABLE dbo.t1 
(
    p1 int PRIMARY KEY,
    v1 varchar(20)
);
GO
CREATE TABLE dbo.t2
(
    c1 int
    CONSTRAINT FK_t2_t1 FOREIGN KEY REFERENCES dbo.t1(p1),
    d1 datetime,
);

SET NOCOUNT ON;
INSERT INTO dbo.t1 SELECT 1, 'Hallo';
INSERT INTO dbo.t1 SELECT 2, 'Welt';
INSERT INTO dbo.t1 SELECT 3, 'WTF';
INSERT INTO dbo.t2 SELECT 1, '20080520';
INSERT INTO dbo.t2 SELECT 1, '20080521';
INSERT INTO dbo.t2 SELECT 1, '20080522';
SET NOCOUNT OFF;

Nun sollen in einer Abfrage alle Zeilen aus t1 zurückgegeben, für die ein Match in der Tabelle t2 existiert, wobei zusätzlich noch eine Einschränkung auf einen bestimmten Datumsbereich von t2 vorgenommen wird (d1 = '20080520'). Bevor wir nun die Abfrage tatsächlich schreiben, zunächst einmal ein kurzer Blick auf das erwartete Resultset. Man stellt fest, dass es genau ein Spaltenpaar p1 - c1 gibt, welches einen Match in seinen Werten aufweist und wo das Prädikat d1 = '20080520' erfüllt ist. Also erwarten wir einen Datensatz in der Ergebnismenge.

Eine Möglichkeit, diese Abfrage zu formulieren ist die Verwendung einer unkorrelierten Unterabfrage in einer IN Klausel:

SELECT 
    *
FROM
    dbo.t1
WHERE
    p1 IN
    (SELECT
        p1
    FROM
        dbo.t2
    WHERE
        d1 = '20080520');

Hier ist die Ergebnismenge:

p1          v1
----------- --------------------
1           Hallo
2           Welt
3           WTF

(3 row(s) affected)

Wir erhalten alle Zeilen aus t1. Und nicht nur das. Schaut man mal genauer auf die Abfrage und dort speziell auf die IN Klausel, wird man feststellen, dass die Spalte p1 in der Tabelle dbo.t2 überhaupt nicht existiert. Trotzdem wird die Abfrage fehlerfrei ausgeführt und liefert auch noch ein Ergebnis. Warum?

An dieser Stelle kommt nun die bereits erwähnte Namensauflösung ins Spiel. SQL Server versucht p1 nach dem inneren SELECT zu dbo.t2.p1 aufzulösen. Da dies nicht gelingt, weil es in der Tabelle dbo.t2 diese Spalte eben nicht gibt, wir uns aber mit unserer Abfrage auf mehr als eine Tabelle beziehen, wird p1 nach dem äusseren SELECT zu dbo.t1.p1 aufgelöst. Diese Spalte existiert in t1 und somit haben wir nun effektiv eine korrelierte Unterabfrage der Form:

SELECT 
    t1.p1
FROM
    dbo.t2
WHERE
    d1 = '20080520'

Sobald es auch nur eine Zeile in t2 gibt, für die diese WHERE Klausel WAHR ist, ist die WHERE Klausel der gesamten äusseren IN Klausel WAHR und somit werden alle Zeilen aus t1 zurückgegeben.

Wie kann man so einen Fehler vermeiden?

Nun, zunächst ist es wichtig zu verstehen, dass in der inneren Abfrage sowohl Referenzen zu inneren als auch zur äusseren Abfrage möglich sind. Anderenfalls gäbe es eben keine Möglichkeit korrelierte Unterabfragen zu erstellen. Hat man dies verinnerlicht, besteht die einfachste Lösung hierfür in der vollständigen Qualifizierung sämtlicher Spaltennamen in der Form:

SELECT 
    *
FROM
    dbo.t1
WHERE
    p1 IN
    (SELECT
        dbo.t2.p1
    FROM
        dbo.t2
    WHERE
        d1 = '20080520');

oder

SELECT 
    *
FROM
    dbo.t1
WHERE
    p1 IN
    (SELECT
        MeinAlias.p1
    FROM
        dbo.t2 MeinAlias
    WHERE
        MeinAlias.d1 = '20080520');

Führt man nun diese beiden Abfragen aus, erhält man in beiden Fällen sofort eine Fehlermeldung:

Msg 207, Level 16, State 1, Line xy
Invalid column name 'p1'.

Fazit?

Dieser Fehler mag vielleicht in der Praxis nicht unbedingt häufig auftreten, wenn er aber doch einmal auftritt, ist aber meiner Meinung nicht unbedingt einfach zu troubleshooten, da er auf der logischen Ebene angesiedelt und daher nicht offensichtlich ist. Die Verwendung von Tabellenaliasen, die generell eine gute Praxis ist, kann hier helfen den Fehler abzufangen, bevor er überhaupt auftreten kann.

CREATE TABLE #t
(
	c1 int
)
GO
CREATE INDEX IX_c1 ON #t(c1)
GO
SELECT 
	[object_id], [name]
FROM 
	tempdb.sys.indexes
WHERE 
	[object_id] = OBJECT_ID('tempdb.dbo.#t') AND
	type > 0
DROP TABLE #t
GO

object_id   name  
----------- -------
389576426   IX_c1

(1 row(s) affected)

Das Buch bietet einen gut strukturierten Zugang in die vielfältige Welt der Business Intelligence (BI) und liefert einen kompetenten Überblick über die BI Funktionalitäten des SQL Server 2005. Dabei reicht die Bandbreite der behandelten Themen von OLAP und Data Mining über die Integration- und Reporting- bis hin zur Programmierung der Analysis Services. Für den Einsteiger bietet das Buch fundiertes Fachwissen, das anschaulich und kompetent präsentiert wird.

Ganze Geschichte »