שליפה מטבלאות המתייחסות זו לזו

ההיכרות הראשונה שלי עם עולם בסיסי הנתונים הרלציוניים הייתה לפני כחמש עשרה שנה בקורס אקסס – באחת מגרסאותיו הראשונות.
אם לפני הקורס חשבתי שאקסס זה אקסל משופר, הרי לאחר השיעור הראשון בו הראו לנו איך יוצרים טבלה, מכניסים לתוכה נתונים, ממינים ומפלטרים אותה- התחלתי לחשוש שמדובר באקסל נחות ומנוון..
רק לאחר שהראו לנו איך עושים Join בין טבלאות אמרתי בליבי "אההה.." ארוך שפירושו הוא "עכשיו אני מבין..".
לזכר ימי התום האלו אני אנסה לרכז בפוסט אחד את כל הדרכים לשלוף מטבלאות תוך שהן מתייחסות זו לזו.

נתחיל ביצירת שתי טבלאות לצורך ההדגמות:

Use tempdb;

Go

If Object_Id('T_Ovdim') Is Not Null Drop Table T_Ovdim

Go

Create Table T_Ovdim(ID Int Primary Key,

                    Oved Varchar(50),

                    Taarih DateTime);

Go

Insert Into T_Ovdim

Select 1,'Anat','20100101' Union All

Select 2,'Beni','20100215' Union All

Select 3,'Geula','20100331' Union All

Select 4,'David','20100410';

Go

If Object_Id('T_Avodot') Is Not Null Drop Table T_Avodot

Go

Create Table T_Avodot(ID Int,

                    TaarihHathala DateTime,

                    TaarihSium DateTime);

Go

Insert Into T_Avodot

Select 1,'20100101','20100131' Union All

Select 1,'20100301','20100331' Union All

Select 3,'20100401','20100430' Union All

Select 4,'20100410','20100430' Union All

Select 5,'20100101','20100331';

Go

Select    *

From    T_Ovdim;

Select    *

From    T_Avodot;

שליפה משתי הטבלאות ללא קישור בינהן יצור מכפלה קרטזית של 20=5*4 שורות:

Select    *

From    T_Ovdim,

        T_Avodot;

אפשר ליצור את הקישור בתנאי ה-Where, אך עדיף להשתמש ב-Join לסוגיו וב-On – כפי שנראה להלן – שהופכים את הקוד ליותר קריא מכיוון שהתנאים מופיעים בסמוך לטבלה אליה הם מתייחסים.

שימושים בסיסיים ב-Join: קישור בין טבלאות

המוכר מכולם הוא ה-Inner Join שמציג שורות מתאימות משתי הטבלאות ומשמיט את אלו שלא נמצאה להן התאמה (ID=2 ו-ID=5):

Select  *

From    T_Ovdim T_O

Inner Join T_Avodot T_A

        On T_O.ID=T_A.ID;

Go

לצידו של הנ"ל מופיע Left Join שמציג את כל השורות מהטבלה הראשונה, ואת המתאימות להן – אם קיימות – מהשניה (יציג את ID=2 אך לא את ID=5):

Select  *

From    T_Ovdim T_O

Left Join T_Avodot T_A

        On T_O.ID=T_A.ID;

Go

מה יקרה אם נרצה להוסיף תנאי שתאריך ההתחלה בטבלת העבודות הוא לאחר 01/04/2010?
אם נוסיף את זה בתור תנאי Where זה יסנן מהשליפה את כל העובדים שאין להם עבודות או שאין להם עבודות לאחר 01/04/2010:

Select  *

From    T_Ovdim T_O

Left Join T_Avodot T_A

        On T_O.ID=T_A.ID

Where   T_A.TaarihHathala>='20100401';

Go

אם עדיין נרצה לראות את כל העובדים, ולצידם אותן עבודות שהחלו לאחר 01/04/2010 נצרף את התנאי ל-On:

Select  *

From    T_Ovdim T_O

