Exist and not exist

Exists (the result set returned by SQL is true)
Not exists (SQL does not return true result sets)
As follows:
Table
ID name
1 A1
2 A2
3 A3

Table B
Id aid name
1 1 B1
2 2 B2
3 2 B3

A. ID => B. Aid

Select ID, name from a where exist (select * from B where a. ID = B. Aid)
The execution result is
1 A1
2 A2
The cause can be analyzed as follows:
Select ID, name from a where exists (select * from B where B. Aid = 1)
---> Select * from B where B. Aid = 1 A value returns true, so there is data

Select ID, name from a where exists (select * from B where B. Aid = 2)
---> Select * from B where B. Aid = 2 returns true because there is data

Select ID, name from a where exists (select * from B where B. Aid = 3)
---> Select * from B where B. Aid = 3 no value returns true, so no data

Not exists is the opposite
Select ID, name from a where not exist (select * from B where a. ID = B. Aid)
The execution result is
3 A3
========================================================== ==========================================
Exists = In, meaning the same, but there is a little difference in syntax, it seems that the efficiency of using in is almost the same, it should be because the index will not be executed
Select ID, name from a where ID in (select aid from B)

Not exists = not in, meaning the same, but there is a little difference in syntax
Select ID, name from a where id not in (select aid from B)

The following is a common usage:

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)

Syntax

Exists subquery
Parameters
Subquery: a restricted SELECT statement (the compute clause and the into keyword are not allowed ). For more information, see the discussion on the subquery in select.

Result type: Boolean

Result value: If the subquery contains rows, true is returned.

Example
A. using NULL in the subquery still returns the result set

In this example, null is specified in the subquery and the result set is returned. By using exists, the value is still true.

Use northwind
Go
Select categoryname
From categories
Where exists (select null)
Order by categoryname ASC
Go

B. Compare queries using exists and in

This example compares two queries with similar semantics. The first query uses exists and the second query uses in. Note that the two queries return the same information.

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

-- Or, using the in clause:

Use pubs
Go
Select distinct pub_name
From publishers
Where pub_id in
(Select pub_id
From titles
Where type =/'business /')
Go

The following is the result set of any query:

Pub_name
----------------------------------------
Algodata infosystems
New Moon books

C. Compare queries using exists and = any

This example shows two query methods: the first method is = any, and the second method is exists. Note that the two methods return the same information.

Use pubs
Go
Select au_lname, au_fname
From authors
Where exists
(Select *
From publishers
Where authors. City = publishers. City)
Go

-- Or, using = any

Use pubs
Go
Select au_lname, au_fname
From authors
Where city = any
(Select city
From publishers)
Go

D. Compare queries using exists and in

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
Go
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

E. 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