JOIN ON vs. WHERE

„Soll man bei einem SELECT-Statement, das mehrere Tabellen benötigt die Verbund-Kriterien in einem expliziten JOIN ON oder in der Selektion angeben?“

Ist das nicht auch eine der Fragen, die Glaubenskriege unter Entwicklern auslösen kann? Zumindest habe ich schon das eine oder andere Mal Diskussionen über dieses Thema mitbekommen.

Natürlich kann man keine allgemeingültige Aussage für alle RDBM-Systeme machen. Wir wollen dieses Thema konkret im Beispiel von MySQL 5.0 etwas näher betrachten.

Betrachten wir dazu zwei Tabellen, user und entry:

CREATE TABLE `user` (
  `uid` int(11) NOT NULL,
  `name` varchar(20) default NULL,
  PRIMARY KEY  (`uid`)
) ENGINE=MyISAM;

CREATE TABLE `entry` (
  `uid` int(11) default NULL,
  `title` varchar(255) default NULL,
  `entry` text,
  INDEX idx_uid (uid)
) ENGINE=MyISAM;

Stellen wir uns vor, wir möchten die Einträge aller User anzeigen. Der „Old-School-Join“, also die SQL-87-Syntax sieht demnach für einen Join von user auf entry folgendermaßen aus:

SELECT user.uid, user.name, entry.title, entry.entry FROM user, entry WHERE user.uid = entry.uid;

Das SQL-92-Pendant sieht folgendermaßen aus:

SELECT user.uid, user.name, entry.title, entry.entry FROM user JOIN entry ON (user.uid = entry.uid);

Gängige Aussagen von Entwicklern:

1. „SQL-87-Syntax ist langsamer“
2. „Die SQL-87-Syntax ist fehleranfälliger“ (vergessen des Kriteriums in der Selektion)
3. „Ich finde die SQL-92-Syntax unbequem“
4. „Mit der SQL-87-Syntax kann man nicht alles erreichen“ (Left Outer Join, etc.)

Wollen wir die Aussagen unter die Lupe nehmen:

1. SQL-87-Syntax ist langsamer

MySQL bietet mit EXPLAIN eines der besten bereits eingebauten Analysetools um dem Query Optimizer auf die Finger zu schauen. Hat man den Output dieser Funktion verstanden, ist einem sehr geholfen wenn man Performanceprobleme bei SQL Queries suchen muss.

Um die Frage zu beantworten machen wir uns EXPLAIN zu Nutze:

mysql> explain SELECT user.uid, user.name, entry.title, entry.entry FROM user, entry WHERE user.uid = entry.uid;

+----+-------------+-------+------+---------------+---------+---------+---------------------+------+-------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref                 | rows | Extra       |
+----+-------------+-------+------+---------------+---------+---------+---------------------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | PRIMARY       | NULL    | NULL    | NULL                |  100 |             |
|  1 | SIMPLE      | entry | ref  | idx_uid       | idx_uid | 5       | phphatesme.user.uid |    1 | Using where |
+----+-------------+-------+------+---------------+---------+---------+---------------------+------+-------------+

2 rows in set (0.00 sec)

mysql> explain SELECT user.uid, user.name, entry.title, entry.entry FROM user JOIN entry ON (user.uid = entry.uid);

+----+-------------+-------+------+---------------+---------+---------+---------------------+------+-------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref                 | rows | Extra       |
+----+-------------+-------+------+---------------+---------+---------+---------------------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | PRIMARY       | NULL    | NULL    | NULL                |  100 |             |
|  1 | SIMPLE      | entry | ref  | idx_uid       | idx_uid | 5       | phphatesme.user.uid |    1 | Using where |
+----+-------------+-------+------+---------------+---------+---------+---------------------+------+-------------+

2 rows in set (0.00 sec)

Wie man erkennt, hat MySQL exakt den gleichen Execution Plan für dieses SQL Query festgelegt. In beiden Fällen hat es erkannt, dass es sich hier um einen Tabellenverbund mit einer „=“-Relation zwischen den Spalten, die als Verbundkriterium angegeben wurden, handelt und der KEY idx_uid, der dazu verwendet werden kann wird für das Query ausgenutzt.

Abgesehen von der Tatsache, dass das 2. Query 2 Bytes länger ist und deshalb in Theorie etwas schneller geparst werden kann und bei vielen Joins auch schneller übers Netz übertragen werden kann müssen die Queries gleich schnell laufen – und das tun sie auch. (Man bedenke, ich bevorzuge die optionalen Klammern im ON immer zu schreiben, ansonsten wären beide Queries sogar gleich auf in Sachen Byte-Anzahl.)

