---
title: "What is sub-query in SQL Server? Explain its Properties?"  
description: "What is sub-query in SQL Server? Explain its Properties?"  
author: "Shikhar Arora"  
published: 2019-12-06  
updated: 2023-04-06  
canonical: https://answers.mindstick.com/qa/92515/what-is-sub-query-in-sql-server-explain-its-properties  
category: "database"  
tags: ["database design", "database programming", "database"]  
reading_time: 3 minutes  

---

# What is sub-query in SQL Server? Explain its Properties?

What is sub-[query in SQL](https://www.mindstick.com/forum/160921/help-with-writing-a-recursive-query-in-sql-server) Server. [Explain](https://yourviews.mindstick.com/view/86025/content-created-for-humans-not-for-bots-explain-this-statement) its [Properties](https://www.mindstick.com/articles/23331/nootropics-7-different-types-and-their-unique-properties)

## Answers

### Answer by Mark John

In SQL Server, a subquery is a query that is embedded within another **query**. A subquery can be used to retrieve data that will be used as input for the main query, allowing for more complex queries to be constructed. In this article, we will explore the properties of subqueries in [**SQL Server**](https://www.mindstick.com/articles/65184/how-to-tune-sql-server-dba) and how they can be used to enhance query functionality.

![What is sub-query in SQL Server? Explain its Properties?](https://answers.mindstick.com/questionanswer/6cfdaeda-b0bb-46c5-b396-b459143468d7/images/4d40200b-65bf-465e-a776-1064cbb6005e.jpg)

## Properties of Subqueries:

**Syntax:** A subquery is enclosed within parentheses and is placed within the WHERE clause of the main query. The subquery can be written using any valid **SELECT statement.**

**Purpose:** The purpose of a subquery is to provide a set of values that can be used as input for the main query. The subquery can be used to filter, sort, or group data before it is used in the main query.

**Execution:** A subquery is executed first, and the result set is then used as input for the **main query.** The result set of a subquery can be a single value, a single row, or multiple rows.

**Relationship:** A subquery can be related to the [main query](https://www.mindstick.com/articles/269427/learn-the-best-way-to-learn-sql-server) in various ways. A **subquery** can be used to filter data based on a condition in the main query, or it can be used to retrieve data that is related to the main query.

**Types:** There are two types of subqueries in SQL Server: correlated and non-correlated subqueries. A correlated subquery is executed for each row of the main query, while a **non-correlated subquery** is executed only once.

**Performance:** Subqueries can impact performance, especially if the subquery returns a large result set. Careful consideration should be given to the use of subqueries to ensure that they do not impact [query performance.](https://www.mindstick.com/articles/13003/create-a-simple-cursor-in-sql-server)

**Examples of Subqueries:**\
To better understand subqueries, let's take a look at some examples:

Subquery to Retrieve Data:\
SELECT FirstName, LastName\
FROM Employees\
WHERE EmployeeID IN (\
SELECT EmployeeID\
FROM Orders\
WHERE OrderDate BETWEEN '2022-01-01' AND '2022-12-31'\
)\
In this example, the subquery retrieves all EmployeeIDs from the Orders table where the OrderDate is between '2022-01-01' and '2022-12-31'. The result set of the subquery is then used as input for the main query to retrieve the FirstNames and LastNames of the employees who have placed orders during that time period.

Correlated Subquery:\
SELECT FirstName, LastName\
FROM Employees e\
WHERE EXISTS (\
SELECT *\
FROM Orders o\
WHERE o.EmployeeID = e.EmployeeID\
AND o.OrderDate BETWEEN '2022-01-01' AND '2022-12-31'\
)\
In this example, the subquery is correlated to the main query by the EmployeeID column. The subquery is executed once for each row of the main query to retrieve all orders placed by the employee in the main query during the time period specified. The EXISTS operator is used to check whether there is at least one row returned by the subquery, and the result set of the main query is the **FirstName and LastName** of employees who have placed orders during that time period.

In conclusion, subqueries are a powerful tool in SQL Server that allows for more complex queries to be constructed. They can be used to **retrieve data, filter data, and group data** before it is used in the main query. However, care should be taken to ensure that subqueries are used appropriately to avoid impacting query performance. Understanding the properties of subqueries and how they can be used will enable developers to write more efficient and effective SQL queries.


---

Original Source: https://answers.mindstick.com/qa/92515/what-is-sub-query-in-sql-server-explain-its-properties

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.