| SQL joins einfach erklärt / SQL joins made easy |
|
|
|
| Freitag, 04 November 2011 | ||||||
|
SQL ist nicht ganz einfach zu verstehen, speziell wenn es darum geht mehrere Tabellen mit joins zusammenzufassen bzw zu verknüpfen. Im Folgenden wird dem SQL Einsteiger sehr einfach mit Venn Diagrammen sowie einfachen SQL queries erklärt wie die verschiedenen SQL joins funktionieren. Dazu werden zwei Beispieltabellen mit Daten benutzt und Joins beschrieben sowie deren Eingabedaten und Ergebnisse. Dateien zum Erstellen der Beispieldaten sowie die SQL queries können downloaded werden. Die Beispiele sind für mySQL und DB2.
===> English <===
SQL is not very easy to learn, in particular joins of multiiple tables are difficult to understand at a first glance. On the next pages it's explained for sql beginners how SQL joins work. Venn diagrams are used to explain how the tables are combined. In addition sample tables with sample data and sample joins are used to explain in detail the query input data results. SQL files to create the sample data and execute the queries can be downloaded. The samples are for mySQL and DB2.
Zur Einführung werden Venn Diagramme benutzt. Im streng mathematischen Sinne benutzt man Venn Diagramme nur für Mengen und deren Operationen wie Vereinigung, Schnitt, Differenz usw, aber sie sind auch sehr hilfreich um sql joins zu erklären und zu verstehen.
Genaugenommen gibt es vier verschiedene sql joins die man verstehen muss:
1) inner join
2) left/right outer join
3) full outer join
4) karthesisches Produkt
Wenn man Venn Diagramme benutzt um die Joins zu erklären gibt es noch zwei weitere Joins, die Entsprechungen in den Venn Diagrammen haben und das Verständnis von Joins fördern.
5) Left outer join where null
6) Full outer joinwhere null
In den folgenden Venn Diagrammen findet man immer zwei Tabellen TabelleA und TabelleB, die jeweils Zeilen enthalten, in denen Namen und weitere Informationen stehen. Der Join wird immer über die Namen gemacht, d.h. es werden Zeilen in beiden Tabellen gesucht, die gleiche Namen haben und deren Joinergebnisse betrachtet.
Bei den folgenden Beispielen enthalten die Tabellen A und B folgende Spalten und Inhalt:
Jede Tabelle hat einen Schlüssel id, eine Spalte name sowie eine Spalte nation mit der Nationalität der Person sowie eine Spalte table, die entweder A oder B enthält um zu kennzeichnen, in welcher Tabelle die Zeilenstehen, also Tabelle A oder Tabelle B.
Die sql Befehle um die Tabelle anzulegen und zu füllen können hier für mySQL und hier für DB2 runtergeladen werden. Die Queries um die Joins über die Tabelle auszuführen befinden sich hier für mySQL und hier für DB2 zum download.
Tabelle A:
mysql> select * from TableA;
+----+-----------+---------+-------+ | id | name | nation | table | +----+-----------+---------+-------+ | 1 | Albert | Germany | A |
| 2 | Elizabeth | England | A |
| 3 | Paul | Spain | A |
| 4 | Julia | Denmark | A |
+----+-----------+---------+-------+
Tabelle B: mysql> select * from TableB;
1) Inner join
Bei einem Inner join werden alle Zeilen aus der Tabelle A und der
Tabelle B zusammengeführt, wo die Join Spaltenelemente gleich sind.
Beispiel:
mysql> select * from TableA inner join TableB on TableA.name = TableB.name;
+----+--------+---------+-------+----+--------+--------+-------+ | id | name | nation | table | id | name | nation | table | +----+--------+---------+-------+----+--------+--------+-------+ | 1 | Albert | Germany | A | 2 | Albert | France | B | | 3 | Paul | Spain | A | 4 | Paul | Poland | B | +----+--------+---------+-------+----+--------+--------+-------+
Man sieht also, dass das Ergebnis die beiden Zeilen aus der Tabelle A
und B zusammengefügt hat, wo jeweils der Name gleich ist, d.h. Albert
und Paul.
2) Left outer join
Bei einem left outer join werden alle Zeilen aus der Tabelle A und der
Tabelle B zusammengeführt, wo die Join Spaltenelemente gleich sind sowie alle Elemente aus der linken Tabelle, die in diesem Falle Tabelle A ist.
Beispiel:
+----+-----------+---------+-------+------+--------+--------+-------+ | id | name | nation | table | id | name | nation | table | +----+-----------+---------+-------+------+--------+--------+-------+ | 1 | Albert | Germany | A | 2 | Albert | France | B | | 2 | Elizabeth | England | A | NULL | NULL | NULL | NULL | | 3 | Paul | Spain | A | 4 | Paul | Poland | B | | 4 | Julia | Denmark | A | NULL | NULL | NULL | NULL | +----+-----------+---------+-------+------+--------+--------+-------+
Man sieht also, dass das Ergebnis die beiden Zeilen aus der Tabelle A
und B zusammengefügt hat, wo jeweils der Name gleich ist, d.h. Albert
und Paul aber auch die Elemente aus der Tabelle A, wo es keinen gleichen Namen in Tabelle B gibt (Elizabeth und Julia). Diese Elemente sind daran zu erkennen, dass der Teil der Ergebnisspalten, wo die Teile aus der Tabelle B stehen mit NULL gefüllt sind.
3) Full outer join
Bei einem full outer join werden alle Zeilen aus der Tabelle A und der
Tabelle B zusammengeführt, wo die Join Spaltenelemente gleich sind sowie
alle Elemente aus den beiden Tabellen, diekeine gleichen Elemente haben.
Beispiel mysql: (Da mysql kein full outer join anbietet wird der full
outer join über eine Vereinigung von dem left und right outer join
vorgenommen, der dasselbe Ergebnis liefert)
mysql> select * from TableA left join TableB on TableA.name = TableB.name
-> UNION -> select * from TableA right join TableB on TableA.name = TableB.name;
Beispiel DB2: (Richtiger full join)
select * from TableA full join TableB on TableA.name = TableB.name;
+------+-----------+---------+-------+------+--------+--------+-------+
| id | name | nation | table | id | name | nation | table | +------+-----------+---------+-------+------+--------+--------+-------+ | 1 | Albert | Germany | A | 2 | Albert | France | B | | 2 | Elizabeth | England | A | NULL | NULL | NULL | NULL | | 3 | Paul | Spain | A | 4 | Paul | Poland | B | | 4 | Julia | Denmark | A | NULL | NULL | NULL | NULL | | NULL | NULL | NULL | NULL | 1 | Marc | China | B | | NULL | NULL | NULL | NULL | 3 | Peter | Japan | B | +------+-----------+---------+-------+------+--------+--------+-------+
Man sieht also, dass das Ergebnis die beiden Zeilen aus der Tabelle A
und B zusammengefügt hat, wo jeweils der Name gleich ist, d.h. Albert
und Paul aber auch die Elemente aus der Tabelle A, wo es keinen gleichen
Namen in Tabelle B gibt (Elizabeth und Julia) sowie die Elemente aus
Tabelle B, wo es kein gleichen Namen in Tabelle A gibt (Marc und Peter).
Diese Elemente sind
daran zu erkennen, dass der Teil der Ergebnisspalten, wo die Teile aus
der anderen Tabelle nicht existieren mit NULL gefüllt sind.Bei dem Zusammenfügen (UNION) der beiden Tabellen ist zu berücksichtigen, dass dabei eigentlich die Zeilen, die gleiche Namen in beiden Tabellen haben, in der Ergebniszeile doppelt auftreten, da sie sowohl im left als auch right join enthalten sind. Allerdings entfernt die UNION diese doppelten Elemente. Im folgenden Beispiel wird durch UNION ALL diese Entfernung verhindert. Die Zeilen mit Albert und Paul tauchen zweimal auf.
mysql> select * from TableA left join TableB on TableA.name = TableB.name UNION ALL select * from TableA right join TableB on TableA.name = TableB.name;
+------+-----------+---------+-------+------+--------+--------+-------+ | id | name | nation | table | id | name | nation | table | +------+-----------+---------+-------+------+--------+--------+-------+ | 1 | Albert | Germany | A | 2 | Albert | France | B | | 2 | Elizabeth | England | A | NULL | NULL | NULL | NULL | | 3 | Paul | Spain | A | 4 | Paul | Poland | B | | 4 | Julia | Denmark | A | NULL | NULL | NULL | NULL | | NULL | NULL | NULL | NULL | 1 | Marc | China | B | | 1 | Albert | Germany | A | 2 | Albert | France | B | | NULL | NULL | NULL | NULL | 3 | Peter | Japan | B | | 3 | Paul | Spain | A | 4 | Paul | Poland | B | +------+-----------+---------+-------+------+--------+--------+-------+ 
4) Cross join - Karthesisches Produkt
In diesem Falle gibt es keine Join Bedingung. Deshalb werden bei diesem
join jeweils alle Zeilen der linken Tabelle mit den jeweiligen Zeilen
der rechten Tabelle zusammengeführt. D.h. die Ergebnistabelle enthält
immer (Anzahl Zeilen TabelleA) * (Anzahl Zeilen TabelleB)
Ergebniszeilen. Diese kann bei großen Ausgangstabellen sehr schnell
riesige Ergebnistabellen ergeben deren Berechnung sehr zeit- und
speicher aufwendig ist und sollte deshalb vermieden werden.
Beispiel:
mysql> select * from TableA join TableB;
+----+-----------+---------+-------+----+--------+--------+-------+ | id | name | nation | table | id | name | nation | table | +----+-----------+---------+-------+----+--------+--------+-------+ | 1 | Albert | Germany | A | 1 | Marc | China | B | | 2 | Elizabeth | England | A | 1 | Marc | China | B | | 3 | Paul | Spain | A | 1 | Marc | China | B | | 4 | Julia | Denmark | A | 1 | Marc | China | B | | 1 | Albert | Germany | A | 2 | Albert | France | B | | 2 | Elizabeth | England | A | 2 | Albert | France | B | | 3 | Paul | Spain | A | 2 | Albert | France | B | | 4 | Julia | Denmark | A | 2 | Albert | France | B | | 1 | Albert | Germany | A | 3 | Peter | Japan | B | | 2 | Elizabeth | England | A | 3 | Peter | Japan | B | | 3 | Paul | Spain | A | 3 | Peter | Japan | B | | 4 | Julia | Denmark | A | 3 | Peter | Japan | B | | 1 | Albert | Germany | A | 4 | Paul | Poland | B | | 2 | Elizabeth | England | A | 4 | Paul | Poland | B | | 3 | Paul | Spain | A | 4 | Paul | Poland | B | | 4 | Julia | Denmark | A | 4 | Paul | Poland | B | +----+-----------+---------+-------+----+--------+--------+-------+
Man sieht also, dass das Ergebnis alle Zeilen aus der Tabelle A
jeweils mit allen Elementen der Tabelle B zusammengefügt hat. Dabei tauchen keine Spalten mit NULL auf.
Eine Darstellung als Venn Diagramm gibt es dafür nicht.
5) Left outer join where null
Bei diesem join werden alle Zeilen aus der Tabelle A mit der Tabelle B zusammengeführt, wo es keine gleichen Elemente gibt. Es entspricht also auch dem left outer join wo der inner join entfernt wurde.
Beispiel:
-> WHERE TableB.id is null; +----+-----------+---------+-------+------+------+--------+-------+ | id | name | nation | table | id | name | nation | table | +----+-----------+---------+-------+------+------+--------+-------+ | 2 | Elizabeth | England | A | NULL | NULL | NULL | NULL | | 4 | Julia | Denmark | A | NULL | NULL | NULL | NULL | +----+-----------+---------+-------+------+------+--------+-------+
Man sieht also, dass das Ergebnis die beiden Zeilen aus der Tabelle A
und B zusammengefügt hat, wo jeweils für Namen aus der linken Tabelle A
keine Namen in der Tabelle B existieren. Da ein Join vorgenommen wird
tauchen Spalten der Tabelle B im Ergebnis auf, die aber alle mit NULL
gefüllt sind.
6) Full outer join where null
Bei diesem join werden alle Zeilen der beiden Tabellen zusammengeführt,
wo es keine gleichen Elemente in der anderen Tabelle gibt. Er entspricht also dem jull outer join wo der inner join entfernt wurde.
Beispiel:
-> UNION -> select * from TableA right join TableB on TableA.name = TableB.name where TableA.id is null; +------+-----------+---------+-------+------+-------+--------+-------+ | id | name | nation | table | id | name | nation | table | +------+-----------+---------+-------+------+-------+--------+-------+ | 2 | Elizabeth | England | A | NULL | NULL | NULL | NULL | | 4 | Julia | Denmark | A | NULL | NULL | NULL | NULL | | NULL | NULL | NULL | NULL | 1 | Marc | China | B | | NULL | NULL | NULL | NULL | 3 | Peter | Japan | B | +------+-----------+---------+-------+------+-------+--------+-------+
Man sieht also, dass das Ergebnis die beiden Zeilen aus der Tabelle A
und B zusammengefügt hat, wo jeweils für Namen aus der linken Tabelle A
keine Namen in der Tabelle B existieren und für Namen aus der rechten
Tabelle B keine Namen in der linken Tabelle A existieren. Da ein Join
vorgenommen wird
tauchen Spalten der Tabelle A und B im Ergebnis auf, die aber alle mit
NULL
gefüllt sind.
References:
In a mathematical sense Venn diagrams are used to explain sets and their operations like union, intersection, difference ... But they also can help to understand sqj joins better because there exist similar operations.So Venn diagrams are used in the following paragraphs to help to explain the common SQL operations.
There basically exist four different types of sql join:
1) inner join
2) left/right outer join
3) full outer join
4) carthesian product
There exist two other joins which also have an equivalent Venn diagramm which helps to understand joins better:
5) Left outer join where null
6) Full outer joinwhere null
The following Venn diagrams use two tables TableA and TableB. They have rows with names of persons and other information. The join is executed on the names every time, i.e. all rows which have the same name are joined.
The examples used below use TableA and TableB and have following columns:
A key called id, a column name and a column nation which reflects the nationality of the person and a column table, which either is A or B if the row is stored in TableA or TableB.
sql commands to create the sample table and will the sample data can be downloaded here for mySQLand here for DB2 . The queries to execute the joins can be downloaded here for mySQL and here for DB2 .
Tabelle A:
mysql> select * from TableA;
+----+-----------+---------+-------+ | id | name | nation | table | +----+-----------+---------+-------+ | 1 | Albert | Germany | A |
| 2 | Elizabeth | England | A |
| 3 | Paul | Spain | A |
| 4 | Julia | Denmark | A |
+----+-----------+---------+-------+
Tabelle B: mysql> select * from TableB;
1) Inner join
An inner join combines all rows from TableA and TableB where the columns for the join are identical.
Example:
mysql> select * from TableA inner join TableB on TableA.name = TableB.name;
+----+--------+---------+-------+----+--------+--------+-------+ | id | name | nation | table | id | name | nation | table | +----+--------+---------+-------+----+--------+--------+-------+ | 1 | Albert | Germany | A | 2 | Albert | France | B | | 3 | Paul | Spain | A | 4 | Paul | Poland | B | +----+--------+---------+-------+----+--------+--------+-------+
The result table has combined the rows from TableA and TableB where the name is identical, i.e. Albert and Paul.
2) Left outer join
A left outer join combines all rows from TableA and TableB where the join columns are identical and all rows from the left table TableA which don't have any identical columns in TableB.
Example:
+----+-----------+---------+-------+------+--------+--------+-------+ | id | name | nation | table | id | name | nation | table | +----+-----------+---------+-------+------+--------+--------+-------+ | 1 | Albert | Germany | A | 2 | Albert | France | B | | 2 | Elizabeth | England | A | NULL | NULL | NULL | NULL | | 3 | Paul | Spain | A | 4 | Paul | Poland | B | | 4 | Julia | Denmark | A | NULL | NULL | NULL | NULL | +----+-----------+---------+-------+------+--------+--------+-------+
The result table has rows where the names are identical in TableA and TableB , e.i. Albert
und Paul. There are also rows from Tabelle A with no match in TableB (Elizabeth und Julia). These rows have NULL values in the columns which come from TableB.
3) Full outer join
A full outer join combines all rows from TableA and TableB.
Example mySQL: (Because mysql has no full outer join the full
outer join is done as the union of the left and right outer join of both tables, which creates the same result)
mysql> select * from TableA left join TableB on TableA.name = TableB.name
-> UNION -> select * from TableA right join TableB on TableA.name = TableB.name;
Example DB2:
select * from TableA full join TableB on TableA.name = TableB.name;
+------+-----------+---------+-------+------+--------+--------+-------+
| id | name | nation | table | id | name | nation | table | +------+-----------+---------+-------+------+--------+--------+-------+ | 1 | Albert | Germany | A | 2 | Albert | France | B | | 2 | Elizabeth | England | A | NULL | NULL | NULL | NULL | | 3 | Paul | Spain | A | 4 | Paul | Poland | B | | 4 | Julia | Denmark | A | NULL | NULL | NULL | NULL | | NULL | NULL | NULL | NULL | 1 | Marc | China | B | | NULL | NULL | NULL | NULL | 3 | Peter | Japan | B | +------+-----------+---------+-------+------+--------+--------+-------+
The result table now has rows where the names of TableA and TableB is identical, i.e.. Albert and Paul. In addition all rows from TableA with no match (Elizabeth und Julia) and rows from TableB with no match (Marc und Peter). They have NULL values in the columns of the opposite table. Pls note that the union of the left and right join in general duplicates the rows hwihc have identical names (Paul and Albert) but the union operation removes these duplicates. The following example doesn#t remove the duplicates and the combination of Paul and Albert occurs twice.
mysql> select * from TableA
left join TableB on TableA.name = TableB.name UNION ALL select * from
TableA right join TableB on TableA.name = TableB.name;
+------+-----------+---------+-------+------+--------+--------+-------+ | id | name | nation | table | id | name | nation | table | +------+-----------+---------+-------+------+--------+--------+-------+ | 1 | Albert | Germany | A | 2 | Albert | France | B | | 2 | Elizabeth | England | A | NULL | NULL | NULL | NULL | | 3 | Paul | Spain | A | 4 | Paul | Poland | B | | 4 | Julia | Denmark | A | NULL | NULL | NULL | NULL | | NULL | NULL | NULL | NULL | 1 | Marc | China | B | | 1 | Albert | Germany | A | 2 | Albert | France | B | | NULL | NULL | NULL | NULL | 3 | Peter | Japan | B | | 3 | Paul | Spain | A | 4 | Paul | Poland | B | +------+-----------+---------+-------+------+--------+--------+-------+
4) Cross join - Carthesian product
If there is no join condition a carthesian product is created. The result table contains all rows from TableA combined with all rows from TableB. That way the number of rows in the result table is (number of rows of TabelleA) * (number of rows TabelleB). This can be a huge number of rows if the joined tables have a lot of rows. This join will be very time and memory consuming and should be avoided. Nevertheless this also can happen by error if the on conditions are wrong.
Example:
mysql> select * from TableA join TableB;
+----+-----------+---------+-------+----+--------+--------+-------+ | id | name | nation | table | id | name | nation | table | +----+-----------+---------+-------+----+--------+--------+-------+ | 1 | Albert | Germany | A | 1 | Marc | China | B | | 2 | Elizabeth | England | A | 1 | Marc | China | B | | 3 | Paul | Spain | A | 1 | Marc | China | B | | 4 | Julia | Denmark | A | 1 | Marc | China | B | | 1 | Albert | Germany | A | 2 | Albert | France | B | | 2 | Elizabeth | England | A | 2 | Albert | France | B | | 3 | Paul | Spain | A | 2 | Albert | France | B | | 4 | Julia | Denmark | A | 2 | Albert | France | B | | 1 | Albert | Germany | A | 3 | Peter | Japan | B | | 2 | Elizabeth | England | A | 3 | Peter | Japan | B | | 3 | Paul | Spain | A | 3 | Peter | Japan | B | | 4 | Julia | Denmark | A | 3 | Peter | Japan | B | | 1 | Albert | Germany | A | 4 | Paul | Poland | B | | 2 | Elizabeth | England | A | 4 | Paul | Poland | B | | 3 | Paul | Spain | A | 4 | Paul | Poland | B | | 4 | Julia | Denmark | A | 4 | Paul | Poland | B | +----+-----------+---------+-------+----+--------+--------+-------+
All rows from TableA are combined with all elements from TableB and no columns are NULL.
Ther doesn't exists an equivalent Venn diagram for this.
5) Left outer join where null
This join combines all rows of the left TableA which don't have a peer in the other table. It's the same as a left outer join reduced by the elements in an inner join.
Example:
-> WHERE TableB.id is null; +----+-----------+---------+-------+------+------+--------+-------+ | id | name | nation | table | id | name | nation | table | +----+-----------+---------+-------+------+------+--------+-------+ | 2 | Elizabeth | England | A | NULL | NULL | NULL | NULL | | 4 | Julia | Denmark | A | NULL | NULL | NULL | NULL | +----+-----------+---------+-------+------+------+--------+-------+
The result table has only rows which don't have teh same name in TableB. Because a join combines both tables the columns from TableB are NULL for every row.
6) Full outer join where null
This join combines all rows which don't have a peer element in the other table.
It's the same as a full outer join reduced by the elements in an inner join.
Example:
-> UNION -> select * from TableA right join TableB on TableA.name = TableB.name where TableA.id is null; +------+-----------+---------+-------+------+-------+--------+-------+ | id | name | nation | table | id | name | nation | table | +------+-----------+---------+-------+------+-------+--------+-------+ | 2 | Elizabeth | England | A | NULL | NULL | NULL | NULL | | 4 | Julia | Denmark | A | NULL | NULL | NULL | NULL | | NULL | NULL | NULL | NULL | 1 | Marc | China | B | | NULL | NULL | NULL | NULL | 3 | Peter | Japan | B | +------+-----------+---------+-------+------+-------+--------+-------+
Both tables are combined and there is no row which has a matching row in the other table. Therefore all rows either have NULL in the columns of the first table or second table.
References:
|
||||||
| Last Updated ( Donnerstag, 19 April 2012 ) | ||||||
| < Prev |
|---|
