Home > DeveloperSection > Articles > Nested Queries in SQL Server

Nested Queries in SQL Server


Database Database 
Ratings:
0 Comment(s)
 2724  View(s)
Rate this:

Nested Queries in SQL Server

Solving complex query calculations used Nested Queries in SQL Server. The fact that Access allows you to easily construct calculated fields based on other calculated fields means that you can produce very complex calculations with great ease. Unfortunately 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 first you create a table in SQL 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

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

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 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 this could involve making what already could be a sequence of views into an even greater layered sequence of views. So what other alternatives are there?

Another alternative is to use SQL Server 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

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


Don't want to miss updates? Please click the below button!

Follow MindStick