not exists usage

not exists is a syntax in SQL. It is commonly used between subqueries and main queries. It is used for conditional judgment. It returns a Boolean value based on a condition to determine how to proceed with the next operation. The same is true for not exists. The opposite of exists or in.

Not exists is the opposite of exists, so to understand the usage of not exists, we first understand the differences and characteristics of exists and in:

exists: The emphasis is on whether to return the result set, without knowing what to return. For example:

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

As long as the exists guide clause returns the result set, then the exists condition is established. Please pay attention to the fields returned. It is always 1. If it is changed to "select 2 from grade where...", then the returned field is 2, and 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 incorrect, exists The clause is allowed, but in only allows one field to be returned. Just remove any two fields in 1, 2, and 3.

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

exists     （sql       返回结果集，为真）

Mainly depends on whether the sql statement result in exists brackets has a result. If there is a result: the where condition will continue to be executed; if there is no result: it is considered that the where condition is not established.

not exists   (sql       不返回结果集，为真）

Mainly depends on whether the sql statement in the not exists brackets has a result. If there is no result: the where condition will continue to be executed; if there is a result: the where condition is deemed not to be true.

not exists: After testing, when the subquery and the main query have related conditions, it is equivalent to removing the subquery data from the main query.

For example:

test data: id name

1 1 Zhang San

2 李四

select * from test c where  not exists
(select 1 from test t where t.id= '1' )
--无结果

select * from test c where  not exists
(select 1 from test t where t.id= '1'  and t.id = c.id)
--返回2 李四

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