子查询与相关子查询之间的区别 [英] Difference between Subquery and Correlated Subquery

问题描述

以下SQL查询是普通查询还是相关子查询？

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

推荐答案

子查询： - 内部查询仅执行一次内部查询将首先执行并且输出外部查询使用的内部查询。内部查询不依赖于外部查询。



例如： - SELECT cust_name，dept_no FROM Customer WHERE cust_name IN（ SELECT cust_name FROM Customer）;



相关子查询： - 外部查询将首先执行，外部每行都执行查询，内部查询将被执行。因此，内部查询将在外部查询的结果中执行与行数相同的次数。外部查询输出可以使用内部查询输出进行比较。这意味着内部查询和外部查询相互依赖



例如： - SELECT cust_name，dept_id FROM Cust

WHERE cust_name in （SELECT cust_name FROM dept WHERE cust.dept_id = dept.dept_id）;

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);

上面的查询是一个子查询。



原因：内部查询可以是独立的，不依赖于外部查询，因此它不是相关子查询。

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.

这篇关于子查询与相关子查询之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！