T-SQL, Teil 2: SELECT in der Praxis

Die Montagsauswertung für den Vertrieb entsteht in Access im Abfrageentwurf: Positionen, Kunden, Umsätze und einige Gruppierungen. Zieht das Backend auf den SQL Server um, laufen Teile davon nicht mehr oder liefern in unserer Konfiguration andere Zahlen. Ein TOP 1 liefert dann eine Zeile statt zweier, ein Durchschnitt verliert seine Nachkommastellen, und eine Summe kommt als Text in Access an. Wer SELECT in T-SQL schreibt, kennt viel davon aus Access. Umdenken müssen wir an einigen Stellen, und einige davon zeigen keine Fehlermeldung, sondern nur andere Ergebnisse. Wie wir die Abfragen schreiben, die im Alltag gebraucht werden, und worauf wir dabei achten, zeigt dieser zweite Teil der Reihe.

Die Auswertungen des Vertriebs

Als Szenario dient wieder die Bestellverwaltung aus T-SQL, Teil 1: Von Jet-SQL zu T-SQL. Der Vertrieb möchte wöchentlich sechs Fragen beantwortet haben:

• Welche Positionen hatten den höchsten Betrag?
• Welche Kunden haben noch keine Bestellung aufgegeben?
• Wie viel Umsatz bringen die einzelnen Warengruppen?
• Welche Kunden haben die meisten Bestellungen?
• In welche Klasse fallen die Kunden nach ihrem Umsatz?
• Wie lässt sich die Kundenliste seitenweise anzeigen, damit nicht die gesamte Liste vom Server geholt wird?

Die Beispiele laufen gegen die Datenbank Bestellverwaltung_TSQL mit den 330 Bestellungen, 32 Kunden und 825 Positionen aus Teil 1. Wer die Daten verändert hat, führt das Skript Bestellverwaltung_TSQL.sql erneut aus. Es legt die Datenbank neu an und löscht dabei eine vorhandene gleichen Namens. Die Hilfsfunktionen PassThroughOeffnen, PassThroughWert, SqlText und SqlDatum stammen aus dem Modul mdlTSQLEinstiegBeispiele aus Teil 1 und liegen auch im Frontend TSQL_SELECTInDerPraxis.accdb bereit. Die neuen VBA-Funktionen dieses Beitrags stehen im Modul mdlSelectBeispiele.

Die größten Positionen

Wir beginnen mit den fünf Positionen mit dem höchsten Betrag. Der Betrag einer Position ist Menge mal Einzelpreis mal (1 minus Rabatt). Der Rabatt steht als Anteil in der Tabelle, 0,05 entspricht fünf Prozent. Berechnete Spalten benennen wir mit einem Alias, den wir wie in Access mit dem Schlüsselwort AS angeben. Die Positionen stehen in tblBestelldetails, den Namen der Produkte holen wir aus tblProdukte (siehe Listing 1).

SELECT TOP (5)
    d.BestellungID,
    p.Produkt,
    d.Menge,
    d.Einzelpreis,
    d.Rabatt,
    CAST(d.Menge * d.Einzelpreis * (1 - d.Rabatt)
         AS decimal(10,2)) AS Zeilenbetrag
FROM dbo.tblBestelldetails AS d
     INNER JOIN dbo.tblProdukte AS p ON p.ProduktID = d.ProduktID
ORDER BY Zeilenbetrag DESC, d.BestelldetailID;

Listing 1: Die fünf größten Bestellpositionen

Das Ergebnis sind die fünf Positionen mit den Beträgen 399,00 Euro bis 3,00 Euro (siehe Bild 1). TOP (5) begrenzt die Ausgabe auf fünf Zeilen. Die ORDER BY-Klausel sortiert nach dem Alias Zeilenbetrag. Als zweites Sortierkriterium dient d.BestelldetailID, damit die Reihenfolge bei gleichen Beträgen festliegt.

Bild 1: Die fünf größten Positionen im Ergebnisraster von SSMS

