Several ways to connect tables in MySQL

The connection method in MySQL table is actually very simple, here we simply list their characteristics.

Table connections (JOIN) can be divided into inner connections (JOIN/INNER JOIN) and outer connections (LEFT JOIN/RIGHT JOIN).

First, let's look at the two tables in this demonstration:

mysql> SELECT * FROM student;
+------+----------+------+------+
| s_id | s_name | age | 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 rows in set (0.00 sec)

mysql> SELECT * FROM class;
+------+---------+-------+
| c_id | c_name | count |
+------+---------+-------+
| 1 | MATH | 65 |
| 2 | CHINESE | 70 |
| 3 | ENGLISH | 50 |
| 4 | HISTORY | 30 |
| 5 | BIOLOGY | 40 |
+------+---------+-------+
5 rows in set (0.00 sec)

First of all, the prerequisite for tables to be connected is that there are the same comparable columns in the two tables.

1. Inner Join

mysql> SELECT * FROM student INNER JOIN class ON student.c_id = class.c_id;
+------+----------+------+------+------+---------+-------+
| s_id | s_name | age | c_id | c_id | c_name | count |
+------+----------+------+------+------+---------+-------+
| 1 | xiaoming | 13 | 1 | 1 | MATH | 65 |
| 2 | xiaohong | 41 | 4 | 4 | HISTORY | 30 |
| 3 | xiaoxia | 22 | 3 | 3 | ENGLISH | 50 |
| 4 | xiaogang | 32 | 1 | 1 | MATH | 65 |
| 5 | xiaoli | 41 | 2 | 2 | CHINESE | 70 |
| 6 | wangwu | 13 | 2 | 2 | CHINESE | 70 |
| 7 | lisi | 22 | 3 | 3 | ENGLISH | 50 |
+------+----------+------+------+------+---------+-------+
7 rows in set (0.00 sec)

Simply put, an inner join is to display all the data of the rows that meet the conditions in the two tables together. That is, if the conditions are not met, the data that can be found in table A but not in B (or vice versa) will not be displayed.

2. Outer Join

mysql> SELECT * FROM student LEFT JOIN class ON student.c_id = class.c_id;
+------+----------+------+------+------+---------+-------+
| s_id | s_name | age | c_id | c_id | c_name | count |
+------+----------+------+------+------+---------+-------+
| 1 | xiaoming | 13 | 1 | 1 | MATH | 65 |
| 2 | xiaohong | 41 | 4 | 4 | HISTORY | 30 |
| 3 | xiaoxia | 22 | 3 | 3 | ENGLISH | 50 |
| 4 | xiaogang | 32 | 1 | 1 | MATH | 65 |
| 5 | xiaoli | 41 | 2 | 2 | CHINESE | 70 |
| 6 | wangwu | 13 | 2 | 2 | CHINESE | 70 |
| 7 | lisi | 22 | 3 | 3 | ENGLISH | 50 |
| 8 | zhangsan | 11 | 9 | NULL | NULL | NULL |
+------+----------+------+------+------+---------+-------+
8 rows in set (0.00 sec)


mysql> SELECT * FROM student RIGHT JOIN class ON student.c_id = class.c_id;
+------+----------+------+------+------+---------+-------+
| s_id | s_name | age | c_id | c_id | c_name | count |
+------+----------+------+------+------+---------+-------+
| 1 | xiaoming | 13 | 1 | 1 | MATH | 65 |
| 4 | xiaogang | 32 | 1 | 1 | MATH | 65 |
| 5 | xiaoli | 41 | 2 | 2 | CHINESE | 70 |
| 6 | wangwu | 13 | 2 | 2 | CHINESE | 70 |
| 3 | xiaoxia | 22 | 3 | 3 | ENGLISH | 50 |
| 7 | lisi | 22 | 3 | 3 | ENGLISH | 50 |
| 2 | xiaohong | 41 | 4 | 4 | HISTORY | 30 |
| NULL | NULL | NULL | NULL | 5 | BIOLOGY | 40 |
+------+----------+------+------+------+---------+-------+
8 rows in set (0.00 sec)

The above shows two cases of outer join: left join and right join. These two are almost the same, the only difference is that the main table of the left join is the table on the left, and the main table of the right join is the table on the right. The difference between an outer join and an inner join is that it displays all the rows of the main table, and replaces the data in the main table that is not in other tables with NULL.

Summarize

This concludes this article about several ways to connect tables in MySQL. For more information about how to connect MySQL tables, please search previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:

Basic multi-table join query tutorial in MySQL
mysql delete multi-table connection deletion function
How to create an index on a join table in MySQL
MySQL table LEFT JOIN left join and RIGHT JOIN right join example tutorial
MySQL multi-table join query example explanation
A simple tutorial on optimizing table join queries in MySQL
mysql three tables connected to create a view
Detailed explanation of MySQL database--multi-table query--inner join, outer join, subquery, correlated subquery
Advanced learning tutorial on MySQL LEFT JOIN table connection
MySQL multi-table join introductory tutorial

<<: Detailed tutorial on using the tomcat8-maven-plugin plugin in Maven

>>: JavaScript to implement checkbox selection or cancellation