An advanced tutorial on MySQL left join table JOIN,

An advanced tutorial on MySQL left join table JOIN,

Left join primary table

The primary table here refers to the table in which MySQL queries in the connection query. For example, in the left join query, the LEFT table is generally the primary table, but this is just an experience. In many cases, the experience is unreliable. To illustrate the problem, let's give an example first, create two tables for Demonstration: categories and posts:

CREATE TABLE IF NOT EXISTS `categories` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(15) NOT NULL,`created` datetime NOT NULL,PRIMARY KEY (`id`));CREATE TABLE IF NOT EXISTS `posts` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`category_id` int(10) unsigned NOT NULL,`title` varchar(100) NOT NULL,`content` varchar(200) NOT NULL,`created` datetime NOT NULL,PRIMARY KEY (`id`),KEY `category_id` (`category_id`));

Pay attention to the index situation of each table and use it later. Remember to insert a little test data without too much, but you have to have more than two rows, and then execute the following SQL:

Explain select * FROM postsLEFT JOIN categories ON posts. category_id = categories. idWHERE categories. id = 'an existing id' order by posts. created DESC

table   key     Extracategories PRIMARY   Using filesortposts   category_id Using where

In the explain results, the table in the first row is the primary table. Therefore, in this query, categories is the primary table. In our experience, in the left join query, the left table (posts Table) should be the primary table, which produces a fundamental contradiction. The reason why MySQL handles this problem is that in our WHERE section, the query conditions are filtered Based on the fields in the categories table. However, the categories table has an appropriate index. Therefore, using the categories table as the primary table during query is more conducive to narrowing the result set.

Why is the Using filesort in the explain result? This is because the main table is a categories table, the slave table is a posts table, and we use the field from the table to go to order by. This is usually not a good choice. It is best to change it to the main table field, if the fields in the master table cannot be changed due to the limited requirements, you can try to add the following indexes:

ALTER TABLE `posts` ADD INDEX ( `category_id` , `created` );

When you run the SQL statement again, there will be no Using filesort. This is because when the main table categories connects from the table posts through category_id, you can directly obtain the sorted posts result through the index.

Subjective, once the primary table is wrong, how to adjust the index will not be able to get an efficient SQL statement, so when writing SQL statements, for example, when writing a LEFT JOIN query, if you want the LEFT table to be a primary table, therefore, make sure that the query conditions in the WHERE statement use as many left table fields as possible. Once the primary table is determined, it is best to use only the primary table field to order.

Left join query efficiency analysis
User table:

Id | name --------- 1 | libk2 | zyfon3 | daodaouser_action table: user_id | action --------------- 1 | jump1 | kick1 | jump2 | run4 | swim

SQL:

select id, name, action from user as uleft join user_action a on u.id = a.user_idresult: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.

Conclusion:
We can imagine that left join works like this.
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:

select id, name, action from user as uleft join user_action a on u.id = a.user_idwhere 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:id | name | action--------------------------3 | daodao | NULL--------------------------------------------------------------------------------

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.
4. Some examples:

mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id;mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id);mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id-> LEFT JOIN table3 ON table2.id=table3.id;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;

Articles you may be interested in:

• Mysql left connection, right connection, and internal connection
• Simple optimization tutorial for table connection query in MySQL
• MySQL basic multi-Table connection query tutorial
• MySQL table examples of left join and RIGHT JOIN