A good analogy for understanding not exists: not exists = not in; exists = in

Simply put, not exists = not in; exists = in

 

However, this is just a simple understanding. There are still some differences between the execution mechanism and the details in it!

 

The following is an excerpt from the network:Article:

 

SQL Exists and in, Not Exists and Not In Efficiency Comparison and usage

In MSSQLInsert a record is very simple, but in some special applications, before inserting a record, you need to check whether this record already exists. The insert operation is performed only when the record does not exist, this article describes the solution to this problem.

Problem:I have created a table to store customer information. I know that the insert statement can be used to insert information into the table. But how can I ensure that duplicate records are not inserted?

Answer:You can useExistsCondition to prevent repeated records from being inserted.

Example 1: insert multiple records

Suppose there is a clients table with the primary key of client_id, you can use the following statement:

Code:

Insert into clients
(Client_id, client_name, client_type)
Select supplier_id, supplier_name, 'advertising'
From suppliers
Where Not Exists (Select * from clients
Where clients. client_id = suppliers. supplier_id );

 

Personal note: Not exists does not exist. That is to say, as long as the data is returned in the brackets, this condition does not exist. It can be understood as not

exists is a left expression, and the query after parentheses is a right expression, the equation is true only when the right expression returns not exists (that is, the result of the subsequent query is not empty.