Stair T-sql: Beyond basic Level 3: Create a correlated subquery

Gregory larsen,2014/03/05

      the series                This is part of the series: Stair Stairs T-sql: Beyond Basics               Below is a more advanced aspect of the T-SQL DML that covers the Gregory Larsen from his staircase, T-SQL, or subqueries.               in this staircase level 2 I discuss how to use subqueries in Transact-SQL statements. This stair level will widen the query for the discussion type of the query topic called the correlated subquery. I'll explore what is related to subqueries and how it differs from normal subqueries. In addition, I will provide you with some of the transaction's SQL statements, beyond the basic examples and using related subqueries to help identify the result set of the returned rows to meet complex business requirements. What are               related subqueries?               on this staircase level 2 we learned that a normal subquery is a SELECT statement, In another Transact-SQL statement, the subquery can run independently of the external query to return the results. A correlated subquery is a form of a subquery that cannot run independently of an external query because it contains one or more columns from an external query. Correlated subqueries, like a normal subquery, are sometimes referred to as internal queries. An error is returned if the correlated subquery (inner query) is run independently of the external query. Because the execution of an internal query relies on the value of an external query, it is called a correlated subquery.               related subqueries can be executed multiple times. It will run once for each candidate row selected in the external query. The values for each candidate will be used for the relevant sub-columns in the external queryQuery each execution provides a value. A declaration contains a correlated subquery based on the final result of each execution of the correlated subquery.               related subquery Instance sample data                to demonstrate how to use related subqueries I need some test data. Instead of creating your own test data, all of my examples will use the ADVENTUREWORKS2008R2 database. If you want to follow me, run an example in your environment you can download the ADVENTUREWORKS2008R2 database from here: http://msftdbprodsamples.codeplex.com/releases/view/93587               example of WHERE clause in a correlated subquery                shows the assumptions used in the WHERE clause of a correlated subquery, I want to find out that the CustomerID have purchased more than 70 styles. In order to complete this requirement, I can run the code:

SELECT CustomerID from Sales.SalesOrderHeader OH
WHERE (SELECT COUNT (*) from Sales.SalesOrderDetail
WHERE SalesOrderID = OH. SalesOrderID) > 70;

Listing 1: In the WHERE clause of the related subquery when I run the code in Listing 1, I get the output from report 1.

CustomerID
-----------
29712
29722
30048
30107

Report 1: Run the code in Listing 1 returns the results if you review the code in Listing 1, you will see me constrain me by using the related subquery. The code for the subquery is placed in the parentheses of my correlated subquery Code Listing 1 and Listing 2.

SELECT COUNT (*) from Sales.SalesOrderDetail
WHERE SalesOrderID = OH. SalesOrderID

Listing 2 shows the code in Listing 1: If you run the code in Listing 2, I'll see that I've shown an error in report 2.

Msg 4104, Level A, State 1, line 3
The multi-part identifier "OH. SalesOrderID
"could not being bound.

Report 2: An error occurred while running the code in Listing 2               I get the error shown in report 2 because my correlated subquery contains a reference column Oh.salesorderid is a column from an external query. Because all related subqueries reference one or more columns, you cannot query from an external query independently from the external queries associated with it. In fact, you cannot run the query independently of the entire Transact-SQL statement except that the correlated subquery is in a normal subquery.               The example given here is a very trivial example of using a related subquery in the WHERE clause. Hopefully this simple example will make it easy to understand the difference between a normal subquery and a correlated subquery. Typically a correlated subquery can be quite complex. Also, remember that there are other ways that you might want to meet your business needs without using related subqueries.               as you can see, writing a correlated subquery is very similar to a normal query, But you just can't run the dependent subquery independently of the external query.               examples of dependent subqueries in having clauses                Sometimes you might want to constrain a clause with a different value by using an external query. This is when you can use the related subquery in the HAVING clause. Suppose you have to write a query that calculates the rebate amount for customers who purchased more than $150000 of products before 2008. The code in Listing 3 calculates the rebate amount for those valued customers by using the related subquery in the HAVING clause.

SELECT Outer_h.[customerid]
, SUM (Outer_h.[subtotal]) as Totalpurchase
, SUM (Outer_h.[subtotal]) *. rebate
from [Sales]. [SalesOrderHeader] As Outer_h
WHERE year (outer_h.[orderdate]) = ' 2008 '
GROUP by Outer_h.[customerid]
Have (SELECT SUM (Inner_h.[subtotal]) from [sales].[ SalesOrderHeader] as Inner_h
WHERE Inner_h.[customerid] = Outer_h.[customerid]
And year (inner_h.[orderdate]) = ' $ ') > 150000
ORDER by rebate DESC;

