SQL

Was ist SQL?

SQL, ausgesprochen als „Sequel“ oder „S-Q-L“, steht für Structured Query Language und wird verwendet, um Daten in relationalen Datenbanken zu lesen und zu schreiben. Es wurde bereits 1986 standardisiert – womit es älter ist als das Web – und ist nach wie vor die Art und Weise, wie fast jede Anwendung mit ihren Daten kommuniziert.

SQL besteht aus zwei Teilen. DDL (Data Definition Language) erstellt und ändert Strukturen: CREATE TABLE, ALTER TABLE, DROP INDEX. DML (Data Manipulation Language) arbeitet mit den Daten selbst: SELECT, INSERT, UPDATE, DELETE. Die meiste Zeit wirst du mit DML verbringen, wobei SELECT mit Abstand das am häufigsten verwendete Statement ist.

Wichtig ist zu verstehen, dass SQL keine Programmiersprache im herkömmlichen Sinne ist. In der Kernsprache gibt es keine Schleifen und in einer einfachen Query keine Variablen. Du beschreibst das gewünschte Ergebnis, und die Datenbank findet selbst heraus, wie sie dieses erzeugt.

Deklarativ und mengenbasiert

In einer Sprache wie JavaScript oder Python würde man einen Bericht durch Iteration berechnen:

const result = [];
for (const author of authors) {
  let count = 0;
  for (const post of posts) {
    if (post.author_id === author.id) count++;
  }
  if (count > 0) result.push({ author, count });
}

In SQL formuliert man dieselbe Absicht in einem einzigen Ausdruck:

SELECT a.name, COUNT(p.id) AS posts
FROM authors a
LEFT JOIN posts p ON p.author_id = a.id
GROUP BY a.id, a.name
HAVING COUNT(p.id) > 0;

Es gibt keine Schleife. Sie haben deklariert, dass Sie jeden Autor zusammen mit der Anzahl seiner Beiträge benötigen. Die Datenbank kann dies durch einen Index-Scan, einen Hash-Join oder etwas völlig anderes lösen – und wenn Sie morgen einen Index hinzufügen, kann dieselbe Abfrage schneller werden, ohne dass Sie ein einziges Zeichen ändern müssen.

Das ist das mengenbasierte (set-based) Mindset: Anweisungen operieren gleichzeitig auf ganzen Mengen von Zeilen. Sobald man dieses Prinzip verinnerlicht hat, wird SQL auf eine Weise prägnant, die Schleifen nur selten sind.

SELECT: Spalten auswählen

Jeder Lesezugriff beginnt mit SELECT, womit die gewünschten Spalten aufgelistet werden.

SELECT id, title, published_at
FROM posts;

SELECT * gibt jede Spalte zurück und ist beim Explorieren völlig in Ordnung, aber benennen Sie Ihre Spalten in Anwendungsabfragen explizit. Explizite Listen bleiben stabil, wenn sich das Schema ändert, vermeiden den Transfer großer, ungenutzter Spalten und machen die Absicht deutlich. Sie können neue Spalten berechnen und diese umbenennen:

SELECT title,
       LENGTH(body) AS body_length,
       COALESCE(published_at, created_at) AS visible_at
FROM posts;

AS weist einer Spalte einen Alias zu. Nutzen Sie dies, um Ergebnisse lesbar zu machen und berechneten Spalten einen Namen zu geben, was besonders wichtig ist, wenn eine Client-Library Zeilen in Objekte mappt.

Filtern mit WHERE

WHERE behält nur die Zeilen, die eine bestimmte Bedingung erfüllen. Es unterstützt die üblichen Vergleiche sowie AND, OR, NOT, IN, BETWEEN und LIKE.

SELECT id, title
FROM posts
WHERE author_id = 7
  AND published_at IS NOT NULL
  AND title ILIKE '%sql%';

Zwei Details sind wichtig zu merken. Erstens ist LIKE in vielen Datenbanken case-sensitive, während ILIKE (Postgres) case-insensitive ist; das LIKE von MySQL ist bei den üblichen Collations ebenfalls case-insensitive. Zweitens verhindert ein führender Wildcard wie '%sql', dass ein gewöhnlicher Index genutzt werden kann, da es keinen Präfix gibt, nach dem gesucht werden kann. Verwenden Sie für eine echte Textsuche stattdessen einen Full-Text-Index.

