Subquery and JOIN2

From SQLZOO
Revision as of 15:59, 2 August 2012 by Connor (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

In this example you are shown how to convert subqueries containing aggregate functions into JOINs allowing for a more efficient response time from the query.

schema:scott
DROP TABLE orders
 CREATE TABLE orders(
  customer VARCHAR(40),
  whn VARCHAR(40),
  totalitems INTEGER );
INSERT INTO orders VALUES ('Jim','2006-10-10', 5);
INSERT INTO orders VALUES ('Jim','2006-10-11', 3);
INSERT INTO orders VALUES ('Jim','2006-10-12', 1);
INSERT INTO orders VALUES ('Brian','2006-10-10', 7);
SELECT customer, whn, totalitems
FROM orders a
WHERE a.whn = (
 SELECT MAX(totalitems)
 FROM orders b
 WHERE a.customer = b.customer)

To make this query more efficient a HAVING clause can be used with a self join to replace the subquery.

SELECT a.customer, a.whn, a.totalitems
  FROM orders a JOIN orders b ON (a.customer = b.customer)
  GROUP BY a.customer, a.whn, a.totalitems
  HAVING a.whn = MAX(b.whn)
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense