MySQL exists and in Details and Differences

MySQL exists and in Details and Differences

There is one query as follows:

SELECT c.CustomerId, CompanyName  
FROM Customers c  
WHERE EXISTS(  
 SELECT OrderID FROM Orders o  
 WHERE o.CustomerID = cu.CustomerID)  

How does EXISTS work here?The subquery returns the OrderId field, but the external query is looking for the CustomerID and CompanyName fields, which are definitely not in OrderID. How does this match?

EXISTS checks to see if a subquery returns at least one row of data, which does not actually return any data but a value of True or False.

EXISTS specifies a subquery to detect the existence of rows.Syntax: EXISTS subquery.The 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 a result set

This example specifies NULL in a subquery and returns a result set that is still TRUE by using EXISTS.

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.Notice that both queries return the same information.

SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS
  (SELECT *
  FROM titles
  WHERE pub_id = publishers.pub_id
  AND type = 'business')

Compare queries using 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 that both methods return the same information.

SELECT au_lname, au_fname
FROM authors
WHERE exists
  (SELECT *
  FROM publishers
  WHERE authors.city = publishers.city)
 

Compare queries using EXISTS and IN

The query shown in this example finds the title of a book published by one publisher in a city that begins with the letter B:

SELECT title
FROM titles
WHERE EXISTS
  (SELECT *
  FROM publishers
  WHERE pub_id = titles.pub_id
  AND city LIKE 'B%')

Use NOT EXISTS

NOT EXISTS has the opposite effect as EXISTS.If the subquery does not return rows, the WHERE clause in NOT EXISTS is satisfied.This example finds the name of a publisher who does not publish a business book:

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:

select distinct  Full name  from xs
where not exists (
select * from kc
where not exists (
select * from xs_kc
where  School Number =xs. School Number  and  Course Number =kc. Course Number 
)

Make an inner subquery with the outermost query row 1 of data from xs.

The middle exists statement only returns true or false to the upper level, because the query is based on the sentence where number=xs.and number=kc.course number.Each exists has a row of values.It just tells Layer 1 that the outermost query conditions are true here or not, and that the value returns back up as well.Return to the result set if it is true (true) until the highest level.Discard for false (false).

where not exists
select * from xs_kc
where  School Number =xs. School Number  and  Course Number =kc. Course Number 

This exists tells the upper level that this line of sentence is not valid for me.Because he's not at the top level, he's going back up.

select distinct Name from xs where not exists (where the exists statement receives the last value of false).In Judgment 1, the result is true, which returns the result of this line (in this case, the query condition) to the result set, since it is the highest level.

Several important points:

The list of inquiry criteria to be used at the innermost level, such as: xs. School Number, kc. Course Number, etc. should be explained in the previous section 1. select * from kc, select distinct Name from xs Don't pay too much attention to the exists statement in the middle. Understand the return values when exists and not exists are nested

Thank you for reading, I hope to help you, thank you for your support on this site!