Common Table Expression
(CTE):
A Common Table Expression (CTE) is a
temporary result set that you can reference
within a SELECT, INSERT, UPDATE, or DELETE
statement. It's defined within the scope of a
single SQL query.
Why to use:
CTEs help make complex queries more readable and
maintainable by breaking them into smaller, logical units.
When to use:
Use CTEs when you need to reuse a subquery multiple
times within a larger query or when you want to simplify
complex queries.
Where to use:
CTEs are used within a single query where temporary
results are needed.
Example
WITH TotalSales AS
( SELECT ProductID, SUM(Quantity) AS
TotalQuantity FROM Sales GROUP BY ProductID )
SELECT ProductID, TotalQuantity
FROM TotalSales
WHERE TotalQuantity > 100;
Advantage:
Improves query readability and maintainability.
Disadvantage:
May impact performance for very large datasets
if not used judiciously.
Subquery:
A subquery is a query nested inside another
query. It's enclosed within parentheses and
typically used to return a single value or a set
of values.
Why to use:
Subqueries allow you to break down complex problems
into simpler steps by nesting one query inside another.
When to use:
Use subqueries when you need to perform a calculation or
filter based on the result of another query.
Where to use:
Subqueries are used within the WHERE, HAVING, or
FROM clauses of a SQL statement.
Example
SELECT Name,
(SELECT AVG(Salary) FROM Employees)
AS AvgSalary FROM Employees;
Advantage:
Flexible and can be used in various parts of
a query.
Disadvantage:
Performance may suffer if the subquery returns a
large result set.
Views:
A view is a virtual table based on the result set
of a SELECT query. It behaves like a table but
doesn't store any data itself.
Why to use:
Views simplify complex queries by encapsulating logic
into a reusable object.
When to use:
Use views when you frequently need to access the same
data with different filters or when you want to hide the
complexity of underlying tables.
Where to use:
Views can be used like tables in SELECT, INSERT,
UPDATE, and DELETE statements.
Example
CREATE VIEW HighSales AS
SELECT ProductID, SUM(Quantity) AS
TotalQuantity FROM Sales
GROUP BY ProductID
HAVING
SUM(Quantity) > 100;
Advantage:
Enhances data security by limiting direct access to
underlying tables and promotes code reusability.
Disadvantage:
May impact performance if the underlying query of
the view is complex or inefficient.
Temporary Tables:
Temporary tables are created in the temporary
database and are automatically deleted when
the session ends or the connection is closed.
Why to use:
Temporary tables are useful for storing intermediate
results or for breaking down complex tasks into simpler
steps.
When to use:
Use temporary tables when you need to store temporary
data that's only relevant for the duration of a session or
connection.
Where to use:
Temporary tables are used within the scope of a session
or connection.
Example
CREATE TEMPORARY TABLE TempSales AS
SELECT * FROM Sales
WHERE SaleDate
BETWEEN '2023-01-01' AND '2023-12-31';
Advantage:
Improves performance by reducing the need for
complex joins or subqueries.
Disadvantage:
Temporary tables can lead to resource contention
in highly concurrent environments and may
consume additional memory.
Follow me
RESHARE/REPOST
