当前位置：网站首页>SQL Server database in clause and exists clause conversion

SQL Server database in clause and exists clause conversion

2022-04-23 19:08:00 【Little brother】

We usually do in the program SQL When optimizing, pay attention to the use of EXISTS With substitution IN How to do it , The reason is that EXISTS Execution is more efficient than IN high .
　　 One thing I've been confused about before is how to use EXISTS To replace IN Well , What is the meaning of the two ？ Today, I will record my personal understanding
　　IN Scope of representation , It means that a field is within a certain range , This scope generally uses subqueries to get , Thus we can see that IN The result returned by the subquery should be this range set .
　　EXISTS Indicates presence , There is at least one place , This condition is made by EXISTS Subquery to complete , But here EXISTS The result returned by the subquery is no longer a result set , It's a Boolean value （true or false）, In fact, this is quite understandable ,EXISTS It means that if the subquery can find the value, it will return true, execute EXISTS The previous statement .
Take a chestnut
　　 If there is a watch user, It has two fields id and name, We need to check the name with a User information for ：
　　 The simplest SQL：select * from user where name like '%a%';
　　 Use IN Of SQL：select u.* from user u where u.id in (select uu.id from user uu where uu.name like '%a%');
　　 We will now use IN Of SQL Change to use EXISTS Of SQL How to write ？
　　 In the beginning, I directly put u.id in Replace with EXISTS, Get the following statement ：
　　　　select u.* from user u where exists(select uu.id from user uu where uu.name like '%a%');
　　 After testing, it is found that the output result is wrong , This statement queries all users without omission , I believe you also found the problem , Later, I modified the above sentence as follows ：
　　　　select u.* from user u where exists (select uu.id from user uu where uu.name like '%a%' and uu.id=u.id);
　　 As you can see , Just add... To the subquery “and uu.id=u.id”, The query result is correct .
　　 So why ？
　　 summary ：EXISTS Subquery can be regarded as an independent query system , Just to get true and false logical values ,EXISTS The tables of subquery and external query are two completely independent and unrelated tables （ When... In the second table name It contains a Your name exists , Then perform the operation of querying all users in the first table ）, When we add id After Association ,EXISTS The tables of sub query and external query are unified , It is a virtual table formed by the combination of the two , It's the same table （ In this way, when the sub query finds the current row in the virtual table uu.name Contained in the a when , The corresponding in the current row of the virtual table u.id And u.name Query to ）
　　 So the focus of everything is on this ID Above Association , add to ID relation , The database will first pass the two tables through ID Associations are combined into a virtual table , All query operations are completed on this virtual table , The operation is the same table , Of course, it won't happen as before ！

版权声明
本文为[Little brother]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/04/202204210601423248.html

边栏推荐

猜你喜欢

随机推荐