子查询与相关子查询之间的区别

本文介绍了子查询与相关子查询之间的区别的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

以下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.