In the last few interviews, I encountered the problem of left-right connection. I will review it here.

Let's show the results of the left and right connections in the following two tables

Left connection and right connection

Employee table:

mysql> select * from employ;
+-----------+------+------+
| name      | id   | sal  |
+-----------+------+------+
| Xiao Wang      | 1000 |    0 |
| petty thief      | 1001 |   90 |
| Xiaohua      | 1002 | NULL |
| xiaogui   | 1008 | 3800 |
| xiaolang  | 1009 | 3900 |
| xiaohu    | 1003 | 4000 |
+-----------+------+------+
6 rows in set (0.00 sec)

Student table:

mysql> select * from student;
+--------+------+------+
| name   | age  | id   |
+--------+------+------+
| Laziness |   10 | 1001 |
| Pleasant Sheep |  100 | 1002 |
| Beautiful sheep |   12 | NULL |
| Grey Wolf | NULL | 1004 |
+--------+------+------+
4 rows in set (0.00 sec)

Left connection

mysql> select employ.sal,student.age from employ left join student on employ.id
= student.id;
+------+------+
| sal  | age  |
+------+------+
|    0 | NULL |
|   90 |   10 |
| NULL |  100 |
| 3800 | NULL |
| 3900 | NULL |
| 4000 | NULL |
+------+------+
6 rows in set (0.00 sec)

Right connection

mysql> select employ.sal,student.age from employ right join student on employ.id
 = student.id;
+------+------+
| sal  | age  |
+------+------+
|   90 |   10 |
| NULL |  100 |
| NULL |   12 |
| NULL | NULL |
+------+------+
4 rows in set (0.00 sec)

Through the above two tables, we can summarize as follows:

Left join: mainly the left table (employ ee). Display all the data in the left table column. If there is no corresponding data in the right table, it is NULL

Right join: the table on the right is the main one. Display all the data in the right table column. If there is no corresponding data in the left table, it is NULL

Cartesian product

Information for two tables:

Employee table:

mysql> select * from employ;
+-----------+------+------+
| name      | id   | sal  |
+-----------+------+------+
| Xiao Wang      | 1000 |    0 |
| petty thief      | 1001 |   90 |
| Xiaohua      | 1002 | NULL |
| xiaogui   | 1008 | 3800 |
| xiaolang  | 1009 | 3900 |
| xiaohu    | 1003 | 4000 |
+-----------+------+------+
6 rows in set (0.00 sec)

Student table:

mysql> select * from student;
+--------+------+------+
| name   | age  | id   |
+--------+------+------+
| Laziness |   10 | 1001 |
| Pleasant Sheep |  100 | 1002 |
| Beautiful sheep |   12 | NULL |
| Grey Wolf | NULL | 1004 |
+--------+------+------+
4 rows in set (0.00 sec)

Cartesian product:

mysql> select * from employ, student;
+-----------+------+------+--------+------+------+
| name      | id   | sal  | name   | age  | id   |
+-----------+------+------+--------+------+------+
| Xiao Wang      | 1000 |    0 | Laziness |   10 | 1001 |
| Xiao Wang      | 1000 |    0 | Pleasant Sheep |  100 | 1002 |
| Xiao Wang      | 1000 |    0 | Beautiful sheep |   12 | NULL |
| Xiao Wang      | 1000 |    0 | Grey Wolf | NULL | 1004 |
| petty thief      | 1001 |   90 | Laziness |   10 | 1001 |
| petty thief      | 1001 |   90 | Pleasant Sheep |  100 | 1002 |
| petty thief      | 1001 |   90 | Beautiful sheep |   12 | NULL |
| petty thief      | 1001 |   90 | Grey Wolf | NULL | 1004 |
| Xiaohua      | 1002 | NULL | Laziness |   10 | 1001 |
| Xiaohua      | 1002 | NULL | Pleasant Sheep |  100 | 1002 |
| Xiaohua      | 1002 | NULL | Beautiful sheep |   12 | NULL |
| Xiaohua      | 1002 | NULL | Grey Wolf | NULL | 1004 |
| xiaogui   | 1008 | 3800 | Laziness |   10 | 1001 |
| xiaogui   | 1008 | 3800 | Pleasant Sheep |  100 | 1002 |
| xiaogui   | 1008 | 3800 | Beautiful sheep |   12 | NULL |
| xiaogui   | 1008 | 3800 | Grey Wolf | NULL | 1004 |
| xiaolang  | 1009 | 3900 | Laziness |   10 | 1001 |
| xiaolang  | 1009 | 3900 | Pleasant Sheep |  100 | 1002 |
| xiaolang  | 1009 | 3900 | Beautiful sheep |   12 | NULL |
| xiaolang  | 1009 | 3900 | Grey Wolf | NULL | 1004 |
| xiaohu    | 1003 | 4000 | Laziness |   10 | 1001 |
| xiaohu    | 1003 | 4000 | Pleasant Sheep |  100 | 1002 |
| xiaohu    | 1003 | 4000 | Beautiful sheep |   12 | NULL |
| xiaohu    | 1003 | 4000 | Grey Wolf | NULL | 1004 |
+-----------+------+------+--------+------+------+
24 rows in set (0.00 sec)

mysql> select count(*) from employ,student;
+----------+
| count(*) |
+----------+
|       24 |
+----------+
1 row in set (0.00 sec)

Summary: the two tables have 14 data in total, but we have 24 data. What's the situation? This is the problem of Cartesian product. The specific execution process of Cartesian product is as follows:

As shown in the figure, there are 6 items in the employ ee table, 4 items in the student table, and 6 * 4 = 24. So left join and right join solve the problem of Cartesian product.