How to Create a Correlated Subquery in SQL Server 2008 R2

This tutorial will show how to create a correlated subquery in SQL Server 2008 R2. Correlated subqueries are subqueries that refer to attributes from the table that appears in the outer query. Since subqueries rely on the outer query, they cannot be invoked independently. It’s as if the subquery is evaluated separately for each outer row. In this example, we will create a query that returns products with the highest Unit price of each product ID in the the Products table.

Setting Up

For this tutorial we will be working with a Product table in the Products database. Creating a database in SQL Server is simple with the CREATE DATABASE statement.

PgSQL

1

CREATEDATABASEProducts;

Next we will create the Product table. This table will have four columns: Prod_Id, ProductName, Unit, and UnitPrice. Prod_Id will have a data type of int, ProductName and Unit will be of nvarchar, and UnitPrice will be of the float data type. First we use the CREATE TABLE statement followed by “Product” and parenthesis. Between the parenthesis will go the columns and data types just mentioned as shown below:

PgSQL

1

2

3

4

5

6

7

8

9

USEProducts

CREATETABLEProduct

(

Prod_Idintnot null,

ProductNamenvarchar(30)not null,

Unitnvarchar(30)not null,

UnitPricefloatnot null

);

Now let’s add values into the Product table. Inserting multiple values in a single query can be done with three statements: INSERT INTO, SELECT, and UNION. The INSERT INTO statement is used to specify which table and columns the values are going to be inserted into. The SELECT statement is the data that is going to be inserted into the table and the UNION statement prevents duplicates from being added to the table.

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

USEProducts

INSERTINTOProduct(Prod_Id,ProductName,Unit,UnitPrice)

SELECT1,'Apples','1000 g',10.45

UNION

SELECT2,'Oranges','1500 g',21.75

UNION

SELECT3,'Strawberries','2000 g',15.30

UNION

SELECT4,'Bananas','500 g',32.56

UNION

SELECT5,'Grapes','750 g',5.15

UNION

SELECT1,'Apples','1000 g',7.45

UNION

SELECT1,'Apples','800 g',11.45

UNION

SELECT2,'Oranges','750 g',9.45

UNION

SELECT5,'Grapes','500 g',5.05

UNION

SELECT5,'Grapes','500 g',5.10

UNION

SELECT4,'Bananas','700 g',6.75

UNION

SELECT3,'Strawberries','1000 g',10.12

Correlated Subquery

We will create a correlated subquery that returns items with the highest unit price of each product ID in the table. First we use the SELECT statement to select all columns from the table, which can be done with the asterisk(*) symbol. Next we use the FROM statement to create an outer query of the Prod_Id column. Next we use the WHERE statement to filter the Unit column from the outer query and create the inner query. The inner query has the basic structure of the outer query with minor differences. The SELECT statement calls the MAX function to retrieve the highest number in the Unit column from the inner query. We then use the WHERE statement to filter out the product names in the inner query that match the products names of the outer query.

PgSQL

1

2

3

4

5

6

7

8

USEProducts

SELECT*

FROMProductASP1

WHEREP1.Unit=

(SELECTMAX(P2.Unit)

FROMProductASP2

WHEREP2.ProductName=P1.ProductName);

Output

Execute the query and the query will retrieve and output the highest unit of each product ID.

Thanks for reading and make sure to download the source files to get a better understanding of how the code works.