Reprint please specify the source!! Before the data table connection operation using commas or joins, the concept of several connections has been a muddle, recently studied a wave, the differences between these connections to understand. Connection: A xjoin B (primary Table Operation Association table) SELECT procedure: From->where->group by->having->order By->limit When the on syntax is not used, the join, inner Join, comma, and cross join results are the same as the Cartesian product of 2 tables. Commas have different precedence than other operators, so it is possible to generate syntax errors, minimizing the use of comma join, inner join, cross join support on and using syntax,The comma does not support on and using syntaxOn syntax: Filter the results of the connection, the corresponding column values of the two tables are the same in the result set, you can connect multiple column values through and matching requirements, column names can be different select * from Tb_test1 inner Join Tb_student on tb_test1.id=tb_student.id;using syntax: Filter the results of the connection, the corresponding column values of the two tables are the same in the result set, with multiple column names in parentheses to require a comma connection, the column name must be the same select * from Tb_test1 Cross join Tb_student using (ID); MySQL only! : (No full outer connection in MySQL, implemented with union) Join=inner Join=cross join (Cartesian product) left join=left outer joinright join=right outer join Sample data: select * from tb_test1;+----+-----------+--------+-------+| ID | name | Gender | Score |+----+-----------+--------+-------+| 1 | Liyi | Men | 95.30 | | 2 | Two ya | Women | 95.30 | | 3 | Zhang San | Women | 95.30 | | 4 | John Doe | Women | 95.30 | | 7 | Juruba | Men | 95.30 | | 9 | Hou Yi | Men | 95.30 |+----+-----------+--------+-------+ select * from tb_student;+----+--------+--------+------------+| ID | name | Gender | birthday |+----+--------+--------+------------+| 1 | Liyi | Men | 1988-03-20 | | 2 | kevin | Men | 1987-08-23 | | 3 | marry | Women | 1989-11-25 | | 4 | lucy | Women | 1989-11-25 | | 5 | lily | Women | 1992-01-25 |+----+--------+--------+------------+
Natural Join : Natural connection (not allowed with on/using) natrual join: All-natural connection, equivalent matching of columns of the same name on the left and right 2 tables, not specified with on,using operation, automatic deletion of excess column names natrual left join: Leave a natural connection, keep the columns of 2 tables (delete extra column names), whichever is left table, no matching right table column, value null natrual Join: and left natural connection opposite select * from Tb_test1 natural join tb_student;+----+--------+--------+-------+------------+| ID | name | Gender | Score | birthday |+----+--------+--------+-------+------------+| 1 | Liyi | Men | 95.30 | 1988-03-20 |+----+--------+--------+-------+------------+ select * from Tb_test1 Natural LEFT join tb_student;+-- --+-----------+--------+-------+------------+| ID | name | Gender | Score | birthday |+----+-----------+--------+-------+------------+| 1 | Liyi | Men | 95.30 | 1988-03-20 | | 2 | Two ya | Women | 95.30 | null | | 3 | Zhang San | Women | 95.30 | null | | 4 | John Doe | Women | 95.30 | null | | 7 | Juruba | Men | 95.30 | null | | 9 | Hou Yi | Men | 95.30 | null |+----+-----------+--------+-------+------------+ select * from Tb_test1 Natural Right Join tb_student;+----+--------+--------+------------+-------+| ID | name | Gender | birthday | Score |+----+--------+--------+------------+-------+| 1 | Liyi | Men | 1988-03-20 | 95.30 | | 2 | kevin | Men | 1987-08-23 | NULL | | 3 | marry | Women | 1989-11-25 | NULL | | 4 | lucy | Women | 1989-11-25 | NULL | | 5 | lily | Women | 1992-01-25 | NULL |+----+--------+--------+------------+-------+
INNER JOIN : Inner connection (do Cartesian product without on/using) Cartesian product result of main table and associated table, filter result set by on or using operation (Cartesian product, post filter, so performance and where), all columns of 2 tables are saved select * from Tb_student a joins Tb_test1 B on a.id=b.id;+----+--------+--------+------------+----+--------+ --------+-------+| ID | name | Gender | birthday | ID | name | Gender | Score |+----+--------+--------+------------+----+--------+--------+-------+| 1 | Liyi | Men | 1988-03-20 | 1 | Liyi | Men | 95.30 | | 2 | kevin | Men | 1987-08-23 | 2 | Two ya | Women | 95.30 | | 3 | marry | Women | 1989-11-25 | 3 | Zhang San | Women | 95.30 | | 4 | lucy | Women | 1989-11-25 | 4 | John Doe | Women | 95.30 |+----+--------+--------+------------+----+--------+--------+-------+ select * from Tb_student a JOin Tb_test1 B on A.gender=b.gender and a.name=b.name;+----+--------+--------+------------+----+--------+--------+-- -----+| ID | name | Gender | birthday | ID | name | Gender | Score |+----+--------+--------+------------+----+--------+--------+-------+| 1 | Liyi | Men | 1988-03-20 | 1 | Liyi | Men | 95.30 | | 6 | Liyi | Men | 1985-10-11 | 1 | Liyi | Men | 95.30 |+----+--------+--------+------------+----+--------+--------+-------+
LEFT Join : Left outer connection (must take on/using, equivalent to outer join) left table as the main table, keep all records in all columns, the right table is the associated table, plus other columns other than on, the record connection that conforms to the on condition The difference between on and where (there is a difference in outer joins): On and where do not differ in performance, they are filtered after the Cartesian product, but on in the from phase, the join operation precedes where. Therefore, when the left join is connected, the columns of the table that do not conform to the on condition must also be preserved, except that the right table of the record is completely null. And where to filter the results after the connection, the non-eligible direct culling select * from Tb_student a LEFT join Tb_test1 B on a.id=b.id;+----+--------+--------+-- ----------+------+--------+--------+-------+| ID | name | Gender | birthday | id | name | Gender | Score |+----+--------+--------+------------+------+--------+--------+-------+| 1 | Liyi | Men | 1988-03-20 | 1 | Liyi | Men | 95.30 | | 2 | kevin | Men | 1987-08-23 | 2 | Two ya | Women | 95.30 | | 3 | marry | Women | 1989-11-25 | 3 | Zhang San | Women | 95.30 | | 4 | lucy | Female &NBSp | 1989-11-25 | 4 | John Doe | Women | 95.30 | | 5 | lily | Women | 1992-01-25 | NULL | null | null | NULL | | 6 | Liyi | Men | 1985-10-11 | NULL | null | null | NULL |+----+--------+--------+------------+------+--------+--------+-------+ select * FROM Tb_student A to join Tb_test1 B on a.id=b.id and a.id<4;+----+--------+--------+------------+------+--------+ --------+-------+| ID | name | Gender | birthday | id | name | Gender | Score |+----+--------+--------+------------+------+--------+--------+-------+| 1 | Liyi | Men | 1988-03-20 | 1 | Liyi | Men | 95.30 | | 2 | kevin | Men | 1987-08-23 | 2 | Two ya | Women | 95.30 | |   3 | marry | Women | 1989-11-25 | 3 | Zhang San | Women | 95.30 | | 4 | lucy | Women | 1989-11-25 | NULL | null | null | NULL | | 5 | lily | Women | 1992-01-25 | NULL | null | null | NULL | | 6 | Liyi | Men | 1985-10-11 | NULL | null | null | NULL |+----+--------+--------+------------+------+--------+--------+-------+ select * FROM Tb_student a LEFT join Tb_test1 B on a.id=b.id where a.id<4;+----+--------+--------+------------+------+------- -+--------+-------+| ID | name | Gender | birthday | id | name | Gender | Score |+----+--------+--------+------------+------+--------+--------+-------+| 1 | Liyi | Men | 1988-03-20 | 1 | Liyi | Male &NBSp | 95.30 | | 2 | kevin | Men | 1987-08-23 | 2 | Two ya | Women | 95.30 | | 3 | marry | Women | 1989-11-25 | 3 | Zhang San | Women | 95.30 |+----+--------+--------+------------+------+--------+--------+-------+
Right join: The connection to the right outer connection, and to the left outer connection, is clear after the use of MySQL. If there is inaccurate place please leave a message to advise
Several connections to MySQL join/inner join/cross join/comma/left join/right join/natural join
