|
Custom Search
| |
|
Using correlated subqueries Inside This Tutorial Series
Using correlated subqueries The name of correlated subqueries means that a subquery is correlated with the outer query. The correlation comes from the fact that the subquery uses information from the outer query and the subquery executes once for every row in the outer query. A correlated subquery can usually be rewritten as a join query. Using joins enables the database engine to use the most efficient execution plan. The query optimizer is more mature for joins than for subqueries, so in many cases a statement that uses a subquery should normally be rephrased as a join to gain the extra speed in performance. Note that alias must be used to distinguish table names in the SQL query that contains correlated subqueries. Practice #1: Using 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 - 77 rows returned: Practice #2: Using correlated subquery and extra inner join. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page. /*
Query result set - 77 rows returned: Practice #3: Using different tables in subquery and 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 - 5 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 |