Der CAST auf decimal(10,2) rundet den Betrag auf zwei Nachkommastellen. Bei Aggregatfunktionen hat er eine weitere Aufgabe, die im Abschnitt Dezimalzahlen in Access folgt.

Aliase und die Reihenfolge der Auswertung

In ORDER BY durften wir den Alias Zeilenbetrag verwenden. In WHERE funktioniert das nicht. Wir versuchen es mit den Positionen, deren Betrag über 300 Euro liegt:

SELECT Menge * Einzelpreis * (1 - Rabatt) AS Betrag
FROM dbo.tblBestelldetails
WHERE Betrag > 300;

Der Server meldet Ungültiger Spaltenname „Betrag“. Woran liegt das? Er wertet eine Abfrage logisch in einer anderen Reihenfolge aus, als wir die Klauseln hinschreiben. Der tatsächliche Ausführungsplan kann davon noch einmal abweichen. Die logische Reihenfolge lautet:

• FROM/JOIN: welche Tabellen verknüpft werden
• WHERE: welche Zeilen übrig bleiben
• GROUP BY: wie die Zeilen zusammengefasst werden
• HAVING: welche Gruppen übrig bleiben
• SELECT: welche Spalten entstehen, und damit erst hier auch die Aliase
• ORDER BY: wie das Ergebnis sortiert wird, hier sind die Aliase schon bekannt
• TOP/OFFSET: wie viele Zeilen davon ausgegeben werden

Ein Alias aus der SELECT-Liste ist in WHERE, GROUP BY und HAVING also noch nicht bekannt. In Access ist das nicht anders. Dort fragt die Abfrage allerdings nach einem Parameterwert, statt einen Fehler zu melden. Wir wiederholen deshalb den Ausdruck in WHERE oder kapseln die Abfrage als Unterabfrage, davon handelt der nächste Teil.

Auch DISTINCT kennen wir aus Access. Es entfernt doppelte Zeilen aus dem Ergebnis. Die Länder der Kunden stehen so nur einmal in der Liste:

SELECT DISTINCT Land
FROM dbo.tblKunden
ORDER BY Land;

Die Abfrage liefert drei Zeilen, obwohl tblKunden 32 Datensätze enthält.

TOP und Gleichstände

Die Frage nach den Kunden mit den meisten Bestellungen führt zu TOP. In Access schreiben wir die Abfrage mit TOP 1 und einer absteigenden Sortierung nach der Zahl der Bestellungen. In unseren Tests lieferte sie zwei Zeilen, weil zwei Kunden jeweils 19 Bestellungen haben. Access gab bei diesem Gleichstand beide Kunden zurück.

In T-SQL liefert TOP (1) ohne weitere Angabe höchstens eine Zeile. Welcher der beiden Kunden das ist, steht ohne ein weiteres Sortierkriterium nicht fest. Wer Gleichstände wie Access behandeln möchte, verwendet WITH TIES (siehe Listing 2).

SELECT TOP (1) WITH TIES
    k.KundeID, k.Nachname, COUNT(*) AS Bestellungen
FROM dbo.tblKunden AS k
     INNER JOIN dbo.tblBestellungen AS b ON b.KundeID = k.KundeID
GROUP BY k.KundeID, k.Nachname
ORDER BY COUNT(*) DESC;

Listing 2: WITH TIES liefert die Gleichplatzierten

Das Ergebnis sind die Kunden Schröder und Vogel mit je 19 Bestellungen. WITH TIES setzt eine ORDER BY-Klausel voraus, und diese Klausel bestimmt, was als Gleichstand zählt. Hängen wir dort k.KundeID an, sind die beiden nicht mehr gleich platziert, und WITH TIES liefert nur eine Zeile.

Statt einer Anzahl lässt sich mit TOP (n) PERCENT auch ein Prozentsatz der Zeilen angeben.

Die Kundenliste seitenweise holen

