How to Use exists in SQL

A query is as follows:
Copy codeThe Code is as follows:
SELECT c. CustomerId, CompanyName
FROM MERs c
Where exists (
SELECT OrderID FROM Orders o
WHERE o. CustomerID = cu. CustomerID)

How does EXISTS work? The OrderId field is returned by the subquery, but the fields CustomerID and CompanyName are used in the external query. These two fields are definitely not in the OrderID field. How does this match?
EXISTS is used to check whether a subquery returns at least one row of data. In fact, this subquery does not return any data, but returns True or False.
EXISTS specifies a subquery to check the existence of rows. Syntax: EXISTS subquery. The subquery parameter is a restricted SELECT statement (the COMPUTE clause and the INTO keyword are not allowed ). The result type is Boolean. If the subquery contains rows, TRUE is returned.
Using null in a 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.
Copy codeThe Code is as follows:
SELECT CategoryName
FROM Categories
Where exists (select null)
Order by CategoryName ASC

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.
Copy codeThe Code is as follows:
Select distinct pub_name
FROM publishers
WHERE EXISTS
(SELECT *
FROM titles
WHERE pub_id = publishers. pub_id
AND type = \ 'business \')

Copy codeThe Code is as follows:
SELECT distinct pub_name
FROM publishers
WHERE pub_id IN
(SELECT pub_id
FROM titles
WHERE type = \ 'business \')

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.
Copy codeThe Code is as follows:
SELECT au_lname, au_fname
FROM authors
WHERE exists
(SELECT *
FROM publishers
WHERE authors. city = publishers. city)

Copy codeThe Code is as follows:
SELECT au_lname, au_fname
FROM authors
WHERE city = ANY
(SELECT city
FROM publishers)

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:
Copy codeThe Code is as follows:
SELECT title
FROM titles
WHERE EXISTS
(SELECT *
FROM publishers
WHERE pub_id = titles. pub_id
AND city LIKE \ 'B % \')

Copy codeThe Code is as follows:
SELECT title
FROM titles
WHERE pub_id IN
(SELECT pub_id
FROM publishers
WHERE city LIKE \ 'B % \')

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:
Copy codeThe Code is as follows:
SELECT pub_name
FROM publishers
WHERE NOT EXISTS
(SELECT *
FROM titles
WHERE pub_id = publishers. pub_id
AND type = \ 'business \')
Order by pub_name

For example, the following SQL statement:
Copy codeThe Code is as follows:
Select distinct name from xs
Where not exists (
Select * from kc
Where not exists (
Select * from xs_kc
Where student ID = xs. Student ID and course number = kc. Course number
)

Perform the layer-4 subquery for one row of data in xs at the outermost layer.
The exists statement in the middle only returns true or false for the previous layer, because the query conditions are in the sentence where student ID = xs. Student ID and course number = kc. Course number. Each exists has a row of values. It only tells the first layer that the query condition of the outermost layer is true or not, and the returned value is the same as the returned value. It is returned to the result set if it is true (true) at the highest level. False.
Copy codeThe Code is as follows:
Where not exists
Select * from xs_kc
Where student ID = xs. Student ID and course number = kc. Course number

This exists tells the previous layer that this row of statements is not valid here. Because he is not the highest level, he must continue to return up.
Select distinct name from xs where not exists (the exists statement here receives the value of the previous one that is false. In his judgment, the result is true (true). Because it is the highest level, the result of this line (the query condition here) is returned to the result set.
Important points:
The tables of the wake-up query conditions to be used at the bottom layer, such as xs. Student ID and kc. Course number, must be described in the previous section: select * from kc, select distinct name from xs
Do not pay too much attention to the exists statement in the middle.
Understand the returned values when exists and not exists are nested.