SQL Server Subqueries Example with Result

A Subquery is a query written inside another query.
It is enclosed within parentheses ().

1. Create Sample Table

CREATE TABLE Employees
(
    EmpID INT,
    EmpName VARCHAR(50),
    Department VARCHAR(50),
    Salary INT
);

Insert Records

INSERT INTO Employees VALUES
(1, 'Amit', 'IT', 50000),
(2, 'Rohit', 'HR', 35000),
(3, 'Sneha', 'IT', 60000),
(4, 'Pooja', 'Finance', 45000),
(5, 'Rahul', 'HR', 30000);

---

Example 1: Subquery with WHERE Clause

Find employees whose salary is greater than average salary.

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

Result

EmpName	Salary
Amit	50000
Sneha	60000

---

Example 2: Subquery with IN Operator

Find employees working in departments where salary is greater than 50000.

SELECT EmpName, Department
FROM Employees
WHERE Department IN
(
    SELECT Department
    FROM Employees
    WHERE Salary > 50000
);

Result

EmpName	Department
Amit	IT
Sneha	IT

---

Example 3: Subquery with MAX()

Find employee having highest salary.

SELECT EmpName, Salary
FROM Employees
WHERE Salary =
(
    SELECT MAX(Salary)
    FROM Employees
);

Result

EmpName	Salary
Sneha	60000

---

Example 4: Correlated Subquery

Find employees earning more than average salary of their department.

SELECT E1.EmpName, E1.Department, E1.Salary
FROM Employees E1
WHERE Salary >
(
    SELECT AVG(E2.Salary)
    FROM Employees E2
    WHERE E1.Department = E2.Department
);

Result

EmpName	Department	Salary
Sneha	IT	60000
Rohit	HR	35000

---

Types of Subqueries in SQL Server

1. Single Row Subquery
   Returns one row.
2. Multiple Row Subquery
   Returns multiple rows.
3. Correlated Subquery
   Depends on outer query.
4. Nested Subquery
   Subquery inside another subquery.