MySQL multi-table joint query instructions

1. Multi-table connection type
1. Cartesian product (cross connection) in MySQL can be CROSS JOIN or omit CROSS, which is JOIN, or use ',' such as:

Because it returns The result is the product of the two connected data tables, so it is generally not recommended to use it when there are WHERE, ON or USING conditions, because when there are too many data table items, it will be very slow. Generally use LEFT [OUTER] JOIN or RIGHT [OUTER] JOIN

2. INNER JOIN INNER JOIN is called an equijoin in MySQL, that is, you need to specify the equijoin conditions. CROSS and INNER JOIN in MySQL divided together. join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition]

SELECT * FROM table1 CROSS JOIN table2 
SELECT * FROM table1 JOIN table2 
SELECT * FROM table1,table2

3. Outer joins in MySQL are divided into left outer joins and right joins, that is, in addition to returning results that meet the join conditions , and also returns results that do not meet the join conditions in the left table (left join) or right table (right join), and NULL is used accordingly.

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:
Notice that there is also a record of user_id=4, action=swim in user_action, but it does not appear in the result,
And in the user table The user with id=3 and name=daodao does not have a corresponding record in user_action, but it appears in the result set
Because it is a left join now, all work is based on left.
Results 1, 2, 3 and 4 are both records in the left table and the right table. 5 is a record only in the left table but not in the right table.

Working principle:

From Read one record from the left table, select all the right table records (n) that match on and connect them to form n records (including repeated rows, such as result 1 and result 3). If there is no record on the right that matches the on condition table, the connected fields are all null. Then continue reading the next item.

Extension:
We can use the rule of displaying null if there is no on match in the right table to find all the records that are in the left table but not in the right table. Note that the column used for judgment must be declared as not null.
For example:
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

(Note:

1. If the column value is null, you should use is null instead of =NULL
2. Here the a.user_id column must be declared as NOT NULL.

)

The result of the above sql:

id | name | action
————————–
3 | daodao | NULL
 
——————————————————————————–

General usage:

a. LEFT [OUTER] JOIN:

In addition to returning results that meet the join conditions, you also need to display the data columns in the left table that do not meet the join conditions. Use NULL accordingly

SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column=table2.column
b. RIGHT [OUTER] JOIN:

RIGHT is similar to LEFT JOIN except that the display matches In addition to the results of the connection conditions, it is also necessary to display the data columns in the right table that do not meet the connection conditions. Use NULL accordingly

SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column=table2.column
Tips:

1. on a.c1 = b.c1 is equivalent to using(c1)
2. INNER JOIN and, (comma) are semantically equivalent
3. When You can prompt MySQL which index to choose when retrieving information from a table.
This feature is useful if EXPLAIN shows that MySQL is using the wrong index from the list of possible indexes.
By specifying USE INDEX (key_list), you can tell MySQL to use the most appropriate index among the possible indexes to find rows in the table.
The optional second-choice syntax IGNORE INDEX (key_list) can be used to tell MySQL not to use a specific index. For example:

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;

2. Constraints of table connection
Add display conditions 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 the join condition of the two tables have the same name, you can use USING

For example:

SELECT FROM LEFT JOIN USING ()

Examples of connecting 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 ON cds.genreID = genres.genreID 
  
 LEFT JOIN artists -> ON cds.artistID = artists.artistID
  
 WHERE (genres.genre = 'Pop');

In addition, when it comes to multi-table queries in MySQL, you need to Depending on the query situation, decide which connection method is more efficient.

1. Cross join (Cartesian product) or inner join [INNER | CROSS] JOIN

2. Left outer join LEFT [OUTER] JOIN or right outer join RIGHT [OUTER] JOIN Note Specify the connection conditions WHERE, ON, USING.

PS: Basic JOIN usage

First we assume that there are two tables A and B. 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 join:

Select A.Name, B.Hobby from A, B where A.id = B.id

This is an implicit inner join. The query result is:

Name Hobby
Tim Football
Jimmy Basketball
Jimmy Tennis
Tom Soccer

Its function is the same as

Select A.Name from A INNER JOIN B ON A.id = B.id

是一样的。这里的INNER JOIN换成CROSS JOIN也是可以的。

2． 外左联结

Select A.Name from A Left JOIN B ON A.id = B.id

典型的外左联结，这样查询得到的结果将会是保留所有A表中联结字段的记录，若无与其相对应的B表中的字段记录则留空，结果如下：

Name Hobby
Tim Football
Jimmy Basketball，Tennis
John
Tom Soccer

所以从上面结果看出，因为A表中的John记录的ID没有在B表中有对应ID，因此为空，但Name栏仍有John记录。
3． 外右联结
如果把上面查询改成外右联结：

