A correlated subquery is a subquery that references columns from the outer query. This type of subquery is executed once for each row processed by the outer query, making it dependent on the outer query.

Syntax:

SELECT column1, column2
FROM table_name outer
WHERE EXISTS (
  SELECT 1
  FROM another_table inner
  WHERE inner.column1 = outer.column1 AND condition
);

Example: Finding Employees with Salaries Above the Average of Their Department

SELECT employee_id, first_name, salary, department_id
FROM employees e
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = e.department_id
);

• Explanation: For each employee in the outer query, the subquery calculates the average salary for their department. The outer query retrieves employees whose salary is higher than this average.

• A non-correlated subquery is an independent query that does not depend on the outer query. It is executed once and its result is used by the outer query.
• Non-correlated subqueries are often used to retrieve a single value or a set of values that will be used in the main query’s conditions.

Syntax

SELECT column1, column2
FROM table_name
WHERE column1 IN (SELECT column1 FROM another_table WHERE condition);

Example: To find employees with higher salaries than the average salary

SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

• Explanation: The subquery (SELECT AVG(salary) FROM employees) calculates the average salary of all employees. The outer query retrieves the details of employees whose salary is greater than this average.

• Correlated Subqueries:
  • Refer to a column from the outer query.
  • Are evaluated for each row of the outer query. Executed once for each row processed by the outer query, making it dependent on the outer query.
  • Often used for one-to-many relationships.
• Non-Correlated Subqueries:
  • Independent of the outer query.
  • Executed once and used by the outer query.
  • Can be used to calculate scalar values or create derived tables.

CTEs (Common Table Expressions) are temporary result sets defined within a single SQL query. They provide a way to break down complex queries into smaller, more manageable parts, improving readability and maintainability.

Syntax:

WITH CTE_name AS (
  SELECT column_name1, column_name2, ...
  FROM table_name
  WHERE condition
)
SELECT *
FROM CTE_name;

• CTE: A named temporary result set defined within a single SQL statement. You can reference this named set multiple times within the main query or even in nested CTEs.
• Subquery: A nested query embedded within another SQL statement (usually in the WHERE, SELECT, FROM, or HAVING clauses). Subqueries can only be used once within the main query.

• CTE for complex queries that benefit from breaking down into logical steps, require recursion, or need the same result set referenced multiple times.
• Subquery for simpler tasks like filtering data based on a condition within a single clause (e.g., WHERE clause).

Views in SQL are virtual tables that present a subset of data from one or more underlying tables. They don’t store any actual data, but rather provide a predefined query that can be used to retrieve data.

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name

• Data Abstraction: Hide the complexity of underlying queries.
• Security: Restrict access to specific columns or rows.
• Data Consistency: Ensure data consistency across multiple queries.
• Query Simplification: Simplify complex queries by creating reusable views.

While CTEs and views both provide temporary result sets, they differ in their scope, persistence, and reusability:

Scope:

• CTEs: Exist only within the context of a single query.
• Views: Can be referenced across multiple queries.

Persistence:

• CTEs: Temporary and discarded after the query finishes.
• Views: Stored as database objects, persisted across sessions.

Reusability:

• CTEs: Can be referenced multiple times within a single query.
• Views: Can be referenced in multiple queries.

Updates:

• CTEs: Not updatable.
• Views: Can be updatable or non-updatable, depending on the underlying query.

• Choose CTEs for temporary, ad-hoc calculations.
• Choose Views for frequently used or shared queries.