What are correlated queries?

RDBMS from IBM and IBM's Hierarchical DBMS and Transaction Manager (IMS DC).
Post Reply
Rajni
New Member
Posts: 5
Joined: Fri May 30, 2014 2:22 am

What are correlated queries?

Post by Rajni »

Hi,

What are correlated queries? Are not they just subqueries?
User avatar
Anuj Dhawan
Founder
Posts: 2799
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Contact:
India

Re: What are correlated queries?

Post by Anuj Dhawan »

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.
Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.
Post Reply

Create an account or sign in to join the discussion

You need to be a member in order to post a reply

Create an account

Not a member? register to join our community
Members can start their own topics & subscribe to topics
It’s free and only takes a minute

Register

Sign in

Return to “IBM DB2 and IMS DB/DC”