Sortieren und Paging

ORDER BY sortiert das Ergebnis, und LIMIT zusammen mit OFFSET schneidet es zu.

SELECT id, title
FROM posts
WHERE published_at IS NOT NULL
ORDER BY published_at DESC
LIMIT 20 OFFSET 40;

Ohne ORDER BY gibt die Datenbank keine Garantie bezüglich der Reihenfolge der Zeilen. Eine Abfrage, die heute “zufällig” sortiert zurückkommt, kann sich ändern, wenn der Planner einen anderen Plan wählt. Sortieren Sie daher immer explizit, wenn die Reihenfolge wichtig ist.

OFFSET überspringt Zeilen, was bei großen Tabellen in tieferen Ebenen kostspielig wird, da die Datenbank diese Zeilen dennoch durchlaufen muss. Keyset pagination vermeidet diese Kosten, indem die letzte gesehene Zeile gespeichert wird:

SELECT id, title
FROM posts
WHERE published_at < :last_published_at
ORDER BY published_at DESC
LIMIT 20;

NULL und dreiwertige Logik

NULL bedeutet nicht Null oder ein leerer String. Es bedeutet unbekannt, und SQL verwendet eine dreiwertige Logik: Jede Bedingung wird als true, false oder unknown ausgewertet. Zeilen passieren eine WHERE-Klausel nur, wenn die Bedingung true ist; unknown wird beim Filtern also wie false behandelt.

SELECT id FROM posts WHERE published_at = NULL;     -- never matches
SELECT id FROM posts WHERE published_at IS NULL;    -- correct
SELECT id FROM posts WHERE published_at IS NOT NULL;

Folgen, auf die man achten sollte:

• NULL = NULL ist unknown, nicht true. Verwenden Sie IS NULL oder IS NOT DISTINCT FROM.
• NULL + 1 ist NULL; verwenden Sie COALESCE(x, 0), um einen Standardwert zu setzen.
• NOT IN (1, 2, NULL) ist niemals true, da ein Vergleich mit einem unbekannten Element unknown ergibt. Bevorzugen Sie NOT EXISTS, wenn die Liste NULL enthalten kann.
• Aggregate ignorieren NULL: COUNT(column) zählt Nicht-Null-Werte, während COUNT(*) die Zeilen zählt.

Nullability ist eine Design-Entscheidung. Deklarieren Sie Spalten als NOT NULL, es sei denn, fehlende Werte sind tatsächlich sinnvoll.

JOINs, mit Diagramm

Ein Join kombiniert Zeilen aus zwei Tabellen basierend auf einer verwandten Spalte. Angenommen, authors enthält Ada, Grace und Linus, und posts enthält zwei Zeilen von Ada und eine von Grace. Die verschiedenen Join-Typen liefern unterschiedliche Ergebnisse.

authors            posts
-------            ----------------------------
id name            id author_id title
1  Ada             10 1         Joins
2  Grace           11 1         Indexes
3  Linus           12 2         NULLs

INNER JOIN  ->  posts 10, 11, 12        (only matches)
LEFT JOIN   ->  posts 10, 11, 12, Linus (all authors, NULL post)
RIGHT JOIN  ->  posts 10, 11, 12        (all posts)
FULL JOIN   ->  posts 10, 11, 12, Linus (both sides)

-- Every author, including those with no posts.
SELECT a.name, p.title
FROM authors a
LEFT JOIN posts p ON p.author_id = a.id
ORDER BY a.name;

Die Join-Bedingung steht in ON. Bei Outer Joins verwandelt das Hinzufügen eines Filters für die rechte Tabelle in WHERE den Outer Join stillschweigend in einen Inner Join, da NULL Zeilen den Filter nicht bestehen. Setzen Sie solche Bedingungen stattdessen in die ON-Klausel, wenn nicht zugeordnete Zeilen erhalten bleiben sollen.

-- Keeps authors with no published posts.
SELECT a.name, p.title
FROM authors a
LEFT JOIN posts p
  ON p.author_id = a.id
 AND p.published_at IS NOT NULL;

