תת שאילתה היא שאילתה אשר ניתנת לקינון בחלקים שונים של שפת ה-SQL ואף בתוך תת שאילתה אחרת. לעיתים קרובות אנו מתייחסים לתת השאילתה כשאילתה פנימית כאשר השאילתה אשר קוראת לה נקראת השאילתה החיצונית. במדריך SQL זה אנו נרחיב על הנושא ונראה כיצד ניתן ליישמו.

שימוש בתתי השאילתות

אחת ממטרותיה הנפוצות של תת השאילתה היא לאפשר לנו להתמודד עם שליפה המורכבת מרמות שונות. לדוגמא, מי הם העובדים אשר מרוויחים יותר מעובד מספר 54?

אם נבחן את השאלה האחרונה נשים לב כי זו מורכבת משתי שאלות (רמות):

  1. כמה מרוויח עובד מספר 54 ?
  2. מי מרוויח יותר מעובד מספר 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

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

האופרטור 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

 

> 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

 

> 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)