This content originally appeared on DEV Community and was authored by Kipngeno Gregory
In the realm of SQL and relational database management, efficiently retrieving and manipulating data is paramount.
This article elucidates the differences between subqueries, Common Table Expressions (CTEs), and stored procedures.
1. Subquery (Nested Query)
A subquery, or nested query, is a SQL query embedded within the WHERE, FROM, or SELECT clause of another SQL query. Its primary role is to return a result set that the outer query uses for its execution.
Characteristics:
Purpose: To compute a value or set of values for use in a filter, calculation, or as a derived table within a single, primary query.
Scope & Lifetime: The subquery is executed for each row processed by the outer query (in some cases) and its result exists only for the duration of the main query's execution. It is not reusable.
Readability: Can quickly become complex and difficult to read, especially when nested multiple levels deep (often called "nested hell").
Use Case: Find all employees whose salary is above the company average.
SELECT employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
2. Common Table Expression (CTE)
A CTE, defined using the WITH clause, is a temporary named result set that exists only within the scope of a single SELECT, INSERT, UPDATE, or DELETE statement. It is primarily a tool for improving query organization and readability.
Characteristics:
Purpose: To break down complex queries into simpler, logical, and reusable parts. CTEs make queries more readable and maintainable, and they enable recursive queries, which are impossible with standard subqueries.
Scope & Lifetime: The CTE is defined at the beginning of a statement and can be referenced multiple times within that same statement. It is discarded after the statement executes.
Readability: Significantly improves readability by allowing a modular, "step-by-step" approach to building queries.
*Use Case: *
WITH RegionalSales AS (
SELECT region_id, SUM(amount) AS total_sales
FROM orders
GROUP BY region_id
)
SELECT region_name, total_sales
FROM regions r
JOIN RegionalSales rs ON r.id = rs.region_id
WHERE rs.total_sales > 1000000;
3. Stored Procedure
A stored procedure is a precompiled collection of SQL statements and optional logic (variables, conditionals, loops) stored within the database itself. It is executed as a single unit, often to encapsulate a business logic operation.
Characteristics:
Purpose: To encapsulate complex operations, promote code reuse, enhance security, and improve performance. They are used for data manipulation, data definition, and administrative tasks.
Scope & Lifetime: Stored procedures are permanent database objects (like a table or view). They are stored on the server side and persist beyond a single query session.
Readability: Encapsulates business logic, keeping application code cleaner. The logic is centralized within the database.
Use Case:
CREATE PROCEDURE PlaceNewOrder (
IN p_customer_id INT,
IN p_product_id INT,
IN p_quantity INT
)
BEGIN
START TRANSACTION;
INSERT INTO orders (customer_id, order_date) VALUES (p_customer_id, NOW());
INSERT INTO order_items (order_id, product_id, quantity) VALUES (LAST_INSERT_ID(), p_product_id, p_quantity);
COMMIT;
END;
This content originally appeared on DEV Community and was authored by Kipngeno Gregory
Kipngeno Gregory | Sciencx (2025-09-08T05:29:24+00:00) Understanding SQL Constructs: Subqueries, CTEs, and Stored Procedures. Retrieved from https://www.scien.cx/2025/09/08/understanding-sql-constructs-subqueries-ctes-and-stored-procedures-2/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.