Select A.Name from A Right JOIN B ON A.id = B.id

则结果将会是：

Name Hobby
Tim Football
Jimmy Basketball
Jimmy Tennis
Tom Soccer

这样的结果都是我们可以从外左联结的结果中猜到的了。

说到这里大家是否对联结查询了解多了？这个原本看来高深的概念一下子就理解了，恍然大悟了吧（呵呵，开玩笑了）？最后给大家讲讲MySQL联结查询中的某些参数的作用：
1．USING (column_list)：其作用是为了方便书写联结的多对应关系，大部分情况下USING语句可以用ON语句来代替，如下面例子：

a LEFT JOIN b USING (c1,c2,c3)

其作用相当于下面语句

a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3

只是用ON来代替会书写比较麻烦而已。

2．NATURAL [LEFT] JOIN：这个句子的作用相当于INNER JOIN，或者是在USING子句中包含了联结的表中所有字段的Left JOIN（左联结）。

3．STRAIGHT_JOIN：由于默认情况下MySQL在进行表的联结的时候会先读入左表，当使用了这个参数后MySQL将会先读入右表，这是个MySQL的内置优化参数，大家应该在特定情况下使用，譬如已经确认右表中的记录数量少，在筛选后能大大提高查询速度。

最后要说的就是，在MySQL5.0以后，运算顺序得到了重视，所以对多表的联结查询可能会错误以子联结查询的方式进行。譬如你需要进行多表联结，因此你输入了下面的联结查询：

SELECT t1.id,t2.id,t3.id
FROM t1,t2
LEFT JOIN t3 ON (t3.id=t1.id)
WHERE t1.id=t2.id;

但是MySQL并不是这样执行的，其后台的真正执行方式是下面的语句：

SELECT t1.id,t2.id,t3.id
FROM t1,( t2 LEFT JOIN t3 ON (t3.id=t1.id) )
WHERE t1.id=t2.id;

这并不是我们想要的效果，所以我们需要这样输入：

SELECT t1.id,t2.id,t3.id
FROM (t1,t2)
LEFT JOIN t3 ON (t3.id=t1.id)
WHERE t1.id=t2.id;

MySQL联合查询效率较高，以下例子来说明联合查询（内联、左联、右联、全联）的好处：

T1表结构（用户名,密码）   
userid（int）   usernamevarchar（20）   passwordvarchar（20）   
1   jack  jackpwd   
2   owen  owenpwd   


T2表结构（用户名,密码）   
userid（int）   jifenvarchar（20）   dengjivarchar（20）   
    1   20   3   
    3   50   6   


第一：内联（inner join）
如果想把用户信息、积分、等级都列出来，那么一般会这样写：

select * from T1, T3 where T1.userid = T3.userid
（其实这样的结果等同于select * from T1 inner join T3 on T1.userid=T3.userid ）。

把两个表中都存在userid的行拼成一行（即内联），但后者的效率会比前者高很多，建议用后者（内联）的写法。

SQL语句：
select * from T1 inner join T2 on T1.userid = T2.userid

运行结果   
T1.userid   username   password   T2.userid   jifen   dengji   
1   jack   jackpwd   1   20   3   

第二：左联（left outer join）
显示左表T1中的所有行，并把右表T2中符合条件加到左表T1中；
右表T2中不符合条件，就不用加入结果表中，并且NULL表示。

SQL语句：
select * from T1 left outer join T2 on T1.userid = T2.userid

运行结果   
T1.userid   username   password   T2.userid   jifen   dengji   
1   jack   jackpwd   1   20   3   
2   owen   owenpwd   NULL   NULL   NULL   

第三：右联（right outer join）。

Display all the rows in the right table T2, and add the matching conditions in the left table T1 to the right table T2;
If the conditions in the left table T1 are not met, there is no need to add them to the result table, and NULL is indicated.

SQL statement:
select * from T1 right outer join T2 on T1.userid = T2.userid

Running result
T1.userid username password T2.userid jifen dengji
1 jack jackpwd 1 20 3
NULL NULL NULL 3 50 6

Fourth: full outer join

Display all the rows in the left table T1 and the right table T2, that is, combine the left join result table + the right join result table together, and then filter out the duplicates.

SQL statement:
select * from T1 full outer join T2 on T1.userid = T2.userid

Running results
T1.userid username password T2.userid jifen dengji
1 jack jackpwd 1 20 3
2 owen owenpwd NULL NULL NULL
NULL NULL NULL 3 50 6

In conclusion, regarding joint queries, the efficiency is indeed relatively high. 4 If this combination method can be used flexibly, basically complex statement structures will become simpler.