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

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.