What is the usage of not exists in oracle

In Oracle, "not exists" is used to determine whether a clause returns a result set. If the clause returns a result set, it is false. If the clause does not return a result set, it is true. The syntax is "select * from daul where not exists (clause query condition)".

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

What is the usage of not exists in oracle

exists: The emphasis is on whether to return the result set, and it is not required to know what is returned. For example:

  select name from student where sex = 'm' and mark exists(select 1 from grade where ...)

As long as the sub-child guided by exists If the sentence has a result set returned, then the exists condition is established. Please note that the returned field is always 1. If it is changed to "select 2 from grade where...", then the returned field is 2. This number is meaningless. So the exists clause does not care about what is returned, but whether there is a result set returned.

The biggest difference between exists and in is that the in clause can only return one field, for example:

  select name from student where sex = 'm' and mark in (select 1,2,3 from grade where ...)

, the in clause returns three fields, which is not true Correct, the exists clause is allowed, but in only allows one field to be returned. Just remove any two fields in 1, 2, and 3.

And not exists and not in are the opposites of exists and in respectively.

• exists (sql returns the result set as true)

• not exists (sql does not return the result set as true)

The process of not exists is described in detail below:

As follows:

Table A

ID NAME

1 A1

2 A2

3 A3

Table B

ID AID NAME

1 1 B1

##2 2 B2

3 2 B3

Table A and table B have a 1-to-many relationship A.ID => B.AID

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID=B.AID)

The execution result is

1 A1

2 A2

The reason can be analyzed as follows

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=１)
--->SELECT * FROM B WHERE B.AID=１有值返回真所以有数据
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2)
--->SELECT * FROM B WHERE B.AID=２有值返回真所以有数据
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3)
--->SELECT * FROM B WHERE B.AID=３无值返回真所以没有数据

NOT EXISTS is the other way around

SELECT ID,NAME FROM A WHERE　NOT EXIST (SELECT * FROM B WHERE A.ID=B.AID)

The execution result is

3 A3

EXISTS = IN, the meaning is the same but there is a slight difference in syntax. It seems that the efficiency of using IN is less, which may be the reason why the index will not be executed.

SELECT ID,NAME FROM A　 WHERE　ID IN (SELECT AID FROM B)

NOT EXISTS = NOT IN, the meaning is the same but there is a difference in syntax. Little difference

SELECT ID,NAME FROM A WHERE　ID　NOT IN (SELECT AID FROM B)

Sometimes we will encounter the situation where we need to select a certain column without duplication, use a certain column as the selection condition, and other columns will be output normally.

Recommended tutorial: "

Oracle Video tutorial》

The above is the detailed content of What is the usage of not exists in oracle. For more information, please follow other related articles on the PHP Chinese website!