MySQL Federated query

Let's talk about the use of simple joins in detail below. First we assume that there are 2 tables A and B, and their table structures and fields are:

Table A:

Id	Name
1	Tim
2	Jimmy
3	John
4	Tom

Table B:

Id	Hobby
1	Football
2	Basketball
2	Tennis
4	Soccer

1. Inner coupling:
Select A.name, b.hobby from A, B where a.id = b.ID, which is an implicit inner join, the result of the query is:

Name	Hobby
Tim	Football
Jimmy	Basketball
Jimmy	Tennis
Tom	Soccer

Its function and Select a.name from A INNER JOIN B on a.id = b.ID are the same. It is also possible to replace the inner join with a cross join.

2. Outer left Junction
Select A.name from A left JOIN B on a.id = b.ID, a typical out-of-port junction, so that the result of the query would be to keep records of the junction fields in all A tables, and if none of the field records in the B table correspond to them, the result is as follows:

Name	Hobby
Tim	Football
Jimmy	Basketball,tennis
John
Tom	Soccer

So from the results above, because the ID of the John Record in table A does not have a corresponding ID in table B, it is empty, but the name column still has a John record.
3. Outer right Junction
If you change the above query to the outside right junction: Select A.name from A, and then JOIN B on a.id = b.id, the result will be:

Name	Hobby
Tim	Football
Jimmy	Basketball
Jimmy	Tennis
Tom	Soccer

The result is that we can guess from the results of the outer left coupling.
Speaking of which, do you know more about coupling queries? This originally seems to be advanced concept suddenly understood, suddenly dawned (hehe, joking)? Finally, let's talk about the role of some parameters in MySQL junction query:

1. Using (column_list): The function is to facilitate the writing of the multiple correspondence of the connection, in most cases the using statement can be replaced with an on statement, such as the following example:

A left JOIN b USING (C1,C2,C3), which acts as the following statement
A left JOIN B on A.c1=b.c1 and A.C2=B.C2 and a.c3=b.c3

Just using on instead of writing is a bit of a hassle.

2. NATURAL [Left] Join: This sentence is equivalent to the inner join, or the right-hand join of all fields in the table containing the junction in the Using clause.

3. Straight_join: Since MySQL will be read into the left table when the table is connected by default, when this parameter is used, MySQL will read into the right table first, this is a MySQL built-in optimization parameters, you should use in certain situations, such as the number of records in the right table is confirmed to be small, The query speed can be greatly improved after filtering.

The last thing to say is that, after MySQL5.0, the order of operations is taken seriously, so the join query for multiple tables may be wrong in the way of sub-join queries. For example, you need to make a multi-table connection, so you enter the following junction query:

SELECT t1.id,t2.id,t3.id
From T1,t2
Left JOIN T3 on (t3.id=t1.id)
WHERE t1.id=t2.id;

But MySQL does not do this, the actual execution of the background is the following statement:

SELECT t1.id,t2.id,t3.id
from T1, (T2 left JOIN T3 on (t3.id=t1.id))
WHERE t1.id=t2.id;

This is not the effect we want, so we need to enter this:

SELECT t1.id,t2.id,t3.id
From (T1,T2)
Left JOIN T3 on (t3.id=t1.id)
WHERE t1.id=t2.id;

The parentheses here are quite important, so we don't forget to write a few more parentheses in the future when writing such queries, at least to avoid a lot of errors.

Copyright notice: I feel like I'm doing a good job. I hope you can move your mouse and keyboard for me to order a praise or give me a comment, under the Grateful!_____________________________________________________ __ Welcome reprint, in the hope that you reprint at the same time, add the original address, thank you with

MySQL Federated query