How to rewrite MySQL not in nested query to external join mode
In this issue, the editor will bring you about how to rewrite MySQL not in nested queries into external joins. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.
In MySQL, not in nested queries create a temporary table in the database, resulting in inefficient execution, which can be changed to external join processing, which is much more efficient.
Not in mode
Mysql > select * from dept where deptno not in (select deptno from emp)
+-+
| | deptno | dname | loc | |
+-+
| | 40 | OPERATIONS | BOSTON |
| | 50 | Research | BeiJing |
+-+
2 rows in set (0.00 sec)
Mysql > explain select * from dept where deptno not in (select deptno from emp)
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | PRIMARY | dept | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
| | 2 | SUBQUERY | emp | ALL | NULL | NULL | NULL | NULL | 14 | NULL |
+-- +
2 rows in set (0.00 sec)
External connection mode
Mysql > select * from dept e left join emp d on e.deptno=d.deptno where d.deptno is null
+-+
| | deptno | dname | loc | empno | ename | job | mgr | hiredate | sal | com | deptno |
+-+
| | 40 | OPERATIONS | BOSTON | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| | 50 | Research | BeiJing | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+-+
2 rows in set (0.00 sec)
Mysql > explain select * from dept e left join emp d on e.deptno=d.deptno where d.deptno is null
+- -+
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+- -+
| | 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 5 | NULL |
| | 1 | SIMPLE | d | ALL | NULL | NULL | NULL | NULL | 14 | Using where; Using join buffer (Block Nested Loop) |
+- -+
2 rows in set (0.00 sec)
This is how the MySQL not in nested query shared by the editor is rewritten into an external join mode. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.
