MySQLs ausführliche Erklärung von JOIN

MySQL-Video-TutorialSpalte mit ausführlicher Erklärung von Join

Verwandte kostenlose Lernempfehlungen: MySQL-Video-Tutorial

0-Index

• JOINAusführungsreihenfolge von. Anweisungen
• INNEN/LINKS/ RIGHT/ Der Unterschied zwischen FULL JOIN
• ON und WHERE

1 Übersicht

Eine vollständige SQL-Anweisung wird in mehrere Klauseln aufgeteilt, und während der Ausführung der Klauseln werden virtuelle Tabellen generiert (vt ), aber das Ergebnis gibt nur die letzte virtuelle Tabelle zurück. Ausgehend von dieser Idee versuchen wir, den Ausführungsprozess der JOIN-Abfrage zu verstehen und einige häufig gestellte Fragen zu beantworten.
Wenn Sie noch keine Ahnung von den Ausführungsergebnissen verschiedener JOINs haben, können Sie diesen Artikel unten lesen

2 Die Ausführungsreihenfolge von JOIN

Das Folgende ist die allgemeine Struktur der JOIN-Abfrage

SELECT <row_list> 
  FROM <left_table> 
    <inner|left|right> JOIN <right_table> 
      ON <join condition> 
        WHERE <where_condition>

Die Ausführungsreihenfolge ist wie folgt (Nr. 1 in der SQL-Anweisung Eine wird immer als FROM-Klausel ausgeführt):

• FROM: Führt das kartesische Produkt der linken und rechten Tabelle aus, um die erste Tabelle vt1 zu generieren. Die Anzahl der Zeilen beträgt n*m (n ist die Anzahl der Zeilen in der linken Tabelle, m ist die Anzahl der Zeilen in der rechten Tabelle
• ON: Filtern Sie vt1 Zeile für Zeile gemäß den ON-Bedingungen und fügen Sie die Ergebnisse ein in vt2
• JOIN: Externe Zeile hinzufügen. Wenn LEFT JOIN(LEFT OUTER JOIN) angegeben ist, wird jede Zeile der linken Tabelle zuerst durchlaufen und die Zeilen, die nicht angegeben sind in vt2 wird in vt2 eingefügt und die verbleibenden Felder der Zeile werden mit NULL gefüllt, was zu VT3 führt. Das Gleiche gilt, wenn RIGHT JOIN angegeben ist angegeben ist, werden keine externen Zeilen hinzugefügt und der obige Einfügevorgang wird ignoriert (vt2=vt3). Es gibt also keinen Unterschied in den Ausführungsergebnissen, wenn die Filterbedingungen von INNER JOIN in ON platziert sind oder WHERE, was weiter unten ausführlich erläutert wird) WHERE: Bedingung für vt3 Filter, Zeilen, die die Bedingungen erfüllen, werden an vt4 ausgegeben
• SELECT: Nehmen Sie die angegebenen Felder von vt4 bis vt5 heraus
• Im Folgenden wird anhand eines Beispiels der oben beschriebene Prozess des Verknüpfens von Tabellen vorgestellt (dieses Beispiel ist keine gute Praxis, sondern dient lediglich der Veranschaulichung der Verknüpfungssyntax)
3 Zum Beispiel

Erstellen Sie eine Benutzerinformationstabelle:

