Difference between revisions of "Subquery and JOIN2"

From SQLZOO
Jump to: navigation, search
 
(5 intermediate revisions by one user not shown)
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='ht'>
 
<div class=params>schema:scott</div>
 
<div class=params>schema:scott</div>
 
<source lang=sql class='tidy'>DROP TABLE orders</source>
 
<source lang=sql class='tidy'>DROP TABLE orders</source>
 
<source lang=sql class='setup'> CREATE TABLE orders(
 
<source lang=sql class='setup'> CREATE TABLE orders(
   employee VARCHAR(40),
+
   customer VARCHAR(40),
   whn DATE(YYYY,MM,DD),
+
   whn VARCHAR(40),
 
   totalitems INTEGER );
 
   totalitems INTEGER );
INSERT INTO orders VALUES ('Jim',(2006,10,10), 5);
+
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-11', 3);
INSERT INTO orders VALUES ('Jim',(2006,10,12), 1);
+
INSERT INTO orders VALUES ('Jim','2006-10-12', 1);
INSERT INTO orders VALUES ('Brian',(2006,10,10), 7);
+
INSERT INTO orders VALUES ('Brian','2006-10-10', 7);
 
</source>
 
</source>
 
<div>
 
<div>
Line 16: Line 17:
 
  FROM orders a
 
  FROM orders a
 
  WHERE a.whn = (
 
  WHERE a.whn = (
   SELECT MAX(whn)
+
   SELECT MAX(totalitems)
 
   FROM orders b
 
   FROM orders b
 
   WHERE a.customer = b.customer)
 
   WHERE a.customer = b.customer)
To make this more efficient a HAVING 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>
 
</div>
<source lang='sql' class='def'>SELECT a.customer, a.whn, a.totalitems
+
<source lang='sql' class='def'>
FROM orders a JOIN orders b ON (a.customer = b.customer)
+
SELECT a.customer, a.whn, a.totalitems
GROUP BY a.customer, a.whn, a.totalitems
+
  FROM orders a JOIN orders b ON (a.customer = b.customer)
HAVING a.whn = MAX(b.whn)</source>
+
  GROUP BY a.customer, a.whn, a.totalitems
 +
  HAVING a.whn = MAX(b.whn)</source>
 
<div class="ecomm e-mysql" style="display: none"></div>
 
<div class="ecomm e-mysql" style="display: none"></div>
 
</div>
 
</div>
 
{{Hacks Ref}}
 
{{Hacks Ref}}

Latest revision as of 15:59, 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(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