Analysis of exists and in, analysis of existsin

Source: Internet
Author: User

Analysis of exists and in, analysis of existsin

Exists and in do the same thing, so why do we need to get two more? It is actually a matter of efficiency.

For example, the following two examples

1. select * from t1 where exists (select 'x' from t2 where t1.a = t2.a)

PS: The 'X' here can be changed to any other regular bright, such as the numeric character etc.

2. select * from t1 where t1.a in (select t2.a from t2)

When exists is used, oracle first executes the primary query and then the subquery. Therefore, when the t1 data volume is small and the t2 data volume is large, the efficiency is high,

When in is used, oracle will first execute the subquery and then the primary query. Therefore, when the t2 data volume is small, the efficiency is high when the t1 data volume is large.


What are the differences between in and exist statements in SQL?

In and exists
In is a hash connection between the external table and the internal table, while exists is a loop on the External table. Each loop then queries the internal table.

If one of the two tables is small and the other is a large table, exists is used for the large subquery table and in is used for the small subquery table:
Example: Table A (small table), table B (large table) 1: select * from A where cc in (select cc from B)
Low Efficiency: the cc column index of Table A is used; select * from A where exists (select cc from B where cc = A. cc)
High Efficiency: the cc column index of Table B is used.
2: select * from B where cc in (select cc from)
High Efficiency: the cc column index of Table B is used; select * from B where exists (select cc from A where cc = B. cc)
Low Efficiency: the cc column index of Table A is used.
Not in and not exists if the query statement uses not in, the internal and external tables are scanned for the whole table, and no index is used. However, the not extsts subquery can still use the table index. Therefore, whether the table is large, not exists is faster than not in.

Difference between in and =
Select name from student where name in ('zhang ', 'wang', 'lil', 'zhao'); and
Select name from student where name = 'zhang' or name = 'lil' or
Name = 'wang' or name = 'zhao'
The results are the same.

What is the difference between exists and in SQL?

11. Replace IN with EXISTS and not exists instead of not in.
In many basic table-based queries, to meet one condition, you often need to join another table. in this case, using EXISTS (or not exists) usually improves the query efficiency. IN a subquery, the not in Clause executes an internal sorting and merging. IN either case, not in is the most inefficient (because it executes a full table traversal for the table IN the subquery ). to avoid the use of not in, we can rewrite it into an OUTER join (outer joins) or not exists.
Example: (efficient) SELECT * from emp (basic table) where empno> 0 and exists (SELECT 'x' from dept where dept. DEPTNO = EMP. deptno and loc = 'melb ')
(Inefficient) SELECT * from emp (basic table) where empno> 0 and deptno in (select deptno from dept where loc = 'melb ')
12. Replace DISTINCT with EXISTS
When you submit a query that contains one-to-many table information (such as the Department table and employee table), avoid using DISTINCT in the SELECT clause. in general, you can consider replacing it with EXIST, and EXISTS makes the query more rapid, because the RDBMS core module will immediately return results once the subquery conditions are met.
Example: (inefficient): select distinct DEPT_NO, DEPT_NAME from dept d, emp e where d. DEPT_NO = E. DEPT_NO
(Efficient): SELECT DEPT_NO, DEPT_NAME from dept d where exists (SELECT 'x' from emp e where e. DEPT_NO = D. DEPT_NO );

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.