Left Join T_Avodot T_A

        On T_O.ID=T_A.ID

        And T_A.TaarihHathala>='20100401';

Go

כלומר- אם נשאלת השאלה מה ההבדל בין שימוש ב-Where לשימוש ב-Join – לגבי Inner Join אין הבדל (לפחות לא לוגית), אבל במקרה של Left Join יש.

שליפות מסוג Left Join מתאפיינות בערכי Null באותם מקומות בהם לא נמצאו שורות מתאימות לטבלה הראשונה. יש המנצלים זאת לזיהוי חריגים ומוסיפים תנאי Where .. Is Null, ויש המציגים ערך ברירת מחדל באותם מקרים תוך שימוש בפונקציה כדוגמת ()IsNull.
לצד Left Join נמצא Right Join שפעולתו דומה רק בסדר ההפוך- כל השורות מהטבלה השניה, ולצידן אלו מהראשונה שנמצאה להן התאמה.
לפיכך מומלץ להשתמש כשגרה באחד מהם – ואני מעדיף את Left Join בו אני מציג קודם את הטבלה העיקרית (למשל- טבלת הפאקט) ובהמשך את המשנית (למשל- טבלת המימד).
מי שרוצה לתפוס שתי צפורים ביד אחת, ולשלוף גם את ה-Left Join וגם את ה-Right Join (ולמעשה גם את ה-Inner Join) – כל זה בבת אחת, ישתמש ב-Full Join:

Select  *

From    T_Ovdim T_O

Full Join T_Avodot T_A

        On T_O.ID=T_A.ID

        And T_O.Taarih=T_A.TaarihHathala;

Go

לפיכך, אם ישלחו אותנו לאי בודד ונוכל להצטייד ב-Join אחד בלבד – מומלץ לקחת את Full Join, ובעזרת Where לחלץ ממנו את מה שמעניין אותנו: Is Not Null על הטבלה הראשונה במקום Left Join,
Is Not Null על הטבלה השניה במקום Right Join,
Is Not Null על שתיהן במקום Inner join,
ו-Is Null על שתיהן כדי למצוא את כל השורות המיותמות בשתיהן.
לסיום סעיף זה וכדי לצאת ידי חובה- ניתן ליצור מכפלות קרטזיות תוך שימוש ב-Join באחת משתי הדרכים הבאות:
1. Inner Join כשתנאי ה-On הוא 1=1 (או כל ביטוי שנכון תמיד ולכן חסר משמעות).
2. להשתמש ב-Cross Join ואז אין צורך בתנאי On:

Select  *

From    T_Ovdim

Cross Join T_Avodot;

Go

האופרטורים In ו-Exists: התניית טבלה אחת באחרת

במבט ראשון האופרטור In משתמש ליצירת תנאי המתבסס על עמודה אחת, למשל- כל השורות מטבלת העובדים שיש להן עבודות:

Select   *

From     T_Ovdim T_O

Where    T_O.ID In (Select T_A.ID

                   From    T_Avodot T_A);

Go

יש דמיון מסויים בין השליפה הזו לבין Inner Join כפי שהוצג קודם (כאמצעי לפלטר שורות מהעובדים שיש להם עבודות) אך יש הבדלים:
1. ב-In כל שורה מהעובדים מוצגת פעם אחת וללא מידע מהעבודות, וב-Join כל התאמה מוצגת עם המידע מהעבודות.
2. מקובל לטעון שהשימוש ב-Join מעט יותר יעיל, אך DBA החרד לשמו הטוב יומר שזה תלוי.
אני אישית מעדיף להשתמש ב-In היכן שאפשר מכיוון שהוא קריא וברור יותר לוגית.
באופן דומה ניתן להשתמש גם ב-Not In, ואז המקבילה שלו מתחום ה-Join עם ההבדלים המתבקשים הוא Left Join עם תנאי Where .. Is Null.
האופרטור Exists משמש לתנאים מורכבים יותר, למשל- תנאי המתבסס על שתי עמודות (אך לא רק).
הנה שליפה של כל העובדים שהתחילו עבודה בתאריך בו נקלטו (עמודת תאריך בטבלת העובדים):

