Correlated Subqueries

السلام عليكم 

Correlated Subqueries

يستخدم الـ Correlated Subqueries لمعالجه الصفوف (صف صف) ويتم تنفيذ كل استعلام فرعى مره واحده لكل صف من الأستعلام الخارجى (outer query) وتتم معالجته بواسطه (parent statement) ويمكن ان يكون parent statement جمله select ,update and delete

اعتقد ان الكلام مبهم بعض الشىء ولكن ان شاء الله من خلال الأسطر القادمه سوف يتضح لك الأمر بشكل واضح واكثر تفصيل ولكن اقرا الموضوع من البدايه للنهايه اكثر من مره  فى حاله لو لم تتضح لك الرؤيا -بس ان شاء الله مع نهايه الموضوع وقرائته بتمعن هتلاقى كله تمام - واى سؤال حاضر ان شاء الله

نرجع لموضعنا الأساسى فى البدايه انت محتاج انك تعرف الفرق بين Nested subquery والـ Correlated Subqueries  ومن خلال الفروقات ده هتقدر تعرف ايه هو اصلا Correlated Subqueries وامتى تستخدمه وهتستفيد ايه لما تستخدمه لان من بره شكل Nested subquery و Correlated Subqueries شبه بعض .

ما الفرق بين  Nested subquery و Correlated Subqueries ؟

Nested Subqueries Versus Correlated Subqueries

** -يفضل القراءه اولا عن  Nested subquery قبل قراءه هذا الدرس-

اوراكل تقوم بقراءه  Nested subquery  اولا وتقوم بتنفيذها مره واحده وتقوم بأسترجاع قيمه ويتم استخدامها فى الأستعلام الرئيسى ولكن Correlated Subqueries  يتم تنفيذه مره  واحده على (صف صف) 

طيب يعنى ايه الكلام ده

 فى حاله  Nested subquery 

الـ inner query  ينفذ اولا لأيجاد قيمه

الـ outer query  ينفذ مره واحده ويستخدم القيمه التى استرجعها الـ inner 

 Correlated Subqueries

- يحصل على الصف المرشح (fetched by the outer query)

-ينفذ الـ inner query  بأستخدام القيمه المسترجعه من الصفوف المرشحه

- يستخدم نتائجج القيم من الـ inner query  للتأكد من تأهيل او عدم تأهيل المرشح منها

- ويكرر نفس العمليه حتى اخر صف مُرشح

صوره (1-1)

وكما هو موضح من صوره (1-1)  انه فى البدايه يحضر الصفوف المرشحه من (outer query)  ثم يقوم بتنفيذ الـ  (inner query)  بأستخدام القيمه المسترجعه من الصفوف المرشحه واخيرا يستخدم القيم الموجوده فى  (inner query) ليرى تأهيلها او عدم تأهيلها 

بطريقه اخرى ...

يمكنك استخدام الـ Correlated Subqueries للأجابه على سؤال متعدد الأجزاء والتى تعتمد اجابته على قيمه تتم معالجتها فى كل صف بواسطه  (parent statement)

ويمكنك استخدام كلا من ANY and ALL مع Correlated Subqueries

صوره (1-2)

وهذا هو الكود العام لـ Correlated Subqueries

SELECT column1, column2, ...
FROM   table1 outer
WHERE  column1 operator
                       (SELECT column1, column2
                        FROM   table2
                        WHERE  expr1 = outer.expr2);

مثال توضيحى

من خلال المثال التالى اريد استعراض الموظفين الذين راتبهم اكبر من متوسط رواتب الأدارات 

الـ  Correlated Subqueries  يقوم تحديدا بحساب متوسط مرتبات كل اداره 

SELECT last_name, salary
       ,department_id
FROM  employees outer
WHERE salary >
              (SELECT AVG(salary)
               FROM   employees
               WHERE  department_id = outer.department_id);

LAST_NAME                   SALARY DEPARTMENT_ID
------------------------- -------- -------------
King                         24000            90 
Hunold                        9000            60 
Lorentz                       7098            60 
Greenberg                    12008           100 
Faviet                        9000           100 
Raphaely                     11000            30

38 rows selected

فى المثال الـ (inner query) و (outer query) يستخدمو نفس الجدول وهو جدول الـ EMPLOYEES فى جمله الـ FROM وقمت بأعطاء alias لجدول الـ EMPLOYEES فى جمله  (outer query) وقمت بأعطاءه alias  مش عشان اخلى الجمله سهله القراءه ولكن بدون  alias  لن تعمل الجمله اصلا . لان  ( inner query statment)  لن تستطيع التمييز ما بين (inner table column)  و (outer table column).

مثال اخر

فى هذه المثال اريد استعراض بيانات الموظفين الذين قامو بتغيير وظيفتهم على الاقل مرتين

