One query is as follows:
1 |
SELECTc.CustomerId, c.CompanyName |
4 |
SELECTOrderID FROMOrders o |
5 |
WHEREo.CustomerID = c.CustomerID) |
How does this exists work? The subquery returns the OrderID field, but the outside query is looking for the CustomerID and CompanyName fields, and the two fields are definitely not in OrderID, how does this match?
exists is used to check if a subquery returns at least one row of data, and the subquery does not actually return any data, but instead returns a value of TRUE or false.
EXISTS Specifies a subquery that detects the existence of a row. Syntax: EXISTS subquery. The parameter subquery is a restricted SELECT statement (the COMPUTE clause and the INTO keyword 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 evaluated to TRUE by using EXISTS.
3 |
WHEREEXISTS (SELECTNULL) |
4 |
ORDERBYCategoryName 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 Two queries return the same information.
6 |
WHEREpub_id = publishers.pub_id |
6 |
WHEREtype = ‘business‘) |
- Compare 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.
1 |
SELECTau_lname, au_fname |
6 |
WHEREauthors.city = publishers.city) |
1 |
SELECTau_lname, au_fname |
- Compare 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:
6 |
WHEREpub_id = titles.pub_id |
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:
6 |
WHEREpub_id = publishers.pub_id |
Another example is the following SQL statement:
1 |
selectdistinct 姓名 fromxs |
6 |
where学号=xs.学号 and课程号=kc.课程号 |
The outermost query of XS in the data row of a row of sub-query.
The EXISTS statement in the middle only makes a return of true or false on the previous layer, because the condition of the query is in the where study number =XS. The number and course number =KC. The course number is in this sentence. Each exists will have a row of values. It just tells the layer that the outermost query condition is set up here or not, and returns the same value back up. Up to the highest level, if True (true), return to the result set. is False (false) discarded.
3 |
where学号=xs.学号 and课程号=kc.课程号 |
This exists is to tell the previous layer that this line of statements is not set up here. Because he is not the highest level, he will continue to return upward.
Select DISTINCT name from the XS where not exists (the EXISTS statement here receives the previous value of false. He is judging, the result is true (set), because it is the highest level, it will be the result of this row (this refers to the query condition) to return to the result set.
A few important points:
- The table of the most used wake-up conditions for example: XS, KC. Class number and so on before the time to explain the select * from kc,select distinct name from XS
- Do not pay too much attention to the middle of the exists statement.
- Figure out the return value when nesting exists and not exists
Introduction to MySQL exists and not EXISTS usage
