Mehrere Möglichkeiten zum Verbinden von Tabellen in MySQL

mysql> AUSWÄHLEN * VON Student;
+------+----------+------+------+
| s_id | s_name | Alter | c_id |
+------+----------+------+------+
| 1 | xiaoming | 13 | 1 |
| 2 | xiaohong | 41 | 4 |
| 3 | xiaoxia | 22 | 3 |
| 4 | xiaogang | 32 | 1 |
| 5 | xiaoli | 41 | 2 |
| 6 | wangwu | 13 | 2 |
| 7 | lisi | 22 | 3 |
| 8 | Zhangsan | 11 | 9 |
+------+----------+------+------+
8 Zeilen im Satz (0,00 Sek.)

mysql> SELECT * FROM Klasse;
+------+---------+-------+
| c_id | c_name | Anzahl |
+------+---------+-------+
| 1 | MATHE | 65 |
| 2 | CHINESISCH | 70 |
| 3 | DEUTSCH | 50 |
| 4 | GESCHICHTE | 30 |
| 5 | BIOLOGIE | 40 |
+------+---------+-------+
5 Zeilen im Satz (0,00 Sek.)

mysql> SELECT * FROM student INNER JOIN class ON student.c_id = class.c_id;
+------+----------+------+------+------+---------+---------+-----------+
| s_id | s_name | Alter | c_id | c_id | c_name | Anzahl |
+------+----------+------+------+------+---------+---------+-----------+
| 1 | xiaoming | 13 | 1 | 1 | MATHEMATIK | 65 |
| 2 | xiaohong | 41 | 4 | 4 | GESCHICHTE | 30 |
| 3 | xiaoxia | 22 | 3 | 3 | DEUTSCH | 50 |
| 4 | xiaogang | 32 | 1 | 1 | MATHEMATIK | 65 |
| 5 | xiaoli | 41 | 2 | 2 | CHINESISCH | 70 |
| 6 | wangwu | 13 | 2 | 2 | CHINESISCH | 70 |
| 7 | lisi | 22 | 3 | 3 | DEUTSCH | 50 |
+------+----------+------+------+------+---------+---------+-----------+
7 Zeilen im Satz (0,00 Sek.)

mysql> SELECT * FROM student LEFT JOIN class ON student.c_id = class.c_id;
+------+----------+------+------+------+---------+---------+-----------+
| s_id | s_name | Alter | c_id | c_id | c_name | Anzahl |
+------+----------+------+------+------+---------+---------+-----------+
| 1 | xiaoming | 13 | 1 | 1 | MATHEMATIK | 65 |
| 2 | xiaohong | 41 | 4 | 4 | GESCHICHTE | 30 |
| 3 | xiaoxia | 22 | 3 | 3 | DEUTSCH | 50 |
| 4 | xiaogang | 32 | 1 | 1 | MATHEMATIK | 65 |
| 5 | xiaoli | 41 | 2 | 2 | CHINESISCH | 70 |
| 6 | wangwu | 13 | 2 | 2 | CHINESISCH | 70 |
| 7 | lisi | 22 | 3 | 3 | DEUTSCH | 50 |
| 8 | zhangsan | 11 | 9 | NULL | NULL | NULL |
+------+----------+------+------+------+---------+---------+-----------+
8 Zeilen im Satz (0,00 Sek.)


mysql> SELECT * FROM student RIGHT JOIN class ON student.c_id = class.c_id;
+------+----------+------+------+------+---------+---------+-----------+
| s_id | s_name | Alter | c_id | c_id | c_name | Anzahl |
+------+----------+------+------+------+---------+---------+-----------+
| 1 | xiaoming | 13 | 1 | 1 | MATHEMATIK | 65 |
| 4 | xiaogang | 32 | 1 | 1 | MATHEMATIK | 65 |
| 5 | xiaoli | 41 | 2 | 2 | CHINESISCH | 70 |
| 6 | wangwu | 13 | 2 | 2 | CHINESISCH | 70 |
| 3 | xiaoxia | 22 | 3 | 3 | DEUTSCH | 50 |
| 7 | lisi | 22 | 3 | 3 | DEUTSCH | 50 |
| 2 | xiaohong | 41 | 4 | 4 | GESCHICHTE | 30 |
| NULL | NULL | NULL | NULL | 5 | BIOLOGIE | 40 |
+------+----------+------+------+------+---------+---------+-----------+
8 Zeilen im Satz (0,00 Sek.)