|
Custom Search
| |
|
Using EXISTS and NOT EXISTS in correlated subqueries in MySQL Inside This Tutorial Series
Using EXISTS and NOT EXISTS in correlated subqueries EXISTS and NOT EXISTS are used with a subquery in WHERE clause to examine if the result the subquery returns is TRUE or FALSE. The true or false value is then used to restrict the rows from outer query select. Because EXISTS and NOT EXISTS only return TRUE or FALSE in the subquery, the SELECT list in the subquery does not need to contain actual column name(s). Normally use SELECT * (asterisk) is sufficient but you can use SELECT column1, column2, ... or anything else. It does not make any difference. Because EXISTS and NOT EXISTS are used with correlated subqueries, the subquery executes once for every row in the outer query. In other words, for each row in outer query, by using information from the outer query, the subquery checks if it returns TRUE or FALSE, and then the value is returned to outer query to use. Practice #1: Using EXISTS in correlated subquery. By examining the query in this practice, we can sum up the following steps that the database engine takes to evaluate the correlated subquery. It demonstrates that the subquery uses data from the outer query and the subquery executes once for every row in the outer query.
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page. /*
Query result set - 7 rows returned: Practice #2: Using NOT EXISTS in correlated subquery. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page. /*
Query result set - 9 rows returned: Other tutorials in this category 1. What is subquery |
Copyright © 2024 GeeksEngine.com. All Rights Reserved. This website is hosted by HostGator. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it. |
Home | Feedback | Terms of Use | Privacy Policy |