Subqueries in SQL Server

A subquery is a query inside another query.

You will learn:
  - Scalar subquery  (returns 1 value)
  - Multi-row subquery with IN / NOT IN / ANY / ALL
  - Subquery in SELECT, FROM (derived table), WHERE, HAVING
  - EXISTS vs IN, and the NOT IN + NULL trap

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Topic table: Students and Exams (a second small dataset)

DROP TABLE IF EXISTS dbo.ExamScores;
DROP TABLE IF EXISTS dbo.Students;

CREATE TABLE dbo.Students
(
    StudentID   INT PRIMARY KEY,
    StudentName VARCHAR(50) NOT NULL,
    ClassName   VARCHAR(10) NOT NULL,
    MentorID    INT NULL          -- refers to Employees.EmployeeID, NULL for some
);

INSERT INTO dbo.Students (StudentID, StudentName, ClassName, MentorID) VALUES
(1, 'Aarav',   '10-A', 14),
(2, 'Diya',    '10-A', 14),
(3, 'Ishaan',  '10-B', 15),
(4, 'Kiara',   '10-B', NULL),
(5, 'Vihaan',  '10-A', 11),
(6, 'Anaya',   '10-B', 15),
(7, 'Reyansh', '10-A', NULL),
(8, 'Myra',    '10-B', 11);

CREATE TABLE dbo.ExamScores
(
    StudentID INT NOT NULL REFERENCES dbo.Students(StudentID),
    Subject   VARCHAR(20) NOT NULL,
    Marks     INT NOT NULL,
    PRIMARY KEY (StudentID, Subject)
);

INSERT INTO dbo.ExamScores (StudentID, Subject, Marks) VALUES
(1,'Maths',95),(1,'Science',88),(1,'English',76),
(2,'Maths',67),(2,'Science',72),(2,'English',91),
(3,'Maths',88),(3,'Science',95),(3,'English',64),
(4,'Maths',45),(4,'Science',58),(4,'English',70),
(5,'Maths',99),(5,'Science',81),(5,'English',85),
(6,'Maths',72),(6,'Science',66),(6,'English',88),
(7,'Maths',55),(7,'Science',49),(7,'English',62);
-- Student 8 (Myra) has no exam scores
GO

(8 rows affected)
(21 rows affected)

Part A: Scalar Subqueries (return exactly one value)

Employees earning more than the company average salary

SELECT FirstName, Salary
FROM dbo.Employees
WHERE Salary > (SELECT AVG(Salary) FROM dbo.Employees);

Show each employee's salary next to the company average and the difference

SELECT FirstName, Salary,
       (SELECT AVG(Salary) FROM dbo.Employees)          AS CompanyAvg,
       Salary - (SELECT AVG(Salary) FROM dbo.Employees) AS DiffFromAvg
FROM dbo.Employees;

The most expensive product

SELECT ProductName, UnitPrice
FROM dbo.Products
WHERE UnitPrice = (SELECT MAX(UnitPrice) FROM dbo.Products);

Second highest salary (classic interview question)

SELECT MAX(Salary) AS SecondHighestSalary
FROM dbo.Employees
WHERE Salary < (SELECT MAX(Salary) FROM dbo.Employees);

Latest order date and the order(s) placed on that day

SELECT OrderID, CustomerID, OrderDate
FROM dbo.Orders
WHERE OrderDate = (SELECT MAX(OrderDate) FROM dbo.Orders);

Part B: Multi-row Subqueries (in, NOT IN, Any, All)

In: employees working in departments located in Hyderabad

SELECT FirstName, DepartmentID
FROM dbo.Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM dbo.Departments WHERE Location = 'Hyderabad');

In: products that were ever ordered

SELECT ProductName
FROM dbo.Products
WHERE ProductID IN (SELECT ProductID FROM dbo.OrderItems);

NOT IN: customers who never ordered

SELECT CustomerName
FROM dbo.Customers
WHERE CustomerID NOT IN (SELECT CustomerID FROM dbo.Orders);

The NOT IN + NULL Trap !!!

Students.MentorID has NULLs. This returns ZERO rows (surprise!) because "x NOT IN (1, 2, NULL)" is UNKNOWN, never TRUE.

SELECT FirstName
FROM dbo.Employees
WHERE EmployeeID NOT IN (SELECT MentorID FROM dbo.Students);

--     Fix 1 : filter NULLs inside the subquery
SELECT FirstName
FROM dbo.Employees
WHERE EmployeeID NOT IN (SELECT MentorID FROM dbo.Students WHERE MentorID IS NOT NULL);