Die Kundenliste soll ein Formular seitenweise zeigen. TOP liefert nur die ersten Zeilen und kennt keine Seiten. Dafür gibt es OFFSET und FETCH: OFFSET überspringt die angegebene Zahl von Zeilen, FETCH NEXT holt die folgenden.

Die zweite Seite mit zehn Kunden je Seite lautet so:

SELECT KundeID, Nachname, Ort
FROM dbo.tblKunden
ORDER BY Nachname, KundeID
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

Das Ergebnis sind die Kunden Köhler bis Neumann. OFFSET gehört zu ORDER BY und ist ohne diese Klausel nicht erlaubt. Die Sortierung muss außerdem eindeutig sein, sonst können Datensätze beim Blättern doppelt oder gar nicht erscheinen. Haben zwei Kunden denselben Nachnamen, entscheidet KundeID als zweites Kriterium, auf welcher Seite sie stehen.

Das setzt voraus, dass sich die Daten zwischen den Seitenaufrufen nicht verändern. Neue, gelöschte oder umsortierte Datensätze können die Seitengrenzen verschieben.

In VBA setzen wir die Anweisung aus der Seitenzahl und der Zahl der Zeilen je Seite zusammen. Das erledigt die Funktion KundenSeite. Sie setzt die Seitenzahl auf 1, wenn sie kleiner ist, und löst bei einer Seitengröße unter 1 einen Fehler aus, weil FETCH NEXT mindestens eine Zeile verlangt (siehe Listing 3).

Public Function KundenSeite(lngSeite As Long, lngProSeite As Long) As DAO.Recordset
    Dim strSQL As String
    If lngSeite < 1 Then lngSeite = 1
    If lngProSeite < 1 Then
        Err.Raise 5, "KundenSeite", "Mindestens 1 Zeile je Seite"
    End If
    strSQL = "SELECT KundeID, Nachname, Ort FROM dbo.tblKunden " & _
             "ORDER BY Nachname, KundeID " & _
             "OFFSET " & (lngSeite - 1) * lngProSeite & " ROWS " & _
             "FETCH NEXT " & lngProSeite & " ROWS ONLY"
    Set KundenSeite = PassThroughOeffnen(strSQL)
End Function

Listing 3: KundenSeite holt eine Seite der Kundenliste

Der Ausdruck (lngSeite – 1) * lngProSeite liefert den Wert für OFFSET. Die Funktion PassThroughOeffnen aus Teil 1 schickt den Text als temporäre Pass-Through-Abfrage an den Server und gibt das Recordset zurück.

Zum Ausprobieren gibt die Prozedur KundenSeiteAusgeben eine Seite im Direktbereich aus (siehe Listing 4).

Public Sub KundenSeiteAusgeben(lngSeite As Long, lngProSeite As Long)
    Dim rst As DAO.Recordset
    Set rst = KundenSeite(lngSeite, lngProSeite)
    Do While Not rst.EOF
        Debug.Print rst!KundeID, rst!Nachname, rst!Ort
        rst.MoveNext
    Loop
    rst.Close
End Sub

Listing 4: KundenSeiteAusgeben zeigt eine Seite der Kundenliste an

Der Aufruf KundenSeiteAusgeben 2, 10 liefert die zehn Kunden der zweiten Seite (siehe Bild 2). Seite 4 enthält die letzten beiden Kunden, Seite 5 ist leer.

Bild 2: Die zweite Seite der Kundenliste, ausgegeben von KundenSeiteAusgeben im Direktbereich

JOIN ohne Klammern

Bei Verknüpfungen unterscheiden sich Access und T-SQL deutlich. Der Abfrageentwurf von Access setzt um die bisherigen Verknüpfungen eine Klammer, sobald ein weiterer INNER JOIN hinzukommt. Ohne diese Klammern meldet die Datenbank-Engine einen Syntaxfehler.

Die Abfrage, die die Positionen über fünf Tabellen zählt, sieht in der SQL-Ansicht so aus (siehe Listing 5).

