Join, Semi Join, Anti Semi Join
ב-Execution Plans אנחנו מוצאים לא פעם את החיוויים הנ"ל צמודים ל-Hash Match או ל-Nested Loops.
מה המשמעות שלהם?
לגבי Join, Left Join, Right Join לא ארחיב כאן ורק אזכיר שהם מחזירים את כל ההתאמות בין שתי טבלאות או סטים בכפוף להגדרות והתנאים, ואציין כאן שתי דוגמאות מפוסט קודם:
--1
Select *
From messages M
Inner Join syslanguages L
On M.language_id=L.lcid;
--2
Select *
From messages M
Cross Apply (Select *
From syslanguages L
Where M.language_id=L.lcid) L;
הסינטקס שונה אך מדובר בפעולות Join זהות במקרה זה: כל ההתאמות בין הטבלה העליונה לתחתונה מוצגות.
Semi Join ("חצי" Join, Join חלקי..) הוא פעולה שמחזירה את כל השורות מהטבלה הראשונה (Left Semi Join) / מהטבלה השניה (Right Semi Join) שיש להן התאמה בטבלה השניה / הראשונה בהתאם. כלומר- לא כל ההתאמות בין שתי הטבלאות חוזרות, אלא כל השורות מהטבלה האחת שיש להן התאמה באחרת (השורה תוצג פעם אחת גם אם יש לה מספר התאמות).
רוצה לומר שהמערכת מחפשת לכל שורה בטבלה האחת התאמה באחרת, ואם היא מצאה- היא עוצרת את החיפוש ומציגה אותה.
Anti Semi Join ("חצי" Join הפוך) הוא פעולה שמחזירה את כל השורות מהטבלה הראשונה (Left Anti Semi Join) / מהטבלה השניה (Right Anti Semi Join) שאין להן התאמה בטבלה השניה / הראשונה בהתאם. המערכת מחפשת לכל שורה בטבלה האחת התאמות באחרת, אם היא מוצאת היא עוצרת את החיפוש ולא מציגה אותה, ואם היא לא מוצאת – היא כן מציגה אותה.
דוגמאות, ונתחיל מיצירת שתי טבלאות – אחת גדולה ואחת קטנה - להדגמה:
If Object_Id('messages','U') Is Not Null Drop Table messages;
Go
Select *
Into messages
From sys.messages;
Go
If Object_Id('syslanguages','U') Is Not Null Drop Table syslanguages;
Go
Select *
Into syslanguages
From sys.syslanguages;
Go
--טבלה גדולה
Select *
From messages;
--טבלה קטנה
Select *
From syslanguages;
Right Semi Join
--Right Semi Join
Select *
From messages M
Where Exists (Select 1
From syslanguages L
Where L.msglangid=M.language_id);
Select *
From messages
Where language_id In (Select msglangid
From syslanguages);
לשתי השליפות הנ"ל אותו Execution Plan: המערכת מבצעת Scan לטבלה השניה/התחתונה (messages) ולכל שורה מחפשת התאמה ב-Hash Table של הראשונה, ואם יש- מציגה אותה.
Right Anti Semi Join
--Right Anti Semi Join
Select *
From messages M
Where Not Exists (Select 1
From syslanguages L
Where L.msglangid=M.language_id);
Select *
From messages
Where language_id Not In (Select msglangid
From syslanguages);
שוב- שני Execution Plans זהים לשתי השליפות: המערכת מבצעת Scan לטבלה השניה/התחתונה (messages) ולכל שורה מחפשת התאמה ב-Hash Table של הראשונה, ואם יש- אינה מציגה אותה.
Left Semi Join
--Left Semi Join
Select *
From syslanguages L
Where Exists (Select 1
From messages M
Where L.msglangid=M.language_id);
Select *
From syslanguages
Where msglangid In (Select language_id
From messages);
Select msglangid
From syslanguages
Intersect
Select language_id
From messages;
Select language_id
From messages
Intersect
Select msglangid
From syslanguages;
לשליפות הראשונה והשניה אותם Execution Plans, והם די דומים ל-Right Semi Joins שפגשנו קודם מבחינת הסינטקס אך לא מבחינת ה-Execution Plans שכן הטבלה הגדולה messages והטבלה הקטנה syslanguages החליפו מקומות והפניות החוזרות ונשנות לגדולה מחייבות תחליף לאינדקס שאינו קיים וזה ה-Table Spool (טבלה אגרגטיבית זמנית שנוצרת ב-tempdb).
מה שמעניין יותר הוא שתי השליפות האחרונות בהן פקודת Intersect יוצרת Join בין העמודות המתאימות בשתי הטבלאות ומסננת כפילויות. למרות שהתוצאות זהות והאופרטור Intersect הוא סימטרי- המערכת בחרה במקרה הראשון להתחיל עם הטבלה הקטנה, למיין אותה ולכל שורה לחפש התאמה בגדולה (לאחר שעברה אגרגציה למניעת כפילויות); ובמקרה השני להתחיל דווקא מהגדולה, לבצע לה אגרגציה, ולחפש התאמות בקטנה מבלי למיין אותה תחילה.
אפשר להבין שכדי למנוע כפילויות המערכת בוחרת למיין (כדי לאתר אותם בקלות) או לבצע אגרגציה.
בשני המקרים זה Left Semi Join אבל בכל פעם מתחילים מטבלה אחרת, וזה קצת מפתיע לאור העובדה ש-Intersect הוא אופרטור סימטרי והסדר אינו אמור להיות משמעותי עבורו.
Left Anti Semi Join
--Left Anti Semi Join
Select *
From syslanguages L
Where Not Exists (Select 1
From messages M
Where L.msglangid=M.language_id);
Select *
From syslanguages
Where msglangid Not In (Select language_id
From messages);
Select msglangid
From syslanguages
Except
Select language_id
From messages;
Select language_id
From messages
Except
Select msglangid
From syslanguages;
גם כאן שתי השליפות הראשונות זהות מבחינת ה-Executions Plans והפלטים, ומה שמעניין הוא שתי השליפות האחרונות:
בשתיהן התבצעה פעולת Except (חיסור של הטבלה השניה מהראשונה) שמשעותה דומה בבירור ל-Left Anti Semi Join (להציג את השורות מהראשונה שאינן בשניה). Except אינה סימטרית ולכן ברור מדוע יש הבדל ב-Execution Plans.
לסיכום: יוסף בורג ז"ל – שר הפנים הנצחי – אמר פעם- ימין ושמאל רק חול וחול (ציטוט משיר תמים בהקשר פוליטי),
ובאופן דומה נוכל לומר- Left Anti Semi or Right Anti Semi – כולם אנטישמים!