Types of Sub-Queries
The difference between an uncorrelated sub-query, which runs once on its own, and a correlated sub-query, which runs once for each row of the outer query.
Journeyman · SQL · MySQL · Updated
There are two types of sub-queries: an uncorrelated sub-query and a correlated sub-query.
Uncorrelated Sub-Queries
An uncorrelated sub-query is a nested query that can be run independently of the outer query. Uncorrelated sub-queries are easy to test because you can execute them to verify the result.
An uncorrelated sub-query executes only once; the result is then used as often as needed within the outer query.
For example, if you need to find a category_id by the category_name, your query would look like this:
SELECT category_id
FROM categories
WHERE category_name = 'Dairy Products';
This is a stand-alone query that can be executed. Maybe you have another query where you want to return a list of products by category_id:
SELECT product_name
, unit_price
FROM products
WHERE category_id = 4;
In this example, the data analyst needs to know the category_id to execute the query.
If you know the category_name but not the category_id, you can use a nested sub-query as follows:
SELECT product_name
, unit_price
FROM products
WHERE category_id = (
SELECT category_id
FROM categories
WHERE category_name = 'Dairy Products'
);
Note that this is an uncorrelated sub-query because the query can be executed independently of the outer query. The WHERE clause in the outer query, however, depends on the result of the nested sub-query.
This query will return a list of all products in the 'Dairy Products' category.
This query can also be written with JOIN statements like this:
SELECT p.product_name
, p.unit_price
FROM products AS p
INNER JOIN categories AS c ON p.category_id = c.category_id
WHERE c.category_name = 'Dairy Products'
Notice that the WHERE clause still only filters the values of the categories table, and the JOIN clause still links the category_id of both tables.
Correlated Sub-Query
A correlated sub-query is a nested query that is dependent on data from the outer query. A correlated sub-query executes once for each row in the outer query. This means that correlated sub-queries can potentially be inefficient for large data sets.
The following query uses a correlated sub-query to find the category name of each product.
SELECT product_name
, (
SELECT category_name
FROM categories AS c
WHERE c.category_id = p.category_id
) AS category_name
FROM products AS p
Note that the outer query defines the alias p, which refers to the products table. The nested sub-query depends on the category_id from the outer query so that it knows how to find the correct category_name.
This query can be re-written as a JOIN as follows:
SELECT p.product_name
, c.category_name
FROM products AS p
INNER JOIN categories AS c ON p.category_id = c.category_id
Do it: find the products priced above their own category's average
The pricing team wants to know which products cost more than the average price of their own category. A drink is compared with the other drinks, and a sauce with the other sauces, never with everything.
The products table has each product's product_name, its category_id (1 is Beverages, 2 is Condiments) and its unit_price. It holds these rows (the Tables tab shows the SQL that makes it):
| product_name | category_id | unit_price |
|---|---|---|
| Chai | 1 | 18.00 |
| Chang | 1 | 19.00 |
| Guaraná Fantástica | 1 | 4.50 |
| Sasquatch Ale | 1 | 14.50 |
| Aniseed Syrup | 2 | 10.00 |
| Chef Anton's Cajun Seasoning | 2 | 22.00 |
| Genen Shouyu | 2 | 15.50 |
| Vegie-spread | 2 | 43.90 |
Each category has its own average:
| category_id | Prices | Average |
|---|---|---|
| 1 | 18.00 + 19.00 + 4.50 + 14.50 = 56.00, over 4 products | 14.00 |
| 2 | 10.00 + 22.00 + 15.50 + 43.90 = 91.40, over 4 products | 22.85 |
For example, Chai costs 18.00, more than its category's 14.00, so Chai is on the list. Chef Anton's Cajun Seasoning costs 22.00, which is more than the average of all eight products (about 18.43) but not more than its own category's 22.85, so it isn't.
The starter compares every product with the average of all eight products, so Chef Anton's Cajun Seasoning gets in and Chai doesn't. That's the part to change.
Your query should give exactly these rows, in this order (the Results tab may show 18.00 as 18; it's the same number):
| product_name | unit_price |
|---|---|
| Chai | 18.00 |
| Chang | 19.00 |
| Sasquatch Ale | 14.50 |
| Vegie-spread | 43.90 |
Your job: write one SELECT query on products.
- Show two columns,
product_nameandunit_price, in that order. - Keep only the products whose
unit_priceis more than the averageunit_priceof the products with the samecategory_id. - Work out that average with a correlated sub-query, so each product gets its own category's average.
- Sort the rows by
product_name, A to Z.
Practiced live on the lesson page: write the SQL right there and check it.