SQL-Subquery

A subquery, also known as a nested query or inner query, is a SQL query that is embedded within another SQL query, referred to as the outer query. A subquery is used to retrieve data that the outer query will use for further processing.

Advantages of Subqueries

1. Simplify complex queries by breaking them into smaller parts.
2. Allow dynamic calculations for conditions or selections.
3. Enhance readability for certain queries.

Disadvantages

1. Can be less efficient than joins for large datasets.
2. Nested subqueries can become difficult to manage and debug.

When to Use

• When the result of one query is needed for another query.
• When filtering data dynamically based on calculations or conditions.
• When a simpler alternative to joins is sufficient.

Types of subqueries

1. Single-row subqueries - Return a single value.
2. Multi-row subqueries - Return multiple rows.
3. Correlated subqueries - Depend on the outer query for their execution.

1. Single-Row Subquery

A subquery returning a single value.

Example

SELECT EmployeeName, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

The inner query calculates the average salary. The outer query selects employees with a salary greater than the average.

2. Multi-Row Subquery

A subquery returning multiple values.

Example

SELECT EmployeeName
FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = 'New York');

The inner query retrieves department IDs for departments in New York. The outer query selects employees who work in those departments.

3. Correlated Subquery

A subquery that references columns from the outer query.

Example

SELECT EmployeeName, Salary
FROM Employees e1
WHERE Salary > (SELECT AVG(Salary) FROM Employees e2 WHERE e1.DepartmentID = e2.DepartmentID);

For each employee in the outer query, the subquery calculates the average salary in the same department. The outer query then selects employees whose salary exceeds the department average.