Mysql join query (commonly known as join table query) inner join, outer join, natural join
Classification of join queries
The test data used in this article
``create table student (
Id int primary key auto_increment
Name varchar (10)
);
Insert into student values
(null,'xiaohong')
(null,'xiaoming')
(null,'xiaogang')
(null,'xiaoliang')
Create table score (
Id int primary key auto_increment
Stu_id int not null
Score decimal (5par 2)
);
Insert into score values
(null,1300.45)
(null,2400.35)
(null,3500); ``
Internal connection inner join / join
Because mysql is connected internally by default, join is equivalent to inner join
In the case of two tables, an inner join returns only records that have a join match during the join process. That is, according to the join condition, a record of the existence of corresponding data in both tables will be returned.
For example:
Select stu.id,stu.name,s.score from student as stu inner join score as s on stu.id = s.stu_id
As shown in the picture above, Xiaogang did not appear in the final result because Xiao Liang had no results.
Connection condition analysis:
On using where can be used for connection conditions.
Difference: on can use on in any case in a join table query, and on is recommended. On is the result of judging whether or not to keep a joined table according to the on condition in the process of joining a table.
Using can be used when the field names of a connected table query are the same. Such as using (id). The fields used in the using condition are returned only once in the result.
Where filters the data according to the where condition after the join table operation is completed. It is not recommended because it is inefficient.
External connection
When an external join query, the other party is allowed to have data that does not match it. The connection conditions of the outer connection are not allowed to use where, you must use one of the on or using, and the others are the same as the inner connection.
Left outer connection (left outer join / left join):
The left table is the main table, and the record of the left table is returned even if the right table does not have a record that matches the left table. Records that do not match the right table and the left table are ignored.
For example:
Select * from student as stu left join score as s on stu.id = s.stu_id
The results are as follows:
+-- +
| | id | name | id | stu_id | score | |
+-- +
| | 1 | xiaohong | 1 | 1 | 300.45 | |
| | 2 | xiaoming | 2 | 2 | 400.35 | |
| | 3 | xiaogang | 3 | 3 | 500.00 | |
| | 4 | xiaoliang | NULL | NULL | NULL | |
+-- +
Right external connection (right outer join / right join):
The right table is the main table, and the record of the right table is returned even if there is no matching record in the left table.
For example:
Select * from score as s right join student as stu on s.stu_id=stu.id
+-+ +
| | id | stu_id | score | id | name | |
+-+ +
| | 1 | 1 | 300.45 | 1 | xiaohong |
| | 2 | 2 | 400.35 | 2 | xiaoming |
| | 3 | 3 | 500.00 | 3 | xiaogang |
| | NULL | NULL | NULL | 4 | xiaoliang | |
+-+ +
Full external connection (full join): it is not supported by mysql and can be simulated by union. Natural connection natural join (same as join) natural left join (same as left join) natural right join (same as right join)
The natural join automatically determines that the query result is returned with the same field in both tables as the join condition.
Note: the result of Cartesian product will occur if the inner connection does not write the connection condition, which should be avoided, while the outer connection without writing the connection condition will report an error.
