DEFINITIONS:
Subquery
- This is a query nested within another query.
- Subqueries are enclosed in parentheses ( ) and can be used in:
SELECT clause
Subqueries in the SELECT clause are used to add an extra column to the result set by calculating a value based on another query.
sql select first_name, last_name,
(select count(*)
from mental_health_visits
where mental_health_visits.patient_id=mental_health_patients.patient_id) as total_visits
from mental_health_patients;
FROM clause
Subqueries in the FROM clause are used to create a temporary table (called a derived table) that the outer query can use.
sql select diagnosis, total_visits
from (select diagnosis,count(visit_id) as total_visits
from mental_health_patients
join mental_health_visits on mental_health_patients.patient_id=mental_health_visits.patient_id
group by diagnosis)
as diagnosis_visits;
WHERE clause
Subqueries in the WHERE clause are used to filter records based on the result of another query. It can be used to compare values dynamically.
sql select first_name, last_name
from mental_health_patients
where patient_id in --in removes duplicates
(select patient_id
from mental_health_visits
join mental_health_doctors on mental_health_visits.doctor_id=mental_health_doctors.doctor_id
where mental_health_doctors.specialization = 'Psychologist');
CORRELATED SUBQUERIES
A correlated subquery is a subquery that references columns from the outer query. The subquery is executed for each row processed by the outer query.
sql select first_name, last_name
from mental_health_patients
where (
select count(*)
from mental_health_visits
where mental_health_visits.patient_id=mental_health_patients.patient_id
and mental_health_visits.visit_type= 'Emergency')>1;
CTE(Common Table Expressions)
- A Common Table Expression (CTE) is a temporary result set in SQL that can be referred to within a SELECT, INSERT, UPDATE, or DELETE query.
- CTEs improve the readability and maintainability of queries by allowing you to break down complex queries into modular components.
sql WITH cte_name AS (
SELECT column1, column2
FROM table_name
WHERE conditions
)
SELECT *
FROM cte_name;
Stored procedure
- A stored procedure is a precompiled collection of one or more SQL statements that are stored in the database and can be executed as a single unit. It acts like a reusable function for the database.
- Stored in the database — once created, it can be used multiple times.
- Reusable — called whenever needed without rewriting the SQL code.
- Can accept parameters — to handle different input values.
- Supports control flow — like IF, WHILE, LOOP, etc.
- Improves performance — reduces the need to send multiple queries over the network.
sql create procedure GetAllCustomers
as
begin
select * from students;
end;
To execute
sql EXEC GetAllCustomers;
DIFFERENCES
- A subquery, a CTE (Common Table Expression), and a stored procedure are all tools used in SQL to handle data and improve query structure, but they serve different purposes and function in different ways.
A subquery is a query placed inside another SQL query, often used within the SELECT, FROM, or WHERE clauses.
It helps retrieve intermediate results to be used by the main query. Subqueries are temporary, cannot be reused, and do not support logic like conditions or loops.
They are typically used for filtering or deriving values within a single SQL statement.A CTE, or Common Table Expression, is also a temporary result set, but it is defined using the WITH clause at the beginning of a query. Unlike subqueries, CTEs can make complex queries easier to read and manage.
They allow you to break a large query into smaller, logical parts. Although CTEs cannot be reused outside the query they are defined in, they are especially useful when dealing with recursion or repeated references within the same query.In contrast, a stored procedure is a precompiled and saved block of SQL code stored in the database.
It can be reused multiple times, accepts parameters, and supports advanced logic such as conditional statements and loops.
Stored procedures are ideal for tasks that need to be repeated often or for encapsulating business rules in a secure and centralized way.
To summarize: subqueries are best for quick, inline filtering; CTEs are great for simplifying and organizing complex queries; and stored procedures are powerful tools for reusability, automation, and procedural logic in the database.
Top comments (0)