SELECT Count(*) AS Anzahl
FROM (((dbo_tblBestelldetails AS d
  INNER JOIN dbo_tblBestellungen AS b ON d.BestellungID = b.BestellungID)
  INNER JOIN dbo_tblKunden AS k ON b.KundeID = k.KundeID)
  INNER JOIN dbo_tblProdukte AS p ON d.ProduktID = p.ProduktID)
  INNER JOIN dbo_tblKategorien AS c ON p.KategorieID = c.KategorieID;

Listing 5: Fünf Tabellen in Access-SQL mit Klammern

Lassen wir die Klammern weg, meldet Access den Fehler 3075, dessen Text mit Syntaxfehler (fehlender Operator) in Abfrageausdruck beginnt.

T-SQL braucht keine Klammern. Die Verknüpfungen stehen hintereinander, und sie beziehen sich auf die Tabellen davor (siehe Listing 6).

SELECT COUNT(*) AS Anzahl
FROM dbo.tblBestelldetails AS d
     INNER JOIN dbo.tblBestellungen AS b ON b.BestellungID = d.BestellungID
     INNER JOIN dbo.tblKunden AS k ON k.KundeID = b.KundeID
     INNER JOIN dbo.tblProdukte AS p ON p.ProduktID = d.ProduktID
     INNER JOIN dbo.tblKategorien AS c ON c.KategorieID = p.KategorieID;

Listing 6: Dieselbe Verknüpfung in T-SQL, ohne Klammern

Der Wert Anzahl ist wie in der Access-Fassung 825. Das Schlüsselwort INNER dürfen wir weglassen, JOIN allein bedeutet dasselbe. In diesem Beitrag steht es ausgeschrieben, damit der Unterschied zu den äußeren Verknüpfungen erkennbar bleibt.

Kunden ohne Bestellung

Für die Frage nach den Kunden ohne Bestellung brauchen wir den LEFT JOIN. Er behält die Zeilen der linken Tabelle, auch wenn in der rechten nichts dazu passt. Ausgeschrieben heißt er LEFT OUTER JOIN. Die Spalten der rechten Tabelle sind für solche Zeilen NULL, und danach fragen wir in WHERE (siehe Listing 7).

SELECT k.KundeID, k.Nachname, k.Ort
FROM dbo.tblKunden AS k
     LEFT JOIN dbo.tblBestellungen AS b ON b.KundeID = k.KundeID
WHERE b.BestellungID IS NULL
ORDER BY k.KundeID;

Listing 7: Kunden ohne Bestellung per LEFT JOIN

Die Abfrage liefert drei Kunden: Peters, Kleinschmidt und Zimmermann. Im Frontend liegt sie als Pass-Through-Abfrage ptKundenOhneBestellung vor (siehe Bild 3).

Bild 3: Die Pass-Through-Abfrage ptKundenOhneBestellung in der Datenblattansicht

Für den Einsatz in VBA genügt oft die Zahl dieser Kunden. Die Funktion KundenOhneBestellungZaehlen schickt dieselbe Bedingung mit COUNT(*) an den Server und holt den Wert mit PassThroughWert (siehe Listing 8).

Public Function KundenOhneBestellungZaehlen() As Long
    Dim strSQL As String
    strSQL = "SELECT COUNT(*) FROM dbo.tblKunden k " & _
             "LEFT JOIN dbo.tblBestellungen b ON b.KundeID = k.KundeID " & _
             "WHERE b.BestellungID IS NULL"
    KundenOhneBestellungZaehlen = Nz(PassThroughWert(strSQL), 0)
End Function

Listing 8: KundenOhneBestellungZaehlen zählt die Kunden ohne Bestellung

Im Direktbereich liefert ?KundenOhneBestellungZaehlen() die 3.

Bedingungen im ON oder im WHERE

Bei einem LEFT JOIN entscheidet der Ort einer Bedingung über das Ergebnis. Das zeigt ein Beispiel: Wir wollen für die Kunden die Zahl der Bestellungen seit Jahresbeginn 2026, auch für Kunden ohne eine solche Bestellung. Im ersten Versuch steht die Bedingung im ON (siehe Listing 9).

