Difference between revisions of "Subquery and JOIN2"

Revision as of 09:55, 2 August 2012

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(whn)
 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)

@@ Line 1: / Line 1: @@
-Converting aggregate subqueries into Join functions.
+In this example you are shown how to convert subqueries containing aggregate functions into
+<code>JOINs</code> allowing for a more efficient response time from the query.
 <div class='ht'>
 <div class=params>schema:scott</div>
@@ Line 19: / Line 20: @@
    FROM orders b
    WHERE a.customer = b.customer)
-To make this more efficient a <code>HAVING</code> clause can be used with
+To make this query more efficient a <code>HAVING</code> clause can be used with
-a self join.
+a self join to replace the subquery.
 </div>
 <source lang='sql' class='def'>SELECT a.customer, a.whn, a.totalitems

Difference between revisions of "Subquery and JOIN2"

Revision as of 09:55, 2 August 2012

Personal tools

Namespaces

Variants

Views

Actions

Search

Reference

Toolbox

Google AdSense