Instance description MySQL joint Query

Instance description MySQL joint Query

Now, I have finally pasted the MySQL joint query content. I have added two articles in the previous article, which I have reproduced. The example explains MySQL joint query. The following describes the usage of simple join. First, assume there are two tables A and B. Their table structure and fields are:

Table:

ID	Name
1	Tim
2	Jimmy
3	John
4	Tom

Table B:

ID	Holobby
1	Football
2	Basketball
2	Tennis
4	Soccer

1. Inner join:
Select a. Name, B. Hober from a, B where a. ID = B. ID, which is an implicit inner join. The query result is:

 

Name	Holobby
Tim	Football
Jimmy	Basketball
Jimmy	Tennis
Tom	Soccer

It works the same as select a. name from a inner join B on A. ID = B. Id. Here, the inner join can be changed to cross join.

2. outer left join
Select. name from a left join B on. id = B. ID, a typical outer left join. In this way, the query result will be record of all join fields in Table A. If there is no corresponding field record in Table B, leave it blank, the result is as follows:

Name	Holobby
Tim	Football
Jimmy	Basketball, tennis
John
Tom	Soccer

Therefore, we can see from the above results that the John Record ID in Table A does not have a corresponding ID in Table B, so it is empty, but the name column still has a John record.
3. Outer right join
If you change the preceding query to outer right join: select a. name from a right join B on A. ID = B. ID, the result will be:

Name	Holobby
Tim	Football
Jimmy	Basketball
Jimmy	Tennis
Tom	Soccer

This result can be guessed from the outer left join result.
Speaking of this, do you know more about join queries? It seems that this profound concept was suddenly understood and suddenly realized (haha, joke )? Finally, let's talk about the functions of some MySQL connection query parameters:

1. Using (column_list): it is used to conveniently write multiple Mappings of the join. In most cases, the using statement can be replaced by the on statement, as shown in the following example:

A left join B using (C1, C2, C3), which is equivalent to the following statement
A left join B on A. C1 = B. C1 and A. C2 = B. C2 and A. C3 = B. C3

It is only troublesome to use on instead of writing.

2. Natural [left] Join: This sentence serves as inner join, or contains left join (left join) of all fields in the joined table in the using clause ).

3. straight_join: by default, MySQL first reads the left table during table join. When this parameter is used, MySQL reads the right table first, this is a built-in Optimization Parameter of MySQL. You should use it in certain circumstances. For example, if you have confirmed that the number of records in the right table is small, the query speed can be greatly improved after filtering.

The final point is that after mysql5.0, the operation order has been paid attention to. Therefore, join queries for multiple tables may fail to be performed in subjoin queries. For example, if you need to join multiple tables, you enter the following join query:

Select t1.id, t2.id, t3.id
From T1, T2
Left join T3 on (t3.id = t1.id)
Where t1.id = t2.id;

However, MySQL does not execute this way. The real execution method in 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 what we want, so we need to input the following:

Select t1.id, t2.id, t3.id
From (T1, T2)
Left join T3 on (t3.id = t1.id)
Where t1.id = t2.id;

Brackets are very important here, so we should not forget to write more parentheses when writing such queries in the future, at least this can avoid many errors (because such errors are hard to be discovered by developers ). If you have any questions about the above content, you can send a letter to query: Chen Pengyi chenpengyi # gmail.com. For more information, see the source and author.