SELECT k.KundeID, COUNT(b.BestellungID) AS Bestellungen
FROM dbo.tblKunden AS k
     LEFT JOIN dbo.tblBestellungen AS b
          ON b.KundeID = k.KundeID AND b.Bestelldatum >= '20260101'
GROUP BY k.KundeID;

Listing 9: Die Bedingung steht im ON

Das Ergebnis hat 32 Zeilen, also einen Eintrag je Kunde. Wer 2026 nicht bestellt hat, steht mit einer 0 in der Liste. Im zweiten Versuch verschieben wir die Bedingung in die WHERE-Klausel (siehe Listing 10).

SELECT k.KundeID, COUNT(b.BestellungID) AS Bestellungen
FROM dbo.tblKunden AS k
     LEFT JOIN dbo.tblBestellungen AS b ON b.KundeID = k.KundeID
WHERE b.Bestelldatum >= '20260101'
GROUP BY k.KundeID;

Listing 10: Dieselbe Bedingung im WHERE

Jetzt sind es 27 Zeilen. Woran liegt das? WHERE wirkt auf das Ergebnis der Verknüpfung. Für die drei Kunden ohne Bestellung ist Bestelldatum dort NULL, und ein Vergleich mit NULL ist weder wahr noch falsch. Die Zeilen fallen heraus. Dasselbe gilt für die zwei Kunden, deren Bestellungen vor 2026 liegen. Allgemein entfernt ein WHERE-Kriterium auf der rechten Tabelle, das NULL ausschließt, auch die Kunden ohne passende Bestellung. Die Abfrage verhält sich dann hinsichtlich dieser Zeilen wie ein INNER JOIN. Steht die Bedingung im ON, bleiben die Zeilen der linken Tabelle erhalten.

Selbstverknüpfung

Eine Tabelle lässt sich auch mit sich selbst verknüpfen. In tblMitarbeiter zeigt das Fremdschlüsselfeld VorgesetzterID auf MitarbeiterID derselben Tabelle. Um zu einem Mitarbeiter den Namen des Vorgesetzten zu bekommen, verwenden wir die Tabelle zweimal unter verschiedenen Aliasen. Mit LEFT JOIN bleibt die Geschäftsführung ohne Vorgesetzten im Ergebnis (siehe Listing 11).

SELECT m.Vorname + N' ' + m.Nachname AS Mitarbeiter,
       m.Funktion,
       ISNULL(v.Vorname + N' ' + v.Nachname, N'(oberste Ebene)') AS Vorgesetzter
FROM dbo.tblMitarbeiter AS m
     LEFT JOIN dbo.tblMitarbeiter AS v ON v.MitarbeiterID = m.VorgesetzterID
ORDER BY m.MitarbeiterID;

Listing 11: Selbstverknüpfung: Mitarbeiter und ihre Vorgesetzten

Das Ergebnis hat acht Zeilen. Bei Claudia Hoffmann steht (oberste Ebene), denn für sie ist VorgesetzterID leer und die rechte Seite des LEFT JOIN NULL.

Weitere Verknüpfungsarten brauchen wir seltener. RIGHT JOIN ist ein LEFT JOIN mit vertauschten Tabellen und lässt sich umschreiben. FULL OUTER JOIN behält die Zeilen beider Seiten und eignet sich für den Abgleich zweier Tabellen, etwa eines Imports gegen den Bestand. CROSS JOIN bildet das Kreuzprodukt beider Tabellen und kommt im nächsten Teil vor.

Umsatz je Warengruppe

Nun zum Umsatz der einzelnen Warengruppen. Beim Gruppieren gelten in T-SQL dieselben Grundregeln wie in Access. Eine Spalte der SELECT-Liste steht entweder in GROUP BY oder in einer Aggregatfunktion wie SUM, COUNT, AVG, MIN oder MAX. Im Abfrageentwurf von Access nehmen wir die Zuordnung über die Zeile Funktion vor. In T-SQL schreiben wir GROUP BY und die Aggregatfunktionen hin.

