MySQL exists and in details and differences, mysqlexists

MySQL exists and in details and differences, mysqlexists

MySQL exists and in details and differences

A query is 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? 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.

SELECT CategoryNameFROM CategoriesWHERE 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. Note that the two queries return the same information.

SELECT DISTINCT pub_nameFROM publishersWHERE 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: the first method is = ANY, and the second method is EXISTS. Note that the two methods return the same information.

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

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:

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

Use NOT EXISTS

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:

SELECT pub_nameFROM publishersWHERE NOT EXISTS  (SELECT *  FROM titles  WHERE pub_id = publishers.pub_id  AND type = 'business')ORDER BY pub_name

For example, the following SQL statement:

Select distinct name from xswhere not exists (select * from kcwhere not exists (select * from xs_kcwhere student ID = xs. Student ID and course number = kc. Course number)

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.

Where not existsselect * from xs_kcwhere student ID = xs. Student ID and course number = kc. Course number

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.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!