In, not, exists, not exists usages and differences in SQL

EXISTS (SQL returns the result set as true)
Not EXISTS (SQL does not return result set is true)
As follows:
Table A
ID NAME
1 A1
2 A2
3 A3

Table B
ID AID NAME
1 1 B1
2 2 B2
3 2 B3

Table A and table B are 1-to-many relationships a.id = B.aid

Select Id,name from A where EXIST (SELECT * from B where a.id=b.aid)
Execution results are
1 A1
2 A2
The reasons 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 has value return 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 has value return true so 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 reverse.
Select Id,name from A where isn't EXIST (select * from B where a.id=b.aid)
Execution results are
3 A3
===========================================================================
EXISTS = in, same meaning but slightly different in syntax, as if using in efficiency to almost, should be the reason why the index is not executed
Select Id,name from A WHERE ID in (select AID from B)

Not EXISTS = No in, same meaning but slightly different in syntax
Select Id,name from A WHERE ID not in (SELECT AID from B)


The following are common uses:

The usage and differences of In,not in,exists,not EXISTS in sql:
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 of the values in the list.
The following query is required when you want to obtain the names and state lists of all authors residing in California, Indiana, or Maryland states:
SELECT ProductID, ProductName from Northwind.dbo.Products WHERE CategoryID = 1 or CategoryID =4 OR CategoryID = 5
However, if you use in, you can also get the same result by typing fewer 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 parentheses.
The following query finds the au_id of all authors who receive less than 50% of the royalties in any book in the titleauthor table, and then selects au_id from the authors table and
TitleAuthor the names of all authors matching the query results:
Select au_lname, au_fname from authors WHERE au_id in (SELECT au_id from titleauthor whereroyaltyper <50)
The results show that some authors belong to less than 50% of the class.
Not in: subqueries introduced through the NOT keyword also return a column of 0 or more values.
The following query finds the name of a publisher who has not published a business book.
Select Pub_name from publishers where pub_id isn't in (SELECT pub_id from the titles WHERE type = ' business ')
Subqueries introduced with EXISTS and not EXISTS can be used for the operation of two sets of principles: intersection and Difference sets.
The intersection of two sets contains all the elements that belong to the two original collection at one time.
The difference set contains elements that belong to only the first collection in the two collection.
EXISTS: Specifies a subquery to detect the presence of a row.
The query in this example looks for titles published by any publisher in a city that begins with the 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 the titles where type= ' business ')
The difference between the two:
EXISTS: The following can be a query for an entire sentence such as: SELECT * FROM titles
In: Only one column later: SELECT pub_id from titles
Not EXISTS:
For example, to find the name of a publisher who does not publish a business book:
Select pub_name from publishers where isn't EXISTS (SELECT * FROM titles WHERE pub_id =publishers.pub_id and type =
' Business ')
The following query finds the name of a book that has not been sold:
Select title from the titles where not EXISTS (SELECT title_id from sales WHERE title_id = titles.title_id)

Grammar

EXISTS subquery
Parameters
Subquery: is a restricted SELECT statement (the COMPUTE clause and the INTO keyword are not allowed). For more information, see the discussion of subqueries in SELECT.

Result type: Boolean


Result value: Returns TRUE if the subquery contains rows.


Example
A. Using NULL in a subquery still returns the result set

This example specifies NULL in the subquery and returns the result set, which is still evaluated to TRUE by using EXISTS.

Use Northwind
GO
SELECT CategoryName
From Categories
WHERE EXISTS (SELECT NULL)
ORDER by CategoryName ASC
GO

B. Comparing queries using EXISTS and in

This example compares two semantically similar queries. The first query uses EXISTS and the second query uses in. Note 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 for either query:

Pub_name
----------------------------------------
Algodata Infosystems
New Moon Books

C. Comparing queries that use EXISTS and = any

This example shows two query methods for finding authors who live in the same city as the Publisher: The first method uses = Any, and the second method uses exists. Note Both of these 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. Comparing queries using EXISTS and in

The query in this example looks for titles published by any publisher in a city that begins with the 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. Using not EXISTS

The role of not EXISTS is opposite to EXISTS. If the subquery does not return rows, the WHERE clause in not EXISTS is satisfied. This example finds the name of the publisher who does not publish a business 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, not, exists, not exists usages and differences in SQL