Checklist 3:having clause related subquery When I run the code in Listing 5, I get the results in report 3.

CustomerID totalpurchase Rebate
----------- --------------------- ---------------------------------------
29923 220496.658 22049.665800
29641 210647.4929 21064.749290
29617 187964.844 18796.484400
29913 186387.5613 18638.756130
29818 179916.2877 17991.628770
29940 175358.3954 17535.839540
29987 172169.4612 17216.946120
29736 157700.6034 15770.060340
29995 156984.5148 15698.451480
29770 151824.9944 15182.499440

Report 3: Run the results of Listing 3 related subquery Listing 3 in the code using CustomerID from the Group external query for the terms in the related subquery. The execution of the correlated subquery returns the GROUP BY clause for each row. This allows the HAVING clause to calculate the total amount of the product from the external query to the sum of the values sold to each CustomerID, and the second full column for each SalesOrderHeader record in the record with information from the external query. The Transact-SQL statement returns only one row in Listing 3, and CustomerID is buying a product worth more than $150000.

The

Update statement contains an example of a related subquery               A related subquery can be used not only to return a result set, but to use a SELECT statement. They can also be used to update data in SQL Server tables. To prove this, I will first generate some test data in the tempdb table by using the code in Listing 4.

Use tempdb;
GO
SET NOCOUNT on;
CREATE TABLE carinventory (
ID int identity,
Carname varchar (),
VIN varchar (),
Stickerprice Decimal ( 7,2),
Invoiceprice Decimal (7,2));
GO
INSERT into Carinventory VALUES (' Explorer ', ' exp2014123456a ', 46198.45,38201.87),
(' Explorer ', ' exp2014123493a ', 47129.98, 38201.87),                               
(' Grand Cherokee ', ' jgc20141234345x ', 41678.45,36201.86),
(' Grand Cherokee ', ' jgc20141234556w ', 44518.31,36201.86),
(' Pathfinder ', ' npf2014987365a ', 32587.73,28917.10),
(' Pathfinder ', ' npf2014239657b ', 33577.54,28917.10),
(' Pathfinder ', ' npf2014098587c ', 35876.12,28917.10),
(' Tahoe ', ' tah201409674a ', 52001.08,46000.01);

Listing 4: Creating and populating the code in the test table for code Listing 4 creates a carinventory table and then populates the eight rows to represent the current inventory of the vehicle. The regular sales manager likes to see his invoicepriceratio by running the query in Listing 5.

SELECT Carname, invoiceprice/stickerprice*100.0 as Invoicepriceratio
From Carinventory;

Checklist 5:invoicepriceratio Query When the manager runs this query she notices that there are some same invoice price quantities that have different invoicepriceratio values similar to the car. In order to maximize their invoice price than she asked her to write a query that would update all her cars, the stickerprice of each car with the same carname value has the same invoicepriceratio IT support. She wants this guy to take stickerprice as the carname highest price. In this way, all cars have the same carname value and have the same stickerprice value. In order to complete this update of the Carinventory table, this guy's trade listing 6 in the SQL statement, which contains a related subquery.

UPDATE carinventory 
SET stickerprice = (SELECT MAX (stickerprice)
                     from Carinventory inner_ci
                     WHERE inner_ci. Carname = Outer_ci. Carname)  
from Carinventory outer_ci;

Listing 6: Related subquery update carinventory Maximum sticker The code in Listing 8 uses the carname of the external query in the related subquery to determine each unique carname maximum stickerprice.              This maximum stickerprice value is found in the correlated subquery and is then used to update the Stickerprice value for each carinventory record with the same carname. Performance considerations for correlated subqueries There are some performance considerations that you should be aware of when writing Transact-SQL statements that contain related subqueries. When an external query contains a small number of rows, performance is not bad. However, when an external query contains a large number of rows, it does not scale well from a performance standpoint. This is because the related subquery requires each candidate row in the external query to execute. Therefore, when an external query contains more and more candidate row-dependent subqueries being executed multiple times, the Transact-SQL statement will take longer to run.              If you find that the performance of your correlated subquery's SQL statement does not meet your requirements, then you should look for alternative solutions, such as queries that use internal or external connection operations, or return a small number of candidate rows from an external query. Summarizing related subqueries is an intrinsic query that includes one or more columns from an external query. The correlated subquery is executed once for each candidate row of the outer query. Because a correlated subquery contains external queries that cannot be listed from an external query, it can be run independently. Related sub-queries where, although not scale from a performance perspective, there are a large number of candidates out of query identification.

Stair T-sql: Beyond base Level 3: Create a correlated subquery