Detailed description of basic multi-Table connection queries in MySQL

This article mainly introduces MySQL's basic multi-table JOIN query tutorial, with the basic JOIN usage explanation, for more information, see the next article. This article describes the basic multi-Table connection query tutorial in MySQL and introduces the basic JOIN usage. For more information, see

I. Multi-Table connection type
1. cartesian products (cross join) can be considered as cross join in MySQL, or CROSS is omitted, or ',' such:

Because the returned result is the product of the two connected data tables, we do not recommend this function when the WHERE, ON, or USING conditions exist, because when there are too many data table projects, it will be very slow. Generally, LEFT [OUTER] JOIN or RIGHT [OUTER] JOIN is used.

2. join inner join in MySQL

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

The nner join is called an equijoin, that is, the equijoin conditions must be specified. In MySQL, CROSS and inner join are divided together. Join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition]

3. mySQL outer join is divided into left Outer Join and right join. In addition to returning results that meet the connection conditions, the left table (left join) or right table (right join) is also returned) results that do not meet the connection conditions, corresponding to the use of NULL.

Example:

User table:

id | name———1 | libk2 | zyfon3 | daodao

User_action table:

user_id | action—————1 | jump1 | kick1 | jump2 | run4 | 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 user_action has a user_id = 4, action = swim record, but it does not appear in the result,
In the user table, the id = 3 and name = daodao users do not have corresponding records in user_action, but they appear in the result set.
Because it is left join, all work is subject to left.
Result 1, 2, 3, 4 are records in both the left table and the right table. 5 is a record in only the left table, not in the right table.

Working principle:

Read one record from the left table and select all records (n records) in the right table that match on to Form n records (including duplicate rows, for example: result 1 and result 3). If there is no table matching the on condition on the right side, all connected fields are null. then read the next one.

Extended:
If there is no on matching in the right table, we can display the null rule to find all records in the left table, not in the right table. Note that the column to be judged 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, it should be "is null" instead of "= NULL ".
2. Here the. user_id column must be declared as not null.

)
Result of the preceding SQL statement:

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

General Usage:

A. LEFT [OUTER] JOIN:

In addition to returning results that meet the connection conditions, you must also display data columns that do not meet the connection conditions in the left table.

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

The difference between RIGHT and left join is that in addition to displaying results that meet the connection conditions, you also need to display data columns that do not meet the connection conditions in the RIGHT table.

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 MySQL retrieves information from a table, you can prompt which index it chooses.
This feature is useful if the EXPLAIN command shows that MySQL uses an index that may be incorrect in the index list.
By specifying the use index (key_list), you can tell MySQL to USE the most appropriate INDEX to find record rows in the table.
The optional 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;

Ii. Constraints for table join
Add the WHERE, ON, and USING display conditions.

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 two tables have the same names, 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, you need to note that when MySQL involves multi-table queries, You need to determine which connection method is more efficient Based on the query conditions.

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. Specify the connection conditions WHERE, ON, and USING.

PS: Basic JOIN usage

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.Hobby from A, B where A.id = B.id

, Which is an implicit inner join. The query result is:

Name HobbyTim FootballJimmy BasketballJimmy TennisTom Soccer

Its role and

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

Is the same. Here, the inner join can be changed to cross join.
2. outer left join

Select A.Name from A Left JOIN B ON A.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 no field record in Table B corresponding to the query result is left blank, the result is as follows:

Name HobbyTim FootballJimmy Basketball，TennisJohn 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 HobbyTim FootballJimmy BasketballJimmy TennisTom 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.idFROM t1,t2LEFT 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.idFROM 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.idFROM (t1,t2)LEFT JOIN t3 ON (t3.id=t1.id)WHERE t1.id=t2.id;

【