Difference between in and exists in SQL

I can see a good example on the Internet to explain the difference between in and exists.

This example shows how to query the title of a book published by any publisher in a city that starts with a letter B:
Use pubs

Select title
From titles where exists
(Select *
From publishers
Where pub_id = titles. pub_id
And city like 'B % ')
Go

-- Or, using in:

Use pubs
Go
Select title
From titles
Where pub_id in
(Select pub_id
From publishers
Where city like 'B % ')
Go

Use not exists

Not exists is opposite to exists. If the subquery does not return rows, the where clause in not exists is satisfied. In this example, find the name of the publisher who does not publish a commercial book:

Use pubs
Go
Select pub_name
From publishers
Where not exists
(Select *
From titles
Where pub_id = publishers. pub_id
And type = 'business ')
Order by pub_name
Go

 

 

In and exists
In is a hash connection between the external table and the internal table, while exists is a loop on the External table. Each loop then queries the internal table. The argument that exists is more efficient than in is always inaccurate.
If the two tables to be queried are of the same size, there is little difference between in and exists.
If one of the two tables is small and the other is a large table, exists is used for the large subquery table and in is used for the small subquery table:
Example: Table A (small table), table B (large table): Select * from a where CC in (select CC from B)
Low Efficiency: the CC column index of Table A is used; select * from a where exists (select CC from B where cc = A. CC)
High Efficiency: the CC column index of Table B is used.
2: Select * from B where CC in (select CC from)
High Efficiency: the CC column index of Table B is used; select * from B where exists (select CC from a where cc = B. CC)
Low Efficiency: the CC column index of Table A is used.
Not in and not exists if the query statement uses not in, the internal and external tables are scanned for the whole table, and no index is used. However, the not extsts subquery can still use the table index. Therefore, whether the table is large, not exists is faster than not in.
Difference between in and =
Select name from student where name in ('zhang ', 'wang', 'lil', 'zhao ');
And
Select name from student where name = 'zhang' or name = 'lil' or name = 'wang' or name = 'zhao'
The results are the same.

 

SQL Execution Efficiency Analysis of in and exists

Two tables A and B,

(1) When only one table's data such as a is displayed and only one relational condition such as ID is displayed, using in is faster:

Select * from a where ID in (select ID from B)

(2) When only the data of a table such as a is displayed, and the link condition such as ID and col1 is different, it is inconvenient to use in. You can use exists:

Select * from

Where exists (select 1 from B where id = A. ID and col1 = A. col1)

(3) when only two tables are displayed, the use of in and exists is not suitable and the connection is required:

Select * from a left join B on ID = A. ID

Therefore, the method used depends on the requirements.