Basic multi-table connection query in MySQL go to script house (author: Real Home)

One, multi-table connection type
1. Cartesian Product (cross-connect) in MySQL can either be crossed join or omit cross as join, or use ', ' such as:

Because the result of its return is the product of the two data tables connected, it is generally not recommended when there is a where, on, or using condition, because it is very slow when there are too many data table items. General use left [OUTER] Join or right [OUTER] Join

2. Internal connection inner join in MySQL put I

SELECT * FROM table1 cross joins Table2  SELECT * FROM table1 join table2  

Nner join is called the equivalent join, that is, you need to specify the equivalent join conditions in MySQL cross and inner join are divided together. join_table:table_reference [INNER | Cross] JOIN table_factor [join_condition]

3. The outer joins in MySQL are divided into left and right connections, that is, in addition to returning results that meet the join conditions, return the results of the left table (left connection) or the right table (right join) that do not conform to the join condition, and correspond with null.

Example:

User table:

ID | Name ——— 1 | LIBK2 | Zyfon3 | Daodao

User_action table:

user_id | Action ————— 1 | JUMP1 | Kick1 | JUMP2 | Run4 | Swim

Sql:

Result

ID | Name  | action —————————— –1 | LIBK     | jump      ①1 | LIBK     | kick       ②1 | LIBK     | jump      ③2 | Zyfon   | run        ④3 | daodao | null       ⑤

Analysis:
Notice that there is also a user_id=4, Action=swim record in the user_action, but it did not appear in the results,
Id=3 in the user table, Name=daodao users do not have a corresponding record in the user_action, but appear in the result set
Because now is the left join, all the work is left to prevail.
Results 1,2,3,4 are both in the left table and in the right table record, 5 is only in the left table, not the right table record

Working principle:

Read one from the left table, select all the right table records (n) to match on, and Form N records (including duplicate rows, such as result 1 and result 3), if there are no tables matching the on condition on the right, the concatenated fields are null. Then continue reading the next one.

Extended:
We can use the right table without an on match to show the law of NULL, to find all the records in the left table, not the right table, note that the column used to judge must be declared as NOT null.
Such as:
Sql:

Note

1. Column value NULL should be null instead of =null
2. Here the a.user_id column must be declared as not NULL.

）
Result of SQL above:

ID | name | Action ———————— –3 | Daodao | NULL —————————————————————————— –

General usage:

A. Left [OUTER] JOIN:

In addition to returning results that meet the join criteria, you also need to display data columns in the left table that do not meet the join criteria, relative to the null corresponding

SELECT column_name from table1 left [OUTER] joins table2 on Table1.column=table2.column
B. Right [OUTER] JOIN:

Right is similar to the left join except that it shows the data columns that do not meet the join criteria in the correct table, with the corresponding null corresponding

SELECT column_name from table1 right [OUTER] joins table2 on Table1.column=table2.column
Tips:

1. On a.c1 = B.C1 equivalent to using (C1)
2. INNER JOIN and, (comma) are semantically equivalent
3. When MySQL retrieves information from a table, you can indicate which index it chooses.
This feature is useful if EXPLAIN shows that MySQL uses the wrong index in the list of possible indexes.
By specifying use index (key_list), you can tell MySQL to find a record row in the table using the most appropriate index in the possible index.
Optional two syntax IGNORE index (key_list) can be used to tell MySQL not to use a specific index. Such as:

Constraint conditions of Table connection
Add display condition where, on, USING

1. WHERE clause

2. On

Mysql>select * FROM table1 left JOIN table2 on table1.id=table2.id;  

3. Using clause, if the two columns of the connected two table join condition have the same name, you can use a using

For example:

SELECT from-left JOIN USING ()

Example of a case where more than two tables are connected:

Mysql>select artists. Artist, Cds.title, genres.genre from the   CDs left   joins genres N Cds.genreid = Genres.genreid left   join artists on Cds.artistid = Artists.artistid;  

Or

Mysql>select artists. Artist, Cds.title, genres.genre from the   CDs left   joins genres on Cds.genreid = Genres.genreid left    join artists-& Gt On cds.artistid = Artists.artistid   WHERE (genres.genre = ' Pop ');  --------------------------------------------

In addition to the need to pay attention to the place in MySQL when the multi-table query, you need to use the query according to the situation, think of which kind of connection is more efficient.

1. Cross-connect (cartesian product) or internal connection [INNER | Cross] JOIN

2. Left outer connection [OUTER] Join or right outer join [OUTER] Join note Specify connection condition where, on,using.

PS: Basic Join usage

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

, this is an implicit inner join, and the result of the query is:

Name Hobbytim footballjimmy basketballjimmy Tennistom Soccer

It's role and

Select a.name from A INNER JOIN B on a.id = b.ID

is 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 outer left junction, so that the result of the query would be to keep all records of the junction fields in the a table, and leave the field records in the B table to be empty if none, the result is as follows:

Name Hobbytim footballjimmy Basketball,tennisjohn 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 right JOIN B on a.id = b.ID

, the result will be:

Name Hobbytim footballjimmy basketballjimmy Tennistom 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:

, 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.idfrom t1,t2left 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.idfrom T1, (T2 left joins 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.idfrom (t1,t2) left JOIN T3 on (t3.id=t1.id) WHERE t1.id=t2.id;

Basic multi-table connection query in MySQL to the home of the script (real destination)