--     Fix 2 (recommended) : use NOT EXISTS, it is NULL-safe
SELECT e.FirstName
FROM dbo.Employees AS e
WHERE NOT EXISTS (SELECT 1 FROM dbo.Students AS s WHERE s.MentorID = e.EmployeeID);

> All: salary greater than Every salary in the Sales department

SELECT FirstName, Salary
FROM dbo.Employees
WHERE Salary > ALL (SELECT Salary FROM dbo.Employees WHERE DepartmentID = 2);

> Any: salary greater than at Least One salary in the It department

SELECT FirstName, Salary
FROM dbo.Employees
WHERE Salary > ANY (SELECT Salary FROM dbo.Employees WHERE DepartmentID = 4)
  AND DepartmentID <> 4;

Part C: Subquery in from (Derived Table)

Average of department averages

SELECT AVG(DeptAvg) AS AvgOfDeptAverages
FROM (
        SELECT DepartmentID, AVG(Salary) AS DeptAvg
        FROM dbo.Employees
        WHERE DepartmentID IS NOT NULL
        GROUP BY DepartmentID
     ) AS d;    -- a derived table MUST have an alias

Order totals, then pick orders above 1 lakh

SELECT t.OrderID, t.OrderTotal
FROM (
        SELECT OrderID, SUM(LineTotal) AS OrderTotal
        FROM dbo.OrderItems
        GROUP BY OrderID
     ) AS t
WHERE t.OrderTotal > 100000
ORDER BY t.OrderTotal DESC;

Student average marks with class average side by side

SELECT s.StudentName, s.ClassName, sa.AvgMarks, ca.ClassAvg
FROM dbo.Students AS s
JOIN (SELECT StudentID, AVG(Marks * 1.0) AS AvgMarks FROM dbo.ExamScores GROUP BY StudentID) AS sa
     ON sa.StudentID = s.StudentID
JOIN (SELECT st.ClassName, AVG(es.Marks * 1.0) AS ClassAvg
      FROM dbo.ExamScores es JOIN dbo.Students st ON st.StudentID = es.StudentID
      GROUP BY st.ClassName) AS ca
     ON ca.ClassName = s.ClassName;

Part D: Subquery in HAVING

Departments whose average salary is above the company average

SELECT DepartmentID, AVG(Salary) AS DeptAvg
FROM dbo.Employees
GROUP BY DepartmentID
HAVING AVG(Salary) > (SELECT AVG(Salary) FROM dbo.Employees);

Customers who placed more orders than the average customer

SELECT CustomerID, COUNT(*) AS OrderCount
FROM dbo.Orders
GROUP BY CustomerID
HAVING COUNT(*) > (SELECT AVG(Cnt * 1.0)
                   FROM (SELECT COUNT(*) AS Cnt FROM dbo.Orders GROUP BY CustomerID) AS x);

Part E: Subqueries in INSERT / UPDATE / DELETE

DROP TABLE IF EXISTS dbo.HighValueCustomers;
CREATE TABLE dbo.HighValueCustomers (CustomerID INT PRIMARY KEY, CustomerName VARCHAR(100), TotalSpent DECIMAL(14,2));

INSERT ... SELECT with subquery

INSERT INTO dbo.HighValueCustomers (CustomerID, CustomerName, TotalSpent)
SELECT c.CustomerID, c.CustomerName,
       (SELECT SUM(oi.LineTotal)
        FROM dbo.Orders o JOIN dbo.OrderItems oi ON oi.OrderID = o.OrderID
        WHERE o.CustomerID = c.CustomerID)
FROM dbo.Customers AS c
WHERE c.CustomerID IN (SELECT CustomerID FROM dbo.Orders GROUP BY CustomerID HAVING COUNT(*) >= 5);

SELECT * FROM dbo.HighValueCustomers ORDER BY TotalSpent DESC;

(6 rows affected)

UPDATE with subquery (inside a transaction so we can undo it)

BEGIN TRAN;
    UPDATE dbo.Products
    SET UnitPrice = UnitPrice * 0.90      -- 10% off for products never ordered
    WHERE ProductID NOT IN (SELECT ProductID FROM dbo.OrderItems);

    SELECT ProductID, ProductName, UnitPrice FROM dbo.Products WHERE ProductID IN (19, 20);
ROLLBACK TRAN;   -- undo, keep data clean

(2 rows affected)

Practice Questions

1. Find the student(s) with the highest Maths marks.
2. List products whose price is above the average price of their category (try with derived table).
3. Find employees who are managers (their ID appears in ManagerID).
4. Find the third highest salary using subqueries only.
5. List customers whose total order count equals the maximum order count of any customer.