MySQL left Join,right Join statement detailed

The (join) connections that are planned in the SQL standard are broadly grouped into the following four categories:

1. INNER JOIN: A join of a recordset that conforms to those records in which the join relationship exists in the two tables.

2. Outer coupling: It is divided into outer left coupling and outer right connection.

The left-bound table A and B means the connection of the records formed by the records of all the records in table A and the joined fields in table B to those recorded by the join of table A, and note that the final set of records includes all records of Table A.

The result of the right connection A and B table is the same as the result of the left coupling B and a, which means:

The code is as follows	Copy Code
Select A.name B.name from the left of Join B on A.id=b.id and select A.name b.name from B right Join A on b.id=a.id

The results after execution are the same.

3. Full join: Takes out all records of a joined field in two tables to form a recordset's join (this does not require memory, as long as the fields of the table mentioned in the query are taken out, regardless of whether the join conditions are met, and therefore not significant).

4. No connection: No need to explain, that is, there is no use of the connection function Bai, there are also the theory of self-connection.

Here I have a simpler method of memorizing, the difference between internal and external connections is that the inner joins will remove all records that are not eligible, and the outer joins retain the parts. The difference between the outer left and the outer right is that if all the records in a LEFT join B are retained in the result, then only the records in B that match the join condition, and the right join is the opposite, so it will not be confused. In fact, we recall that in the book "Introduction to Database Systems" published by Higher Education publishers, it is not difficult to understand the content of the Relationship algebra chapter (that is, the chapter of Cartesian product and projection).

MySQL supports the join syntax for select and some update and delete cases, with specific grammatical details:

The code is as follows

Copy Code

Table_references: Table_reference [, Table_reference] ... Table_reference: Table_factor | Join_table Table_factor: Tbl_name [[as] alias] [{use| ignore| FORCE} INDEX (Key_list)] | (table_references) | {OJ table_reference left OUTER JOIN table_reference On conditional_expr} Join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition] | Table_reference Straight_join Table_factor | Table_reference straight_join Table_factor on condition | Table_reference left [OUTER] JOIN table_reference join_condition | Table_reference NATURAL [Left [OUTER]] JOIN table_factor | Table_reference right [OUTER] JOIN table_reference join_condition | Table_reference NATURAL [Right [OUTER]] JOIN table_factor Join_condition: On conditional_expr | USING (column_list)

The use of the above excerpt from authoritative information, but did you see a little dizzy? Oh, should be the main problem is table_reference, what is Table_factor? The table_reference here is actually a reference to the table, because in the case of MySQL, a join is a reference to a table, so the table that needs to be joined is defined as Table_reference, as is the case in SQL standard. And Table_factor is the function of MySQL on this reference enhancements and extensions, so that the referenced table can be a series of tables in parentheses, as in the following example, join after parentheses:

The code is as follows	Copy Code
SELECT * from T1 left JOIN (T2, T3, T4) on (t2.a=t1.a and t3.b=t1.b and t4.c=t1.c)

The execution result of this statement is the same as the following statement:

The code is as follows	Copy Code
SELECT * from T1 LEFT join (T2 CROSS join T3 CROSS join T4) On (t2.a=t1.a and t3.b=t1.b and t4.c=t1.c)

These two examples not only let us understand the meaning of table_factor and table_reference in MySQL, but also understand the use of cross join, I would like to add the role of cross join in the existing version of MySQL and inner Joins are the same (although they are not the same in SQL standard, however, their difference in MySQL is simply that the inner join needs to append an on parameter, and the cross join is not required).