Mysql tips-mysql (join) combined query instance tutorial _ web developer _ MySQL

Mysql tips-mysql (join) combined query instance tutorial _ web development network bitsCN.com

Today, when I encountered mysql multi-table queries, I had no knowledge to start with. I forgot some knowledge for a long time. so I went online to find mysql joint query related information and then summarized the following content. I hope you can perform the following operations on the web development network. php learning is helpful in MySQL J.

Today, I encountered a problem with mysql multi-table queries. I had some knowledge for a long time. I forgot it. so I went online to find mysql joint query related information and then summarized the following content:

We hope that the web development network will help you learn php.

Let's review the Join syntax before talking about the Join syntax of MySQL.

The Join connections planned in the MYSQL standard are 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:

Select A. name B. name From A Left Join B On A. id = B. id

The result is the same as that of Select A. name B. name From B Right Join A onB. id = A. id.

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 ).

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

====== Www.iiwnet.com ==========

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

====== Www.iiwnet.com ==========

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 INNERJOIN, or contains the 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 t3ON (t3.id = t1.id)

WHEREt1.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

FROMt1, (t2 left join t3 ON (t3.id = t1.id ))

WHEREt1.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 t3ON (t3.id = t1.id)

WHEREt1.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 ).

BitsCN.com