CREATE TABLE `user_info` (
  `userid` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  UNIQUE `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Erstellen Sie eine Benutzersaldotabelle:

CREATE TABLE `user_account` (
  `userid` int(11) NOT NULL,
  `money` bigint(20) NOT NULL,
 UNIQUE `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Importieren Sie einfach einige Daten:

select * from user_info;
+--------+------+
| userid | name |
+--------+------+
|   1001 | x    |
|   1002 | y    |
|   1003 | z    |
|   1004 | a    |
|   1005 | b    |
|   1006 | c    |
|   1007 | d    |
|   1008 | e    |
+--------+------+
8 rows in set (0.00 sec)

select * from user_account;
+--------+-------+
| userid | money |
+--------+-------+
|   1001 |    22 |
|   1002 |    30 |
|   1003 |     8 |
|   1009 |    11 |
+--------+-------+
4 rows in set (0.00 sec)

Insgesamt 8 Benutzer haben Benutzernamen und 4 Benutzer haben Kontostände

Erhalten Sie den Benutzernamen und den Kontostand mit der Benutzer-ID 1003. Die SQL lautet wie folgt

:

SELECT i.name, a.money 
  FROM user_info as i 
    LEFT JOIN user_account as a 
      ON i.userid = a.userid 
        WHERE a.userid = 1003;

Schritt 1: Führen Sie die FROM-Klausel aus, um eine kartesische Produktoperation für die beiden Tabellen durchzuführen. Nach der kartesischen Produktoperation werden alle Zeilen in den beiden Tabellen zurückgegeben. Die Kombination aus der linken Tabelle user_info hat 8 Zeilen und die Die rechte Tabelle user_account hat 4 Zeilen. Die generierte virtuelle Tabelle vt1 ist 8*4=32 Zeilen:

SELECT * FROM user_info as i LEFT JOIN user_account as a ON 1;
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1001 |    22 |
|   1003 | z    |   1001 |    22 |
|   1004 | a    |   1001 |    22 |
|   1005 | b    |   1001 |    22 |
|   1006 | c    |   1001 |    22 |
|   1007 | d    |   1001 |    22 |
|   1008 | e    |   1001 |    22 |
|   1001 | x    |   1002 |    30 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1002 |    30 |
|   1004 | a    |   1002 |    30 |
|   1005 | b    |   1002 |    30 |
|   1006 | c    |   1002 |    30 |
|   1007 | d    |   1002 |    30 |
|   1008 | e    |   1002 |    30 |
|   1001 | x    |   1003 |     8 |
|   1002 | y    |   1003 |     8 |
|   1003 | z    |   1003 |     8 |
|   1004 | a    |   1003 |     8 |
|   1005 | b    |   1003 |     8 |
|   1006 | c    |   1003 |     8 |
|   1007 | d    |   1003 |     8 |
|   1008 | e    |   1003 |     8 |
|   1001 | x    |   1009 |    11 |
|   1002 | y    |   1009 |    11 |
|   1003 | z    |   1009 |    11 |
|   1004 | a    |   1009 |    11 |
|   1005 | b    |   1009 |    11 |
|   1006 | c    |   1009 |    11 |
|   1007 | d    |   1009 |    11 |
|   1008 | e    |   1009 |    11 |
+--------+------+--------+-------+
32 rows in set (0.00 sec)

Schritt 2: Führen Sie die ON-Klausel aus, um die Zeilen herauszufiltern, die die Bedingungen nicht erfüllen

ON i.userid = a. userid Nach dem Filtern sieht vt2 wie folgt aus:

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

Schritt 3: JOIN, um externe Zeilen hinzuzufügen

LEFT JOIN

fügt Zeilen aus der linken Tabelle, die nicht in vt2 erscheinen, in vt2 ein, und die verbleibenden Felder jeder Zeile werden dies tun wird mit NULL gefüllt,

RIGHT JOIN

ÄhnlichIn diesem Beispiel wird LEFT JOIN verwendet, sodass die verbleibenden Zeilen der linken Tabelle user_info
zur generierten Tabelle vt3 hinzugefügt werden:

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
|   1004 | a    |   NULL |  NULL |
|   1005 | b    |   NULL |  NULL |
|   1006 | c    |   NULL |  NULL |
|   1007 | d    |   NULL |  NULL |
|   1008 | e    |   NULL |  NULL |
+--------+------+--------+-------+

Schritt 4: WHERE-Bedingung Filter WHERE a.userid = 1003 Tabelle vt4 generieren:

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

Schritt 5: SELECT

SELECT i.name, a.money VT5 generieren:

+------+-------+
| name | money |
+------+-------+
| z    |     8 |
+------+-------+

Die virtuelle Tabelle vt5 wird als Endergebnis an den Client zurückgegeben

Einführung Nachdem wir den Prozess der Tabellenverknüpfung abgeschlossen haben, schauen wir uns die Unterschiede zwischen häufig verwendeten

JOIN

4 an. Die Unterschiede zwischen INNER/LEFT/RIGHT/FULL JOIN

INNER JOIN...ON...

: Return die linken und rechten Tabellen, die miteinander übereinstimmen Alle Zeilen (da nur der zweite Schritt der ON-Filterung oben ausgeführt wird und der dritte Schritt des Hinzufügens externer Zeilen nicht ausgeführt wird)

• LEFT JOIN...ON...: Gibt alle Zeilen der linken Tabelle zurück, wenn einige Zeilen vorhanden sind. Wenn es keine entsprechende übereinstimmende Zeile in der rechten Tabelle gibt, setzen Sie die Spalten der rechten Tabelle in der neuen Tabelle auf NULL
• RIGHT JOIN...ON... : Gibt alle Zeilen in der rechten Tabelle zurück, wenn sich einige Zeilen in der linken befinden. Wenn es keine entsprechende übereinstimmende Zeile in der Tabelle gibt, setzen Sie die Spalten der linken Tabelle in der neuen Tabelle auf NULL
• INNER JOIN
Nehmen Sie das Dritter Schritt oben

externe Zeile hinzufügen

als Beispiel: Wenn

LEFT JOIN

in INNER JOIN ersetzt, wird dieser Schritt übersprungen und die generierte Tabelle vt3 ist genau die gleiche wie vt2:

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

RIGHT JOINIf

LEFT JOIN

wird durch

RIGHT JOIN

ersetzt, die generierte Tabelle vt3 lautet wie folgt:

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
|   NULL | NULL |   1009 |    11 |
+--------+------+--------+-------+

Da die Zeile userid=1009 in user_account (rechte Tabelle) vorhanden ist, der Datensatz dieser Zeile jedoch nicht in user_info gefunden werden kann ( linke Tabelle), daher wird im dritten Schritt die folgende Zeile eingefügt:

|   NULL | NULL |   1009 |    11 |

FULL JOIN

上文引用的文章中提到了标准SQL定义的FULL JOIN，这在mysql里是不支持的，不过我们可以通过LEFT JOIN + UNION + RIGHT JOIN 来实现FULL JOIN：

SELECT * 
  FROM user_info as i 
    RIGHT JOIN user_account as a 
      ON a.userid=i.userid
union 
SELECT * 
  FROM user_info as i 
    LEFT JOIN user_account as a 
      ON a.userid=i.userid;

他会返回如下结果：

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
|   NULL | NULL |   1009 |    11 |
|   1004 | a    |   NULL |  NULL |
|   1005 | b    |   NULL |  NULL |
|   1006 | c    |   NULL |  NULL |
|   1007 | d    |   NULL |  NULL |
|   1008 | e    |   NULL |  NULL |
+--------+------+--------+-------+

ps：其实我们从语义上就能看出LEFT JOIN和RIGHT JOIN没什么差别，两者的结果差异取决于左右表的放置顺序，以下内容摘自mysql官方文档：

RIGHT JOIN works analogously to LEFT JOIN. To keep code portable across databases, it is recommended that you use LEFT JOIN instead of RIGHT JOIN.

所以当你纠结使用LEFT JOIN还是RIGHT JOIN时，尽可能只使用LEFT JOIN吧

5 ON和WHERE的区别

上文把JOIN的执行顺序了解清楚之后，ON和WHERE的区别也就很好理解了。
举例说明:

SELECT * 
  FROM user_info as i
    LEFT JOIN user_account as a
      ON i.userid = a.userid and i.userid = 1003;

SELECT * 
  FROM user_info as i
    LEFT JOIN user_account as a
      ON i.userid = a.userid where i.userid = 1003;

第一种情况LEFT JOIN在执行完第二步ON子句后，筛选出满足i.userid = a.userid and i.userid = 1003的行，生成表vt2，然后执行第三步JOIN子句，将外部行添加进虚拟表生成vt3即最终结果：

vt2:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+
vt3:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   NULL |  NULL |
|   1002 | y    |   NULL |  NULL |
|   1003 | z    |   1003 |     8 |
|   1004 | a    |   NULL |  NULL |
|   1005 | b    |   NULL |  NULL |
|   1006 | c    |   NULL |  NULL |
|   1007 | d    |   NULL |  NULL |
|   1008 | e    |   NULL |  NULL |
+--------+------+--------+-------+

而第二种情况LEFT JOIN在执行完第二步ON子句后，筛选出满足i.userid = a.userid的行，生成表vt2；再执行第三步JOIN子句添加外部行生成表vt3；然后执行第四步WHERE子句，再对vt3表进行过滤生成vt4，得的最终结果：

vt2:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+
vt3:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
|   1004 | a    |   NULL |  NULL |
|   1005 | b    |   NULL |  NULL |
|   1006 | c    |   NULL |  NULL |
|   1007 | d    |   NULL |  NULL |
|   1008 | e    |   NULL |  NULL |
+--------+------+--------+-------+
vt4:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

如果将上例的LEFT JOIN替换成INNER JOIN，不论将条件过滤放到ON还是WHERE里，结果都是一样的，因为INNER JOIN不会执行第三步添加外部行

SELECT * 
  FROM user_info as i
    INNER JOIN user_account as a
      ON i.userid = a.userid and i.userid = 1003;

SELECT * 
  FROM user_info as i
    INNER JOIN user_account as a
      ON i.userid = a.userid where i.userid = 1003;

返回结果都是:

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

想了解更多编程学习，敬请关注php培训栏目！

Das obige ist der detaillierte Inhalt vonMySQLs ausführliche Erklärung von JOIN. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!