A query is as follows:
1 |
Select C. customerid, companyName |
4 |
Select OrderidFrom Orders o |
5 |
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.
3 |
Where Exists (Select Null) |
4 |
Order By CategorynameASC |
- 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.
1 |
Select Distinct Pub_name |
6 |
Where Pub_id = publishers. pub_id |
1 |
Select Distinct Pub_name |
6 |
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.
1 |
Select Au_lname, au_fname |
6 |
Where Authors. City = publishers. City) |
1 |
Select Au_lname, au_fname |
- 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:
6 |
Where Pub_id = titles. pub_id |
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:
6 |
Where Pub_id = publishers. pub_id |
For example, the following SQL statement:
1 |
Select Distinct NameFrom XS |
6 |
Where Student ID = Xs. Student IDAnd Course No. = KC. course No. |
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.
3 |
Where Student ID = Xs. Student IDAnd Course No. = KC. course No. |
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.
