What types of SQL subqueries in SQL Server

In SQL Server, subqueries are queries nested inside another SQL statement — such as inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery.

Subqueries are powerful because they allow you to perform complex operations in multiple steps.
Let’s go through the types of subqueries clearly 

 1. Based on Number of Rows Returned

a) Single-row Subquery

• Returns only one row.

• Used with comparison operators like =, <, >, <=, >=, <>.

Example:

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

 The inner query returns a single average value.

b) Multi-row Subquery

• Returns multiple rows.

• Used with operators like IN, ANY, ALL.

Example:

SELECT Name
FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = 'Chennai');

 The inner query returns multiple department IDs.

c) Multi-column Subquery

• Returns multiple columns (can be multiple rows too).

• Used with row value comparisons.

Example:

SELECT EmployeeID, DepartmentID
FROM Employees
WHERE (DepartmentID, Salary) IN
      (SELECT DepartmentID, MAX(Salary) FROM Employees GROUP BY DepartmentID);

 The inner query returns two columns per row — DepartmentID and MAX(Salary).

 2. Based on Placement in SQL Statement

a) Scalar Subquery (used in SELECT clause)

• Returns a single value used directly in a column expression.

Example:

SELECT Name,
       (SELECT AVG(Salary) FROM Employees) AS AvgSalary
FROM Employees;

b) Column Subquery (used in WHERE clause)

• Compares a column to a list or a single value from the subquery.

Example:

SELECT Name FROM Employees
WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE Name = 'HR');

c) Row Subquery

• Returns an entire row of data, often used in comparison of multiple columns.

Example:

SELECT * FROM Employees e
WHERE (e.DepartmentID, e.Salary) =
      (SELECT DepartmentID, MAX(Salary) FROM Employees GROUP BY DepartmentID HAVING DepartmentID = e.DepartmentID);

 3. Based on Relationship with Outer Query

a) Correlated Subquery

• References a column from the outer query.

• Executed once per row of the outer query.

Example:

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

 The inner query depends on the outer query’s DepartmentID.

b) Non-Correlated Subquery

• Independent of the outer query.

• Executed once and its result is used by the outer query.

Example:

SELECT Name
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

 4. Based on Clause Usage

Subquery Location	Example
SELECT clause	Used to show derived values
FROM clause	Acts as a virtual table (inline view)
WHERE / HAVING clause	Used to filter results
INSERT / UPDATE / DELETE	Used for conditional data changes

Example (FROM subquery):

SELECT dept.DepartmentID, dept.AvgSalary
FROM (SELECT DepartmentID, AVG(Salary) AS AvgSalary
      FROM Employees
      GROUP BY DepartmentID) AS dept;

 Summary Table

Type	Description	Returns
Single-row	Returns only one row	One value
Multi-row	Returns multiple rows	List of values
Multi-column	Returns multiple columns	Multiple columns
Correlated	Depends on outer query	Row-by-row
Non-correlated	Independent query	One-time execution
Inline (FROM)	Used as virtual table	Result set