What is the usage of join and on in mysql

Usage: 1. Join is used to obtain data that exists in different tables based on the relationship between columns in two or more tables. It has the function of joining. The syntax is "Table 1 join Table 2 "; 2. On is used to add constraints between two connected tables. The syntax is "table 1 join table 2 on condition".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

#What is the usage of join and on in mysql

# The role of join
is that when two or more tables are related, join needs to be used to connect these related tables to process or analyze data:

• Usage of join: Connection Take a look at the example first and you will understand:
For example: There is a table called stu and a table called class. stu join class will generate a new table,
• After we execute: stu join class, the new table becomes:
• To summarize through the above example: The column name after the join is the sum of the
column names of the two tables, which may produce the same column name, such as id and name
• First use a row of data in table stu
and each row of

data

in table class to continuously splice to generate new rows

Then use the second row

of

table stu to splice with each row data in table class, and so on

table stu is 3 rows, the table class is 2 rows, so according to the above rules, a new table with 3*2 = 6 rows

on usage: add constraints

Generally, the table after we join is not what we want. In this case, we can use ON to add some conditions:

For example: stu join class on classid = class.id

, after on is the condition we added. We want the data in the classid column to be equal to the data in the id column. Note here that the table column names after the join are repeated, so ON We need to add the original table name to the subsequent conditional statement. For example, here:

classid = class.id, since the id has two columns, here we want to table the id in the class, so it is class.id.

So: After executing: stu join class on classid = class.id

, the table becomes:

Tips:

When there is only join

, the following on can be replaced with where; is verified in the database:

mysql -u root -p                                 # 登录数据库，输入密码
CREATE DATABASE new;             # 创建数据库new
use new;                                            # 使用这个数据库
CREATE TABLE stu(id int primary key,name char(10),classid int)default charset = utf8mb4;             # 创建表stu
INSERT INTO stu (id,name,classid)VALUES(1,'A',1),(2,'B',1),(3,"C",2);                        # 插入数据
SELET * FROM stu;

#########

CREATE TABLE class(id int primary key,name char(10),teacher char(10))default charset = utf8mb4;          # 创建表class
INSERT INTO class (id,name,teacher)VALUES(1,'实验班',"小红"),(2,'普通班',"小蓝");                      #  插入数据
SELECT * FROM class;

#########

SELECT * FROM stu join class;                  #   用join连接两个表

## #######

SELECT * FROM stu join class ON classid = class.id;               # 用on来添加条件

############### Recommended learning: ###mysql video tutorial######

The above is the detailed content of What is the usage of join and on in mysql. For more information, please follow other related articles on the PHP Chinese website!