When working with SQL, often at times we will encounter subquery, CTE(Common Table Expression) and stored procedure. All these may look similar since they deal with querying and organizing data, but they each serve a different purpose.
1. A subquery
A subquery is a query inside another query. Its often used to fetch intermediate results in the main query.(Think of it as asking a question inside another question)
The main role is to return a set result that the outer query uses to execute.
EXAMPLE
SELECT first_name, last_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
WHERE total_amount > 500
);
When to use the subqueries
- Filtering results (
WHERE,EXISTS,IN) - Calculating aggregated values
The pros of using the subquery is because they are simple and quick to write while the downside is they are harder to read if the queries are complex.
2. Common Table Expression(CTE)
A CTE is defined the WITH clause. It is a temporary result set that exists within the scope of SELECT, INSERT, DELETE or UPDATE. CTEs main role in SQL is to improve readability, maintainability and are great for breaking complex queries into small pieces.
EXAMPLE
WITH customer_totals AS (
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
)
SELECT c.first_name, c.last_name, t.total_spent
FROM customers c
JOIN customer_totals t ON c.customer_id = t.customer_id
WHERE t.total_spent > 500;
When to use
- There is no need to create a separate summary table.
- Always uses up-to-date values.
- Keeps queries dynamic and flexible.
The pros of using improves readability and supports repercussions and the cons not reusable across sessions.
3. Stored Procedure
A stored procedure is SQL code saved in the database, which you can call whenever needed. It can include multiple queries, loops, and conditional logic.
The main role of a stored procedure in SQL is to encapsulate and execute a set of SQL statements and procedural logic as a single, pre-compiled unit within the database.
EXAMPLE
CREATE PROCEDURE GetVIPCustomers()
BEGIN
SELECT c.first_name, c.last_name, SUM(o.total_amount) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id
HAVING total_spent > 500;
END;
The pros of reusable, encapsulates complex operations and the cons are harder to debug.
Top comments (0)