When you use advanced SQL features in the Query Builder to create a subquery, you must determine if you want to correlate your subquery. Correlation is the process of using a value returned by one query in the execution of another query. If a subquery is correlated, it is executed once for every row returned by the main query. Some of the queries you create will need to be correlated, and some will not. It all depends on what you are trying to do.
For example, assume you want a list of all customers who have placed bigger orders than last year’s average order throughout the company. The subquery needs to obtain a single value, the average order from last year. Since this subquery needs to run only once, it would not be correlated. However, if you wanted a list of customers who have increased their orders from last year, the subquery would run once for each customer. In this case, the subquery would be correlated.
You correlate a subquery with its parent query by joining one or more query fields in the Correlate dialog box. To access the Correlate dialog box, click Next after selecting fields in the Query Builder for your subquery.
To correlate queries, use the mouse to drag query fields from one table to another. You can also click Auto Bind to automatically join table columns for which both the name and the data type are the same. You may bind zero or many table columns in the main query to table columns in the subquery.
To remove a correlation, click on the arrow representing it and press the [Delete key]. To remove all the existing correlations between the queries, click Remove All.
Correlation is an operation that binds two queries within a single SQL statement. In SQR Production Reporting Studio, this operation is done entirely within the Query Builder.
A similar technique to bind separate queries is the Bind operation. This operation binds completely separate queries in separate SQL statements, using SQR Production Reporting Studio to link the queries.
A join operation is also part of a single SQL statement, but refers only to binds between two tables, rather than binds between queries.
Creating a Correlated Subquery
Creating an Uncorrelated Subquery