What is a subquery?

A subquery (also known as a nested query) is a query that is embedded inside another query. It is used to perform operations that require the result of one query to be used in another query, often within the WHERE, FROM, or SELECT clauses.

Key Characteristics of a Subquery:

1. Encapsulated in Parentheses: A subquery is always enclosed in parentheses.
2. Executed First: The subquery is executed first, and its result is used by the outer query.
3. Can Return Single or Multiple Values: A subquery can return a single value (scalar), a list of values (e.g., a list of IDs), or a table-like result.

Types of Subqueries:

1. Scalar Subquery:

   • Returns a single value (one column, one row).

   • Used in situations where the outer query expects a single value.

   • Example:

     SELECT name, salary
     FROM employees
     WHERE salary > (SELECT AVG(salary) FROM employees);  -- Subquery returns a single value (average salary)
     

2. Column Subquery:

   • Returns a list of values (a single column).

   • Used in the IN clause to filter rows based on multiple values.

   • Example:

     SELECT name
     FROM employees
     WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');  -- Subquery returns multiple department IDs
     

3. Row Subquery:

   • Returns a set of columns (multiple columns and a single row).

   • Used in the WHERE clause with operators like =, >, <, etc., to compare entire rows.

   • Example:

     SELECT name, salary
     FROM employees
     WHERE (department_id, job_id) = (SELECT department_id, job_id FROM jobs WHERE job_title = 'Manager');  -- Subquery returns multiple columns
     

4. Table Subquery (or Inline View):

   • Returns a table (multiple rows and columns).

   • Used in the FROM clause, where the subquery essentially acts as a temporary table.

   • Example:

     SELECT department_id, AVG(salary) AS avg_salary
     FROM (SELECT department_id, salary FROM employees WHERE salary > 50000) AS high_salary_employees
     GROUP BY department_id;  -- Subquery returns a table of department_id and salary
     

Types of Subqueries Based on Use:

1. Correlated Subquery:

   • A correlated subquery refers to the outer query. It uses columns from the outer query in its own conditions.

   • The subquery is executed once for each row selected by the outer query.

   • Example:

     SELECT name, salary
     FROM employees e1
     WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);  -- Subquery references the outer query's department_id
     

2. Non-correlated Subquery:

   • A non-correlated subquery does not reference any columns from the outer query. It is independent of the outer query and can be executed on its own.

   • Example:

     SELECT name, salary
     FROM employees
     WHERE salary > (SELECT AVG(salary) FROM employees);  -- Subquery is independent of the outer query
     

Subquery Performance:

• Subqueries can sometimes lead to inefficient queries, especially when used in the WHERE or FROM clauses.
• For better performance, consider replacing subqueries with joins or common table expressions (CTEs) when applicable.

Example Using a Subquery:

-- List employees who earn more than the average salary
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

In this case:

• The subquery (SELECT AVG(salary) FROM employees) calculates the average salary.
• The outer query selects the names and salaries of employees whose salary is greater than the average.

Subqueries are useful for breaking down complex queries, but it's important to consider their performance impact in large datasets.