MySQL Left Join and Right Join statements

I have collected various query statements about MySQL Left Join and Right Join. For more information, see explain.

In the SQL standard, the planned Join is roughly divided into the following four types:

1. Inner join: the records that match the joined fields in the two tables form a join of the record set.

2. Outer Join: outer left join and outer right join.

Table A and table B on the left join means to link all the records in Table A and the joined fields in Table B with those records in Table A that meet the join conditions., note that the final record Assembly includes all records of Table.

The result of right join A and B is the same as that of left join B and A, that is:

The Code is as follows:	Copy code
Select A. name B. name From A Left Join B On A. id = B. id And Select A. name B. name From B Right Join A on B. id = A. id

The execution result is the same.

3. full join: retrieve all records of fields with a join relationship between the two tables to form a join of the record set (this does not need to be remembered, as long as the fields in the table mentioned in the query are retrieved, whether or not the join conditions are met, it is of little significance ).

4. No join: You don't have to explain it, that is, you don't have to use the join function. You also have a self join statement.

Here I have a simple memory method. The difference between the inner and the inner join is that the inner join removes all non-conforming records, while the outer join retains some of them. The difference between outer left join and outer right join is that if A joins B on the left, all records in A are retained in the result. In this case, only records meeting the join condition in B are retained, on the contrary, the right join will not be confused. In fact, we recall the contents of the chapter on relational algebra (that is, the chapter "Introduction to database systems" published by the Higher Education Press). I believe it is not difficult to understand the connotation of these functions.

MySQL supports the Join syntax in Select and some Update and Delete conditions. The syntax details include:

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 above usage is taken from authoritative materials, but do you think it is a bit dizzy? What is table_reference and table_factor? Table_reference actually refers to Table reference, because in MySQL, join is a reference to a table. Therefore, the table to be joined is defined as table_reference, this is also true in SQL Standard. Table_factor is the enhancement and expansion of MySQL's reference function, so that the referenced table can be a series of tables in parentheses, as follows:

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 that of 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 show us the meanings of table_factor and table_reference in MySQL, but also understand the usage of cross join, what I want to add is that in the current MySQL version, cross join works the same as inner join (although it is different in SQL Standard, however, in MySQL, the difference is that inner join requires an ON parameter, but cross join does not ).