בפוסט הקודם הוסבר מהן תתי שאילתות וכיצד ניתן לעבוד עימן באופן בסיסי. בפוסט זה נעמוד על ההבדלים בין שני סוגים שונים של תתי שאילות – 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.
נכתב ע"י רם קדם