Introduction to MySQL exists and not EXISTS usage

Source: Internet
Author: User

One query is as follows:

1 SELECTc.CustomerId, c.CompanyName  
2 FROMCustomers c  
3 WHEREEXISTS(  
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.

1 SELECTCategoryName
2 FROMCategories
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.

1 SELECTDISTINCTpub_name
2 FROMpublishers
3 WHEREEXISTS
4     (SELECT*
5     FROMtitles
6     WHEREpub_id = publishers.pub_id
7     ANDtype = ‘business‘)
1 SELECTdistinctpub_name
2 FROMpublishers
3 WHEREpub_id IN
4     (SELECTpub_id
5     FROMtitles
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
2 FROMauthors
3 WHEREexists
4     (SELECT*
5     FROMpublishers
6     WHEREauthors.city = publishers.city)
1 SELECTau_lname, au_fname
2 FROMauthors
3 WHEREcity = ANY
4     (SELECTcity
5     FROMpublishers)
      • 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:

1 SELECTtitle
2 FROMtitles
3 WHEREEXISTS
4     (SELECT*
5     FROMpublishers
6     WHEREpub_id = titles.pub_id
7     ANDcity LIKE‘B%‘)
1 SELECTtitle
2 FROMtitles
3 WHEREpub_id IN
4     (SELECTpub_id
5     FROMpublishers
6     WHEREcity LIKE‘B%‘)
      • Using not EXISTS

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:

1 SELECTpub_name
2 FROMpublishers
3 WHERENOTEXISTS
4     (SELECT*
5     FROMtitles
6     WHEREpub_id = publishers.pub_id
7     ANDtype = ‘business‘)
8 ORDERBYpub_name

Another example is the following SQL statement:

1 selectdistinct 姓名 fromxs
2 wherenotexists (
3 selectfromkc
4 wherenotexists (
5 selectfromxs_kc
6 where学号=xs.学号 and课程号=kc.课程号
7 )

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.

1 wherenotexists
2 selectfromxs_kc
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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.