Ein Self Join verwendet dieselbe Tabelle zweimal mit unterschiedlichen Aliasen, was besonders für Hierarchien nützlich ist. Ein Cross Join paart jede Zeile mit jeder anderen Zeile und ist selten das, was man versehentlich erreichen möchte.

GROUP BY und HAVING

Aggregate-Funktionen fassen viele Zeilen zu einer zusammen: COUNT, SUM, AVG, MIN, MAX. GROUP BY definiert dabei die Gruppen.

SELECT a.name          AS author,
       COUNT(p.id)     AS post_count,
       MAX(p.created_at) AS latest
FROM authors a
LEFT JOIN posts p ON p.author_id = a.id
GROUP BY a.id, a.name
HAVING COUNT(p.id) > 0
ORDER BY post_count DESC;

Die Regel besagt, dass jede ausgewählte Spalte entweder im GROUP BY enthalten oder in ein Aggregate eingepackt sein muss. Datenbanken, die dies erzwingen (Postgres und MySQL 8 standardmäßig), schützen dich so vor willkürlichen Ergebnissen.

WHERE filtert Zeilen vor der Gruppierung, während HAVING Gruppen danach filtert. Eine Bedingung für ein Aggregate gehört daher in HAVING, und eine Bedingung für eine einfache Spalte gehört aus Effizienzgründen normalerweise in WHERE.

SELECT author_id, COUNT(*) AS posts
FROM posts
WHERE published_at IS NOT NULL   -- filter rows
GROUP BY author_id
HAVING COUNT(*) > 5;             -- filter groups

Subqueries und CTEs

Eine Subquery ist eine Abfrage, die in einer anderen verschachtelt ist. Sie kann in SELECT, FROM oder WHERE vorkommen.

SELECT title
FROM posts
WHERE author_id IN (
  SELECT id FROM authors WHERE name = 'Ada'
);

Ein Common Table Expression (CTE) gibt einer Subquery mit WITH einen Namen. Dies ist in der Regel besser lesbar als eine Verschachtelung und ermöglicht es, das Ergebnis wiederzuverwenden.

WITH published AS (
  SELECT id, author_id, title
  FROM posts
  WHERE published_at IS NOT NULL
)
SELECT a.name, COUNT(p.id) AS posts
FROM authors a
LEFT JOIN published p ON p.author_id = a.id
GROUP BY a.id, a.name;

CTEs können verkettet werden, und ein rekursiver CTE referenziert sich selbst, um beispielsweise einen Baum aus Kategorien oder Kommentar-Threads zu durchlaufen. Greifen Sie immer dann zu einem CTE, wenn eine Abfrage mehr als eine Ebene tief verschachtelt wird; Lesbarkeit ist ebenfalls ein Performance-Feature.

Zeilen schreiben

INSERT fügt Zeilen hinzu, UPDATE ändert sie und DELETE entfernt sie.

INSERT INTO posts (author_id, title, body)
VALUES (7, 'Learning SQL', 'SQL is declarative.');

UPDATE posts
SET published_at = now()
WHERE id = 42;

DELETE FROM posts
WHERE id = 42;

Geben Sie UPDATE und DELETE immer eine WHERE-Klausel mit. Ohne diese wird jede Zeile beeinflusst. Eine gute Gewohnheit ist es, zuerst das entsprechende SELECT auszuführen und die Anzahl der betroffenen Zeilen zu prüfen. Ein UPDATE kann sich auch auf bestehende Werte beziehen:

UPDATE posts
SET title = title || ' (updated)'
WHERE author_id = 7;

INSERT kann mehrere Zeilen gleichzeitig hinzufügen und kann aus einer Abfrage gespeist werden:

INSERT INTO posts (author_id, title)
SELECT id, 'Welcome' FROM authors;

Transaktionen

Eine Transaktion gruppiert Statements, sodass entweder alle erfolgreich ausgeführt werden oder alle fehlschlagen. Dies stellt die Konsistenz bei mehrstufigen Änderungen sicher.

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

Wenn ein Statement fehlschlägt oder Sie den Vorgang abbrechen, macht ROLLBACK jede Änderung seit BEGIN rückgängig. Ohne eine Transaktion würde ein Absturz zwischen den beiden Updates dazu führen, dass Geld verloren geht. Fassen Sie zusammengehörige Schreibvorgänge zusammen, halten Sie die Transaktion kurz und warten Sie niemals auf eine Benutzereingabe oder einen Netzwerkaufruf, während eine Transaktion offen ist.

