Difference in the usage of in/not in and exists/not exists in SQL, sqlexists

Difference in the usage of in/not in and exists/not exists in SQL, sqlexists

1; first, the usage of in/not in

In/not in is used to determine whether a single attribute value matches a given value or a subquery value;

Select * from Student s where s. id in (1, 2, 3); <pre name = "code" class = "SQL"> select * from Student s where s. name in (select distinct name from Project) 2; for now, exists/not exists is used to solve the intersection and difference set of the two tables.

   select * from proj_basic_info p  where   not exists (select * from proj_basic_info q where   p.id=q.id and q.proj_type=1 and q.qy_source='SUAEE')  <pre name="code" class="sql">   select * from proj_basic_info p  where  exists (select * from proj_basic_info q where   p.id=q.id and q.proj_type=1 and q.qy_source='SUAEE')

Conclusion: if a single attribute is matched, select in/not in. If multiple attributes are judged, you can use exitst/not exists as a set;

The usage of SQL statements not in and not exist is different.

In and exists are also quite different.

In is a set operator.

A in {a, c, d, s, d ....}

In this operation, the first element is followed by a set, and the element type in the set is the same as the previous element.

Exists is an existence judgment. If there is a result in the subsequent query, exists is true, otherwise it is false.

The in operation is used in a statement. The select operation next to it must select a field instead of select *.

For example, if you want to determine whether a student named "Xiao Ming" exists in a class, you can use the in operation:

"James" in (select sname from student)

In this way, (select sname from student) returns a set of class names. in is used to determine whether "James" is a data in this set;

You can also use the exists statement:

Exists (select * from student where sname = "James ")

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 );