Was ist der Unterschied zwischen WHERE und ON in SQL JOINs?

Wenn Sie Tabellen in SQL verknüpfen, können Sie Bedingungen in einer ON-Klausel und in einer WHERE-Klausel haben. Viele verwirrt der Unterschied zwischen diesen beiden Klauseln. In diesem Artikel werden wir dieses Thema erörtern, indem wir Sie zunächst an den Zweck der ON- und WHERE-Klauseln erinnern und dann anhand von Beispielen zeigen, welche Arten von Bedingungen in jeder dieser Klauseln enthalten sein sollten.

Sowohl in der ON -Klausel als auch in der WHERE -Klausel können Bedingungen angegeben werden. Aber gibt es irgendwelche Unterschiede zwischen ihnen? Wenn ja, wo sollten Sie welche Bedingungen in Ihrer SQL-Abfrage angeben? Lassen Sie es uns gemeinsam herausfinden!

ON vs. WHERE-Bedingungen

Der Zweck der ON-Klausel besteht darin, die Verknüpfungsbedingungen anzugeben, d. h. zu definieren, wie die Tabellen verknüpft werden sollen. Insbesondere legen Sie fest, wie die Datensätze abgeglichen werden sollen.

Im Gegensatz dazu dient die WHERE -Klausel dazu, die Filterbedingungen anzugeben, d. h. zu definieren, welche Zeilen in der Ergebnismenge verbleiben sollen.

Schauen wir uns ein Beispiel an, um den Unterschied zu verstehen. Wir haben die folgenden zwei Tabellen, die (1) die Benutzer (die Tabelle users) unserer Vermietungswebseite und (2) die Häuser (die Tabelle houses), die zu vermieten sind.

SELECT u.id, u.name, u.registration_date, h.address, h.city
FROM users u
JOIN houses h
ON u.id = h.owner_id
WHERE u.registration_date < '2020-01-01';

Beachten Sie, dass wir sowohl in der ON-Klausel als auch in der WHERE -Klausel Bedingungen haben:

• Mit der Bedingung ON geben wir an, dass die Tabellen verbunden werden, indem wir die Spalte id in der Tabelle users und die Spalte owner_id in den Häusern abgleichen.
• Mit der Bedingung WHERE filtern wir die Ergebnismenge, indem wir nur die Benutzer behalten, die sich vor dem 1. Januar 2020 registriert haben.

Wir haben also die Bedingungen ON und WHERE ihrem Zweck entsprechend verwendet, was zu einer klaren und lesbaren SQL-Abfrage führt.

Hier ist die Ergebnismenge:

Sie sind sich nicht sicher, wie JOIN in unserer SQL-Abfrage funktioniert? Üben Sie das Verbinden von Tabellen mit diesem interaktiven SQL JOINs Kurs.

ON- und WHERE-Bedingungen in INNER JOINs

Im obigen Beispiel können wir sehen, wie die Bedingungen ON und WHERE entsprechend ihrem jeweiligen Zweck und der gängigen Praxis verwendet werden.

Es ist jedoch nützlich zu wissen, dass Sie für (INNER) JOINs sowohl die Bedingung JOIN als auch die Filterungsbedingung mit einer ON-Klausel angeben können. Mit der folgenden SQL-Abfrage können wir beispielsweise das gleiche Ergebnis wie oben erzielen:

SELECT u.id, u.name, u.registration_date, h.address, h.city
FROM users u
JOIN houses h
ON u.id = h.owner_id AND u.registration_date < '2020-01-01';

Diese Abfrage wird auf dieselbe Weise ausgeführt. Ich empfehle jedoch nicht, die Join-Bedingung und die Filterungsbedingung in derselben Klausel zu mischen. Wenn Sie die beiden Abfragen vergleichen, werden Sie feststellen, dass die erste Abfrage besser lesbar ist:

• Es ist einfacher, der ersten Abfrage zu folgen: Zuerst verbinden Sie die Tabellen nach einer bestimmten Bedingung, dann filtern Sie das Ergebnis nach einer anderen Bedingung.
• Die Absicht der gesamten Abfrage ist für den außenstehenden Leser klarer, wenn die Bedingungen durch Befolgung der Regeln getrennt sind.

ON- und WHERE-Bedingungen in OUTER JOINs

Bei OUTER JOINs (d. h. LEFT JOIN, RIGHT JOIN und FULL JOIN) ist es entscheidend, die Bedingungen ON und WHERE so zu verwenden, wie sie vorgesehen sind. Andernfalls werden Sie falsche Ergebnisse erhalten. Lassen Sie uns das an einem Beispiel sehen.

Auch hier wollen wir die Liste der Benutzer, die sich vor dem 1. Januar 2020 registriert haben, zusammen mit ihren jeweiligen Häusern erhalten. Diesmal wollen wir jedoch alle Nutzer behalten, auch diejenigen, die keine Häuser auf unserer Vermietungswebsite registriert haben. Daher werden wir eine LEFT JOIN anstelle einer JOIN (d.h. eine INNER JOIN)) erstellen.

Wir werden sehen, ob es Unterschiede zwischen der Angabe der Filterungsbedingung in der ON-Klausel und der Angabe in der WHERE -Klausel gibt. Wenn wir die Regeln befolgen und die Bedingungen wie vorgesehen verwenden, erhalten wir die folgende Abfrage:

SELECT u.id, u.name, u.registration_date, h.address, h.city
FROM users u
LEFT JOIN houses h
ON u.id = h.owner_id
WHERE u.registration_date < '2020-01-01';

