There is a query as follows:
Copy Code code as follows:
SELECT C.customerid, CompanyName
From Customers C
WHERE EXISTS (
SELECT OrderID from Orders o
WHERE O.customerid = cu. CustomerID)
How does this exists work? Subquery returned the OrderID field, but the outside query to find the CustomerID and CompanyName fields, these two fields are certainly not in the OrderID inside Ah, this is how to match it?
exists is used to check whether a subquery returns at least one row of data, and the subquery does not actually return any data, but rather returns a value of TRUE or false.
EXISTS Specifies a subquery that detects the existence of a row. Syntax: EXISTS subquery. Parameter subquery is a restricted SELECT statement (COMPUTE clauses and into keywords are not allowed). The result type is Boolean, and returns TRUE if the subquery contains rows.
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 TRUE by using EXISTS.
Copy Code code as follows:
SELECT CategoryName
From Categories
WHERE EXISTS (SELECT NULL)
ORDER BY CategoryName ASC
Compare queries using EXISTS and in
This example compares two semantically similar queries. The first query uses EXISTS and the second query uses in. Note that two queries return the same information.
Copy Code code as follows:
SELECT DISTINCT pub_name
From publishers
WHERE EXISTS
(SELECT *
From titles
WHERE pub_id = publishers.pub_id
and type = \ ' Business\ ')
Copy Code code as follows:
SELECT distinct pub_name
From publishers
WHERE pub_id in
(SELECT pub_id
From titles
WHERE type = \ ' Business\ ')
Compare queries that use EXISTS and = any
This example shows two ways to find authors who live in the same city as the Publisher: The first method uses = Any, and the second method uses EXISTS. Note that both of these methods return the same information.
Copy Code code as follows:
SELECT au_lname, au_fname
From authors
WHERE exists
(SELECT *
From publishers
WHERE authors.city = publishers.city)
Copy Code code as follows:
SELECT au_lname, au_fname
From authors
WHERE City = Any
(SELECT City
From publishers)
Compare queries using EXISTS and in
The query in this example looks for a title published by any publisher in a city that begins with the letter B:
Copy Code code as follows:
SELECT Title
From titles
WHERE EXISTS
(SELECT *
From publishers
WHERE pub_id = titles.pub_id
and city like \ ' b%\ ')
Copy Code code as follows:
SELECT Title
From titles
WHERE pub_id in
(SELECT pub_id
From publishers
WHERE city like \ ' b%\ ')
Use Not EXISTS
The function of not EXISTS is the opposite of EXISTS. If the subquery does not return a row, the WHERE clause in the not EXISTS is met. This example finds the name of a publisher that does not publish a business book:
Copy Code code 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
Another example is the following SQL statement:
Copy Code code as follows:
Select distinct name from XS
Where NOT EXISTS (
SELECT * FROM KC
Where NOT EXISTS (
SELECT * FROM XS_KC
The where study number =XS. and course number =KC. Course Number
)
The outermost query xs in the data line to do the inside of the subquery.
The EXISTS statement in the middle makes only the return true or false to the previous layer, because the query's condition is in the same sentence as the course number of the where-number =xs. Each exists will have a row of values. It just tells a layer, the outermost query condition is set up here or not set up, return the time value also return to go up. Returns to the result set at the highest level if True (true). is False (false) discarded.
Copy Code code as follows:
Where NOT EXISTS
SELECT * FROM XS_KC
The where study number =XS. and course number =KC. Course Number
This exists is to tell the previous layer, this line of statements is not tenable here. Because he is not the highest level, so continue to go up.
Select distinct name from XS where not exists (the EXISTS statement here receives a value that is last false. He's judging that the result is true (set), because it's the highest level, so the result of this line (which refers to the query condition) is returned to the result set.
A few important points:
The table of the innermost conditions to be used for such as: XS. School number, KC. Course number, etc. to be explained in front of the SELECT * from kc,select distinct name from XS
Don't pay too much attention to the middle exists statement.
To figure out the return value of exists and not exists when nested
