A faster alternative to correlated sub-queries

I recently had to rewrite a query that was running very slowly. It was taking over 30 seconds.

Upon analyzing it, I saw that it had 2 correlated sub-queries in it, and each was doing a count(*).

This meant that for each row in the main query, the two inner queries were running. In this case, the table the outer query was running in had just been increased from about 300 records to about 1500, so suddenly the query was running slowly. This kind of problem becomes more apparent as the number of rows in the outer query increases.

Here is the original query:

And here is the refactored version:

What I did was turn each of the sub-queries into a derived table, and then joined on those tables. To do this, I needed to have a column to match on in the join.

Because they were from my work, the queries I show here are similar, but not exactly the same as the real ones I used. I changed the table names and other values.

A great resource for learning about derived tables can be found at http://www.mysqltutorial.org/mysql-derived-table.

SQL sub query challenge

Goal: Write a query which will get the first and last name from the customer table, and the total amount of all that customer’s orders.

SELECT first,last
FROM customers C

This will get all the customers.Next we need to write a query that will get the total amount of all orders for one customer.

SELECT SUM(order_total) FROM orders
WHERE cust_id =

Now we need to combine this previous query with the first one. It will be a correlated sub query.

SELECT first,last,
(SELECT SUM(order_total) FROM orders OWHERE O.cust_id = C.id) AS total_order_amt
FROM customers C