Das Ergebnis sieht gut aus. Wir haben alle Benutzer, die wir in unserem ersten Beispiel erhalten haben. Darüber hinaus haben wir zwei weitere Nutzer, die keine entsprechenden Häuser auf unserer Website haben, aber aufgrund der LEFT JOIN in der Ergebnismenge enthalten sind. Beachten Sie, dass sich beide vor dem 1. Januar 2020 registriert haben, wie in unserer Filterbedingung angegeben.

Erhalten wir das gleiche Ergebnis, wenn wir die Verknüpfungsbedingung und die Filterungsbedingung in der ON -Klausel mischen? Das wollen wir herausfinden:

SELECT u.id, u.name, u.registration_date, h.address, h.city
FROM users u
LEFT JOIN houses h
ON u.id = h.owner_id AND u.registration_date < '2020-01-01';

Wie Sie sehen können, sind die Ergebnisse unterschiedlich. Es sind alle Benutzer enthalten, auch diejenigen, die sich erst 2020 oder 2021 angemeldet haben. Das liegt daran, dass LEFT JOIN alle Datensätze aus der linken Tabelle behält, auch wenn die Logik von ON fehlschlägt. In diesem Beispiel funktioniert die Angabe der Filterbedingung in der ON -Klausel also nicht für uns. Um das richtige Ergebnis zu erhalten, müssen wir die Bedingungen wie vorgesehen angeben.

Interessanterweise gibt es Situationen, in denen die Bedingung WHERE die Absicht einer OUTER JOIN"aufheben" kann. Nehmen wir an, wir möchten alle Benutzer mit den entsprechenden Häusern auflisten, aber nur, wenn die Häuser 3 oder mehr Schlafzimmer haben.

Da wir alle Benutzer behalten wollen, verwenden wir eine OUTER JOIN, insbesondere eine LEFT JOIN. Unsere Anforderung an die Anzahl der Schlafzimmer ist eindeutig eine Filterbedingung. Wir fügen sie also in die WHERE Klausel ein. Hier ist unsere SQL-Abfrage mit den Bedingungen, die wie vorgesehen angegeben wurden:

SELECT u.id, u.name, h.address, h.city, h.bedrooms
FROM users u
LEFT JOIN houses h
ON u.id = h.owner_id
WHERE h.bedrooms > 2;

Das scheint nicht richtig zu sein, oder? Das Ergebnis sieht so aus, als hätten wir INNER JOIN und nicht LEFT JOIN verwendet. Benutzer ohne Haus sind in der Ergebnistabelle nicht enthalten, da sie beim Zusammenführen der Tabellen NULL in der Spalte Schlafzimmer haben. Da die Werte von NULL als kleiner als 0 angesehen werden, werden die entsprechenden Zeilen entfernt, wenn wir die Filterbedingung anwenden - die Anzahl der Schlafzimmer ist größer als 2.

Es gibt zwei mögliche Lösungen für dieses Problem:

• Fügen Sie der Klausel WHERE eine weitere Filterbedingung hinzu, z. B. bedrooms is NULL:
  

  SELECT u.id, u.name, h.address, h.city, h.bedrooms
  FROM users u
  LEFT JOIN houses h
  ON u.id = h.owner_id
  WHERE h.bedrooms > 2 OR h.bedrooms is NULL;
  

• Verschieben der Filterbedingung in die Klausel ON:
  

  SELECT u.id, u.name, h.address, h.city, h.bedrooms
  FROM users u
  LEFT JOIN houses h
  ON u.id = h.owner_id AND h.bedrooms > 2;
  

Jede dieser Abfragen führt zu folgendem Ergebnis:

Jetzt wissen Sie es! In OUTER JOINs macht es einen Unterschied, wie wir die Bedingungen angeben.

Lassen Sie uns JOINs in SQL üben!

SQL JOINs ist nicht allzu schwer zu verstehen. Wie Sie jedoch an den Beispielen in diesem Artikel sehen konnten, gibt es einige Feinheiten, die beim Verknüpfen von Tabellen und beim Schreiben von Verknüpfungsbedingungen in SQL beachtet werden sollten.

Wenn Sie SQL-JOINs wirklich beherrschen wollen , ist das Üben mit realen Datensätzen ein wichtiger Erfolgsfaktor. Ich empfehle, mit dem interaktiven SQL-Kurs JOINs zu beginnen - er enthält 93 Programmieraufgaben, die die gängigsten Arten von Joins wie JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN und sogar Self-Joins und Non-Equi-Joins abdecken. Nach diesem Kurs wissen Sie, wie man mehrere Tabellen verbindet, wie man Tabellen ohne eine gemeinsame Spalte verbindet und wie man Daten mit verschiedenen Arten von JOINs korrekt filtert.

Für diejenigen, die Erfahrungen mit noch mehr SQL JOIN Anwendungsfällen sammeln möchten, empfehle ich den SQL-Praxis Kurs. Er umfasst fünf interaktive Kurse mit mehr als 600 Programmieraufgaben, in denen nicht nur die Grundlagen von SQL-JOINs sondern auch, wie man die Ergebnismenge mit einer WHERE Klausel filtert, wie man Daten mit GROUP BY und HAVING aggregiert und wie man Unterabfragen einschließlich korrelierter Unterabfragen verwendet. Sie werden eine Menge Spaß haben!

BONUS. Hier sind die 10 besten SQL JOIN Interview-Fragen mit Antworten.