Difference between revisions of "Subquery and JOIN2"
| (One intermediate revision by one user not shown) | |||
| Line 17: | Line 17: | ||
FROM orders a | FROM orders a | ||
WHERE a.whn = ( | WHERE a.whn = ( | ||
| − | SELECT MAX( | + | SELECT MAX(totalitems) |
FROM orders b | FROM orders b | ||
WHERE a.customer = b.customer) | WHERE a.customer = b.customer) | ||
| Line 23: | Line 23: | ||
a self join to replace the subquery. | 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.
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)
Hack 10 Converting subqueries into joins
Hack 11 Converting aggregate subqueries into joins
Hack 16 Search for a String across columns
Hack 24 Multiply Across a Result Set
Hack 25.5 Splitting and combining columns
Hack 26 Include the rows your JOIN forgot
Hack 30 Calculate the maximum/minimum of two fields
Hack 33 Get values and subtotals in one shot
Hack 50 Combine tables containing different data
Hack 51/52 Display rows as columns
Hack 55 Import Someone Else's Data
Hack 62 Issue Queries Without Using a Table
Hack 63 Generate rows without tables
Hack 72 Extract a subset of the results
Hack 78 Break it down by Range
Hack 88 Test two values from a subquery
