Detailed explanation of connection query

Connection query:

Find the qualified union of the two tables with a common field. Join the two tables through a common field.

Common connections:

Inner join: match according to the common fields in the table

There are two kinds of external links: left outer link and right outer link.

1 intra-connection syntax:

Select field from Table 1 inner join Table 2 on Table 1. Field = Table 2. Field

2 intra-join: match according to the common fields in the table

Create table student (

Sid int (4) primary key auto_increment

Name varchar (50)

);

Mysql > insert into student values (1), (2)), (3)), (4))

Create a grade table: grade

Create table grade (

Id int (4) primary key auto_increment

Score varchar (20)

Sid int (4)

);

Mysql > insert into grade (score,sid) values ('1231), (' 1231)), ('1231) (5), (())

Inquire about the information of people who have achieved some results.

Mysql > select student.*,grade.* from student,grade where student.sid=grade.sid

Or:

Or:

Mysql > select student.*,grade.* from student inner join grade on student.sid=grade.sid

Or:

Mysql > select student.*,grade.* from student join grade on student.sid=grade.sid

Use table aliases to abbreviate SQL statements

Mysql > select s.journal g.* from student as s inner join grade as g on s.sid=g.sid

3 external join: all the records of a data table and the records that meet the connection conditions in another appearance.

Left connection: select field from a table left join b table on connection condition

Table an is the main table and is displayed.

B form from table

There are all the contents of the master table, and there is no real null in the slave table.

Mysql > select * from student as s left join grade as g on s.sid=g.sid

Right connection:

Select field from a table right join b table on condition

A table is from the table, both are displayed.

B table master table

Select * from student as s right join grade as g on s.sid=g.sid

The grade table is the master table, all records are displayed, and student is the slave table. The mismatch is shown in NULL

Right join, can be multi-table connection

Note: there is a join multi-table join is that the first two tables are joined after becoming a slave table, and then joined with the third table.