What types of SQL subqueries in SQL Server

Question

Ajay Bansode · Answer

1. Based on the Number of Rows Returned a. Scalar Subquery Returns a single value (one row, one column). Often used in SELECT , WHERE , or SET . b. Row Subquery Returns one row with multiple columns . Used with IN , = , or in tuple comparison. c. Table Subquery Returns multiple rows and columns . Often used with IN , EXISTS , JOIN . 2. Based on Location in Query a. Subquery in SELECT Clause Used to calculate values for each row. b. Subquery in FROM Clause (Derived Table or Inline View) Acts as a temporary table. c. Subquery in WHERE Clause Filters records based on another query’s result. 3. Based on Use of Correlation a. Non-Correlated Subquery Independent of the outer query; runs once. b. Correlated Subquery Depends on data from the outer query; runs once per outer row . 4. Based on Usage with Operators IN : Matches any value in a list ANY / SOME : Matches any one of the values returned ALL : Must satisfy the condition for all values EXISTS : True if subquery returns at least one row

Sandhiya Priya · Answer

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

What types of SQL subqueries in SQL Server

What types of SQL subqueries in SQL Server

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (2)