In modern data-driven applications, the efficiency and readability of SQL queries can affect performance, maintainability, and developer productivity. AWS Aurora, a fully managed relational database service compatible with MySQL and PostgreSQL, offers several techniques to manage query complexity and optimize performance through: Subqueries, Common table expressions, Temporary Tables, Views, and Materialized views.
Each of these approaches serves different use cases, from simplifying nested logic to organizing reusable queries until persisting precomputed results.
In this article, we will dive into definitions, comparisons, real-world use cases, and examples in AWS Aurora, helping you to distinguish between them and answer when and why to use them.
1.Subquery
It is simply a query nested inside another query, which runs from the inner query to the outer one during execution and produces temporary results.
Used heavily in simple filtering or transformations, especially when the intermediate result is disposable.
The example below clearly explains how to write it in Aurora:
-- Find departments where the average salary exceeds 80,000
SELECT department_id, avg_salary
FROM (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
) dept_avg
WHERE avg_salary > 80000;
As is known, for any nested logic, it can lead to performance issues if it is not optimized well, and by optimization, here we talk about complex joins or using it repeatedly. However, Aurora’s query optimizer can handle many cases efficiently, but excessive nesting can hurt performance.
2. Common Table Expression (CTE)
Always, when redundant code the initial solution comes to mind is to “define it once, use it many”, and it’s exactly what the common table expressions do, it is a named temporary result set defined with the “WITH” clause, and as same as subqueries, both live during query execution.
If we take the same example in a subquery and rewrite it again with CTEs, it will be like this:
-- Find departments where the average salary exceeds 80,000
WITH dept_avg AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT d.department_id, d.avg_salary
FROM dept_avg d
WHERE d.avg_salary > 80000;
As we can observe, the CTE approach is more readable and organized and its value appears more with complex or recursive queries and while combining multiple result sets, making it highly recommended against subqueries that fit with simple queries.
3.Temporary Table
For jumping from execution level approaches to session level duration, the temporary tables are the ones to choose as it is an explicitly created table that persists only during session duration serving when the result set needs to be reused multiple times in the same session. It’s clearly improved performance when working with larger datasets repeatedly, but it requires more permissions than subqueries/CTEs, meaning it requires extra privilege to create tables or alter database/schema objects, unlike others that require only select permission.
Here is an example to clarify how we can use this approach to calculate the same result as previous queries:
-- Temp table example (persisting for the whole session)
CREATE TEMP TABLE dept_avg_temp AS
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
-- You can run multiple queries against the temp table:
SELECT department_id, avg_salary
FROM dept_avg_temp
WHERE avg_salary > 80000;
-- (When done) optionally drop it
DROP TABLE IF EXISTS dept_avg_temp;
4. View
A “View” is a virtual table defined by a query. It simplifies the query but does not store data, can be considered as a blueprint to provide a layer of abstraction and encapsulation for reuse, besides simplifying maintenance and consistency, even if the performance is not improved.
Below is an example of how to use “views” in Aurora:
-- Create a view of department's average salary
CREATE VIEW dept_avg_view AS
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
-- Usage of them
SELECT *
FROM dept_avg_view
WHERE avg_salary > 80000;
5. Materialized View
The “Materialized View” stores the results of query physically unlike standard view, and effectively used for precomputing expensive aggregations (e.g., reporting dashboards) which speeds up repeated queries on large datasets, this concept is supported in AWS Aurora PostgreSQL, but not Aurora MySQL, but it can be simulated by a table and scheduled refresh with permission in both for creation part. On the persistence side views mainly are permanent until dropped as definition (views/ materialized views), and refresh stored result data for materialized views, the refresh MV frequency is a controversial topic and heavily based on the use case, because high refresh rate can hurt performance with massive dataset and low rate can break the consistency of old snapshot data and being behind the recent result.
Below is an example of how to write a “Materialized view” for our result
-- Create a materialized view
CREATE MATERIALIZED VIEW dept_avg_mv AS
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
-- Query it like a normal table
SELECT *
FROM dept_avg_mv
WHERE avg_salary > 80000;
-- Refresh when data changes
REFRESH MATERIALIZED VIEW dept_avg_mv;
The table below summarizes the differences between these approaches:
| Technique | Persistence | Permissions | Best For | Aurora Notes |
|---|---|---|---|---|
| Subqueries | Execution only | Fewer | Simple one-off queries | Can degrade performance if deeply nested |
| CTEs | Execution only | Fewer | Readability, recursive queries | Inline execution not materialized |
| Temporary Tables | Session duration | More | Reuse of intermediate results | Useful for large intermediate sets |
| Views | Permanent until dropped | More | Abstraction maintainability and query reuse | No inherent performance gains |
| Materialized Views | Permanent until dropped, stored results | More | Performance optimization via caching | Aurora PostgreSQL only |
Note: Here, we are using basic comparative examples to focus on demonstrating the differences between approaches on the same idea and results. I encourage you to go a step further and experiment with these methods using more complex queries in order to put this information to use and broaden the true significance of these elements.
Conclusion:
In AWS Aurora, selecting between Subqueries, CTEs, Temporary tables, Views, and Materialized Views is all about balancing readability, reuse, and performance, based on business needs.
The flow of selecting the best approach begins with Subqueries or CTEs for simplicity, like infrequent queries, but if the intermediate results are reused multiple times through the session, we can switch to Temporary tables. However, views are superior for maintainability and abstraction, but not speed. Finally, the materialized view caching solution is performance-critical for high-volume and computation reporting queries, which can be utilized effectively in Aurora PostgreSQL.
Top comments (0)