Correlated Subqueries - 达人(小新)

2005/08/17 | Correlated Subqueries
类别(数据库[原创]) | 评论(0) | 阅读(86) | 发表于 19:43: Correlated Subqueries
Morning a colleague asked me a SQL statement how to update rows in a table base on other from another table.So easy.
My answer is "update emp_salary es set es.subsidy1 = (select worktime * 10 from employee emp where emp.empid = es.empid and emp.worktime > 10)".
What Is a Subquery?
A subquery is a SELECT statement embedded in a clause of another SQL statement.
For Example "select * from employee emp where empid in (select emp from salary where tax > 1000)" . in this statement "select emp from salary where tax > 1000" is the subquery embedded in the main statement .
Correlated subqueries are useful whenever a subquery must return a different result for each candidate row.[from Extended Data Retrieval with SQL by Oracle]
With Exists operator you can return boolean value;

some point:
1、Subquery(inner query) excutes once before the main query(outer query)(once of every row of the outer query).
2、The result of the subquery is used by the main query.; 0