Nested queries are supported five different places within a SELECT statement:

• WHERE clause
• EXISTS clause within a WHERE clause
• FROM clause
• HAVING clause
• Select-item in the SQL SELECT item list
• Limitations

Omnidex may behave differently depending on the location of the nested query. See Nested Queries in the Optimization section for more details on how Omnidex behaves in different scenarios.

WHERE Clause

The most common nested query is found in the WHERE clause. The subquery results are applied as the right operand in a WHERE clause predicate.

WHERE clause predicates that use subqueries that return a single value can use comparison operators (=, <, >, <=, >=, <>, !=, like).

WHERE left_operand = subquery_result

WHERE clause predicates that use subqueries that return one or more values can use an IN clause.

WHERE left_operand IN (subquery_results)

Example

WHERE clause allows aggregation to become criteria for the outer query.

SELECT * FROM orders WHERE total > (SELECT AVG(total) FROM orders)

Nested query prevents cartesian product between two child tables.

SELECT p.acct, p.company, a.dt, a.tickler, a.initials FROM prospects p, activity a
WHERE p.acct = a.acct AND
p.acct IN (SELECT DISTINCT acct FROM orders WHERE quantity > 1)

EXISTS Clause Within a WHERE Clause

The result of an EXISTS clause as a WHERE clause predicate, is a boolean. If the subquery returns any results, the EXISTS clause evaluates to TRUE, otherwise it is FALSE. No data is returned by the subquery.

WHERE EXIST(subquery)

Example

Return the product number only if there is at least one in inventory.

SELECT PRODUCT_NO FROM PRODUCTS WHERE PRODUCT_NO = 'H672' AND
EXISTS (SELECT PRODUCT_NO FROM INVENTORY WHERE PRODUCT_NO = 'H672')

Return account and company name from prospects only if there is at least one record in the orders table for that prospect.

SELECT acct, company FROM prospects
WHERE acct = 1 and
EXISTS (SELECT acct FROM orders WHERE acct = 1)

FROM Clause

A subquery in a FROM clause uses the results of a subquery as a table. The results of the nested query will be output to an OST (Omnidex Stand-Alone Table), then the outer query will be resolved using the OST.

SELECT * FROM (subquery_results) ...

Because OSTs are not indexed, criteria used in the outer query will not be optimized and must be specified as such, ie. case sensitive.

However, the inner query will be optimized as much as possible. This can be useful for reducing the amount of data that will be used in certain operations.

Example

The inner query totals the sales for products and groups them by customer number and product number. This portion of the query is optimized because of aggregation indexes installed on the orders table. The results are output to an OST.

The outer query displays the product number and performs the min and max aggregations against the sales totals from the inner query, grouped by the product number. Because indexes cannot be installed on an OST, this portion of the query is not optimized. It is still fast, however, because a large portion of the aggregation and sorting were performed in the inner query using the aggregation indexes.

SELECT b.product_no, max(b.order_amount), min(b.order_amount)
FROM (SELECT product_no, sum(amount) order_amount
FROM orders GROUP BY customer_no, product_no) AS b
GROUP BY a.company

HAVING Clause

A nested query can be in a HAVING clause. HAVING clause criteria is processed after the outer query results have been returned. The nested query must return a single column. Can use comparison operators if only a single value will be returned or an IN clause if multiple values could be returned.

HAVING left_operand > subquery_result

HAVING left_operand in (subquery_results)

Example

The inner query returns the average total from all orders in the orders table. The outer query returns the account number and average total from the orders table, grouped by the account number. The HAVING clause filters the results of the outer query using the result from the inner query. Both the inner and outer query are optimized.

SELECT acct, AVG(total) FROM orders GROUP BY acct
HAVING AVG(total) > (SELECT AVG(total) FROM orders)

Select-Item

A nested query can be used as a select-item. The nested query must return a single column and a single row.

SELECT select-item, (SELECT select-item FROM table WHERE criteria) FROM table

Omnidex will not attempt to verify that the nested query results pertain to the outer query results. The queries should be constructed to assure that the results of the subquery are meaningful to the outer query. For example:

SELECT status, order_no,
(SELECT company FROM customers WHERE customer_no = 1)
FROM orders
WHERE customer_no = 2.

This statement is structurally valid but meaningless. The results of the inner query have nothing to do with the outer query results, but the statement will succeed.

Example

The inner query sums the total column from the orders table for the state of Colorado. The result is converted to a literal and returned as a select item in the outer query. Both queries are optimized.

SELECT state, description, (SELECT SUM(total) FROM orders WHERE tax_state = 'CO') FROM states WHERE state='CO'

Limitations

• A nested query cannot contain an ORDER BY clause. This is the SQL 2 standard, not an Omnidex limitation.
• Correlated subqueries are not supported. A correlated subquery is a nested query that depends on the results from the outer query to complete the inner query. Although correlated subqueries are not supported, they can often be rewritten as joins to achieve the same results.
  Correlated Subquery (not supported),
  SELECT p.acct, p.company, p.last_order FROM prospects p
  WHERE p.last_order <(SELECT MAX(order_date) FROM orders o WHERE o.acct=p.acct)
  can be rewritten as a join.
  SELECT p.acct, p.company, p.last_order FROM prospects p, orders o
  WHERE p.acct = o.acct
  GROUP BY p.acct, p.company, p.last_order
  HAVING p.last_order < MAX(o.order_date)
  Omnidex supports the second statement but not the first.
• Nested queries cannot contain set operations. A set operation is a UNION, INTERSECT, EXCEPT. Support for this ability is planned for a future release.

Top