SELECT e.employee_id
      ,last_name,e.job_id
FROM   employees e
WHERE 2 <= (SELECT count(*)
            FROM   job_history
            WHERE employee_id = e.employee_id);

EMPLOYEE_ID LAST_NAME                 JOB_ID   
----------- ------------------------- ----------
        101 Kochhar                   AD_VP      
        176 Taylor                    SA_REP     
        200 Whalen                    AD_ASST 

نبدا فى موضوع تانى وهو :

Correlated UPDATE

الكود العام لـ Correlated UPDATE

UPDATE table1 alias1
SET column = (SELECT expression
              FROM   table2 alias2
              WHERE  alias1.column = alias2.column);

مثال توضيحى 

SETUP

CREATE TABLE EMMP AS 
SELECT * FROM employees
WHERE 1 = 2
table EMMP created.

DESC EMMP
EMPLOYEE_ID             NUMBER(6)    
FIRST_NAME              VARCHAR2(20) 
LAST_NAME      NOT NULL VARCHAR2(25) 
EMAIL          NOT NULL VARCHAR2(25) 
PHONE_NUMBER            VARCHAR2(20) 
HIRE_DATE      NOT NULL DATE         
JOB_ID         NOT NULL VARCHAR2(10) 
SALARY                  NUMBER(8,2)  
COMMISSION_PCT          NUMBER(2,2)  
MANAGER_ID              NUMBER(6)    
DEPARTMENT_ID           NUMBER(4)

INSERT INTO HR.EMMP VALUES (100,'Steven','King','SKING','515.123.4567',TO_DATE('17/06/03','DD/MM/RR'),'AD_PRES',24000,NULL,NULL,90);
INSERT INTO HR.EMMP VALUES (101,'Neena','Kochhar','NKOCHHAR','515.123.4568',TO_DATE('21/09/05','DD/MM/RR'),'AD_VP',17000,NULL,100,90);
INSERT INTO HR.EMMP VALUES (102,'Lex','De Haan','LDEHAAN','515.123.4569',TO_DATE('13/01/01','DD/MM/RR'),'AD_VP',17000,NULL,100,90);
INSERT INTO HR.EMMP VALUES (103,'Alexander','Hunold','AHUNOLD','590.423.4567',TO_DATE('03/01/06','DD/MM/RR'),'IT_PROG',9000,NULL,102,60);
INSERT INTO HR.EMMP VALUES (104,'Bruce','Ernst','BERNST','590.423.4568',TO_DATE('21/05/07','DD/MM/RR'),'IT_PROG',6000,NULL,103,60);
INSERT INTO HR.EMMP VALUES (105,'David','Austin','DAUSTIN','590.423.4569',TO_DATE('25/06/05','DD/MM/RR'),'IT_PROG',4800,NULL,103,60);
INSERT INTO HR.EMMP VALUES (106,'Valli','Pataballa','VPATABAL','590.423.4560',TO_DATE('05/02/06','DD/MM/RR'),'IT_PROG',4800,NULL,103,60);
INSERT INTO HR.EMMP VALUES (107,'Diana','Lorentz','DLORENTZ','590.423.5567',TO_DATE('07/02/07','DD/MM/RR'),'IT_PROG',7098,NULL,103,60);
INSERT INTO HR.EMMP VALUES (108,'Nancy','Greenberg','NGREENBE','515.124.4569',TO_DATE('17/08/02','DD/MM/RR'),'FI_MGR',12008,NULL,101,100);
COMMIT;

ALTER TABLE EMMP
ADD (DEPARTMENT_NAME VARCHAR2(50));
table EMMP altered.

من خلال هذا المثال وبأستخدام Correlated Subqueries  اريد اضافه اسماء الادارت فى جدول EMMP بناء على كود الاداره DEPARTMENT_ID

UPDATE EMMP E
SET DEPARTMENT_NAME = (SELECT DEPARTMENT_NAME
                       FROM   DEPARTMENTS D
                       WHERE  D.DEPARTMENT_ID = E.DEPARTMENT_ID);
COMMIT;

9 rows updated.
commited.

SELECT DEPARTMENT_ID 
      ,DEPARTMENT_NAME
FROM  EMMP

DEPARTMENT_ID DEPARTMENT_NAME                                  
------------- --------------------------------------------------
           90 Executive                                          
           90 Executive                                          
           90 Executive                                          
           60 IT                                                 
           60 IT                                                 
           60 IT                                                 
           60 IT                                                 
           60 IT                                                 
          100 Finance                                            

 9 rows selected 

Correlated DELETE

DELETE FROM table1 alias1
WHERE  column operator
       (SELECT expression
        FROM   table2 alias2
        WHERE  alias1.column = alias2.column);

-->