fbpx

בפוסט הקודם הוסבר מהן תתי שאילתות וכיצד ניתן לעבוד עימן באופן בסיסי. בפוסט זה נעמוד על ההבדלים בין שני סוגים שונים של תתי שאילות – Correlated Subqueries ו Nested Subqueries ונראה כיצד ניתן אותם בחלקים שונים של משפט ה SELECT.

ניתן לחלק את תתי השאילתות לשני סוגים עיקריים – Nested ו Correlated.

·       Nested Subqueries הן תתי שאילתות אשר מחזירות תמיד תוצאה סטטית (התוצאה יכולה להיות שורה אחת או יותר אך בכל מקרה היא תהיה קבועה)

·       Correlated Subqueries הן תתי שאילתות המחזירות תוצאות באופן דינמי ומשתנה בהתאם לערכים של השאילתה החיצונית.

שילוב תתי שאילתות במשפט ה SELECT

ניתן לשלב תתי שאילתות במשפט ה SELECT כדי להציג עמודות אשר משפט SELECT  רגיל לא היה מאפשר להציגן, לדוגמא – יחד עם שם משפחתו ושכרו של העובד, הצגת עמודה המראה את הממוצע של המשכורות בטבלה.

HR >> -- Error
HR >> SELECT last_name , salary, AVG(salary)
  2  FROM employees ;
SELECT last_name , salary, AVG(salary)
       *
ERROR at line 1:
ORA-00937: not a single-group group function

HR >>
HR >> -- Nested Subquery
HR >> SELECT  last_name ,
  2          salary,
  3     (SELECT avg(salary) FROM employees) AS AVG_SAL
  4  FROM employees emps
  5  WHERE rownum <= 10 ;

LAST_NAME                     SALARY    AVG_SAL
------------------------- ---------- ----------
OConnell                        6000 6493.62617
Grant                           2600 6493.62617
Whalen                          4400 6493.62617
Hartstein                      13000 6493.62617
Fay                             6000 6493.62617
Mavris                          6500 6493.62617
Baer                           10000 6493.62617
Higgins                        12008 6493.62617
Gietz                           8300 6493.62617
King                           24002 6493.62617

10 rows selected.

באמצעות Correlated Subqueries ניתן לייצר עמודה עם ערכים דינמיים לדוגמא – יחד עם שם משפחתו ושכרו של העובד, הצגת עמודה המראה את ממוצע המחלקה של אותו עובד.

HR >>
HR >> -- Error
HR >> SELECT last_name , salary, AVG(salary)
  2  FROM employees
  3  GROUP BY department_id  ;
SELECT last_name , salary, AVG(salary)
       *
ERROR at line 1:
ORA-00979: not a GROUP BY expression
HR >> -- Correlated Subquery
HR >> SELECT  last_name ,
  2          salary,
  3     department_id ,
  4     (SELECT avg(salary) FROM employees WHERE emps.department_id = department_id) AS AVG_SAL
  5  FROM employees emps
  6  WHERE rownum

שילוב תתי שאילתות במשפט ה FROM

ניתן להשתמש בתתי שאילתות במשפט ה FROM כדי לייצר שאילתה המייצגת טבלה, באמצעות אותה טבלה יהיה ניתן לעשות JOIN עם טבלה אחרת

HR >>
HR >> -- Subquery in the FROM clause
HR >> SELECT emp.last_name , emp.salary , emp_avg.avg_sal
  2  FROM employees emp , (     SELECT department_id , AVG(salary) AS "AVG_SAL"
  3                     FROM employees
  4                     GROUP BY department_id ) emp_avg
  5  WHERE emp.department_id = emp_avg.department_id
  6  AND rownum <= 10 ;

LAST_NAME                     SALARY    AVG_SAL
------------------------- ---------- ----------
OConnell                        6000 3551.11111
Grant                           2600 3551.11111
Whalen                          4400       4400
Hartstein                      13000       9500
Fay                             6000       9500
Mavris                          6500       6500
Baer                           10000      10000
Higgins                        12008      10154
Gietz                           8300      10154
King                           24002      19334

10 rows selected.

ניתן להשתמש בתתי שאילתות במשפט ה FROM כדי לייצר שאילתות המציגות את N האובייקטים הנמוכים הגבוהים ביותר, לדוגמא מי הם חמשת העובדים אשר מרוויחים הכי הרבה.

HR >> -- TOP N
HR >> SELECT last_name , salary
  2  FROM (SELECT last_name, salary FROM employees ORDER BY salary DESC)
  3  WHERE rownum

תתי שאילתות במשפט ה WHERE

ניתן להשתמש ב Correlated Subquery על מנת להציג תנאי המשתנה בהתאם לערכים של השאילתה החיצונית, לדוגמא, מיהם העובדים אשר מרוויחים מעל הממוצע במחלקה שלהם

HR >
HR > SELECT  last_name ,
  2     salary
  3  FROM employees OUT
  4  WHERE salary > (SELECT avg(salary) FROM employees WHERE OUT.department_id = department_id)
  5  AND rownum <= 10 ;

LAST_NAME                     SALARY
------------------------- ----------
Hartstein                      13000
Raphaely                       11000
OConnell                        6000
Weiss                           8000
Fripp                           8200
Kaufling                        7900
Vollman                         6500
Mourgos                         5800
Ladwig                          3600
Sarchand                        4200

10 rows selected.

תתי שאילתות במשפט ה HAVING

ניתן לבצע סינון לפונקציות קבוצה באמצעות תתי שאילתות, לדוגמא – באלו מחלקות השכר הממוצע גבוה מהשכר הממוצע של מחלקה מספר 110

HR > SELECT department_id , avg(salary)
  2  FROM employees
  3  GROUP BY department_id
  4  HAVING avg(salary) > (SELECT avg(salary) FROM employees WHERE department_id = 110) ;

DEPARTMENT_ID AVG(SALARY)
------------- -----------
           90       19334

תתי שאילתות במשפט ה ORDER BY

ניתן לשלב תתי שאילתות במשפט ה ORDER BY על מנת לבצע מיונים מתוחכמים, לדוגמא – הצגת העובדים הממויינים לפי שם מחלקתם (מיון על סמך עמודה בטבלה אחרת)

HR > SELECT last_name , salary , department_id
  2  FROM employees OUT
  3  WHERE rownum <= 10
  4  ORDER BY (SELECT department_name FROM departments WHERE department_id = OUT.department_id) ;

LAST_NAME                     SALARY DEPARTMENT_ID
------------------------- ---------- -------------
Higgins                        12008           110
Gietz                           8300           110
Whalen                          4400            10
King                           24002            90
Mavris                          6500            40
Hartstein                      13000            20
Fay                             6000            20
Baer                           10000            70
Grant                           2600            50
OConnell                        6000            50

10 rows selected.

נכתב ע"י רם קדם