Hi,
What are correlated queries? Are not they just subqueries?
What are correlated queries?
- Anuj Dhawan
- Founder
- Posts: 2803
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: What are correlated queries?
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:
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:
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.
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')
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)
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.
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.
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