Master SQL Server From Scratch

Clear, interactive, and structured SQL Server lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to SQL Server
Introduction to SQL Server SQL Server Installation SQL Server Fundamentals
2. Data Types
Data Types Numeric Data Types Character & String Data Types Date & Time Data Types Other SQL Server Data Types
3. Database & Tables
Database & Tables Introduction Creating a Database Creating Tables Table Structure Altering Tables Dropping Tables
4. Constraints
Constraints Introduction PRIMARY KEY FOREIGN KEY UNIQUE Constraint NOT NULL & DEFAULT CHECK Constraint
5. SQL Basics
SQL Basics Introduction SQL Commands SQL Command Categories SELECT Statement INSERT, UPDATE & DELETE
6. CRUD Operations
CRUD Operations Introduction INSERT Operation SELECT Operation UPDATE Operation DELETE Operation
7. SQL Queries
Queries Introduction WHERE Clause ORDER BY Clause DISTINCT LIKE Operator IN, BETWEEN & IS NULL
8. Joins
Joins Introduction INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN CROSS JOIN
9. SQL Functions
SQL Functions Introduction String Functions Numeric Functions Date & Time Functions Conversion Functions NULL Functions
10. Grouping & Aggregate Functions
GROUP BY Aggregate Functions COUNT() SUM() AVG() MIN() & MAX()
11. Subqueries
Subqueries Introduction Single-Row Subquery Multiple-Row Subquery Correlated Subquery Subquery with DML
12. Views
SQL Views Introduction Creating a View Updating a View Altering a View Dropping a View
13. Stored Procedures
Stored Procedures Introduction Creating a Stored Procedure Input Parameters Output Parameters Executing & Modifying Stored Procedures
14. Triggers
Triggers Introduction AFTER INSERT Trigger AFTER UPDATE Trigger AFTER DELETE Trigger INSTEAD OF Trigger
15. Transactions
Transactions Introduction COMMIT ROLLBACK SAVEPOINT Transaction with TRY...CATCH
16. Indexes
Indexes Introduction Clustered Index Non-Clustered Index Creating & Dropping Indexes Index Performance
17. Common Table Expressions
CTE Introduction Simple CTE CTE with JOIN CTE with Aggregate Functions Recursive CTE
18. Window Functions
Window Functions Introduction ROW_NUMBER() RANK() and DENSE_RANK() LEAD() and LAG() PARTITION BY
19. Advanced SQL
Advanced SQL Introduction Temporary Tables Table Variables Dynamic SQL Error Handling Performance Optimization

Multiple-Row Subquery

Learn how to use a subquery that returns multiple rows or multiple values.

What is a Multiple-Row Subquery?

A Multiple-Row Subquery is a subquery that returns more than one row or value.

Since the subquery can return multiple values, operators such as IN, ANY and ALL are commonly used with it.

Basic Syntax


SELECT column_name
FROM table_name
WHERE column_name IN
(
    SELECT column_name
    FROM another_table
    WHERE condition
);
            

The inner query returns multiple values and the outer query uses those values to filter records.

Example Tables

Consider the following Employees table:

EmployeeId EmployeeName DepartmentId Salary
1 Rahul 101 30000
2 Priya 102 45000
3 Amit 101 50000
4 Sneha 103 40000
5 Neha 102 55000

And consider the following Departments table:

DepartmentId DepartmentName Location
101 IT Pune
102 HR Pune
103 Finance Mumbai

Example 1: Multiple-Row Subquery with IN

Suppose we want to find employees who work in departments located in Pune.


SELECT EmployeeName, DepartmentId
FROM Employees
WHERE DepartmentId IN
(
    SELECT DepartmentId
    FROM Departments
    WHERE Location = 'Pune'
);
            
How it works
  1. The inner query finds department IDs where the location is Pune.
  2. The inner query can return multiple department IDs.
  3. The outer query checks whether Employee DepartmentId exists in those returned values.

Example 2: Multiple-Row Subquery with NOT IN

The NOT IN operator can be used when we want records that do not match any value returned by the subquery.


SELECT EmployeeName, DepartmentId
FROM Employees
WHERE DepartmentId NOT IN
(
    SELECT DepartmentId
    FROM Departments
    WHERE Location = 'Pune'
);
            

This query returns employees who do not belong to departments located in Pune.

Example 3: Using ANY

The ANY operator compares a value with the values returned by a subquery.

For example, find employees whose salary is greater than the salary of ANY employee in department 101.


SELECT EmployeeName, Salary
FROM Employees
WHERE Salary > ANY
(
    SELECT Salary
    FROM Employees
    WHERE DepartmentId = 101
);
            

The condition is true when the comparison is true for at least one value returned by the subquery.

Example 4: Using ALL

The ALL operator compares a value with every value returned by the subquery.

Find employees whose salary is greater than the salary of ALL employees in department 101.


SELECT EmployeeName, Salary
FROM Employees
WHERE Salary > ALL
(
    SELECT Salary
    FROM Employees
    WHERE DepartmentId = 101
);
            

The condition must be true for every value returned by the subquery.

IN vs ANY vs ALL

Operator Purpose
IN Checks whether a value matches any value returned by the subquery.
ANY The comparison must be true for at least one value returned by the subquery.
ALL The comparison must be true for every value returned by the subquery.

Important Points

  • A multiple-row subquery can return more than one row.
  • The IN operator is commonly used with multiple-row subqueries.
  • NOT IN can be used to exclude matching values.
  • ANY checks whether the comparison is true for at least one returned value.
  • ALL checks whether the comparison is true for every returned value.
  • Using = with a subquery that returns multiple rows can cause an error.

Summary

A Multiple-Row Subquery returns multiple values or rows. Operators such as IN, NOT IN, ANY and ALL can be used to work with the returned values. Multiple-row subqueries are useful when the result of one query needs to be compared with a set of values.