7. Mysql data query: inner join, left join, right join, full join query

Ready to work

First of all, I prepared two tables, namely the student table and the score table. The fields are as follows:

Student table

StudentID   Sname      sex     cardID            Birthday                 Email                    Class   enterTime               
----------  ---------  ------  ----------------  -----------------------  -----------------------  ------  ------------------------
0000000001  曹园贞        男       4966368447381562  1981-02-18 00:00:00.000  [email protected]  测试      2019-12-18 17:58:52.83  
0000000002  邓咏桂        女       4390725259203972  1981-10-15 00:00:00.000  [email protected]  网络      2019-12-18 17:58:52.83  
0000000003  武奇乐        男       9210284780293246  1987-11-07 00:00:00.000  [email protected]       网络      2019-12-18 17:58:52.84  
0000000004  方伊家        女       4748815837298104  1986-03-13 00:00:00.000  [email protected]    网络      2019-12-18 17:58:52.84  
0000000005  田育朋        男       1391060157410118  1988-11-06 00:00:00.000  [email protected]   开发      2019-12-18 17:58:52.84  

Score table

StudentID   subJectID  mark    
----------  ---------  --------
0000000001  0001       54      
0000000002  0002       51      
0000000003  0003       79      
0000000004  0001       77      
0000000005  0002       61      

problem

Query the student ID, name, class, and score of each student?
Next, we use inner join, left join, right join, and full join query to achieve.

Inner join query

Keywords:inner join on

Statement:

select * from a_table a inner join b_table b on a.a_id = b.b_id;

Description: Combine the records in the two tables and return the records that match the associated fields, that is, return the intersection (shaded) part of the two tables.

Insert picture description here

Analysis: Through the above two tables, we found that the student information and scores are in the two tables respectively. It is impossible to directly use a simple query method. Therefore, we continue to observe and find that there is one commonality in the student table and the score table, that is, there is a StudentID field.

SELECT A.StudentID,Sname,class,B.mark  
FROM tstudent A 
INNER JOIN tscore B 
ON A.StudentID = B.StudentID 
LIMIT 5 ;    #只显示前5行

result:

StudentID   Sname      class   mark    
----------  ---------  ------  --------
0000000001  曹园贞        测试      79      
0000000002  邓咏桂        网络      77           
0000000003  武奇乐        网络      60           
0000000004  方伊家        网络      84         
0000000005  田育朋        开发      61      

Two, left join query

Keywords:left join on / left outer join on

Statement:

SELECT  * FROM a_table a left join b_table b ON a.a_id = b.b_id;

Explanation: left join is the abbreviation of left outer join, its full name is left outer join, which is a kind of outer join. For the left (outer) connection, all the records in the left table (a_table) will be displayed, while the right table (b_table) will only display the records that meet the search criteria.Insufficient places in the table on the right are NULL.

SELECT A.StudentID,Sname,class,B.mark 
FROM tstudent A 
LEFT JOIN tscore B 
ON A.StudentID = B.StudentID 
LIMIT 5
StudentID   Sname      class   mark    
----------  ---------  ------  --------
0000000001  曹园贞        测试      79      
0000000002  邓咏桂        网络      77           
0000000003  武奇乐        网络      60           
0000000004  方伊家        网络      84         
0000000005  田育朋        开发      61      

Because the tables we prepare have data, it is impossible to display null values.

Three, right join right join

Keywords:right join on / right outer join on

Statement:

SELECT  * FROM a_table a right outer join b_table b on a.a_id = b.b_id;

Note: right join is the abbreviation of right outer join, its full name is right outer join, which is a kind of outer join. Contrary to the left (outer) connection, the right (outer) connection, the left table (a_table) will only display the records that meet the search criteria, and the right table (b_table) will show all the records.Insufficient records in the left table are all NULL.

SELECT A.StudentID,Sname,class,B.mark 
FROM tstudent A 
RIGHT JOIN tscore B 
ON A.StudentID = B.StudentID 
LIMIT 5
StudentID   Sname      class   mark    
----------  ---------  ------  --------
0000000001  曹园贞        测试      79      
0000000002  邓咏桂        网络      77           
0000000003  武奇乐        网络      60           
0000000004  方伊家        网络      84         
0000000005  田育朋        开发      61      

Because the tables we prepare have data, it is impossible to display null values.

Four, fully connected union

Keywords:union /union all

Statement:

(select colum1,colum2...columN from tableA ) union (select colum1,colum2...columN from tableB )
(select colum1,colum2...columN from tableA ) union all (select colum1,colum2...columN from tableB );

Notes on the union statement:

  • The number of columns separately retrieved by the SQL connected through union must be the same;
  • When the names of the merged table columns are not required to be the same, the first SQL table column name shall prevail;
  • When using union, completely equal rows will be merged. Because the merging is time-consuming, generally do not use union directly for merging, but usually use union all for merging;
  • For sql clauses connected by union, there is no need to write order by in a single clause, because there will be no sorting effect. But you can sort the final result set;

No sorting effect

(select id,name from A order by id) union all (select id,name from B order by id); 

Sorting effect

(select id,name from A ) union all (select id,name from B ) order by id;