Writing Single Row Subqueries

A single row subquery is one that returns zero or one row to the outer SQL statement. As you ll see in this section, you may place a subquery in a WHERE clause, a HAVING clause, or a FROM clause of a SELECT statement. You ll also see some errors you might encounter when issuing subqueries.

Subqueries in a WHERE Clause

You may place a subquery in the WHERE clause of another query. Let s take a look at a very simple example of a query that contains a subquery placed in its WHERE clause; notice the subquery is placed within parentheses (...):

  SELECT first_name, last_name   FROM customers   WHERE customer_id =   (SELECT customer_id   FROM customers   WHERE last_name = 'Brown');  FIRST_NAME LAST_NAME ---------- ---------- John Brown 

This example retrieves the first_name and last_name of the row from the customers table whose last_name is Brown. Let s break this query down and analyze what s going on. The subquery in the WHERE clause is as follows :

 SELECT customer_id FROM customers WHERE last_name = 'Brown'; 

This subquery is executed first (and only once) and returns the customer_id for the row whose last_name is Brown. The customer_id for this row is 1, which is passed to the WHERE clause of the outer query. Therefore, the outer query may be considered to be identical to the following query:

 SELECT first_name, last_name FROM customers WHERE customer_id = 1; 

Using Other Single Row Operators

The previous example used the equality operator ( = ) in the WHERE clause. You may also use other comparison operators such as <>, <, >, < = , and > = with a single row subquery. The following example uses > in the outer query s WHERE clause; the subquery uses the AVG() function to get the average price of the products, which is passed to the WHERE clause of the outer query. The final result of the entire query is to get the product_id , name , and price of products whose price is greater than that average price.

  SELECT product_id, name, price   FROM products   WHERE price  >  (SELECT AVG(price)   FROM products);  PRODUCT_ID NAME PRICE ---------- ------------------------------ ----------  1 Modern Science 19.95  2 Chemistry 30  3 Supernova 25.99  5 Z Files 49.99 

Let s break the example down to understand how it works. The following shows the output from the subquery when it s run on its own:

  SELECT AVG(price)   FROM products;  AVG(PRICE) ---------- 19.7308333 

The value of 19.7308333 is used in the WHERE clause of the outer query shown earlier to get the products whose price is greater than that average.

Subqueries in a HAVING Clause

As you saw in Chapter 3, you use the HAVING clause to filter groups of rows. You may place a subquery in a HAVING clause of an outer query. This allows you to filter groups of rows based on the result returned by your subquery.

The following example uses a subquery in the HAVING clause of the outer query. The example retrieves the product_type_id and the average price for products whose average price is less than the maximum of the average for the groups of the same type product type:

  SELECT product_type_id, AVG(price)   FROM products   GROUP BY product_type_id   HAVING AVG(price)  <  (SELECT MAX(AVG(price))   FROM products   GROUP BY product_type_id);  PRODUCT_TYPE_ID AVG(PRICE) --------------- ----------  1 24.975  3 13.24  4 13.99  13.49 

Notice the subquery uses AVG() to first compute the average price for each product type. The result returned by AVG() is then passed to MAX() , which returns the maximum of the averages.

Let s break the example down to understand how it works. The following shows the output from the subquery when it is run on its own:

  SELECT MAX(AVG(price))   FROM products   GROUP BY product_type_id;  MAX(AVG(PRICE)) ---------------  26.22 

This value of 26.22 is used in the HAVING clause of the outer query shown earlier to filter the group s rows to those having an average price less than 26.22. The following query shows a version of the outer query that retrieves the product_type_id and average price of the products grouped by product_type_id :

  SELECT product_type_id, AVG(price)   FROM products   GROUP BY product_type_id;  PRODUCT_TYPE_ID AVG(PRICE) --------------- ----------  1 24.975  2 26.22  3 13.24  4 13.99  13.49 

You can see that that the groups with a product_type_id of 1, 3, 4, and null have an average price less than 26.22. As expected, these are the same groups returned by the query containing the subquery at the start of this section.

Subqueries in a FROM Clause (Inline Views)

You may place a subquery in the FROM clause of an outer query. These types of subqueries are also known as inline views because the subquery provides data inline with the FROM clause. The following simple example retrieves the products whose product_id is less than 3:

  SELECT product_id   FROM   (SELECT product_id   FROM products   WHERE product_id  <  3);  PRODUCT_ID ----------  1  2 

Notice the subquery returns the rows from the products table whose product_id is less than 3 to the outer query, which then retrieves and displays those product_id values. As far as the FROM clause of the outer query is concerned , the output from the subquery is just another source of data.

The next example is more useful and retrieves the product_id and price from the products table in the outer query, and the subquery retrieves the number of times a product has been purchased:

  SELECT prds.product_id, price, purchases_data.product_count   FROM products prds,   (SELECT product_id, COUNT(product_id) product_count   FROM purchases   GROUP BY product_id) purchases_data   WHERE prds.product_id = purchases_data.product_id;  PRODUCT_ID PRICE PRODUCT_COUNT ---------- ---------- -------------  1 19.95 4  2 30 4  3 25.99 1 

Notice the subquery retrieves the product_id and COUNT(product_id) from the purchases table and returns them to the outer query. As you can see, the output from subquery is just another source of data to the FROM clause of the outer query.

A Couple of Errors You Might Encounter

In this section, you ll see some errors you might encounter. Specifically, you ll see that a single row subquery may return a maximum of one row, and you ll see a subquery may not contain an ORDER BY clause.

Single Row Subqueries May Return a Maximum of One Row

If your subquery returns more than one row, you ll get the following error:

ORA-01427: single-row subquery returns more than one row.

For example, the subquery in the following statement attempts to pass multiple rows to the equality operator (=) in the outer query:

 SQL>  SELECT product_id, name  2  FROM products  3  WHERE product_id =  4  (SELECT product_id  5  FROM products  6  WHERE name LIKE '%e%');  (SELECT product_id  * ERROR at line 4: ORA-01427: single-row subquery returns more than one row 

There are nine rows in the products table whose name contains the letter e , and the subquery attempts to pass these rows to the equality operator in the outer query. Since the equality operator can only handle a single row, the query is invalid and an error is returned.

You ll learn how to return multiple rows from a subquery later in the section Writing Multiple Row Subqueries.

Subqueries May Not Contain an ORDER BY Clause

A subquery may not contain an ORDER BY clause. Instead, you must do any ordering in your outer query. For example, the following outer query has an ORDER BY clause at the end that sorts on the product_id column:

  SELECT product_id, name, price   FROM products   WHERE price  >  (SELECT AVG(price)   FROM products)   ORDER BY product_id DESC;  PRODUCT_ID NAME PRICE ---------- ------------------------------ ----------  5 Z Files 49.99  3 Supernova 25.99  2 Chemistry 30  1 Modern Science 19.95