Usage and difference of in, not in, exists, and not exists in SQL _in_not_exists_ SQL

Summary:Exists (the result set returned by SQL is true) not exists (the result set returned by SQL is true) is as follows: Table A ID name 1 A1 2 A2

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)
Run [Zhi hang] and the 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 returns true if a value exists, so there is data [Shu Ju]

Select ID, name from a where exists (select * from B where B. Aid = 2)
---> Select * from B where B. Aid = 2 returns real data because of a value [Shu Ju]

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 [Shu Ju]

Not exists is the opposite
Select ID, name from a where not exist (select * from B where a. ID = B. Aid)
Run [Zhi hang] and the result is
3 A3
========================================================== ==========================================
Exists = In, meaning the same, but there is a little difference in syntax [Yu fa]. It seems that the efficiency of using in is almost the same. It should be because [Zhi hang] indexing [suo Yin] is not 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 [Yu fa]
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 [lie Biao.
The in keyword [Guan Jian Zi] allows you to select a row in which [Xuan ZE] matches any value in the list [lie Biao.
To obtain the name and state list of all authors living in California, Indiana, Or Maryland [lie Biao], you need the following query:
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 less characters [Zi Fu:
Select productid, productname from northwind. DBO. products where categoryid in (1, 4, 5)
Project [Xiang Mu] After the in keyword [Guan Jian Zi] must be separated by commas and enclosed in brackets.
In the titleauthor table, Query [Cha Zhao] For the au_id of all authors whose royalties are less than 50% in any of the following queries, and then select [Xuan ZE] 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 [Xian shi] has some authors of less than 50%.
Not in: The subquery introduced by the not in keyword [Guan Jian Zi] also returns a column of zero or more values.
The following query lists the names of publishers who [Cha Zhao] 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 the operation of the [Yong Yu] set principles: 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 [Cha Zhao] published by any publisher in a city starting with 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 [Yu Ju] following 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 in [Cha Zhao:
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 [Cha Zhao] that is not sold:
Select title from titles where not exists (select title_id from sales where title_id = titles. title_id)

Syntax [Yu fa]

Exists subquery
Parameter [Can Shu]
Subquery: a restricted SELECT statement [Yu Ju] (the compute clause [Zi Ju] And the into keyword [Guan Jian Zi] are not allowed). For more information about [Xin XI], see the discussion on the subquery in select.

Result type [Lei Xing]: 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 similar queries with the semantics [Yu Yi. The first query uses exists and the second query uses in. Note that the two queries return the same information [Xin XI].

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 for [Xian shi] To search for [Cha Zhao] authors in the same city as Publishers [Fang fa]: The first method [Fang fa] to use = any, the second method [Fang fa] uses exists. Note that the two methods [Fang fa] return the same information [Xin XI].

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 [Cha Zhao] published by any publisher in a city starting with 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: [Zuo Yong] is opposite to exists. If no row is returned for the subquery, the where clause [Zi Ju] in not exists is satisfied. In this example, find the name of the publisher who [Cha Zhao] does not publish commercial books:

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