Oracle related subquery __oracle

--Start

Let's take a look at the definition of two tables:

--User
CREATE TABLE EMPLOYEE
(USERID number       (9,0) not NULL,---User ID
companyid number    (9,0),--- Company ID
telno        VARCHAR2 (a)---subscriber phone
);
ALTER TABLE "EMPLOYEE" ADD CONSTRAINT "Pk_employee" PRIMARY KEY ("USERID");

--Company
CREATE TABLE companies
(CompanyID number
(9,0) not NULL,---Corporate ID
telno     VARCHAR2 ()--- Company telephone
);
ALTER TABLE "Company" ADD CONSTRAINT "Pk_company" PRIMARY KEY ("CompanyID");

Everyone is very familiar with the subquery, but I found that many people do not know the subquery has two formats: one is the related subquery (correlated sub-query), the other is a non-correlated subquery (uncorrelated sub-query). Let's use an example to compare the differences between the two seed queries. Let's say now that you have a look at the company phone number is 88888888 of what users have, we can use the following statement:

--Non-correlated subqueries (uncorrelated sub-query)
select * from EMPLOYEE WHERE CompanyID in
(
select CompanyID from company WH ERE telno= ' 88888888 '
);

--dependent subquery (correlated sub-query)
select * from EMPLOYEE U where EXISTS
(
SELECT * from company C where telno= ' 888 88888 ' and U.companyid=c.companyid
);

The above two statements are used in the same way, and then we find that the clauses of the relevant subquery (that is, the statement in parentheses: Elect * from company C WHERE telno= ' 88888888 ' and u.companyid=c. CompanyID) depends on the condition of the external statement, cannot be executed separately, and the clauses of the unrelated subquery can be executed separately. For the above example, we use correlated subqueries, both in terms of performance and readability, as well as unrelated subqueries, let's look at an example of using unrelated subqueries, assuming that now you can update the user's phone to the company phone. Some people may construct update SQL in the following manner, and then execute it as follows:

SELECT ' UPDATE EMPLOYEE SET telno= ' | | Telno | | ' WHERE companyid= ' | | CHAR (CompanyID) | | ';' From company

It's OK to do it, but it's a bit stupid (of course, I think it's smart, because he probably thinks he's using a use SQL to made SQL that someone else is not used to or knows about), and there's a better way to do that is to take the relevant subquery as follows:

UPDATE EMPLOYEE U SET telno=
(
SELECT Telno from company C WHERE U.companyid=c.companyid
);

What do you think. Do you feel very bad, there is no easier way. Take a look at the following statement.

UPDATE 
(
SELECT u.telno Employee_telno, C.telno company_telno from EMPLOYEE U, company C WHERE U.companyid=c.comp Anyid
)
SET employee_telno = Company_telno;

-- more see: Oracle SQL Extract

-- statement: Reprint please indicate the source

--Last edited on 2015-06-19

--Created by Shangbo on 2015-06-19

--End