Difference between Subquery and Correlated Subquery

Question

1.44/5 (2 votes)

See more:

Is the following piece of SQL Query a normal query or a Correlated Subquery ??

select UserID,
                                          FirstName,
                                          LastName,
                                          DOB,
                                          GFName,
                                          GLName,                                      
                                          LoginName,
                                          LoginEffectiveDate,
                                          LoginExpiryDate,
                                          Password,
                                          Email,
                                          ReportingTo,
                                          Mobile,
                                          CommunicationPreference,
                                          IsActive
                                          from (
                                          select row_number() over (order by FirstName) as Row,
                                          UserID,
                                          FirstName,
                                          LastName,
                                          DOB,
                                          GFName,
                                          GLName,
                                          LoginName,
                                          LoginEffectiveDate,
                                          LoginExpiryDate,
                                          Password,
                                          Email,
                                          ReportingTo,                                    
                                          Mobile,
                                          CommunicationPreference,
                                          IsActive
                                          from DivakarUserRegistration

Also, can someone state the difference between the both

Posted 23-Jun-13 19:55pm

Divakar Raj M

Updated 18-Nov-16 2:46am

Add a Solution

Comments

Divakar Raj M 24-Jun-13 2:52am

I heard from my collegues that it is a correlated subquery. But i am not able to understand how, since the inner query runs independently.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Vedant Joshi · Answer 1 · 2013-06-23T21:23:00

Subquery :- The inner query is executed only once The inner query will get executed first and the output of the inner query used by the outer query.The inner query is not dependent on outer query.

Eg:- SELECT cust_name, dept_no FROM Customer WHERE cust_name IN (SELECT cust_name FROM Customer);

Correlated subquery:-The outer query will get executed first and for every row of outer query, inner query will get executed. So the inner query will get executed as many times as no.of rows in result of the outer query.The outer query output can use the inner query output for comparison. This means inner query and outer query dependent on each other

Eg:- SELECT cust_name,dept_id FROM Cust
WHERE cust_name in (SELECT cust_name FROM dept WHERE cust.dept_id=dept.dept_id);

NavakumarM · Answer 2 · 2016-11-18T02:46:00

Solution 4

The above query is a subquery.

Reason: The inner query can be independent which does not depend on the outer query and hence it is not Correlated Subquery.

Posted 18-Nov-16 2:46am

NavakumarM