Teil 1 - Verbindungen und Zusammenschlüsse
Diese Antwort umfasst:
- Teil 1
- Teil 2
- Unterabfragen - was sie sind, wo sie verwendet werden können und worauf man achten muss
- Cartesian joins AKA - Oh, das Elend!
Es gibt eine Reihe von Möglichkeiten, Daten aus mehreren Tabellen in einer Datenbank abzurufen. In dieser Antwort werde ich die ANSI-92 Join-Syntax verwenden. Dies unterscheidet sich vielleicht von einer Reihe anderer Tutorials, die die ältere ANSI-89-Syntax verwenden (und wenn Sie 89 gewohnt sind, mag es Ihnen weniger intuitiv erscheinen - aber ich kann nur sagen, probieren Sie es aus), denn es ist viel leichter zu verstehen, wenn die Abfragen komplexer werden. Warum es verwenden? Gibt es einen Leistungsgewinn? Die kurze Antwort ist nein, aber es es leichter zu lesen, wenn man sich daran gewöhnt hat. Es ist einfacher, Abfragen zu lesen, die von anderen Personen mit dieser Syntax geschrieben wurden.
Ich werde auch das Konzept eines kleinen Fahrzeugparks verwenden, der über eine Datenbank verfügt, um zu verfolgen, welche Fahrzeuge verfügbar sind. Der Eigentümer hat Sie als IT-Mitarbeiter eingestellt und erwartet von Ihnen, dass Sie ihm die gewünschten Daten auf Anhieb liefern können.
Ich habe eine Reihe von Nachschlagetabellen erstellt, die von der Abschlusstabelle verwendet werden sollen. Damit haben wir ein vernünftiges Modell, mit dem wir arbeiten können. Zu Beginn werde ich meine Abfragen mit einer Beispieldatenbank mit folgender Struktur durchführen. Ich werde versuchen, an häufige Fehler zu denken, die am Anfang gemacht werden, und erklären, was dabei schief läuft - und natürlich auch zeigen, wie man sie korrigiert.
Die erste Tabelle ist einfach eine Farbauflistung, damit wir wissen, welche Farben wir auf dem Autohof haben.
mysql> create table colors(id int(3) not null auto_increment primary key,
-> color varchar(15), paint varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from colors;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| color | varchar(15) | YES | | NULL | |
| paint | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> insert into colors (color, paint) values ('Red', 'Metallic'),
-> ('Green', 'Gloss'), ('Blue', 'Metallic'),
-> ('White' 'Gloss'), ('Black' 'Gloss');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from colors;
+----+-------+----------+
| id | color | paint |
+----+-------+----------+
| 1 | Red | Metallic |
| 2 | Green | Gloss |
| 3 | Blue | Metallic |
| 4 | White | Gloss |
| 5 | Black | Gloss |
+----+-------+----------+
5 rows in set (0.00 sec)
In der Markentabelle sind die verschiedenen Automarken aufgeführt, die unser Hof möglicherweise verkaufen könnte.
mysql> create table brands (id int(3) not null auto_increment primary key,
-> brand varchar(15));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from brands;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| brand | varchar(15) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> insert into brands (brand) values ('Ford'), ('Toyota'),
-> ('Nissan'), ('Smart'), ('BMW');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from brands;
+----+--------+
| id | brand |
+----+--------+
| 1 | Ford |
| 2 | Toyota |
| 3 | Nissan |
| 4 | Smart |
| 5 | BMW |
+----+--------+
5 rows in set (0.00 sec)
Die Modelltabelle wird verschiedene Fahrzeugtypen abdecken. Es wird einfacher sein, verschiedene Fahrzeugtypen zu verwenden, als tatsächliche Fahrzeugmodelle.
mysql> create table models (id int(3) not null auto_increment primary key,
-> model varchar(15));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from models;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| model | varchar(15) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> insert into models (model) values ('Sports'), ('Sedan'), ('4WD'), ('Luxury');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from models;
+----+--------+
| id | model |
+----+--------+
| 1 | Sports |
| 2 | Sedan |
| 3 | 4WD |
| 4 | Luxury |
+----+--------+
4 rows in set (0.00 sec)
Und schließlich, um all diese anderen Tabellen miteinander zu verbinden, die Tabelle, die alles zusammenhält. Das ID-Feld ist die eindeutige Losnummer, mit der Autos identifiziert werden.
mysql> create table cars (id int(3) not null auto_increment primary key,
-> color int(3), brand int(3), model int(3));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from cars;
+-------+--------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| color | int(3) | YES | | NULL | |
| brand | int(3) | YES | | NULL | |
| model | int(3) | YES | | NULL | |
+-------+--------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert into cars (color, brand, model) values (1,2,1), (3,1,2), (5,3,1),
-> (4,4,2), (2,2,3), (3,5,4), (4,1,3), (2,2,1), (5,2,3), (4,5,1);
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from cars;
+----+-------+-------+-------+
| id | color | brand | model |
+----+-------+-------+-------+
| 1 | 1 | 2 | 1 |
| 2 | 3 | 1 | 2 |
| 3 | 5 | 3 | 1 |
| 4 | 4 | 4 | 2 |
| 5 | 2 | 2 | 3 |
| 6 | 3 | 5 | 4 |
| 7 | 4 | 1 | 3 |
| 8 | 2 | 2 | 1 |
| 9 | 5 | 2 | 3 |
| 10 | 4 | 5 | 1 |
+----+-------+-------+-------+
10 rows in set (0.00 sec)
Damit haben wir (hoffentlich) genug Daten, um die folgenden Beispiele für verschiedene Arten von Verbindungen abzudecken und auch genug Daten, um sie sinnvoll zu gestalten.
Um zur Sache zu kommen, möchte der Chef wissen Die IDs aller Sportwagen, die er besitzt .
Dies ist eine einfache Verknüpfung zweier Tabellen. Wir haben eine Tabelle, die das Modell identifiziert, und eine Tabelle, die den verfügbaren Bestand enthält. Wie Sie sehen können, sind die Daten in der Tabelle model Spalte der cars Tabelle bezieht sich auf die models Spalte der cars Tabelle haben wir. Wir wissen jetzt, dass die Tabelle models die ID 1 für Sports schreiben wir also die Verbindung.
select
ID,
model
from
cars
join models
on model=ID
Diese Abfrage sieht also gut aus, oder? Wir haben die beiden Tabellen identifiziert und enthalten die Informationen, die wir benötigen, und verwenden eine Verknüpfung, die korrekt angibt, welche Spalten verknüpft werden sollen.
ERROR 1052 (23000): Column 'ID' in field list is ambiguous
Oh nein! Ein Fehler in unserer ersten Abfrage! Ja, und es ist eine Pflaume. Die Abfrage hat zwar die richtigen Spalten, aber einige von ihnen sind in beiden Tabellen vorhanden, so dass die Datenbank verwirrt ist, welche Spalte wir eigentlich meinen und wo. Es gibt zwei Lösungen, um dieses Problem zu lösen. Die erste ist einfach und schön, wir können verwenden tableName.columnName um der Datenbank genau zu sagen, was wir meinen, etwa so:
select
cars.ID,
models.model
from
cars
join models
on cars.model=models.ID
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 3 | Sports |
| 8 | Sports |
| 10 | Sports |
| 2 | Sedan |
| 4 | Sedan |
| 5 | 4WD |
| 7 | 4WD |
| 9 | 4WD |
| 6 | Luxury |
+----+--------+
10 rows in set (0.00 sec)
Die andere wird wahrscheinlich häufiger verwendet und heißt Tabellen-Aliasing. Die Tabellen in diesem Beispiel haben schöne und kurze einfache Namen, aber die Eingabe von etwas wie KPI_DAILY_SALES_BY_DEPARTMENT würde wahrscheinlich schnell langweilig werden, also ist eine einfache Möglichkeit, die Tabelle wie folgt zu benennen:
select
a.ID,
b.model
from
cars a
join models b
on a.model=b.ID
Nun zurück zu dem Antrag. Wie Sie sehen, haben wir die Informationen, die wir brauchen, aber wir haben auch Informationen, nach denen nicht gefragt wurde, also müssen wir eine Where-Klausel in die Anweisung aufnehmen, um nur die Sportwagen zu erhalten, nach denen gefragt wurde. Da ich die Tabellenalias-Methode bevorzuge, anstatt die Tabellennamen immer wieder zu verwenden, werde ich ab diesem Punkt dabei bleiben.
Wir müssen also eine Where-Klausel in unsere Abfrage aufnehmen. Wir können Sportwagen entweder identifizieren durch ID=1 o model='Sports' . Da die ID indiziert und der Primärschlüssel ist (und zufällig weniger typisiert ist), können wir sie in unserer Abfrage verwenden.
select
a.ID,
b.model
from
cars a
join models b
on a.model=b.ID
where
b.ID=1
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 3 | Sports |
| 8 | Sports |
| 10 | Sports |
+----+--------+
4 rows in set (0.00 sec)
Bingo! Der Chef ist zufrieden. Da er natürlich ein Chef ist und nie mit dem zufrieden ist, was er verlangt hat, sieht er sich die Informationen an und sagt dann Ich möchte auch die Farben .
Okay, wir haben also einen großen Teil unserer Abfrage bereits geschrieben, aber wir müssen eine dritte Tabelle verwenden, nämlich die Farben. Nun, unsere Hauptinformationstabelle cars speichert die Farb-ID des Fahrzeugs und verknüpft diese mit der ID-Spalte der Farben. Auf ähnliche Weise wie im Original können wir also eine dritte Tabelle verbinden:
select
a.ID,
b.model
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
where
b.ID=1
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 3 | Sports |
| 8 | Sports |
| 10 | Sports |
+----+--------+
4 rows in set (0.00 sec)
Verdammt, obwohl die Tabelle korrekt verbunden wurde und die zugehörigen Spalten verknüpft wurden, haben wir vergessen, die aktuelle Informationen aus der neuen Tabelle, die wir gerade verknüpft haben.
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
where
b.ID=1
+----+--------+-------+
| ID | model | color |
+----+--------+-------+
| 1 | Sports | Red |
| 8 | Sports | Green |
| 10 | Sports | White |
| 3 | Sports | Black |
+----+--------+-------+
4 rows in set (0.00 sec)
Gut, damit ist der Chef für einen Moment von uns weg. Nun möchte ich Ihnen das Ganze etwas genauer erklären. Wie Sie sehen können, ist die from Klausel in unserer Anweisung verknüpft unsere Haupttabelle (ich verwende oft eine Tabelle, die Informationen enthält, und nicht eine Nachschlage- oder Dimensionstabelle. Die Abfrage würde genauso gut funktionieren, wenn die Tabellen vertauscht wären, aber weniger Sinn machen, wenn wir in ein paar Monaten zu dieser Abfrage zurückkehren, um sie zu lesen. Deshalb ist es oft am besten, eine Abfrage zu schreiben, die schön und leicht zu verstehen ist - sie intuitiv zu gestalten, schöne Einrückungen zu verwenden, damit alles so klar wie möglich ist. Wenn Sie später andere unterrichten, sollten Sie versuchen, diese Eigenschaften in ihre Abfragen einfließen zu lassen - vor allem, wenn Sie bei der Fehlersuche helfen werden.
Es ist durchaus möglich, immer mehr Tabellen auf diese Weise zu verknüpfen.
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
Ich habe zwar vergessen, eine Tabelle einzubeziehen, in der wir vielleicht mehr als eine Spalte in der join Anweisung, hier ein Beispiel. Wenn die models Tabelle hatte markenspezifische Modelle und daher auch eine Spalte namens brand die mit der brands Tabelle auf der ID Feldes könnte man wie folgt vorgehen:
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
and b.brand=d.ID
where
b.ID=1
Wie Sie sehen können, verbindet die obige Abfrage nicht nur die verbundenen Tabellen mit der Haupt cars Tabelle, sondern gibt auch Joins zwischen den bereits verbundenen Tabellen an. Wurde dies nicht getan, wird das Ergebnis als kartesische Verknüpfung bezeichnet - das ist DBA-Sprache für schlecht. Bei einer kartesischen Verknüpfung werden Zeilen zurückgegeben, weil die Datenbank nicht weiß, wie sie die Ergebnisse einschränken soll, so dass die Abfrage Folgendes zurückgibt alle die Zeilen, die die Kriterien erfüllen.
Um ein Beispiel für eine kartesische Verknüpfung zu geben, führen wir die folgende Abfrage aus:
select
a.ID,
b.model
from
cars a
join models b
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 1 | Sedan |
| 1 | 4WD |
| 1 | Luxury |
| 2 | Sports |
| 2 | Sedan |
| 2 | 4WD |
| 2 | Luxury |
| 3 | Sports |
| 3 | Sedan |
| 3 | 4WD |
| 3 | Luxury |
| 4 | Sports |
| 4 | Sedan |
| 4 | 4WD |
| 4 | Luxury |
| 5 | Sports |
| 5 | Sedan |
| 5 | 4WD |
| 5 | Luxury |
| 6 | Sports |
| 6 | Sedan |
| 6 | 4WD |
| 6 | Luxury |
| 7 | Sports |
| 7 | Sedan |
| 7 | 4WD |
| 7 | Luxury |
| 8 | Sports |
| 8 | Sedan |
| 8 | 4WD |
| 8 | Luxury |
| 9 | Sports |
| 9 | Sedan |
| 9 | 4WD |
| 9 | Luxury |
| 10 | Sports |
| 10 | Sedan |
| 10 | 4WD |
| 10 | Luxury |
+----+--------+
40 rows in set (0.00 sec)
Guter Gott, ist das hässlich. Was jedoch die Datenbank betrifft, so ist sie genau was verlangt wurde. In der Abfrage haben wir nach den ID de cars et le model de models . Da wir jedoch nicht angegeben haben wie um die Tabellen zu verknüpfen, hat die Datenbank einen Abgleich jede Zeile aus der ersten Tabelle mit jede Zeile aus der zweiten Tabelle.
Okay, der Chef ist also wieder da und will wieder mehr Informationen. Ich möchte die gleiche Liste, aber auch 4WDs in ihr enthalten .
Dies gibt uns jedoch einen guten Grund, zwei verschiedene Möglichkeiten zu untersuchen, um dies zu erreichen. Wir könnten der Where-Klausel eine weitere Bedingung hinzufügen, etwa so:
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
or b.ID=3
Auch wenn das oben beschriebene perfekt funktioniert, ist dies eine gute Gelegenheit, um zu zeigen, wie eine union Abfrage wird funktionieren.
Wir wissen, dass die folgenden alle Sportwagen zurückbringen werden:
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
Und das folgende würde alle 4WDs zurückbringen:
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=3
Durch Hinzufügen einer union all Klausel dazwischen, werden die Ergebnisse der zweiten Abfrage an die Ergebnisse der ersten Abfrage angehängt.
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
union all
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=3
+----+--------+-------+
| ID | model | color |
+----+--------+-------+
| 1 | Sports | Red |
| 8 | Sports | Green |
| 10 | Sports | White |
| 3 | Sports | Black |
| 5 | 4WD | Green |
| 7 | 4WD | White |
| 9 | 4WD | Black |
+----+--------+-------+
7 rows in set (0.00 sec)
Wie Sie sehen können, werden die Ergebnisse der ersten Abfrage zuerst zurückgegeben, gefolgt von den Ergebnissen der zweiten Abfrage.
In diesem Beispiel wäre es natürlich viel einfacher gewesen, einfach die erste Abfrage zu verwenden, aber union Abfragen können für bestimmte Fälle sehr nützlich sein. Sie sind eine großartige Möglichkeit, um bestimmte Ergebnisse aus Tabellen zurückzugeben, die nicht einfach miteinander verbunden werden können - oder, was das betrifft vollständig unverbundene Tabellen. Es sind jedoch einige Regeln zu beachten.
- Die Spaltentypen der ersten Abfrage müssen mit den Spaltentypen aller nachfolgenden Abfragen übereinstimmen.
- Die Namen der Spalten aus der ersten Abfrage werden verwendet, um die gesamte Ergebnismenge zu identifizieren.
- Die Anzahl der Spalten in jeder Abfrage muss gleich sein.
Nun, Sie könnten sich fragen, was die Unterschied ist zwischen der Verwendung union y union all . A union Abfrage werden Duplikate entfernt, während eine union all wird nicht. Dies bedeutet, dass es einen kleinen Leistungsabfall gibt, wenn Sie union 上 union all aber die Ergebnisse könnten es wert sein - darüber will ich hier aber nicht spekulieren.
In diesem Zusammenhang ist es vielleicht sinnvoll, einige zusätzliche Anmerkungen zu machen.
- Wenn wir die Ergebnisse ordnen wollen, können wir eine
order by aber Sie können den Alias nicht mehr verwenden. In der obigen Abfrage wird durch das Anhängen eines order by a.ID würde zu einem Fehler führen - was die Ergebnisse anbelangt, so heißt die Spalte ID よりも a.ID - auch wenn in beiden Abfragen derselbe Alias verwendet wurde.
- Wir können nur einen haben
order by Anweisung, und zwar als letzte Anweisung.
Für die nächsten Beispiele füge ich einige zusätzliche Zeilen zu unseren Tabellen hinzu.
Ich habe hinzugefügt Holden zum Markentisch. Ich habe auch eine Zeile in cars die die color Wert von 12 - die keinen Verweis in der Farbtabelle hat.
Okay, der Chef ist wieder da und bellt Anfragen heraus - *Ich will eine Zählung jeder Marke, die wir führen, und die Anzahl der Autos, die sie haben!` - Typisch, wir kommen gerade zu einem interessanten Abschnitt unserer Diskussion und der Chef will mehr Arbeit.
Also, als Erstes müssen wir eine vollständige Liste der möglichen Marken erstellen.
select
a.brand
from
brands a
+--------+
| brand |
+--------+
| Ford |
| Toyota |
| Nissan |
| Smart |
| BMW |
| Holden |
+--------+
6 rows in set (0.00 sec)
Wenn wir diese Tabelle nun mit der Tabelle "Autos" verknüpfen, erhalten wir das folgende Ergebnis:
select
a.brand
from
brands a
join cars b
on a.ID=b.brand
group by
a.brand
+--------+
| brand |
+--------+
| BMW |
| Ford |
| Nissan |
| Smart |
| Toyota |
+--------+
5 rows in set (0.00 sec)
Was natürlich ein Problem ist - wir sehen keine Erwähnung der schönen Holden Marke, die ich hinzugefügt habe.
Das liegt daran, dass eine Verknüpfung nach übereinstimmenden Zeilen in beide Tabellen. Da es in Autos keine Daten vom Typ Holden es wird nicht zurückgegeben. An dieser Stelle können wir eine outer beitreten. Dies ergibt alle die Ergebnisse aus einer Tabelle, unabhängig davon, ob sie in der anderen Tabelle übereinstimmen oder nicht:
select
a.brand
from
brands a
left outer join cars b
on a.ID=b.brand
group by
a.brand
+--------+
| brand |
+--------+
| BMW |
| Ford |
| Holden |
| Nissan |
| Smart |
| Toyota |
+--------+
6 rows in set (0.00 sec)
Jetzt können wir eine schöne Aggregatfunktion hinzufügen, um eine Zählung zu erhalten und uns den Chef für einen Moment vom Hals zu schaffen.
select
a.brand,
count(b.id) as countOfBrand
from
brands a
left outer join cars b
on a.ID=b.brand
group by
a.brand
+--------+--------------+
| brand | countOfBrand |
+--------+--------------+
| BMW | 2 |
| Ford | 2 |
| Holden | 0 |
| Nissan | 1 |
| Smart | 1 |
| Toyota | 5 |
+--------+--------------+
6 rows in set (0.00 sec)
Und damit schleicht sich der Chef davon.
Um dies näher zu erläutern, können äußere Verknüpfungen von der Art left o right Typ. Das Feld Links oder Rechts bestimmt, welche Tabelle vollständig enthalten. A left outer join enthält alle Zeilen der Tabelle auf der linken Seite, während (Sie haben es erraten) eine right outer join bringt alle Ergebnisse aus der Tabelle auf der rechten Seite in die Ergebnisse.
Einige Datenbanken erlauben eine full outer join die die Ergebnisse (ob übereinstimmend oder nicht) von beide Tabellen, aber dies wird nicht von allen Datenbanken unterstützt.
Wahrscheinlich fragen Sie sich jetzt, ob Sie Join-Typen in einer Abfrage zusammenführen können - und die Antwort lautet: Ja, das können Sie auf jeden Fall.
select
b.brand,
c.color,
count(a.id) as countOfBrand
from
cars a
right outer join brands b
on b.ID=a.brand
join colors c
on a.color=c.ID
group by
a.brand,
c.color
+--------+-------+--------------+
| brand | color | countOfBrand |
+--------+-------+--------------+
| Ford | Blue | 1 |
| Ford | White | 1 |
| Toyota | Black | 1 |
| Toyota | Green | 2 |
| Toyota | Red | 1 |
| Nissan | Black | 1 |
| Smart | White | 1 |
| BMW | Blue | 1 |
| BMW | White | 1 |
+--------+-------+--------------+
9 rows in set (0.00 sec)
Warum sind die Ergebnisse nicht so, wie sie erwartet wurden? Es liegt daran, dass wir zwar die äußere Verknüpfung von Autos zu Marken ausgewählt haben, diese aber nicht in der Verknüpfung zu Farben angegeben wurde - diese spezielle Verknüpfung liefert also nur Ergebnisse, die in beiden Tabellen übereinstimmen.
Hier ist die Abfrage, die die erwarteten Ergebnisse liefern würde:
select
a.brand,
c.color,
count(b.id) as countOfBrand
from
brands a
left outer join cars b
on a.ID=b.brand
left outer join colors c
on b.color=c.ID
group by
a.brand,
c.color
+--------+-------+--------------+
| brand | color | countOfBrand |
+--------+-------+--------------+
| BMW | Blue | 1 |
| BMW | White | 1 |
| Ford | Blue | 1 |
| Ford | White | 1 |
| Holden | NULL | 0 |
| Nissan | Black | 1 |
| Smart | White | 1 |
| Toyota | NULL | 1 |
| Toyota | Black | 1 |
| Toyota | Green | 2 |
| Toyota | Red | 1 |
+--------+-------+--------------+
11 rows in set (0.00 sec)
Wie wir sehen können, haben wir zwei äußere Verknüpfungen in der Abfrage, und die Ergebnisse werden wie erwartet angezeigt.
Was ist mit den anderen Arten von Verbindungen, fragen Sie? Was ist mit Überschneidungen?
Nun, nicht alle Datenbanken unterstützen die intersection aber so gut wie alle Datenbanken erlauben es Ihnen, eine Schnittmenge durch eine Verknüpfung (oder zumindest eine gut strukturierte Where-Anweisung) zu erstellen.
Eine Kreuzung ist eine Art von Verknüpfung, ähnlich wie eine union wie oben beschrieben - aber mit dem Unterschied, dass es seulement gibt Datenzeilen zurück, die zwischen den einzelnen Abfragen, die durch die Vereinigung verbunden sind, identisch sind (und ich meine wirklich identisch). Nur Zeilen, die in jeder Hinsicht identisch sind, werden zurückgegeben.
Ein einfaches Beispiel könnte so lauten:
select
*
from
colors
where
ID>2
intersect
select
*
from
colors
where
id<4
Während eine normale union Abfrage würde alle Zeilen der Tabelle zurückgeben (die erste Abfrage, die etwas über ID>2 und das zweite etwas mit ID<4 ), was zu einer vollständigen Menge führen würde, würde eine Intersect-Abfrage nur die Zeile zurückgeben, die id=3 da sie beide Kriterien erfüllt.
Wenn Ihre Datenbank keine Unterstützung für eine intersect Abfrage kann die obige Abfrage leicht durch die folgende Abfrage ergänzt werden:
select
a.ID,
a.color,
a.paint
from
colors a
join colors b
on a.ID=b.ID
where
a.ID>2
and b.ID<4
+----+-------+----------+
| ID | color | paint |
+----+-------+----------+
| 3 | Blue | Metallic |
+----+-------+----------+
1 row in set (0.00 sec)
Wenn Sie eine Kreuzung zwischen zwei verschiedenen Tabellen durchführen möchten und dabei eine Datenbank verwenden, die nicht von Haus aus eine Kreuzungsabfrage unterstützt, müssen Sie eine Verknüpfung auf jede Spalte der Tabellen.
