In the graduation design, the database query with a multi-table query, before learning a little knowledge, take this opportunity, and check some information, review, record, in order to use when the future can quickly solve the problem.
Mysql-5.7.13-winx64, Navicat, Navicat, Navicat, Navicat, Navicat, Navicat, Navicat
1.1 What are the classification of multi-table joins?
1.2 What are the connection methods for these categories?
1.3 Which application scenarios are these connection methods applied to?
These three points are illustrated by examples. The purpose is to exhaust all scenarios and all methods, and to illustrate the use of each method.
First of all, the classification (inner join, outer join, cross join) and connection method (as follows) used are listed:
A) Inner join: join, inner join
Left join, left outer join, right join, right outer join, union
C) Cross join
Ii. The following is an example for analysis
Suppose you have two tables A and B, and think of the table as A set, then the records in the table are one element of the set.
The two forms are as follows:
The Table A:
Table B:
2.1 Internal Connection (Only one Scenario)
Inner join or join (equivalent to inner join)
SELECT a.*, b.* FROM a a JOIN b ON a.id = b.idCopy the codeThe results are as follows:
A.* b.* indicates that the result display includes all the fields of table A and table B. You can also customize the fields to display (directly using aliases). A is the alias of table A, and b is the alias of table B. The alias must be declared after the first mention of the table name. After that, the alias can replace the table name.
Application scenario: Obtain the intersection of tables A and B
Condition is the constraint defined by ON. In this scenario, the internal data of A and B that meet A certain condition is obtained. Because you get internal common data, the join method is called inner join.
2.2 External Connection (Six Scenarios)
2.2.1 Left JOIN or left join
SELECT a.*, b.* FROM A a
LEFT JOIN B b
ON a.id = b.id
Copy the codeTable B is filled with Null for records that do not exist:
The display result of left Join depends on the table before left Join, and the display result of right Join depends on the table after left Join
Application Scenarios:
In this scenario, all the data of A is obtained, and the data of B satisfies A certain condition;
2.2.2 [left JOIN or left join] + [where b.column is null]
SELECT a.id aid,a.age, b.id bid, b.name FROM A a
LEFT JOIN B b
ON a.id = b.id
WHERE b.id is NULL
Copy the codeTable A and table B have the same field ID, so the name of the field to be displayed for the result is defined during the query, where the result of A. id is represented by the AID field, where the condition is filtered again, and b.id is not null
The results are as follows:
Application Scenarios:
In this scenario, all data in A is subtracted from “data that meets the same condition with B”, and then the remaining data of A is obtained.
2.2.3 Right join or fight outer join
SELECT a.id aid,a.age, b.id bid, b.name FROM A a
RIGHT JOIN B b
ON a.id = b.id
Copy the codeTable A is Null for all records that do not exist in TableB:
Application Scenarios:
In this scenario, all the data of B and the data of A that satisfies A certain condition are obtained;
2.2.4 [left JOIN or left join left OUTER join] + [where a.column is null]
SELECT a.id aid,a.age, b.id bid, b.name FROM A a
RIGHT JOIN B b
ON a.id = b.id
WHERE a.id is NULL
Copy the codeThe results are as follows:
Application Scenarios:
In this scenario, all data in B is subtracted from “data that meets the same condition with A”, and then the remaining data of B is obtained.
2.2.5 Full join (mysql does not support left join union and right join)
SELECT a.id aid,a.age, b.id bid, b.name FROM A a
LEFT JOIN B b
ON a.id = b.id
UNION
SELECT a.id aid,a.age, b.id bid, b.name FROM A a
RIGHT JOIN B b
ON a.id = b.id
Copy the codeAfter the union, the duplicate records are merged (the three records with ids 2,3,4), so the result is as follows:
Application Scenarios:
What you get in this scenario is a public record that meets a certain condition, and a unique record
2.2.6 Full join+isnull (mysql does not support, but can use (left join+isnull) union (right join+isnull)
SELECT a.id aid,a.age, b.id bid, b.name FROM A a
LEFT JOIN B b
ON a.id = b.id
WHERE b.id is NULL
UNION
SELECT a.id aid,a.age, b.id bid, b.name FROM A a
RIGHT JOIN B b
ON a.id = b.id
WHERE a.id is NULL
Copy the codeThe results are as follows:
Application Scenarios:
In this scenario, you get the sum of the records in A, B that do not meet A certain condition
Note: There are seven (2^3-1) application scenarios above, and one is blank, that is, nothing is checked. The seven scenarios include all possible scenarios of practical application
2.3 Cross Join
2.3.1 In practical application, there is also such A situation that the permutations and combinations of A and B records, namely cartesian product, are difficult to be represented by sets and elements. Cross join is needed:
SELECT a.id aid,a.age, b.id bid, b.name FROM A a
CROSS JOIN B b
Copy the codeThe results are as follows:
2.3.2 You can also specify conditions for cross join (where) :
SELECT a.id aid,a.age, b.id bid, b.name FROM A a
CROSS JOIN B b
WHERE a.id = b.id
Copy the codeThe results are as follows;
Note: In this case the effect of an inner join is actually achieved
Three Points for attention
There are still omissions in mysql’s error tolerance for SQL statements, i.e., in cases where SQL statements do not fully comply with the writing recommendation, mysql will allow this situation to be interpreted as best as possible:
3.1 Where conditions are generally added after cross join, but cross join+ ON is also interpreted as cross join+ WHERE;
3.2 Generally, internal connections need to be qualified with on, as in scenario 2.1 above; If not, it will be interpreted as cross join;
3.3 If the join table uses commas, it will be interpreted as a cross join;
Mysql does not support union join and natural inner join. The result of the join can be obtained by using the above methods