Select   *

From     T_Ovdim T_O

Where    Exists (Select *

                From    T_Avodot T_A

                Where   T_A.ID=T_O.ID

                        And T_A.TaarihHathala=T_O.Taarih);

Go

הסינטקס של Exists מעט מסובך וקשה להבנה בפעם הראשונה, אבל הוא מאוד שימושי בשליפות מורכבות ומומלץ להתיידד איתו,
וגם בו ניתן להשתמש בשלילה (Where Not Exists).
במבט שני, גם כשמשתמשים באופרטור In ניתן ליצור תנאים מורכבים כמו ב-Exists, שחורגים מהמשמעות היסודית והאינטואיטיבית שלו, למשל:

Select   *

From     T_Ovdim T_O

Where    1 In (Select 1

              From    T_Avodot T_A

              Where   T_A.ID=T_O.ID

                      And T_A.TaarihHathala=T_O.Taarih);

Go

כרגע לא עולה בדעתי אפשרות שניתן לבצעה בעזרת Exists אך לא ניתן בעזרת In,
או לחילופין- תנו לי שליפה המשתמשת ב-Exists ואהפוך לכם אותה ל-In..
אם מישהו ימצא משהו- אשמח להתעדכן.

שימוש ב-Join תוך התניה (שילוב של שני הנ"ל)

נפתח בדוגמה:

Select  *

From    T_Ovdim T_O

Cross Apply (Select *

            From    T_Avodot T_A

            Where   T_A.ID=T_O.ID

                    And T_A.TaarihHathala=T_O.Taarih) T;

Go

השליפה הזו המשתמשת ב-Cross Apply דומה במבט ראשון לשימוש ב-In וב-Exists הנ"ל בכך שהשליפה שלאחר האופרטור מתייחסת לזו שלפניה, אך מציגה את העמודות של שתיהן (In ו-Exists משתמשים בשניה לצורך התנאי בלבד ועמודותיה אינן מוצגות).
ניתן איפוא להבחין בדמיון שבין Cross Apply לבין Inner Join, והזריזים יוכלו בוודאי להציג חלופה פשוטה שמגיעה לאותה תוצאה בדיוק:

Select  *

From    T_Ovdim T_O

Inner Join T_Avodot T_A

        On T_O.ID=T_A.ID

        And T_O.Taarih=T_A.TaarihHathala;

Go

נכון, אך מה עם שליפה שמתאימה לכל עובד את העבודה הראשונה שלו תוך שימוש באופרטור Top?

Select  *

From    T_Ovdim T_O

Cross Apply (Select Top 1 *

            From     T_Avodot T_A

            Where    T_A.ID=T_O.ID

            Order By T_A.TaarihHathala) T;

Go

טוב, גם את זה ניתן לעשות בעזרת Inner Join בדרך מתחכמת, אבל עדיין יש דברים שניתן לבצע רק בעזרת Cross Apply, למשל – פונקציה שמחזירה טבלה ושמקבלת את הפרמטר שלה מהטבלה הראשונה.
ניצור פונקציה פשוטה לצורך ההדגמה שמקבלת ID כפרמטר ומחזירה את כל השורות מטבלת העבודות שזה ה-ID שלהן:

Create Function dbo.F_Avodot(@ID As Int) Returns Table As

Return  Select  *

        From    T_Avodot

        Where   ID=@ID;

Go

וכדי ליצור שווה ערך ל-Join בין טבלת העובדים לבינה נעשה כך:

Select  *

From    T_Ovdim T_O

Cross Apply dbo.F_Avodot(T_O.ID);

Go

ואולי דוגמה מתחום שונה לחלוטין: ה-DMV הבא שולף נתונים שונים לגבי ה-Connections הפתוחים:

Select  *

From    sys.dm_exec_connections;

Go

בין היתר יש שם עמודה בשם most_recent_sql_handle שכוללת קוד הקסדצימלי של הפקודה האחרונה שאותו Connection ביצע.
כדי לפענח את הקוד ולהציג אותו ב-SQL-ית יש להשתמש בפונקציה sys.dm_exec_sql_text שתציג אותו בעמודה Text באופן הבא:

Select  DS.Text,

        DC.*

From    sys.dm_exec_connections DC

Cross Apply sys.dm_exec_sql_text(DC.most_recent_sql_handle) As DS;

Go

לצד ה-Cross Aplly (שבמקרים מסויימים הוא שווה ערך ל-Inner Join) נמצא ה-Outer Apply שלצורך העניין הוא שווה הערך ל-Left Join. שתי דוגמאות בהתאם לנ"ל:

Select  *

From    T_Ovdim T_O

Outer Apply (Select *

            From    T_Avodot T_A

            Where   T_A.ID=T_O.ID

                    And T_A.TaarihHathala=T_O.Taarih) T;

Go

Select  *

From    T_Ovdim T_O

Outer Apply dbo.F_Avodot(T_O.ID);

Go

שימוש בשליפות משנה בנות שורה ועמודה אחת

להשלמת התמונה- שליפות משנה המחזירות שורה אחת בלבד ועמודה אחת המהווה חלק מה-Select או חלק מתנאי מהסוג של <=> ב-Where.
למשל- שליפה של כל העובדים ותאריך ההתחלה הראשון שלהם:

Select  *,

        (Select Top 1 TaarihHathala From T_Avodot T_A Where T_A.ID=T_O.ID Order By TaarihHathala) TaarihHathala

From    T_Ovdim T_O;

Go

או אולי של אלו שהתחילו לעבוד עם קבלתם לעבודה:

Select   *

From     T_Ovdim T_O

Where    Taarih=(Select Top 1 TaarihHathala From T_Avodot T_A Where T_A.ID=T_O.ID Order By TaarihHathala);

Go

יש לוודא שרק שורה אחת ורק עמודה אחת מוחזרות על ידי שליפת המשנה בשימושים האלו,
וליתר בטחון ניתן להיעזר באופרטור Top או ב-Group By או ב-Distinct (עבור השורות),
ולציין במפורש את שם השדה ולא להשתמש ב-* ב-Select.

שליפות אנכיות: השימוש באופרטורים Union ו-Except

בכל הסעיפים הקודמים- שתי הטבלאות שכנו זו לצד זו וחיפשנו התאמות בין עמודות ה-ID והתאריכים.
בסעיף הזה נחבר ונחסר את הטבלאות על פי הצורך.
אם אנחנו רוצים למצוא את ה-ID-ים שבשתי הטבלאות, כולל אלו שנמצאות רק באחת מהן, נוכל להשתמש ב-Union All כך:

Select  ID

From    T_Ovdim

Union All

Select  ID

From    T_Avodot;

Go

אם רוצים לנטרל את הכפילויות, משתמשים ב-Union במקום Union All ואז המערכת מבצעת Distinct על השליפה:

Select  ID

From    T_Ovdim

Union

Select  ID

From    T_Avodot;

Go

אם לא צפויות כפילויות – כדאי להסתפק ב-Union All ולחסוך למערכת את הצורך בבדיקת הנתונים.
אפשר, כמובן, לקבל את הרשימה בדרכים אחרות, למשל- תוך ביצוע Full Join בין שתי הטבלאות והצגת ה-ID שאינו ריק (אם אחד מהשניים הוא Null).
אם אנחנו רוצים להציג את כל ה-ID שמופיעים בטבלת העובדים ואינם בטבלת העבודות – נחסר מהראשונה את השניה כך:

Select  ID

From    T_Ovdim

Except

Select  ID

From    T_Avodot;

Go

גם במקרה זה ניתן לפנות לחלופות  כמו Not In או אולי Left Join עם Is Null על הטבלה השניה – תלוי איזה מידע אנחנו רוצים לקבל (השימוש ב-Except מחזיר רק את עמודת ID).