Sql-exists and NOT EXISTS

exists is used to check if a subquery returns at least one row of data, and the subquery does not actually return any data, but instead returns a value of TRUE or False
EXISTS Specifies a subquery that detects the existence of a row.

Instance:

(i). Using NULL in a subquery still returns the result set

Select *  from where exists (selectnull
Select *  from Tablein

Query results

(b). Compare queries that use EXISTS and in. Note Two queries return the same result.

Select *  from where exists (Selectfromwhere bname=tablein.aname) Select *  from where inch (Select from Tableex)

(iii). Compare queries that use EXISTS and = any. Note Two queries return the same result.

Select *  from where exists (Selectfromwhere bname=tablein.aname) Select *  from where ANAME=any(select from Tableex)

The role of not EXISTS is the opposite of EXISTS. If the subquery does not return a row, the WHERE clause in not EXISTS is satisfied.

Conclusion:
The return value of the EXISTS (including not EXISTS) clause is a bool value. There is a subquery inside the exists (SELECT ... From ...), which I call an inner query statement for exist. The inner query statement returns a result set. The EXISTS clause returns a Boolean value based on the result set of its query statement, either empty or non-empty.

A popular can be understood as: each row of the outer query table, in the query as a test, if the query returned by the result of a non-null value, then the EXISTS clause returns true, this row of rows can be used as the result row of the outer query, otherwise it cannot be a result.

The parser first looks at the first word of the statement, and when it finds out that the first word is the SELECT keyword, it jumps to the FROM keyword and then finds the table name from the keyword and loads the table into memory. Then find the WHERE keyword, if not found, return to the Select to find the field resolution, if found where, then analyze the conditions, complete and then return to the Select analysis field. Finally form a virtual table we want.
The WHERE keyword is followed by a conditional expression. When the conditional expression is evaluated, a return value of 0 or 0, not 0 is true (true), and 0 is False (false). Similarly, the condition behind the where also has a return value, true or FALSE, to determine that the next hold does not execute the SELECT.
The parser first finds the keyword Select, jumps to the FROM keyword, imports the student table into memory, finds the first record through the pointer, and then finds the conditional expression where the keyword evaluates it, and if it is true then put the record in a virtual table, and the pointer points to the next record. If False then the pointer points directly to the next record without any other action. Retrieves the entire table and returns the retrieved virtual table to the user. Exists is part of the conditional expression, and it also has a return value (TRUE or false).

Before inserting a record, you need to check to see if the record already exists, and to perform the insert operation only if the record does not exist, you can prevent the insertion of duplicate records by using the EXISTS conditional clause.

INSERT  intoTablein (aname,asex)SELECT Top 1 'Zhang San','male'  fromTableinWHERE  not exists(Select *  fromTableinwhereTablein.aid= 7)

The use of exists and in efficiency, usually using exists is higher than in efficiency, because in does not walk the index, but depends on the actual use of:
In the case of large appearance and small inner table, exists is suitable for small appearance and large inner table .

Sql-exists and NOT EXISTS