Home arrow MySQL arrow Page 3 - Taking a Look at MySQL 4.1

Subqueries As Scalar Values - MySQL

Many of you are still working with earlier versions of the MySQL database. This article takes a look at MySQL 4.1. It is the first of several parts that examine more recent versions of the software. It is excerpted from chapter eight of Beginning MySQL Database Design and Optimization: From Novice to Professional, written by Jon Stephens and Chad Russell (Apress, ISBN: 1590593324).

TABLE OF CONTENTS:
  1. Taking a Look at MySQL 4.1
  2. MySQL 4.1
  3. Subqueries As Scalar Values
  4. Benefits of Subqueries
  5. Other New Features in MySQL 4.1
By: Apress Publishing
Rating: starstarstarstarstar / 11
April 20, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

The simplest use of a subquery is one in which it produces a single scalar value, as in this example:

The subquery, sometimes also referred to as an inner query, is written inside parentheses to distinguish it from the outer query.

This particular example may seem trivial, but it forms the basis for some more useful possibilities, as we’ll see shortly. You should note that a subquery used in this manner (with=or any other relational operator that compares scalar values) must return a single value; otherwise an error will result, as shown here:

If the categories table contained only one row, no error would result from dropping theWHEREclause of the subquery.

Subqueries can also be used in expressions and with SQL functions, as shown in the three examples in the next illustration. In the third of these example queries, you can see that they may also be aliased.

The real power of subqueries begins to become more apparent when we start using them inWHEREclauses. Here’s a simple instance that illustrates a very common scenario:

In the second of the two example queries just shown, we do a join on the categories table in order to obtain the category name. You should notice that the inner query doesn’t require the use of the table aliases from the outer query; it’s autonomous in that respect, as the outer query is concerned only with the value returned by the inner query, and not with any of the tables or columns referenced by the inner query.

It’s also possible in MySQL to reverse the order of the subquery yielding the scalar value and the value you’re comparing it to:

However, you should be aware that this isn’t part of the SQL standards and may not work in other databases, so if portability is a concern, use the value or column name first, followed by the subquery.

There are other ways to accomplish this particular task without the use of the subquery. One of these is to store an intermediate result in application code or to employ a MySQL user variable for the same purpose, like so:

SELECT @pmax := MAX(price) FROM products; SELECT c.name AS category, p.name AS product, p.price
FROM products p
JOIN categories c
ON c.id = p.category_id
WHERE id = @pmax;

Alternatively, you could write something like this:

SELECT c.name AS category, p.name AS product, p.price
FROM products p
JOIN categories c
ON c.id = p.category_id
ORDER BY p.price DESC LIMIT 1;

This at least has the advantage of being a single query. However, neither of these methods is as easy to read and understand as when we use the subquery. There’s also the problem of what happens when more than one product has the maximum price.

You’re not limited to testing for equality in such cases; you can use any comparison that’s appropriate to the type of data returned by the subquery. Suppose you want to promote those products having greater than the average price of all products, and you’d like a list with the names and IDs of the products and their prices. It would also be helpful to have the product categories listed as well. This will require another join on the products and categories tables, with a nested subquery in theWHEREclause, as shown here:

In this example, we did a greater-than test, and the value returned by the subquery was obtained by using the AVG() function within the subquery.


Subqueries That Return Sets

In the last example from the previous section, you may notice that we were still testing against a single scalar value. In the current scenario, this is helpful when we want to compare prices of products against the average price for all products or the price of the largest product overall. How about finding the most expensive product in each category? It turns out that you can also make comparisons against subqueries that return sets of values using the IN, ANY, and SOME operators. In previous versions of MySQL, these operators were useful only with a set of values that you supplied directly. Beginning in MySQL 4.1, the set can be the result of another query.


NOTE
  Remember that the operator IN is equivalent to = ANY or = SOME. We discussed this in Chapter 4; see the “Operators for Working with Sets” section of that chapter if you need to refresh your memory.

For example, you can use a subquery in the WHERE clause of a query, like so:

When usingINor its synonyms, you can putGROUP BYandHAVINGclauses in the subquery, unlike the case with equality and other operators that make direct comparisons between scalar values. Notice that the set must still be one-dimensional; that is, it can return results only from a single column.



 
 
>>> More MySQL Articles          >>> More By Apress Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: