When used in the FROM clause, a SQL subquery creates a temporary table that can be queried like any other table. These queries are sometimes called derived tables or table expressions because the outer query uses the results of the subquery as a data source.
In this article we look at three different examples of SQL subqueries that are placed into the FROM clause.
Example 1: Calculating Running Totals
Let’s start with a sales theme. We’ll calculate the running totals of sales by date.
First, here’s some sample data:
CREATE TABLE daily_sales (
sale_date DATE,
total_sales DECIMAL(10, 2)
);
INSERT INTO daily_sales (sale_date, total_sales)
VALUES
('2024-07-01', 1000.00),
('2024-07-02', 1500.50),
('2024-07-03', 1200.75),
('2024-07-04', 2000.25),
('2024-07-05', 1800.00),
('2024-07-06', 2100.50),
('2024-07-07', 1900.75);Now, let’s use a subquery in the FROM clause to calculate running totals:
SELECT
ds.sale_date,
ds.total_sales,
SUM(prev.total_sales) AS running_total
FROM
daily_sales AS ds
JOIN (
SELECT sale_date, total_sales
FROM daily_sales
) AS prev ON prev.sale_date <= ds.sale_date
GROUP BY ds.sale_date, ds.total_sales
ORDER BY ds.sale_date;Result:
sale_date total_sales running_total
------------------------ ----------- -------------
2024-07-01T00:00:00.000Z 1000 1000
2024-07-02T00:00:00.000Z 1500.5 2500.5
2024-07-03T00:00:00.000Z 1200.75 3701.25
2024-07-04T00:00:00.000Z 2000.25 5701.5
2024-07-05T00:00:00.000Z 1800 7501.5
2024-07-06T00:00:00.000Z 2100.5 9602
2024-07-07T00:00:00.000Z 1900.75 11502.75
This query uses a self join in the subquery to calculate the running total of sales up to and including each date.
Example 2: Ranking Employees by Department
In this example, we’ll rank employees within their departments based on their salaries.
Let’s create and populate our sample data:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);
INSERT INTO employees (employee_id, first_name, last_name, department, salary)
VALUES
(1, 'John', 'Doe', 'Sales', 50000),
(2, 'Jane', 'Smith', 'Marketing', 55000),
(3, 'Bob', 'Johnson', 'Sales', 48000),
(4, 'Alice', 'Williams', 'HR', 52000),
(5, 'Charlie', 'Brown', 'Marketing', 51000),
(6, 'Diana', 'Davis', 'HR', 53000),
(7, 'Ethan', 'Evans', 'Sales', 49000),
(8, 'Fiona', 'Fisher', 'Marketing', 54000);Now, we’ll use a subquery in the FROM clause to rank employees:
SELECT department, first_name, last_name, salary, salary_rank
FROM (
SELECT
department,
first_name,
last_name,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees
) AS ranked_employees
WHERE salary_rank <= 2
ORDER BY department, salary_rank;Result:
department first_name last_name salary salary_rank
---------- ---------- --------- ------ -----------
HR Diana Davis 53000 1
HR Alice Williams 52000 2
Marketing Jane Smith 55000 1
Marketing Fiona Fisher 54000 2
Sales John Doe 50000 1
Sales Ethan Evans 49000 2
This query uses a window function (in this case, the RANK() function) in the subquery to assign ranks within each department. The outer query then selects only the top two employees from each department.
Example 3: Calculating Average Sales per Category
For our final example, we’ll look at how to calculate the average sales for each product category, but with a twist; we’re only interested in those products that have sold more than the overall average. So we’re looking for the average sales for each product category that has sold more than the average.
Here’s our sample data:
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(50),
category VARCHAR(50),
price DECIMAL(10, 2)
);
CREATE TABLE sales (
sale_id INT PRIMARY KEY,
product_id INT,
sale_date DATE,
quantity INT
);
INSERT INTO products (product_id, product_name, category, price)
VALUES
(1, 'Widget A', 'Widgets', 10.99),
(2, 'Gadget X', 'Gadgets', 24.99),
(3, 'Widget B', 'Widgets', 12.99),
(4, 'Gizmo Y', 'Gizmos', 15.99),
(5, 'Gadget Z', 'Gadgets', 29.99);
INSERT INTO sales (sale_id, product_id, sale_date, quantity)
VALUES
(1, 1, '2024-07-01', 5),
(2, 2, '2024-07-02', 3),
(3, 3, '2024-07-03', 7),
(4, 4, '2024-07-04', 2),
(5, 5, '2024-07-05', 4),
(6, 1, '2024-07-06', 6),
(7, 2, '2024-07-07', 2),
(8, 3, '2024-07-08', 8),
(9, 4, '2024-07-09', 1),
(10, 5, '2024-07-10', 5);Now, let’s use a subquery in the FROM clause to calculate the average sales:
SELECT category, AVG(total_sales) AS avg_category_sales
FROM (
SELECT p.category, p.product_id, SUM(s.quantity * p.price) AS total_sales
FROM products p
JOIN sales s ON p.product_id = s.product_id
GROUP BY p.category, p.product_id
HAVING SUM(s.quantity * p.price) > (
SELECT AVG(quantity * price)
FROM sales
JOIN products ON sales.product_id = products.product_id
)
) AS above_average_sales
GROUP BY category;Result:
category avg_category_sales
-------- ------------------
Gadgets 197.43
Widgets 157.87
This query first calculates the overall average sales, then uses it as a filter in the subquery to include only products with above-average sales (remember, we’re only interested in those products that have sold more than the overall average). Finally, it averages these sales by category.
In this example, not only do we have a subquery in the FROM clause, but that subquery also has its own subquery in the HAVING clause. So we effectively have a subquery within a subquery.
Other Subqueries
SQL subqueries can also be placed in other parts of the query (not just in the FROM clause). For example, they’re often placed in the WHERE clause, but they can also be placed in a HAVING clause, and even in the SELECT list.
See Understanding the SQL Subquery for examples of these different placements.