Mysql Multi-Table query detailed

The end of the paper is shallow

1. Preface: in the last section, the order of the keywords executed in MySQL involves only one table, and in most cases, the query statement will involve multiple tables:

1) What is the classification of multi-table connection;

2) What are the connection methods for these classifications?

3) which application scenarios are these connection methods used for respectively?

This article is about these three points through an example, with the aim of exhausting all the scenarios and all the methods, and making examples of the use of each method.

First of all, we first enumerate the connection methods used in this article:

inside link :join,inner Join

outer connection : Leftjoin,left outer join,right join,right outer join,union

cross-joins: crosses join

2. Assuming that there are two tables A and B, and that the table is a collection, the record in the table is an element in the collection.

The two tables are as follows:

Tablea:tableb:

1) Category : inner Connection (only one scene)

INNER JOIN or join (equivalent to inner join)

Select A.*, b.* from TableA ainner join TableB bon a.id = b.id

Or

Select A.*, b.* from TableA ajoin tableb bon a.id = b.id

The results are as follows:

Application Scenarios :

In this scenario, the data that satisfies a certain condition is a/b internal, and because of the internal data, the connection method is called inner connection.

2) Category : external connection (six scenarios)

A) Left join or LEFT OUTER join (equivalent to left join)

Select A.*, b.* from TableA aleft join TableB bon a.id = b.id

Or

Select A.*, b.* from TableA aleft outer join tableb bon a.id = b.id

As a result, records that are more nonexistent in TableB are populated with null:

Application Scenarios :

In this scenario, all the data of a is obtained, and the data of B which satisfies a certain condition;

B) [left JOIN or LEFT outer join (equivalent to LEFT join)] + [where b.column is null]

Select a.ID aid,a.age,b.id bid,b.name from TableA Aleft joins TableB bon a.id = B.idwhere b.id is null

The results are as follows:

Application Scenarios :

This scenario gets all the data in a minus and B satisfies the same condition of the data, and then obtains a remainder of the data;
C) Right join or fight outer join (equivalent to right join)

Select a.ID aid,a.age,b.id bid,b.name from TableA aright join TableB bon a.id = b.id

As a result, records that are more nonexistent in TableB are populated with null:

Application Scenarios:

In this scenario, all the data of B is obtained, and the data of a which satisfies a certain condition;

D) [left JOIN or LEFT outer join (equivalent to LEFT join)] + [where a.column is null]

Select a.ID aid,a.age,b.id bid,b.name from TableA aright joins TableB bon a.id = B.idwhere a.id is null

The results are as follows:

Application Scenarios:

In this scenario, all the data in B is subtracted from the data that satisfies the same condition, and then the remaining B data is obtained;
E) Full join (MySQL is not supported, but can be replaced with left join Union right join)

Select a.ID aid,a.age,b.id bid,b.name from TableA aleft join TableB bon a.id = b.idunionselect a.id aid,a.age,b.id BID,B.N Ame from TableA aright join TableB bon a.id = b.id

After the union, the duplicate records are merged (three records with ID 2,3,4), so the result is as follows:

Application Scenarios:

In this scenario, a public record that satisfies a certain condition is obtained, and a unique record

F) Full join + is null (MySQL not supported, but can be replaced with (left JOIN + was null) union (right Join+is null)

Select a.ID aid,a.age,b.id bid,b.name from TableA Aleft joins TableB bon a.id = B.idwhere b.id is nullunionselect a.id aid, A.age,b.id bid,b.name from TableA aright joins TableB bon a.id = B.idwhere a.id is null

The results are as follows:

Application Scenarios:

In this scenario, the sum of records that do not meet a certain condition in A/b is obtained.

Note: There are seven of them (2^3-1) in the application scenario , there is a full blank, that is nothing to check, seven scenarios include the actual application of all possible scenarios:

3) Category : Cross Join

a) in practical applications there is also a situation, to want a A, a record of the permutation combination, that is, Cartesian product, this is not a good use of sets and elements to represent. A cross join is required:

Select a.ID aid,a.age,b.id bid,b.name from TableA across join TableB b

is a record number *b record number, the result is as follows:

B) You can also specify a condition (where) for a cross join:

Select a.ID aid,a.age,b.id bid,b.name from TableA across join TableB bwhere a.id = b.id

The results are as follows:

Note: In this case, the effect of the inner join is actually realized

3. There are still omissions, which is the problem with MySQL's fault tolerance of SQL statements, that is, if the SQL statement does not fully conform to the writing proposal, MySQL will allow this situation, as far as possible to explain it:

1) The general cross join is followed by a where condition, but the cross Join+on is also interpreted as cross join+where;

2) The general internal connection is required to add on qualification, such as the above scene one, if not added will be interpreted as cross-linking;

3) If the connection table uses a comma, it is interpreted as a cross-connection;

NOTE: The SQL standard also has a union join and natural inner Join,mysql not supported, and it does not make much sense, the results can be obtained using the above several connection methods.

Summary: A summary of all MySQL connection methods, some of which are not noticed before the problem, usually development is nothing more than this. The next article summarizes the functions that SQL provides

Mysql Multi-Table query detailed