How to convert outer connection to inner connection in mysql
Editor to share with you how to achieve external connection to internal connection in mysql, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!
In general, the outer join is as follows: a right join b on a.id=b.id, then b must be the driver table, because only b as the driver table can get the complete result set. If an is the driver, then the returned result set
It may be incomplete, but in special cases, the outer connection may be converted to the inner connection.
Consider the following situation
B
Id name
1 g1
1 g2
2 g3
2 g4
A
Id name
2 gname2
Use the following statement:
Select b.id,a.id from
A right join b on a.id=b.id
Where a.id=2
Forget about where a.id=1.
The value returned by the external connection should be
B.id a.id
1 null
1 null
2 2
2 2
Now consider a.id = 2
So if you filter like this, the set of results is as follows:
B.id a.id
2 2
2 2
This is the result set of the inner join, that is, as long as the a.id is defined to a certain fixed non-empty value, the inner join structure set can
If the predicate condition is fully filtered to get all the intermediate result sets of the results, then the database will be transformed. It's the same with MYSQL ORACLE.
Mysql:
Mysql > explain select b.id from a right join b on a.id=b.id where a.id = 2
+- -+
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+- -+
| | 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| | 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 4 | Using where; Using join buffer (Block Nested Loop) |
+- -+
2 rows in set (0.00 sec)
An is used as the driver table
Oracle:
SQL > select b.id from a right join b on a.id=b.id where a.id=2
Execution Plan
Plan hash value: 652036164
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
| | 0 | SELECT STATEMENT | | 1 | 26 | 5 (20) | 00:00:01 |
| | * 1 | HASH JOIN | | 1 | 26 | 5 (20) | 00:00:01 |
| | * 2 | TABLE ACCESS FULL | A | 1 | 13 | 2 (0) | 00:00:01 |
| | * 3 | TABLE ACCESS FULL | B | 2 | 26 | 2 (0) | 00:00:01 |
You can see that the HASH JOIN OUTER that should have appeared did not appear.
But if you change the condition where a. IDC. 2; to where a.id is null;, then the conversion is obviously unreasonable, because
The result set connected within can no longer meet the requirements of a.id is null;.
Why do you want to convert in this way? we know that when making a connection, both NEST LOOP and HASN JOIN should put small tables
As a driver table, the efficiency is generally higher, then the outer join is limited to the dead order, and the large table may be used as the driver table, so the efficiency
Obviously lower, if you do the conversion of the inner connection, then the order of choice will be more, and the efficiency may be improved, of course.
This has a lot to do with the accuracy of the statistics.
The above is all the contents of the article "how to convert an external connection into an inner connection in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!
