Subqueries and Derived Tables
Subqueries are used because:
- they may be the most logical way to retrieve the desired information,
- they can be used to isolate each part of a statement, and
- they may run more quickly than joins.
- Enclose subqueries in parentheses.
- Cannot use
ORDER BYin subqueries, but can use them in queries that contain subqueries.
- Subqueries in
WHEREclauses must use operators that handle multiple values, such as
- For readability, it is best to indent subqueries.
Subqueries allow users to retrieve information dynamically, as shown below, rather than hard coding in specific items.
Subqueries also allow for membership testing. That is, testing certain rows to see if they are members of other groups of rows.
NOT EXISTS are used for this purpose. These keywords are conceptually similar to
NOT IN, except that they are logical statements that return true or false, rather than testing specific values of a given table entry.
The following query selects all the users in the users table who were also in the dogs table. This is
Subqueries can also be used in the
FROM clause, as shown below. When structured in this manner, SQL actually creates a temporary table, called a derived table, that is then incorporated into the rest of the query.
When using subqueries in the
FROM clause, there are a few things to remember:
- Any alias is required for any derived tables created in subqueries within
- This alias must be used every time we want to execute a function that uses the derived table.
- Inner subqueries can refer to table aliases assigned outside the subqueries, but outer queries cannot refer to aliases created within subqueries unless they are included in the subquery
- Subqueries in
FROMstatements can be very computationally intensive.
|ce134be0-7144-11e5-ba71-058fbc01cf0b||ce134be0-7144-11e5-ba71-058fbc01cf0b||fd27c956-7144-11e5-ba71-058fbc01cf0b||German Shepherd Dog-Nova Scotia Duck Tolling Retriever Mix|
Some content in this note are taken from the Jupyter Notebook below. They are accessible as part of the “Managing Big Data with MySQL” course on coursera.org, and licensed by Jana Schaich Borg under CC BY-NC 4.0.
- MySQL Exercise 09 Subqueries and Derived Tables
Other content for this note is taken from the Coursera course “Managing Big Data with MySQL.”