Fidan Baghirzade
Subquery
vs
CTE
Syntax:
SELECT column1, column2 WITH cte_name AS (
FROM SELECT column1, column2
(SELECT column1, column2 FROM table_name
FROM table_name WHERE condition)
WHERE condition) AS SELECT column1, column2
alias_name; FROM cte_name;
Fidan Baghirzade
Subquery
SELECT emp_name
FROM
(SELECT emp_name, AVG(salary) AS salary
FROM employee
GROUP BY emp_name)
WHERE salary > 6000
Output:
emp_name
Andrew
A subquery is a query that appears inside another query
statement.
It allows you to retrieve data based on the results of another
query.
Subqueries can be used in SELECT, FROM, WHERE or JOIN clauses.
Fidan Baghirzade
CTE
WITH AverageSalary AS (
SELECT emp_name, AVG(salary) AS avg_salary
FROM employee
GROUP BY emp_name)
SELECT emp_name
FROM AverageSalary
WHERE avg_salary > 6000;
Output:
emp_name
Andrew
CTE (Common Table Expression) is like a temporary table that you
can define within a query.
Can be referenced within the scope of a single SELECT, INSERT,
UPDATE, or DELETE statement.
CTEs are useful for breaking down complex queries into smaller,
more manageable parts.
Fidan Baghirzade
Conclusion:
In SQL, both Subqueries and Common Table Expressions
(CTEs) are tools used for tackling complex queries or
manipulating data. However, they vary in their syntax and
how they are applied in practice.
CTEs are much more readable than subqueries when
you're writing a complex report. A CTE can be used many
times within a query, whereas a subquery can only be
used once.
With subqueries in SQL, you can incorporate them into a
WHERE clause using keywords like IN or EXISTS.
However, this capability isn't available with CTEs.
