Subqueries and Query Expressions - Query Expressions in the FROM Clause
(Page 12 of 12 )
SQL2 query expressions provide a much more powerful and flexible method for generating and combining tables of query results than the simple subquery and UNION operations provided by the SQL1 standard. To make query expressions even more useful and more general-purpose, the SQL2 standard allows them to appear almost anywhere that a table reference could appear in a SQL1 query. In particular, a query expression can appear in place of a table name in the FROM clause. Here is a simple example of a SQL2 query for the sample database that uses this feature:
Show the names and total outstanding orders of all customers with credit limits over $50,000.
SELECT COMPANY, TOT_ORDERS
FROM CUSTOMER, (SELECT CUST, SUM(AMOUNT) AS TOT_ORDERS FROM ORDERS
GROUP BY CUST),
WHERE (CREDIT_LIMIT > 50000.00)
AND (CUST_NUM = CUST)
The second “table name” in the FROM clause of the main query is not a table name at all, but a full-blown query expression. In fact, the expression could have been much more complex, involving UNION or JOIN operations. When a query expression appears in the FROM clause, as it does here, the DBMS conceptually carries it out first, before any other processing of the query, and creates a temporary table of the query results generated by the query expression. In this case, this temporary table consists of two columns, listing each customer number and the total of orders for that customer number. This temporary table then acts as one of the source tables for the main query. In this example, its contents are joined to the CUSTOMER table to obtain the company name and generate the answer to the main question.
There are many other ways in which this query could be written. The entire query could be written as one top-level grouped query that joins the CUSTOMER and ORDERS table. The join operation could be made explicit with a SQL2 JOIN operator, and then the results of the join could be grouped in the top-level query. As this example shows, one of the benefits of the SQL2 query expression capabilities is that they typically provide several different ways to obtain the same query results.
The general philosophy behind the SQL2 capabilities in this area is that the SQL language should provide the flexibility to express a query in the most natural form. The underlying DBMS must be able to take the query, however expressed, break it down into its fundamentals, and then determine the most efficient way to carry out the query. This internal query execution plan may be quite different than the apparent plan called for by the actual SQL statement, but as long as it produces the same query results, the net effect is to shift the optimization workload from the human user or programmer to the DBMS.
SQL Queries: A Final Summary This concludes the discussion of the SQL queries and the SELECT statement that began in Chapter 6. As described in Chapters 6–9, the clauses of the SELECT statement provide a powerful, flexible set of capabilities for retrieving data from the database. Each clause plays a specific role in data retrieval:
- The FROM clause specifies the source tables that contribute data to the query results. Every column name in the body of the SELECT statement must unambiguously identify a column from one of these tables, or it must be an outer reference to a column from a source table of an outer query.
- The WHERE clause, if present, selects individual combinations of rows from the source tables to participate in the query results. Subqueries in the WHERE clause are evaluated for each individual row.
- The GROUP BY clause, if present, groups the individual rows selected by the WHERE clause into row groups.
- The HAVING clause, if present, selects row groups to participate in the query results. Subqueries in the HAVING clause are evaluated for each row group.
- The SELECT clause determines which data values actually appear as columns in the final query results.
- The DISTINCT keyword, if present, eliminates duplicate rows of query results.
- The UNION operator, if present, merges the query results produced by individual SELECT statements into a single set of query results.
- The ORDER BY clause, if present, sorts the final query results based on one or more columns.
- The SQL2 query expression capabilities add row-valued and table-valued expressions and INTERSECT and EXCEPT operations to the SQL1 capabilities. The fundamental flow of query processing is not changed, but the capability to express queries within queries is greatly enhanced.
 This article is excerpted from MySQL The Complete Reference by Vikram Vaswani (McGraw-Hill, 2003; ISBN 0072224770). Check it out at your favorite bookstore today. Buy this book now. |
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 19 
