Mainframe, MVS and zOS Discussion

Posted: **Tue Jun 03, 2014 3:39 pm**

Hi,

What are correlated queries? Are not they just subqueries?

Posted: **Wed Jun 04, 2014 3:17 pm**

Hi,

To make the concept clear, we have to go in reverse order. And we'll start with an uncorrelated (sub)query.

For example, have a look at the below query:

Code: Select all

select Employee.Name from Employee 
where Employee.ID NOT IN(
select Projects.Employee_id from Projects, Client 
where Projects.cust_id = Client.ID 
and Client.Name = 'India')

The above SQL is not much tough to understand -- the “query“ which starts after the “NOT IN” statement is “sub-query” and this sub-query is uncorrelated sub-query. The reason that the query above is an uncorrelated sub-query is that the sub-query can be run independently of the outer query. Or in simple terms, the sub-query has no relationship with the outer query.

Now, If you understand what is said above – it’s easy to remember that, a correlated sub-query has the opposite property as this type of sub-query can not be run independently of the outer query. For example:

Code: Select all

SELECT *
FROM Employee Emp1
WHERE (1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)

You should notice that in (the correlated sub-query) the inner sub-query uses Emp1.Salary which does not have any meaning if the alias Emp1 created in the outer query does not exist. Such a query is called a correlated sub-query, because the sub-query references a value in its WHERE clause (here, sub-query using a column which belongs to Emp1) that is used in the outer query.

So to answer, what you have asked for -- yes, they are sub-queries but they don't exist their own.

Hope this helps.

Mainframe, MVS and zOS Discussion

What are correlated queries?

What are correlated queries?

Re: What are correlated queries?