תת שאילתה היא שאילתה אשר ניתנת לקינון בחלקים שונים של שפת ה-SQL ואף בתוך תת שאילתה אחרת. לעיתים קרובות אנו מתייחסים לתת השאילתה כשאילתה פנימית כאשר השאילתה אשר קוראת לה נקראת השאילתה החיצונית. במדריך SQL זה אנו נרחיב על הנושא ונראה כיצד ניתן ליישמו.
אחת ממטרותיה הנפוצות של תת השאילתה היא לאפשר לנו להתמודד עם שליפה המורכבת מרמות שונות. לדוגמא, מי הם העובדים אשר מרוויחים יותר מעובד מספר 54?
אם נבחן את השאלה האחרונה נשים לב כי זו מורכבת משתי שאלות (רמות):
- כמה מרוויח עובד מספר 54 ?
- מי מרוויח יותר מעובד מספר 54 ?
|
מי מרוויח יותר מעובד מספר 54 ? |
à |
כמה עובד מספר 54 מרוויח ? |
מבנה תת השאילתה
SELECT …
FROM טבלה
WHERE תנאי
(SELECT …
FROM טבלה )
קווים מנחים לעבודה עם תתי שאילות
SELECT first_name , last_name , salary
FROM employees
WHERE salary >
(SELECT salary
FROM employees
WHERE employee_id = 54)
v תת השאילתה מתבצעת קודם ולאחר מכן התוצאה שהיא החזירה עוברת לשאילתה החיצונית (קודם פועלת השאילתה אשר בודקת כמה עובד מספר 54 מרוויח, ולאחר מכן פועלת השאילתה החיצונית על התוצאה שלה, כך ששכרו עובר לשאילתה החיצונית).
v תת השאילה חייבת להיות מוקפת בסוגריים.
v על מנת לשפר את הקריאות, מומלץ לכתוב את תת השאילתה בצד ימין של תנאי ההשוואה.
v לא ניתן להשתמש בתת השאילתה במשפט GROUP BY.
v ניתן לחלק את תתי השאילתות לשני סוגים :
o תתי שאילתות אשר מושוות לעמודה ע"י אופרטורים פשוטים
(Single Row Subquery).
o תתי שאילתות אשר מושוות לעמודה ע"י אופרטורים מורכבים
(Multiple Row Subquery).
Single Row Subquery היא תת שאילתה אשר עובדת עם אופרטורים פשוטים (כגון =, >, <).
תת שאילתה זו יכולה להחזיר שדה אחד בלבד לשאילתה החיצונית (הדוגמא שפתחה את הפוסט היא תת שאילתה מסוג זה).
העובדים אשר עובדים במחלקה של עובד ששמו Moshe :
SELECT last_name , first_name , salary , department_id
FROM employees
WHERE department_id = (SELECT department_id
FROM employees
WHERE first_name = ‘Moshe’)
v תת השאילתה חייבת להחזיר שורה בודדת, אם היינו כותבים את תת השאילתה ללא משפט WHERE היינו מקבלים שגיאה.
v תת השאילתה חייבת להחזיר עמודה אחת, אם היינו כותבים במשפט SELECT של תת השאילתה יותר מעמודה אחת היינו מקבלים שגיאה.
v אנו יכולים לסכם ולומר כי תת שאילתה מסוג זה יכולה לקבל שדה בודד.
v אילו רצינו להציג את העובדים אשר עובדים במחלקה של עובד ששמו Moshe, לא כולל Moshe היינו מוסיפים את המשפט And first_name <> ‘Moshe’ בשורה האחרונה של השאילתה (מחוץ לתת השאילתה).
ניתן לשלב בתת השאילתה פונקציות קבוצה
העובדים אשר מרוויחים יותר מממוצע השכר במחלקה מספר 60 :
SELECT first_name , last_name , salary
FROM employees
WHERE salary >
(SELECT AVG(salary)
FROM employees
WHERE department_id = 60)
ניתן לשלב את תת השאילתה במשפט HAVING
המחלקות אשר שכרן הממוצע גבוהה יותר מהשכר הממוצע במחלקה 90 :
SELECT department_id , AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) >
(SELECT AVG(salary)
FROM employees
WHERE department_id = 90)
ניתן לעבוד עם מספר תתי שאילות בו זמנית
העובדים אשר מרוויחים יותר מעובד מספר 54 וגם נמצאים במחלקה עם עובד ששמו Moshe (לא כולל Moshe) :
SELECT first_name , last_name , salary
FROM employees
WHERE salary >
(SELECT salary
FROM employees
WHERE employee_id = 54 )
AND department_id =
(SELECT department_id
FROM employees
WHERE first_name = ‘Moshe’)
AND last_name <> ‘Moshe’
Multiple Row Subquery
Multiple Row Subquery היא שאילתה אשריכולה להחזיר יותר משדה אחד לשאילתה החיצונית. תת שאילתה זו עובדת עם האופרטורים IN, ANY, ALL.
לצורך הדגמת צורת פעולתה של תת שאילתה זו אנו נתייחס בדוגמאות הבאות לערכי עמודת השכר עבור העובדים של מחלקה מספר 80 :
|
שכר |
|
4300 |
|
5200 |
|
6700 |
|
8200 |
|
12500 |
האופרטור IN מאפשר לנו להשוות עמודה מול רשימה של ערכים החוזרים מתת השאילתה.
העובדים אשר שכרם שווה לאחת המשכורות של העובדים במחלקה 80 :
SELECT first_name , last_name , salary
FROM employees
WHERE salary IN
(SELECT salary
FROM employees
WHERE department_id = 80)
! אם נרצה להציג את כל העובדים אשר שכרם שווה לאחת המשכורות של העובדים במחלקה 80 חוץ מהעובדים במחלקה 80 נכתוב AND department_id <> 80 בשורה האחרונה של השאילתה (מחוץ לתת השאילתה).
האופרטור ANY מאפשר לנו להשוות עמודה מול לפחות אחד מהערכים החוזרים מתת השאילתה.
אנו יכולים לעבוד עם שלוש צורות ההשוואה הבאות כאשר נשתמש באופרטור זה :
> ANY , < ANY , = ANY
> ANY
העובדים אשר שכרם גדול לפחות מאחת המשכורות של העובדים במחלקה 80.
כאשר אנו מבקשים לדעת את הערך הגדול לפחות מאחד הערכים בתוך רשימה מסויימת אנו למעשה מבקשים את הערך הגדול מהמינימום (כי אין זה משנה ממי הוא יהיה גדול, העיקר שיהיה גדול לפחות מאחד הערכים).
SELECT first_name , last_name , salary
FROM employees
WHERE salary > ANY
(SELECT salary
FROM employees
WHERE department_id = 80)
12500 4300
|————————————–|
MIN MAX
ANY <
à
< ANY
העובדים אשר שכרם קטן לפחות מאחת המשכורות של העובדים במחלקה 80.
כאשר אנו מבקשים לדעת את הערך הקטן לפחות מאחד הערכים בתוך רשימה מסויימת אנו למעשה מבקשים את הערך הקטן מהמקסימום (כי אין זה משנה ממי הוא יהיה קטן, העיקר שיהיה קטן לפחות מאחד הערכים).
SELECT first_name , last_name , salary
FROM employees
WHERE salary < ANY
(SELECT salary
FROM employees
WHERE department_id = 80)
12500 4300
|————————————–|
MIN MAX
< ANY
ß
= ANY
העובדים אשר שכרם שווה לפחות לאחת המשכורות של העובדים במחלקה 80 .
למעשה פקודת ANY = פועלת באותה צורה כמו IN.
SELECT first_name , last_name , salary
FROM employees
WHERE salary = ANY
(SELECT salary
FROM employees
WHERE department_id = 80)
האופרטור ALL מאפשר לנו להשוות עמודה מול כל הערכים החוזרים מתת השאילתה.
אנו יכולים לעבוד עם שתי צורות ההשוואה הבאות כאשר אנו משתמשים באופרטור זה :
> ALL , < ALL
> ALL
העובדים אשר שכרם גדול מכל המשכורות של העובדים במחלקה 80.
כאשר אנו מבקשים לדעת את הערך הגדול מכל הערכים בתוך רשימה מסויימת אנו למעשה מבקשים את הערך הגדול מהמקסימום (כדי שערך יהיה גדול מכל הערכים עליו בהכרח להיות גדול מהערך המקסימלי ברשימת הערכים).
SELECT first_name , last_name , salary
FROM employees
WHERE salary > ALL
(SELECT salary
FROM employees
WHERE department_id = 80)
12500 4300
|————————————–|
MIN MAX
> ALL
à
< ALL
העובדים אשר שכרם קטן מכל המשכורות של העובדים במחלקה 80.
כאשר אנו מבקשים לדעת את הערך הקטן מכל הערכים בתוך רשימה מסויימת אנו למעשה מבקשים את הערך הקטן מהמינימום (כדי שהערך יהיה קטן מכל הערכים עליו בהכרח להיות קטן מהערך המינימלי ברשימת הערכים).
SELECT first_name , last_name , salary
FROM employees
WHERE salary < ALL
(SELECT salary
FROM employees
WHERE department_id = 80)
12500 4300
|————————————–|
MIN MAX
< ALL
ß
! האופרטור ALL = מבקש את הערך אשר שווה לכל הערכים אשר חוזרים מתת השאילתה (העובד אשר שכרו שווה ל-4300 וגם ל-5200 וגם ל-6700 וגם ל-8200 וגם ל-12500).
משום שתנאי זה מבקש דבר לא הגיוני לרוב לא נשתמש בו
האופרטור IN וערכי NULL
|
מספר עובד |
שם העובד |
מספר מנהל |
|
1 |
משה |
NULL |
|
2 |
יוסי |
1 |
|
3 |
דוד |
1 |
|
4 |
אלי |
NULL |
|
5 |
רועי |
3 |
|
6 |
יובל |
5 |
עבודה עם אופרטור IN – עובד בצורה תקינה
העובדים אשר הם בעצמם גם מנהלים :
SELECT employee_id , last_name
FROM employees
WHERE employee_id IN (SELECT manager_id
FROM employees)
עבודה עם אופרטור NOT IN – לא מחזיר תוצאות
העובדים אשר אינם מנהלים :
SELECT employee_id , last_name
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees)
היינו מצפים בשאילתה זו לקבל את התוצאות ההפוכות מהתוצאות שקיבלנו כאשר עבדנו עם האופרטורIN , אך למעשה אנו לא מקבלים אף תוצאה.
כאשר תת השאילתה מכילה ערכי NULL אופרטור NOT IN לא יחזיר אף תוצאה.
כדי לפתור זאת עלינו לדאוג שתת השאילתה לא תחזיר ערכי NULL :
SELECT employee_id , last_name
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
