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