מדריך SQL – נושאים מתקדמים ב-JOIN

רצוי לעבור על פרק זה לאחר שרכשת ידע בסיסי ב-SQL

מבט כללי

שאילתות join הן שאילתות איחוד, שמאפשרות לנו לשלוףלעדכןלמחוק (select/update/delete) משתי טבלאות או יותר, ע"פ תנאי מסויים.

בפרק זה אפרט על כל סוגי שאילתות האיחוד ועל איחוד מקונן ועל ריבוי תנאים לאיחוד.

• שאילתת Inner Join.
• שאילתת Left Outer Join.
• שאילתת Right Outer Join.
• שאילתת Full Outer Join.
• שאילתת Cross Join.
• שאילתת Self Join.
• שאילתות איחוד מקוננות (שאילתה בתוך שאילתה בתוך שאילתה וכו'..).
• שאילתות איחוד מרובות תנאים.

* המנוע של אקסס לא מזהה את המילה Outer, לכן להוריד אותה בשימוש בLeft או בRight.

* Full Outer Join אינו נתמך במנוע של אקסס.

* מי שלא יודע קשרי גומלין, רצוי שיקרא את המאמר של ניר בנושא.

בעזרת קשרי הגומלין שאילתות האיחוד יוכלו לזהות רשומות מתאימות ורשומות לא מתאימות, וגם יהיו יותר מהירות.

תחביר כללי

משפטי האיחוד נכתבים בתוך פיסקת הfrom (מהסיבה הפשוטה שבאותה פיסקה קובעים מאיזה טבלה/טבלאות לשלוף).

SELECT  
FROM tbl1 
     <join type> JOIN tbl2 ON tbl1.<field>=tbl2.<field> 

דוגמה

הדוגמאות במאמר זה מבוססות על המסד שניתן להורדה בפרק "תכנון מסד הנתונים" שבמדריך זה.

בהמשך נעזר בטבלאות ונראה את התוצאות ששאילתות האיחוד מחוללות בהן.

Inner Join

(חזרה לראש הדף)

Inner Join זהו סוג של שאילתת איחוד, שמהותה היא שליפה של הנתונים שנמצאו מתאימים בשתי הטבלאות (מימין ושמאל לjoin) בלבד.

לסוג זה של שאילתת איחוד קיימות שתי קריאות שונות:

קריאה לא ספציפית:

SELECT <fields> FROM tbl1, tbl2
WHERE tbl1.<field>=tbl2.<field> 

קריאה ספציפית:

SELECT <fields> 
FROM tbl1 
     INNER JOIN tbl2 ON tbl1.<field>=tbl2.<field> 

נניח שביצענו את השאילתה הבאה על טבלת הספרים (titles) וטבלת המו"לים (מוצאים לאור, publishers).

SELECT titles.*, publishers.pubId, publishers.Name
FROM titles 
     INNER JOIN publishers ON titles.pubId=publishers.pubId

התוצאה תהיה טבלה מאוחדת עם כל הספרים והid והשם של המו"לים שלהם.

את התוצאה תוכלו לראות במסד שניתן פה להורדה, תחת שם השאילתה inner join.

Left Outer Join

(חזרה לראש הדף)

Left Join זהו סוג של שאילתת איחוד, שמהותה היא שליפה של כל הנתונים בטבלה שמשמל לleft join בשאילתה ושליפה של כל הרשומות שנמצאו מתאימות בטבלה השנייה (מימין לleft join בשאילתה).

השאילתה נראית כך:

SELECT <fields> 
FROM tbl1 
     LEFT JOIN tbl2 ON tbl1.<field>=tbl2.<field> 

עשינו את השאילתה הבאה:

SELECT titles.title, titles.ISBN, publishers.pubId, publishers.Name
FROM titles 
     LEFT JOIN publishers ON titles.pubId=publishers.pubId; 

השאילתה שולפת את כל הרשומות מטבלת הספרים (titles) ואך ורק את הרשומות שנמצאו מתאימות מטבלת המו"לים.

את התוצאה תוכלו לראות במסד שניתן כאן להורדה.

Right Outer Join

(חזרה לראש הדף)

Right Join זהו סוג של שאילתת איחוד, שמהותה היא שליפה של כל הנתונים בטבלה שמימין לright join בשאילתה ושליפה של כל הרשומות שנמצאו מתאימות בטבלה השנייה (משמאל לright join בשאילתה).

השאילתה נראית כך:

SELECT <fields> 
FROM tbl1 
     RIGHT JOIN tbl2 ON tbl1.<field>=tbl2.<field>

ולדוגמה, ביצענו את השאילהת הבאה:

SELECT publishers.pubId, publishers.Name, titles.title, titles.ISBN
FROM titles 
     RIGHT JOIN publishers ON titles.pubId=publishers.pubId;

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

התוצאה במסד להורדה.

Full Outer Join

(חזרה לראש הדף)

Full Outer Join זהו סוג של שאילתת איחוד, שמחזירה את כל הרשומות שלא נמצאו מתאימות בין הטבלאות שמימין ומשמאל לfull outer join.

למעשה, שאילתת איחוד זו היא ההפך לinner join (כי inner join מחזירה את כל הרשומות שכן נמצאו מתאימות).

השאילתה נראית כך:

SELECT <fields> 
FROM tbl1 
     FULL OUTER JOIN tbl2 ON tbl1.<field>=tbl2.<field>

ולדוגמה, ביצענו את השאילהת הבאה: (לא על אקסס)

SELECT publishers.*, titles.pubId
FROM publishers 
     FULL OUTER JOIN titles ON publishers.pubId=titles.pubId;

היינו מקבלים את כל המו"לים שלא הוציאו ספרים (את התוצאות אני לא יכול להציג כי אני עובד עם אקסס, ובאקסס אין full outer join).

אם כל המו"לים כבר הוציאו ספרים לאור, אז הטבלה שנקבל משאילתת האיחוד הנ"ל תהיה ריקה.

Cross Join

(חזרה לראש הדף)

Cross Join זהו סוג של שאילתת איחוד, שזהה במהותה לinner join, אבל יש הבדל אחד ביניהם – בcross join לא כותבים ON (תנאי איחוד).

בcross join, הטבלה שמשמאל היא כמו הטבלה השמאלית בleft join (עבור כל רשומה בה יוחזרו כל הרשומות המתאימות בטבלה הימנית המקושרת).

כדי להתנות שאילתת איחוד מסוג זה מוסיפים פיסקת where לשאילתה, ואם ישנם כמה תנאים כותבים ביניהם or או and.

כמו לinner join, גם לcross join יש שתי קריאות שונות:

קריאה לא ספציפית:

SELECT <fields> FROM tbl1, tbl2 

קריאה ספציפית: (לא נתמכת באקסס)

SELECT <fields> FROM tbl1 CROSS JOIN tbl2  

ביצענו את השאילתה הבאה:

SELECT MAX(Authors.AU_ID) AS maxAuthors, MAX(publishers.pubId) AS maxPublishers
FROM Authors, publishers; 

נקבל את מספר הכתבים (המקסימלי) ואת מספר המו"לים (המקסימלי):

ניתן היה גם להתנות את תוצאות השאילתה ע"י where, ובכך להגיע לממצאים הרצויים לכם.

Self Join

(חזרה לראש הדף)

בשאילתת האיחוד self join, הטבלה עושה inner join לעצמה ע"י קריאה לא ספציפית וalias לטבלה.

בסוג איחוד זה יש אפשרות להוסיף כמה תנאים לWHERE ע"י שימוש באופרטורים And ו – Or.

תחביר:

SELECT <fields>
FROM tbl1 AS t1, tbl2 AS t2
WHERE t1.<field>=t2.<field> And t1.<field2><>t2.<field2> 

שאילתות איחוד מקוננות

(חזרה לראש הדף)

כמו שניתן לעשות שאילתות מקוננות, כך ניתן לעשות גם שאילתות איחוד מקוננות.

כל תת שאילתה מתבצעת על השאילתה שלפניה.

התחביר:

SELECT <fields> 
FROM tbl1 
     <join type> JOIN (tbl2 <join type> JOIN tbl3
            ON tbl2.field2<>tbl3.field)
            ON tbl1.field=tbl2.field1 

שאילתות איחוד מרובות תנאים

(חזרה לראש הדף)

אם ברצוננו לעשות שאילתה שתבדוק כמה תנאים לאיחוד, באפשרותנו להשתמש באופרטורים And ו-Or.

התחביר: (לדוגמה)

SELECT <fields> 
FROM tbl1 
     INNER JOIN tbl2 ON tbl1.field=tbl2.field 
          And tbl1.field2<>tbl2.field2 
         Or tbl1.field3<tbl2.field3

ניתן להוסיף תנאים לאיחוד גם בקריאה לא ספציפית (ע"י where).

לדוגמה:

SELECT <fields> 
FROM tbl1, tbl2 
WHERE tbl1.field=tbl2.field And tbl1.field2<>tbl2.field2 
     Or tbl1.field3<tbl2.field3 

הערות

* בכל שאילתות איחוד כתבו רק ON אחד, גם בשאילתות מרובות תנאי איחוד.

* השימוש בcross join לא כל כך נפוץ.

* cross join בקריאה ספציפית לא נתמך באקסס.

* בעיקרון, left וright זהות במשמעותן, אך נגדיות (בleft נשלפות כל הרשומות מהטבלה השמאלית, בוright מהטבלה הימנית).

* ניתן גם בrightleft להשתמש בקריאה לא ספציפית: (לא מומלץ, בSQL SERVER יכולים להתקבל נתונים שגויים).

SELECT <fields> 
FROM tbl1, tbl2 
WHERE tbl1.field*=tbl2.field

הורדת המסד

במסד שתי הטבלאות עם בדיוק אותם נתונים כמו שהראתי פה, כולל קשר גומלין, וכמה שאילתות view להדגמת שאילתות join (חוץ מfull outer join שלא נתמך באקסס וחוץ מself join).

להורדה לחץ כאן (מכווץ לזיפ).