---
title: "Nested Queries in SQL Server"  
description: "Nested Queries in SQL Server"  
author: "AVADHESH PATEL"  
published: 2012-09-17  
updated: 2019-09-07  
canonical: https://www.mindstick.com/articles/1007/nested-queries-in-sql-server  
category: "database"  
tags: ["database"]  
reading_time: 4 minutes  

---

# Nested Queries in SQL Server

Solving complex query calculations used Nested [Queries in SQL](https://www.mindstick.com/forum/33678/sub-queries-in-sql-server) Server. The fact that Access allows you to easily construct [calculated](https://answers.mindstick.com/qa/31699/what-is-sensex-and-how-it-is-calculated) fields based on other calculated fields means that you can produce very complex calculations with great ease. Unfortunately [SQL Server](https://www.mindstick.com/articles/34/create-table-in-microsoft-sql-server) will not allow you to use this technique. If faced with a poorly performing Access Query that uses multiple layered queries, complex calculations and lots of IF logic in the queries, then you can be facing a bit of a nightmare to convert the SQL to a server-side query written in T-SQL. \

For better [understanding](https://yourviews.mindstick.com/view/88474/why-understanding-yourself-is-so-difficult) first you create a [table in SQL](https://www.mindstick.com/forum/205/find-the-all-column-with-schema-for-any-table-in-sql-server) Server and insert some values.

```
-- CREATE DATABASE
CREATE DATABASE OrgPvtLtd
GO
 
-- USE DATABASE
USE OrgPvtLtd
GO
 
-- CREATE TABLE
CREATE TABLE [ORDER DETAILS]
(
[ORDERID] INT IDENTITY(1,1) PRIMARY KEY,
PRODUCTID INT UNIQUE,
UNITPRICE MONEY NOT NULL,
QUANTITY INT NOT NULL
)
GO
 
-- INSERT VALUES
INSERT INTO [ORDER DETAILS](PRODUCTID,UNITPRICE,QUANTITY)
SELECT 100,265.00,2
UNION ALL
SELECT 101,362.00,3
UNION ALL
SELECT 102,635.35,1
UNION ALL
SELECT 103,562.00,5
UNION ALL
SELECT 104,32.00,4
GO
```

Problem Converting Calculations

```
SELECT [Order Details].UnitPrice, [Order Details].Quantity,
[UnitPrice]*[Quantity] AS LinePrice,
0.175*[LinePrice] AS VAT,
[LinePrice]+[VAT] AS TotalPrice
FROM [Order Details]
GO
```

##### Screen Shot

![Nested Queries in SQL Server](https://www.mindstick.com/mindstickarticle/a9c74ebf-83af-423e-a39f-019a48525fb5/images/85c98ab9-4941-40f6-bada-ec06ed280ea7.png)

SQL Server does not allow calculated fields to refer to other calculations. You could try the following restating each calculation, but for complex calculations this can be very difficult.

```
-- ALternate solution
SELECT [Order Details].UnitPrice, [Order Details].Quantity,
[UnitPrice]*[Quantity] AS LinePrice,
0.175*[Quantity] *[UnitPrice] AS VAT,
[Quantity] *[UnitPrice]+ 0.175*[Quantity] *[UnitPrice] AS TotalPrice
FROM [Order Details]
GO
```

##### Screen Shot

![Nested Queries in SQL Server](https://www.mindstick.com/mindstickarticle/a9c74ebf-83af-423e-a39f-019a48525fb5/images/c307e8e9-a071-429c-9dd5-df4a3aef814f.png)

##### Possible Solution

In Access it is common practice to design a query which uses another query, this process of layering queries on top of queries can be reproduced using views in SQL Server. Views have more [limitations](https://www.mindstick.com/interview/121/what-are-the-benefits-and-limitations-of-using-hidden-fields) than Access queries but breaking a query down into a series of steps layered on top of each other is one possible solution. Although in a complex [application](https://www.mindstick.com/articles/12824/calculator-application-in-android) this could involve making what already could be a sequence of views into an even greater layered sequence of views. So what other [alternatives](https://www.mindstick.com/interview/370/what-are-some-alternatives-to-inheritance) are there? \
\
Another [alternative](https://www.mindstick.com/forum/2324/what-is-the-alternative-to-the-mvc) is to use SQL Server [functions](https://www.mindstick.com/forum/34086/jquery-callback-functions), building functions which use other functions, and if you have time this could also provide a possible solution. \
\
A third option would be to utilize joins, below is a partial solution to our problems, but the need here to specify the join criteria can again lead to unwanted complexity.

```
-- Complex solution
SELECT od.[OrderId], od.[ProductId],od.UnitPrice, od.Quantity,LinePrice, 0.175*[LinePrice] AS VAT
FROM [Order Details] AS od
INNER JOIN
(SELECT OrderId, ProductId,UnitPrice, Quantity, [UnitPrice]*[Quantity] AS LinePrice
FROM [Order Details] ) AS JoinedQuery
ON od.[OrderId] = JoinedQuery.[OrderId]
AND od.[ProductId] = JoinedQuery.[ProductId]
GO
```

Screen Shot

![Nested Queries in SQL Server](https://www.mindstick.com/mindstickarticle/a9c74ebf-83af-423e-a39f-019a48525fb5/images/1561a29c-4904-416f-b869-4af7d6f50e34.png)

##### Nested Queries

Whilst subqueries allow a query to be injected into another queries WHERE clause, nested queries allow a query to be injected into another queries FROM clause. This is an extremely elegant method for solving problems which require data to have complex calculations and summaries in a single step. \
\
The example below goes beyond the join solution, to give a full solution based on a three level query. The only syntactical issue is that at each level the query must be given a name using an AS clause.

```
-- Final Solution
SELECT *,[LinePrice]+[VAT] AS TotalPrice FROM
(SELECT *, 0.175*[LinePrice] AS VAT FROM
(SELECT UnitPrice, Quantity, [UnitPrice]*[Quantity] AS LinePrice
FROM [Order Details]
) AS InnerQuery
) AS NextLevel
GO 
```

##### Screen Shot

![Nested Queries in SQL Server](https://www.mindstick.com/mindstickarticle/a9c74ebf-83af-423e-a39f-019a48525fb5/images/1a898aee-7bdf-46f3-bc18-c4ddbfcbdf58.png)

\

---

Original Source: https://www.mindstick.com/articles/1007/nested-queries-in-sql-server

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