Basic multi-table connection query tutorial in Mysql _mysql

One, multiple table connection type
1. Cartesian product (cross join) in MySQL can be a cross join or omit cross that join, or use ', ' such as:

Because the result it returns is the product of the two data tables that are connected, it is generally not recommended when there is a where, on, or using conditions, because it is very slow when there are too many datasheet items. Generally use left [OUTER] Join or right [OUTER] Join

2. INNER join inner join in MySQL put I

SELECT * FROM table1 CROSS join table2  
SELECT * to table1 join table2  
SELECT * from Table1,table2 

The Nner join is called an equivalent connection, which requires that the equivalent join condition be specified in MySQL cross and inner join are divided together. join_table:table_reference [INNER | CROSS] JOIN table_factor [join_condition]

3. The external connection in MySQL is divided into left outer and right connections, that is, in addition to returning the results of the join conditions, return to the left table (left) or the right table (right connection) does not conform to the conditions of the connection, the corresponding use of NULL correspondence.

Example:

User table:

ID | Name
———
1 | libk
2 | Zyfon
3 | daodao

User_action table:

user_id | Action
—————
1 | Jump
1 | Kick
1 | Jump
2 | Run
4 | swim

Sql:

Select ID, name, action from user as U left 
join user_action A on u.id = a.user_id 

Result

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

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

Working principle:

Read one from the left table and select all the right table records (n bars) to match on. Forms N Records (including duplicate rows, such as: result 1 and result 3), and if the right side does not have a table matching the on condition, the connected field is null. Then continue reading the next one.

Extended:
We can use the right table without 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 to be judged must be declared not NULL.
Such as:
Sql:

Select ID, name, action from user as U left 
join user_action A on u.id = a.user_id 
where a.user_id is NULL 

Attention

1. A column value of NULL should be null and cannot be used with =null
2. Here the a.user_id column must be declared to not NULL.

）
Result of SQL above:

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

—————————————————————————— –

General usage:

A. Left [OUTER] JOIN:

In addition to returning results that match the join condition, you need to display data columns in the left table that do not meet the join criteria, and should use a null corresponding

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

Right is different from the left join simply by displaying data columns that do not conform to the join condition in the right-hand table in addition to the results that match the join condition, using the null corresponding

SELECT column_name from table1 right [OUTER] JOIN 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 a table using the most appropriate index in a possible index.
An optional two-choice syntax IGNORE index (key_list) can be used to tell MySQL not to use a specific index. Such as:

Mysql> SELECT * FROM table1 use INDEX (key1,key2) 
-> WHERE key1=1 and key2=2 and key3=3; 
Mysql> SELECT * FROM table1 IGNORE INDEX (key3) 
-> WHERE key1=1 and key2=2 and key3=3; 

Constraint conditions for table joins
add display criteria where, ON, USING

1. WHERE clause

Mysql>

SELECT * from Table1,table2 WHERE table1.id=table2.id; 

2. On

Mysql>

SELECT * FROM table1 left JOIN table2 on table1.id=table2.id; 
 
SELECT * FROM table1 LEFT join table2 on table1.id=table2.id left 
join Table3 on table2.id=table3.id; 

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

For example:

SELECT from left JOIN USING ()

Example of a connection with more than two tables:

mysql>

SELECT Artists.  Artist, Cds.title, genres.genre from  
 
CDs left  
 
join genres N Cds.genreid = Genres.genreid Left  
 
Join artists on cds.artistid = Artists.artistid;  

Or

mysql>

SELECT Artists. Artist, Cds.title, genres.genre from  
 
CDs left  
 
join genres in Cds.genreid = Genres.genreid left  
 
 join artists-& Gt On cds.artistid = Artists.artistid 
 
 WHERE (genres.genre = ' Pop ');  
--------------------------------------------

Also need to pay attention to the place in MySQL involved in multiple table query, need to query according to the situation, want to use which connection way more efficient.

1. Cross-linking (cartesian product) or inner join [INNER | CROSS] JOIN

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

PS: Basic Join usage

First we assume that there are 2 tables A and B, and their table structure 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 JOIN:

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 Hobby
Tim Football
Jimmy Basketball
Jimmy Tennis
Tom Soccer

Its 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 coupling

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

, a typical outer-left join, so that the result of the query would be to keep records of all the join fields in table A, and leave blank if there is no field record in the corresponding B table, the result is as follows:

Name Hobby
Tim Football
Jimmy basketball,tennis
John 
Tom Soccer

So it turns out that 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 bar still has John Records.
3. Outer Right Connection
If you change the above query to the outer right join:

Select A.name from right 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 result of the outer left join.
Speaking of this, do you know more about the connection query? This originally seems to be a profound concept of a sudden understanding, it dawned on the bar (oh, joking)? Finally, we'll talk about the role of some of the parameters in the MySQL join query:
1. Using (column_list): Its purpose is to facilitate the multiple correspondence of writing joins, in most cases the using statement can be substituted with an on statement, as in 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 more cumbersome.

2. NATURAL [Left] Join: This sentence acts as a inner join, or as a left-hand join with all fields in a table containing joins in a using clause.

3. Straight_join: By default, MySQL reads the left table first when it joins the table. When you use this parameter MySQL will read the right table first, this is a MySQL built-in optimization parameters, you should be used in certain circumstances, such as the right table has been confirmed that a small number of records, After filtering, the query speed can be greatly improved.

The last thing to say is that after the MySQL5.0, the order of operations has been paid attention to, so the query for multiple tables may be incorrectly linked to the subquery. For example, you need to perform multiple table joins, so 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;

But MySQL does not do this, and the real way the backend is executed 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;

That's not the effect we want, so we need to type this:

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