Database left connection, right connection, internal connection, full connection
The effects of various connection modes in the database are shown as follows:
The following uses examples to understand the usage scenarios of various connections. In order to intuitively observe the results of the above different connection operations, two tables are prepared first:
-- sql_test.a definition
CREATE TABLE `a` (
`id` int NOT NULL AUTO_INCREMENT,
`value` varchar(100) NOT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- sql_test.a definition
CREATE TABLE `b` (
`id` int NOT NULL AUTO_INCREMENT,
`value` varchar(100) NOT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Copy the code
Then add some data to table A and table B, as shown below:
mysql> select * from a;
+----+----------+
| id | value |
+----+----------+
| 1 | Kobe |
| 2 | James |
| 3 | Forlogen |
| 4 | Bill |
| 5 | Amy |
| 6 | Coco |
+----+----------+
6 rows in set (0.00 sec)
mysql> select * from b;
+----+----------+
| id | value |
+----+----------+
| 1 | Kobe |
| 2 | James |
| 3 | Forlogen |
| 7 | Green |
| 8 | Pope |
| 9 | Adam |
+----+----------+
6 rows in set (0.00 sec)
Copy the code
Inner join
An inner join is equivalent to finding the intersection of two tables and executing the following statement:
mysql> select * from a inner join b on a.id=b.id;
+----+----------+----+----------+
| id | value | id | value |
+----+----------+----+----------+
| 1 | Kobe | 1 | Kobe |
| 2 | James | 2 | James |
| 3 | Forlogen | 3 | Forlogen |
+----+----------+----+----------+
3 rows in set (0.00 sec)
Copy the code
Left join
Left join: all data of the left table can be queried, while the right table can only be queried and the left table published data, other data is displayed as NULL.
mysql> select * from a left join b on a.id=b.id;
+----+----------+------+----------+
| id | value | id | value |
+----+----------+------+----------+
| 1 | Kobe | 1 | Kobe |
| 2 | James | 2 | James |
| 3 | Forlogen | 3 | Forlogen |
| 4 | Bill | NULL | NULL |
| 5 | Amy | NULL | NULL |
| 6 | Coco | NULL | NULL |
+----+----------+------+----------+
6 rows in set (0.00 sec)
Copy the code
Right join
All data of the right table can be queried, while the left table can only be queried and the right table published data, other data is displayed as NULL.
mysql> select * from a right join b on a.id=b.id;
+------+----------+----+----------+
| id | value | id | value |
+------+----------+----+----------+
| 1 | Kobe | 1 | Kobe |
| 2 | James | 2 | James |
| 3 | Forlogen | 3 | Forlogen |
| NULL | NULL | 7 | Green |
| NULL | NULL | 8 | Pope |
| NULL | NULL | 9 | Adam |
+------+----------+----+----------+
6 rows in set (0.00 sec)
Copy the code
Outer join (outer join)
The outer join (full join) queries all the data that the two tables do not duplicate.
Mysql does not support external connections.
Use both left join and right join to achieve the effect of full join, and use UNION to store non-duplicate data.
mysql> select a.id a_id,a.value a_value,b.id b_id,b.value b_value from a left join b on a.id=b.id
-> union
-> select a.id a_id,a.value a_value,b.id b_id,b.value b_value from a right join b on a.id=b.id;
+------+----------+------+----------+
| a_id | a_value | b_id | b_value |
+------+----------+------+----------+
| 1 | Kobe | 1 | Kobe |
| 2 | James | 2 | James |
| 3 | Forlogen | 3 | Forlogen |
| 4 | Bill | NULL | NULL |
| 5 | Amy | NULL | NULL |
| 6 | Coco | NULL | NULL |
| NULL | NULL | 7 | Green |
| NULL | NULL | 8 | Pope |
| NULL | NULL | 9 | Adam |
+------+----------+------+----------+
9 rows in set (0.00 sec)
Copy the code
The above query results are displayed separately. If you want to display them together, you can use select * from a union select * from b.
mysql> select * from a union select * from b;
+----+----------+
| id | value |
+----+----------+
| 1 | Kobe |
| 2 | James |
| 3 | Forlogen |
| 4 | Bill |
| 5 | Amy |
| 6 | Coco |
| 7 | Green |
| 8 | Pope |
| 9 | Adam |
+----+----------+
9 rows in set (0.00 sec)
Copy the code
Left join excluding inner join (left join excluding inner join)
The query results in data unique to the left table.
mysql> select a.id a_id,a.value a_value,b.id b_id,b.value b_value from a
-> left join b on a.id=b.id where b.id is null;
+------+---------+------+---------+
| a_id | a_value | b_id | b_value |
+------+---------+------+---------+
| 4 | Bill | NULL | NULL |
| 5 | Amy | NULL | NULL |
| 6 | Coco | NULL | NULL |
+------+---------+------+---------+
3 rows in set (0.00 sec)
Copy the code
Right join excluding inner join (right join excluding inner join)
The query results in data unique to the right table.
mysql> select a.id a_id,a.value a_value,b.id b_id,b.value b_value from a
-> right join b on a.id=b.id where a.id is null;
+------+---------+------+---------+
| a_id | a_value | b_id | b_value |
+------+---------+------+---------+
| NULL | NULL | 7 | Green |
| NULL | NULL | 8 | Pope |
| NULL | NULL | 9 | Adam |
+------+---------+------+---------+
3 rows in set (0.00 sec)
Copy the code
conclusion
For Mysql, it supports inner join, left join, and right join. Other forms of join implementation, need to use where to set certain restrictions to implement.