Several ways to connect tables in MySQL

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

Recommend

Solution to MySQL 8.0 cannot start 3534

MySQL 8.0 service cannot be started Recently enco...

CSS list sliding to prevent being covered by the bottom and adapt to the longer screen model processing

1. When the mobile terminal processes the list sl...

Detailed steps to install MySQL on CentOS 7

In CentOS7, when we install MySQL, MariaDB will b...

Detailed explanation of DOM DIFF algorithm in react application

Table of contents Preface What is VirtualDOM? Rea...

Get the calculated style in the CSS element (after cascading/final style)

To obtain the calculated style in a CSS element (t...

mysqldump parameters you may not know

In the previous article, it was mentioned that th...

Vue implements paging function

This article example shares the specific code of ...

Use of MySQL truncate table statement

The Truncate table statement is used to delete/tr...

Detailed steps for installing and using vmware esxi6.5

Table of contents Introduction Architecture Advan...

Tutorial on how to modify element.style inline styles

Preface When we were writing the web page style a...

Detailed explanation of Vue's sync modifier

Table of contents 1. Instructions 2. Modifiers 3....

MySQL master-slave configuration study notes

● I was planning to buy some cloud data to provid...

W3C Tutorial (10): W3C XQuery Activities

XQuery is a language for extracting data from XML...

mysql IS NULL using index case explanation

Introduction The use of is null, is not null, and...

jQuery realizes dynamic particle effect

This article shares the specific code of jQuery t...