2. „Die SQL-87-Syntax ist fehleranfälliger“ (vergessen des Kriteriums in der Selektion)

Führt man das SQL-87-Query ohne Verbundkriterium aus, erhält man als Ergebnis das Kreuzprodukt beider Tabellen.

SELECT user.uid, user.name, entry.title, entry.entry FROM user, entry;

Das ist jedoch in der Regel nicht das, was man eigentlich wollte. Da jedoch das Query vollkommen korrektes SQL ist wird MySQL kein Warning anzeigen sondern brav die (fehlerhafte) Anweisung befolgen.

Die SQL-92-Variante wird deshalb gerne empfohlen. Diese kann jedoch auch ohne Verbundkriterium aufgerufen werden:

SELECT user.uid, user.name, entry.title, entry.entry FROM user JOIN entry;

Das Ergebnis hierbei ist ebenfalls das Kreuzprodukt der Tabellen; wie zu erwarten auch hier kein Warning – es könnte ja auch so gewünscht sein.

Bei beiden Varianten kann also das Verbundkriterium vergessen werden, was dazu führen kann, dass überdimensional große Kreuzprodukte entstehen können, die den Betrieb der Datenbankserver ernsthaft gefährden können.

(Im Fall von MyISAM als Storage Engine blockiert ein lange laufendes SELECT Statement sobald ein INSERT/UPDATE/DELETE-Statement auf die Tabelle durchgeführt werden soll alle Statements, die diese Tabelle mit einbeziehen. Das führt zu wartenden Apache-Prozessen, die auf das SELECT Statement warten müssen und nach kurzer Zeit dazu, dass der Apache keine Apache-Prozesse mehr frei sind. Goodbye Lenin.)

Ein Hoffnungslicht für JOIN ON-Freunde: Ein kleiner Vorteil von JOIN ON ist, dass man beim Betrachten einfach sehen kann, dass kein Verbundkriterium angegeben ist. Bei einem SQL-87-Join mit vielen Tabellen kann es leichter passieren, den Überblick zu verlieren.

3. “Ich finde die SQL-92-Syntax unbequem”

Solche Argumente finden den Weg in Diskussionen, sobald man sich nicht mehr schlagkräftiger Argumente bedienen kann. In der Tat ist es so, dass der Entwickler die JOIN-Syntax bei SQL-87 gar nicht kennen muss. Alles kann mit „handwerkszeug“ erledigt werden. SELECT, Projektion, Tabellennamen kommagetrennt, Selektion. Aber handelt es sich hier um schwarze Magie? Die hohe Kunst der JOINs? Ich glaube nicht.

4. „Mit der SQL-87-Syntax kann man nicht alles erreichen“ (Left Outer Join, etc.)

MySQL verfügt meines Wissens in der aktuellen Version nicht über die Möglichkeit, OUTER JOINs in der SQL-87-Syntax anzugeben. Einige RDBM-Systeme wie Oracle verwenden hierfür die Notation (+).

Auch ein NATURAL JOIN kann in der SQL-87-Syntax bei MySQL nicht erreicht werden.

Fazit:

Einige Argumente sind einfach falsch, weder die Eine, noch die Andere Variante hat hier gepunktet. (1,2,3)

Argument 4 spricht eindeutig für die konsistente Verwendung von SQL-92 und bringt es hiermit als Sieger aus dem Rennen.

MySQL erlaubt bei mehr als 2 Tabellen im Verbund auch den „Mixed Style“. Das bedeutet, dass swohl die SQL-87-Syntax, als auch die SQL-92-Syntax verwendet wird.

Beispiel:

SELECT
 user.uid, user.name, entry.title, entry.entry, auth.password
FROM
 user JOIN auth ON (user.uid=auth.uid),
 entry 
WHERE user.uid = entry.uid;

Hier ist offensichtlich, dass das nur im Chaos enden kann. Hat man im Nachhinein beim 2. Überblicken des Queries wirklich noch im Auge, für welchen Verbund das Kriterium schon angegeben ist und für welche nicht?

Erschwerenderweise kommt hinzu dass wohl MySQL die Priorität bei der Bindung irgendwo beim Versionswechsel mitten in 5.0.x geändert hat. So kann das Query jetzt eine ganz andere Semantik besitzen.

(Falls jemand hierfür eine offizielle Quelle hat, bitte hier als Kommentar posten. Meine bisherige Source ist lediglich Mundpropaganda…)

Auf jeden Fall spricht auch das dafür, konsequent SQL-92 einzusetzen.