What is subquery in SQL Server with example

A subquery in SQL Server is a query within a query. It is used to retrieve data from one table based on the data from another table.

Here is an example of a subquery in SQL Server:

SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary >= (SELECT AVG(Salary) 
                 FROM Employees)

In this example, the subquery SELECT AVG(Salary) FROM Employees retrieves the average salary of all employees. The main query SELECT FirstName, LastName, Salary FROM Employees WHERE Salary >= (SELECT AVG(Salary) FROM Employees) then retrieves the first name, last name, and salary of all employees who have a salary greater than or equal to the average salary retrieved from the subquery.