Keys und Constraints

Constraints sind Regeln, die die Datenbank für dich erzwingt:

• PRIMARY KEY — identifiziert jede Zeile eindeutig; erstellt zudem einen Index und impliziert NOT NULL.
• FOREIGN KEY — erfordert, dass der Wert in einer anderen Tabelle existiert, wodurch die referenzielle Integrität gewahrt bleibt.
• UNIQUE — verbietet Duplikate in einer Spalte oder einer Kombination von Spalten.
• NOT NULL — erfordert einen Wert.
• CHECK — erfordert, dass eine Bedingung erfüllt ist, wie zum Beispiel price >= 0.
• DEFAULT — liefert einen Wert, wenn keiner angegeben wurde.

CREATE TABLE posts (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id    bigint NOT NULL REFERENCES authors (id) ON DELETE CASCADE,
  title        text NOT NULL,
  body         text NOT NULL,
  published_at timestamptz,
  created_at   timestamptz NOT NULL DEFAULT now()
);

Ein Surrogate Primary Key, wie etwa eine Identity-Spalte, ist stabil und kompakt. Ein Natural Key, wie zum Beispiel eine E-Mail-Adresse, ist zwar aussagekräftig, kann sich aber ändern. Daher ist ein Surrogate Key in Kombination mit einem UNIQUE Constraint für den Natural Key vorzuziehen.

Indexe auf konzeptioneller Ebene

Ein Index ist eine separate, sortierte Struktur, die Spaltenwerte auf Zeilenpositionen abbildet. Ohne einen Index bedeutet das Finden von Zeilen einen vollständigen Tabellenscan; mit einem Index kann die Datenbank direkt zu den Treffern springen.

CREATE INDEX posts_author_published_idx
  ON posts (author_id, published_at DESC);

Stellen Sie sich konzeptionell ein Telefonbuch vor, das nach Nachnamen sortiert ist. Die Suche nach einem Namen geht schnell, weil das Buch geordnet ist; das Filtern nach einer Spalte, die nicht der Sortierschlüssel ist, würde bedeuten, jede einzelne Seite zu lesen. Indexe tauschen Schreibgeschwindigkeit und Speicherplatz gegen Lesegeschwindigkeit ein, da jeder Insert und jedes Update die Indexe aktualisieren muss.

Setzen Sie Indexe auf die Spalten, über die Sie filtern und Joins durchführen. Bevorzugen Sie composite indexes, die den tatsächlichen Query-Shapes entsprechen, und prüfen Sie mit EXPLAIN, ob der Planner diese auch tatsächlich nutzt. Mehr Indexe sind nicht automatisch besser; es kommt auf die richtigen Indexe an.

Window-Funktionen in einem Rutsch

Eine Window-Funktion berechnet Werte über eine Menge von Zeilen, die mit der aktuellen Zeile in Beziehung stehen, ohne diese so zusammenzufassen, wie es GROUP BY tut. Dadurch werden laufende Summen, Rankings und Vergleiche innerhalb von Gruppen einfach umsetzbar.

SELECT author_id,
       title,
       published_at,
       ROW_NUMBER() OVER (
         PARTITION BY author_id
         ORDER BY published_at DESC
       ) AS rank_in_author
FROM posts
WHERE published_at IS NOT NULL;

Die OVER-Klausel definiert das Window: PARTITION BY teilt die Zeilen in Gruppen auf und ORDER BY sortiert sie innerhalb jeder Gruppe. Zu den gängigen Funktionen gehören ROW_NUMBER, RANK, LAG, LEAD und SUM(...) OVER (...). Der entscheidende Unterschied zur Aggregation besteht darin, dass jede ursprüngliche Zeile erhalten bleibt.

Normalisierung: 1NF, 2NF, 3NF

Normalisierung ist der Prozess zur Entfernung von Redundanzen, sodass jeder Fakt nur einmal gespeichert wird. Die ersten drei Normalformen sind diejenigen, die Sie am häufigsten verwenden werden:

