Comparison and use of exists and in, not exists and not in SQL

 

 

In MSSQL, inserting a record is simple, but in some special applications, before inserting a record, you need to check whether the record 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 use the exists condition 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 );

Example 1: Insert a single record

Code:

Insert into clients
(Client_id, client_name, client_type)
Select 10345, 'ibm ', 'advertising'
From dual
Where not exists (select * from clients
Where clients. client_id = 10345 );

UseDualThe table name allows you to directly keep up with the values of the fields to be inserted after the SELECT statement, even if these values do not exist in the current table.

The system requires SQL optimization to optimize low-efficiency SQL statements so that they can run more efficiently. Specifically, you must change some in/not in SQL statements to exists/not exists.

 

The modification method is as follows:

SQL statement of in

Select ID, category_id, htmlfile, title, convert (varchar (20), begintime, 112) as pubtime
From tab_oa_pub where is_check = 1 and
Category_id in (select ID from tab_oa_pub_cate where no = '1 ')
Order by begintime DESC

SQL statement modified to exists
Select ID, category_id, htmlfile, title, convert (varchar (20), begintime, 112) as pubtime
From tab_oa_pub where is_check = 1 and
Exists (select ID from tab_oa_pub_cate where tab_oa_pub.category_id = convert (INT, no) and no = '1 ')
Order by begintime DESC

 

Is exists really more efficient than in?

 

Let's first discuss in and exists.
Select * from T1 where X in (select y from T2)
In fact, it can be understood:
Select *
From T1, (select distinct y from T2) T2
Where t1.x = t2.y;
-- If you have some experience in SQL optimization, from this sentence, you can naturally think that T2 cannot be a large table, because it is necessary to perform "unique sorting" on the entire table of T2 ", if T2 is large, the sorting performance is intolerable. But T1. why? The most common understanding is that t1.x = t2.y can be indexed. But this is not a good explanation. Imagine if t1.x and t2.y both have indexes, we know that indexes are ordered. Therefore, the best solution between T1 and T2 is merge join. In addition, if t2.y has an index, the sorting performance of T2.
Select * from T1 where exists (select null from T2 where Y = X)
It can be understood:
For X in (select * from T1)
Loop
If (exists (select null from T2 where Y = x. x)
Then
Output the record!
End if
End Loop
-- This is easier to understand. T1 is always a table scan! Therefore, T1 cannot be a large table, while T2 can be large, because y = x. x can be indexed by t2.y.
Based on the above discussions on in/exists, we can draw a general conclusion:In is suitable for the case where the external table is large but the internal table is small; exists is suitable for the case where the external table is small but the internal table is large.

We need to optimize based on the actual situation. We cannot say who is more efficient, who is less efficient, and everything is relative.

 

 

Bytes -----------------------------------------------------------------------------------------------------------------------

 

 

In SQL, not in, exists, not exists usage and difference:

In: determines whether the given value matches the value in the subquery or list.
The in keyword allows you to select a row that matches any value in the list.
To obtain a list of names and States of all authors living in California, Indiana, Or Maryland, You need to query the following:
Select productid, productname from northwind. DBO. products where categoryid = 1 or categoryid = 4 or categoryid = 5
However, if you use in, you can get the same result by typing less characters:
Select productid, productname from northwind. DBO. products where categoryid in (1, 4, 5)
Items after the in keyword must be separated by commas and enclosed in brackets.
In the titleauthor table, find the au_id of all authors whose royalties are less than 50% in any of the following queries, and then select au_id and

Names of all authors matching the titleauthor query results:
Select au_lname, au_fname from authors where au_id in (select au_id from titleauthor where royaltyper <50)
The results show that some authors belong to the category of less than 50%.

Not in: subqueries introduced by the not in keyword also return a column of zero or more values.
The following query lists the names of publishers who have not published any commercial books.
Select pub_name from publishers where pub_id not in (select pub_id from titles where type = 'business ')

Subqueries introduced by exists and not exists can be used for two sets of operations: intersection and difference set. The intersection of the two sets contains all elements of the two original sets.

The difference set contains only the elements of the first set in two sets.

Exists: Specifies a subquery to check the existence of a row.
This example shows how to query the title of a book published by any publisher in a city that starts with a letter B:
Select distinct pub_name from publishers where exists (select * from titles where pub_id = publishers. pub_id and type =

'Business ')
Select distinct pub_name from publishers where pub_id in (select pub_id from titles where type = 'business ')
Differences between the two:
Exists: the query statement that can be followed by the entire sentence, for example: Select * from titles
In: The following can only be a single column: Select pub_id from titles

Not exists:
For example, to find the name of a publisher who does not publish a commercial book:
Select pub_name from publishers where not exists (select * from titles where pub_id = publishers. pub_id and type =

'Business ')
The following query finds the name of a book that is not sold:
Select title from titles where not exists (select title_id from sales where title_id = titles. title_id)