SQL Subquery

A subquery (also known as an inner query or nested query) is a query embedded within another SQL query. It is used to retrieve data that will be used in the main query (outer query) for filtering, comparison, or other operations. Subqueries can be used in various SQL statements such as SELECT, INSERT, UPDATE, or DELETE.

 

Types of Subqueries

1. Single-row Subquery: Returns a single row and can be used with comparison operators like =, >, <, etc.
2. Multiple-row Subquery: Returns multiple rows and is typically used with operators like IN, ANY, ALL, etc.
3. Correlated Subquery: Refers to columns of the outer query and is evaluated once for each row processed by the outer query.
4. Non-correlated Subquery: Independent of the outer query and can be executed on its own.

 

Syntax of Subquery

SELECT column1, column2, ...
FROM table_name
WHERE column_name operator (SELECT column_name FROM table_name WHERE condition);

• operator: Comparison operators such as =, IN, >, <, etc.
• condition: The condition that is evaluated within the subquery.

 

Example of a Subquery

Consider the following tables:

Employees Table:

Departments Table:

 

Subquery Example

To find the employees who have a salary greater than the average salary of employees in the IT department:

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = 102);

Explanation:

• The inner query (SELECT AVG(salary) FROM employees WHERE department_id = 102) computes the average salary of employees in the IT department.
• The outer query retrieves the employees whose salary is greater than this average.

Output:

 

Types of Subqueries

1. Single-Row Subquery: This returns a single value (one row and one column) and is often used with operators like =, <, >, etc.

Example:

SELECT name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

1. Multiple-Row Subquery: This returns multiple values (multiple rows) and is used with operators like IN, ANY, ALL, etc.

Example:

SELECT name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'IT');

1. Correlated Subquery: This subquery depends on the outer query for its values.

Example:

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

Explanation:

• The inner query uses a reference to the department_id of the current employee (e.department_id), making this a correlated subquery.
• For each employee, the average salary for their department is calculated, and the employee's salary is compared to it.

 

Key Points

• Subqueries can be used in the SELECT, INSERT, UPDATE, and DELETE statements.
• Single-row subqueries return one value, typically used with comparison operators.
• Multiple-row subqueries return multiple values, typically used with operators like IN.
• Correlated subqueries reference columns of the outer query, and are evaluated for each row.
• Non-correlated subqueries are independent of the outer query and can be executed separately.

 

Summary

Subqueries allow you to perform more complex queries by embedding a query inside another. They are useful for comparing values, filtering data, and performing calculations within a query. Subqueries can be single-row or multi-row, correlated or non-correlated, and can be used in various SQL operations.