Erste Normalform (1NF) — keine sich wiederholenden Gruppen oder Spalten mit Mehrwertwerten. Eine tags-Spalte, die "sql,indexes" enthält, verletzt diese Form; eine separate post_tags-Tabelle behebt das Problem.

Zweite Normalform (2NF) — 1NF plus keine partielle Abhängigkeit von einem Teil eines zusammengesetzten Schlüssels. Wenn eine Bestellposition über (order_id, product_id) identifiziert wird und product_name speichert, hängt dieser Name nur von product_id ab und gehört daher in products.

Dritte Normalform (3NF) — 2NF plus keine transitive Abhängigkeit zwischen Nicht-Schlüsselspalten. Wenn posts sowohl author_id als auch author_email speichern würde, hänge die E-Mail vom Autor ab und nicht vom Post; sie gehört also in authors.

Stellen Sie sich eine Tabelle vor, in der die E-Mail des Autors bei jedem Post wiederholt wird. Wenn Sie die E-Mail ändern, müssen Sie viele Zeilen aktualisieren; übersehen Sie eine, widersprechen sich die Daten. Teilen Sie die Tabelle in authors und posts auf, und der Fakt existiert nur noch einmal. Denormalisieren Sie später ganz bewusst, wenn ein gemessenes Performance-Problem dies rechtfertigt.

Best Practices

• Benennen Sie Spalten explizit, anstatt SELECT * in Anwendungsabfragen zu verwenden.
• Nutzen Sie immer ORDER BY, wenn die Reihenfolge der Ergebnisse wichtig ist.
• Deklarieren Sie standardmäßig NOT NULL und behandeln Sie fehlende Werte mit COALESCE.
• Verwenden Sie IS NULL anstelle von = NULL und bevorzugen Sie NOT EXISTS gegenüber NOT IN bei nullable Listen.
• Filtern Sie Zeilen in WHERE und Gruppen in HAVING.
• Verwenden Sie CTEs, um komplexe Abfragen lesbar zu halten.
• Geben Sie UPDATE und DELETE eine WHERE-Klausel und bestätigen Sie zuerst die Zielzeilen.
• Fassen Sie zusammengehörige Schreibvorgänge in einer Transaction zusammen und halten Sie diese kurz.
• Fügen Sie Indexe für reale Abfragemuster hinzu und verifizieren Sie diese mit EXPLAIN.
• Speichern Sie jede Information nur einmal und streben Sie die dritte Normalform an, bevor Sie denormalisieren.

Häufige Fehler

• WHERE column = NULL schreiben und null Zeilen erhalten.
• NOT IN auf eine Liste anwenden, die NULL enthält, wodurch stillschweigend nichts zurückgegeben wird.
• WHERE bei einem UPDATE oder DELETE vergessen und so jede Zeile modifizieren.
• Nicht gruppierte Spalten auswählen und dadurch willkürliche Werte erhalten.
• Davon ausgehen, dass Zeilen in einer sinnvollen Reihenfolge zurückgegeben werden, ohne ORDER BY zu verwenden.
• LIMIT ... OFFSET für tiefes Pagination nutzen und damit steigende Performance-Kosten in Kauf nehmen.
• Die falsche Tabelle in WHERE filtern und so versehentlich einen LEFT JOIN in einen Inner Join verwandeln.
• Jede Spalte indexieren und so Schreibvorgänge verlangsamen, ohne einen Lesevorteil zu haben.
• Benutzereingaben direkt in SQL-Strings konkatenieren, anstatt Parameter zu verwenden, was Injection-Angriffe ermöglicht.
• Wiederholte Fakten in einer einzigen breiten Tabelle speichern, anstatt sie zu normalisieren.

Wie geht es weiter?

SQL ist das Fundament jeder relationalen Datenbank. Der nächste Schritt besteht also darin, eine Engine auszuwählen und deren Dialekt sowie Besonderheiten kennenzulernen. Beginnen Sie mit PostgreSQL aufgrund seiner Standardkonformität und der vielfältigen Datentypen oder mit MySQL wegen seiner weiten Verbreitung und der Replikationsmöglichkeiten. Erfahren Sie anschließend im REST-Guide, wie Abfrageergebnisse zu HTTP-Antworten werden, und in den Node.js basics, wie ein Node.js-Service diese Abfragen über eine pooled connection sendet.