Wir werten das Jahr 2025 aus, ohne stornierte Bestellungen, und behalten nur Gruppen mit mehr als 500 Euro Umsatz (siehe Listing 12).

SELECT c.Kategorie,
       COUNT(*) AS Positionen,
       CAST(SUM(d.Menge * d.Einzelpreis * (1 - d.Rabatt))
            AS decimal(12,2)) AS Umsatz
FROM dbo.tblBestellungen AS b
     INNER JOIN dbo.tblBestelldetails AS d ON d.BestellungID = b.BestellungID
     INNER JOIN dbo.tblProdukte AS p ON p.ProduktID = d.ProduktID
     INNER JOIN dbo.tblKategorien AS c ON c.KategorieID = p.KategorieID
WHERE b.Bestelldatum >= '20250101' AND b.Bestelldatum < '20260101'
  AND b.BestellstatusID <> 5
GROUP BY c.Kategorie
HAVING SUM(d.Menge * d.Einzelpreis * (1 - d.Rabatt)) > 500
ORDER BY Umsatz DESC;

Listing 12: Umsatz je Warengruppe 2025 mit GROUP BY und HAVING

Das Ergebnis zeigt sieben Warengruppen, die größte ist Zubehör mit 7.205,65 Euro.

Der Unterschied zwischen WHERE und HAVING ergibt sich aus der Auswertungsreihenfolge. WHERE wirkt auf die einzelnen Zeilen vor dem Gruppieren, HAVING auf die fertigen Gruppen. Eine Bedingung auf eine Aggregatfunktion in WHERE führt zu einer Fehlermeldung. Eine Bedingung auf ein Gruppierungsfeld gehört in WHERE, weil dann weniger Zeilen in die Gruppierung gehen.

COUNT: Zeilen und Werte

Bei COUNT kommt es darauf an, was gezählt wird. COUNT(*) zählt Zeilen, COUNT(Spalte) zählt die Zeilen, in denen die Spalte nicht NULL ist. Eine leere Zeichenkette ist nicht NULL und wird mitgezählt.

Die folgende Abfrage zählt in tblBestellungen 330 Bestellungen, davon 14 mit einer Bemerkung und 312 mit einem Lieferdatum:

SELECT COUNT(*) AS Alle,
       COUNT(Bemerkung) AS MitBemerkung,
       COUNT(Lieferdatum) AS Geliefert
FROM dbo.tblBestellungen;

Die Fehlermeldung beim Gruppieren

Fehlt in GROUP BY eine Spalte aus der SELECT-Liste, nennt der Server die Spalte beim Namen. Im folgenden Beispiel fehlt MitarbeiterID:

SELECT KundeID, MitarbeiterID, COUNT(*) AS Anzahl
FROM dbo.tblBestellungen
GROUP BY KundeID;

Die Meldung lautet Die dbo.tblBestellungen.MitarbeiterID-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion und nicht in der GROUP BY-Klausel enthalten ist (siehe Bild 4). Welche Lösung passt, hängt von der Frage ab: Mit MitarbeiterID in GROUP BY entsteht eine Zeile je Kunde und Mitarbeiter, mit MAX(MitarbeiterID) bleibt es bei einer Zeile je Kunde.

Bild 4: Die Meldung von SSMS, wenn eine Spalte weder in GROUP BY noch in einer Aggregatfunktion steht

Eine Gesamtzeile mit ROLLUP

Oft soll die Auswertung eine Gesamtzeile enthalten. In Access erzeugen wir sie mit einer Ergebniszeile im Datenblatt oder mit einer zweiten Abfrage.

In T-SQL ergänzt ROLLUP die gruppierte Abfrage um eine Zeile, in der das Gruppierungsfeld NULL ist und die Aggregate über die gesamte Abfrage laufen. Die Funktion GROUPING unterscheidet diese Zeile von einer echten NULL-Gruppe. Mit ihr sortieren wir die Gesamtzeile ans Ende (siehe Listing 13).