מדריך SQL – JOIN, UNION, CASE WHEN

במדריך SQL הקודם, למדנו על GROUP BY ואיך משתמשים בו. במדריך הבא, כחלק מתכנית קורס SQL למתחילים, נלמד הכל על:

JOIN, UNION, CASE WHEN.

תכירו, יש כל מיני סוגי JOIN:

  1. LEFT JOIN
  2. RIGHT JOIN
  3. INNER JOIN
  4. CROSS JOIN
  5. FULL OUTER JOIN

SQL JOINS משמשים לחיבור בין טבלאות.

בגדול, הכלל הוא כזה:

אם אתם רוצים לחבר בין טבלה מסוימת לאחרת, אתם צריכים לקבוע מה סוג החיבור.

נשמע מסובך? אל תדאגו, זה בגלל שאנחנו בהתחלה.

INNER JOIN – משמש כאשר אנו רוצים את הערכים שיחזרו רק כשיש התאמה בין שתי הטבלאות.

בואו נראה דוגמא:


טבלת SQL תרגול first name, last name, data role, domain, email, salary, country code


יש לנו טבלה שאנחנו כבר מכירים.

אנחנו רוצים לנתח את הנתונים לפי מדד של מדינות.

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

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

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

טבלת מדינות בעלת שתי עמודות country_code, country SQL

LEFT JOIN

לפניכם שתי טבלאות:

  1. table1 עם salary, country_code.
  1. table2 עם country_code, country.

אנחנו רוצים לייבא ל-table1, את השם המלא של המדינה.

טבלת sql המכילה שתי עמודות: קוד מדינה ומדינהטבלת sql המכילה עמודת שכר וקוד מדינה

אם אנחנו רוצים לקבל תוצאה בה כל העמודות מטבלה אחת נשארות, ואנו נקבל את כל ההתאמות מטבלה שתיים, אשתמש ב-SQL LEFT JOIN.

בכללי, זו אחת הצורות היעילות ביותר לשימוש ב-JOIN. למה, בעצם?

  1. היא דומה ל-VLOOKUP מאקסל.
  1. היא לא מחסירה לנו שורות מהטבלה המובילה.

שאלה: כמה שורות יתקבלו?


טבלת sql עם שלוש עמודות טבלת שכר מדינה וקוד מדינה לתרגול LEFT JOIN

משמעות “null” – לא נמצאה התאמה.


SELECT salary,table1.country_code,country

FROM table1

LEFT JOIN table2

ON table1.country_code = table2.country_code

שימו לב למשהו חדש שלמדנו:

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

נסו בעצמכם, מה היה קורה אם הייתם בוחרים להציג את country_code מ-table2?

  1. שימו לב ל-Syntax, תמיד נעשה חיבורים סוג ה JOIN ואיזו טבלה נחבר , ואז המפתח המקשר לאחר ה ON

JOIN/ INNER JOIN

לפניכם, שתי טבלאות.

המטרה שלנו, היא למצוא רק את ההתאמה המלאה:

רק את השורות שיש בהן התאמה בין שתי הטבלאות.

אנחנו רוצים לייבא לטבלה table1 את השם המלא של המדינה.

טבלת sql המכילה שתי עמודות: קוד מדינה ומדינהטבלת sql המכילה עמודת שכר וקוד מדינה

שאלה: כמה שורות נקבל?


תשובה: נקבל רק שתי שורות, כי רק בשתי שורות יש התאמה.


טבלת sql עם שלוש עמודות טבלת שכר מדינה וקוד מדינה לתרגול JOIN/INNER JOIN

SELECT salary,table1.country_code,country

FROM table1

JOIN table2

ON table1.country_code = table2.country_code

RIGHT JOIN

לפניכם, שתי טבלאות.

המטרה שלנו היא למצוא רק את ההתאמה המלאה, כאשר הטבלה המובילה היא table2.

טבלת sql המכילה שתי עמודות: קוד מדינה ומדינהטבלת sql המכילה עמודת שכר וקוד מדינה

שאלה: כמה שורות נקבל?

תשובה:

נקבל ארבע שורות:


טבלת sql עם שלוש עמודות טבלת שכר מדינה וקוד מדינה לתרגול RIGHT JOIN

SELECT salary,table2.country_code,country

FROM table2

RIGHT JOIN table1

ON table1.country_code = table2.country_code

שימו לב, שקיבלנו את כל השורות מ-table2, אבל לא קיבלנו התאמה מלאה ולכן חלק משורות השכר לא התמלאו.

CROSS JOIN

לפניכם, שתי טבלאות.

המטרה שלנו היא להבין מה המשמעות של פעולה זו:

SQL CROSS JOIN מתבצעת בדרך כלל על מניפולציות מורכבות יותר, ובעצם מכפילה כל שורה במספר השורות בטבלה השנייה.


טבלת sql המכילה שתי עמודות: קוד מדינה ומדינהטבלת sql המכילה עמות שכר וקוד מדינה

שאלה: כמה שורות נקבל?


תשובה: נקבל 16 שורות.


טבלת sql עם שלוש עמודות טבלת שכר מדינה וקוד מדינה לתרגול CROSS JOIN

SELECT salary,table2.country_code,country

FROM table2

CROSS JOIN table1

שימו לב, שקיבלנו את כל השורות מ-table2. אבל, לא קיבלנו התאמה מלאה ולכן, חלק משורות השכר לא התמלאו.

FULL OUTER JOIN

לפניכם, שתי טבלאות.

המטרה שלנו היא להבין מה המשמעות של פעולה זו:

FULL OUTER JOIN מיועדת לשימוש במצבים בהם יש ערך לקבלת כל העמודות, גם כשאין התאמה מלאה.

דוגמא:

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

נניח ולא השקענו בשיווק בארה״ב, ועדיין יש לנו הכנסות מהמדינה.

לעומת זאת, כן השקענו בשיווק ביוון, אבל לא היו לנו הכנסות.

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

כיצד הטבלה תיראה אצלנו?


טבלת sql המכילה שתי עמודות: קוד מדינה ומדינהטבלת sql המכילה עמודת שכר וקוד מדינה

שאלה: כמה שורות נקבל?

תשובה: נקבל שש שורות.


טבלת sql עם עמודות טבלת שכר מדינה וקוד מדינה לתרגול FULL OUTER JOIN

SELECT table1.country_code,country, table2.country_code,salary

FROM table2

FULL OUTER JOIN table1

ON table1.country_code = table2.country_code

שאלות SQL מסוג JOIN:


טבלת sql עמודות first name, last name, data role, domain, email, salary, country code לתרגול שאלות sql מסוג FULL OUTER JOIN

שאלות:

  1. חברו בין הטבלאות והציגו רק את העובדים ממדינת ארה״ב.
  2. חברו בין הטבלאות והציגו את המדינות בהן לא היו עובדים כלל.
  3. חברו בין הטבלאות והציגו את רשימת המדינות DISTINCT המופיעות בטבלה הראשונה או השנייה.
  4. חברו בין הטבלאות, והציגו את המדינות שיש בהן עובדים (ממוינים מהגדול לקטן, לפי כמות עובדים).

CASE WHEN

CASE WHEN היא פונקציה פשוטה, יעילה ושכיחה ביותר.

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

יש לזכור: כשאנחנו מפעילים פונקציה ב-SQL, היא תבצע את החישוב על כל העמודה.

אז, איך כותבים אותה?

CASE WHEN [COLUM] = THEN 1 ELSE 0 END

דוגמא:

נניח ואנחנו רוצים להוסיף עמודה חדשה שבה יהיה רשום:

כל מי שמרוויח מעל $40,000, מרוויח – High.

כל מי שמרוויח בין $30,000 ל-$39,999, מרוויח – Medium.

כל מי שמרוויח פחות מ-$30,000, מרוויח – Low.


טבלת אימייל ושכר sql Marita, Elsworth, Kathryn, Maura

שאלה: כמה שורות נקבל?


SELECT

Email,

salary,

CASE WHEN salary>40000 THEN ‘High’

WHEN salary between 30000 and 39999 then THEN ‘Medium’

ELSE ‘Small’ END as salary_bucket

FROM Data_salaries


טבלת sql תרגול CASE WHEN עמודות אימייל שכר מידת רווחיות Marita, Elsworth, Kathryn, Maura

שימו לב לכמה דברים שלמדנו והשתמשנו בהם כאן:

  1. between < אנחנו משתמשים בו כל הזמן, הוא לא מוגבל רק למקומות מסוימים כמו WHERE.
  1. שימו לב להיררכיה: בהתחלה עשינו תנאי ״גדול מ-40000״. אם לא עומד בתנאי, ממשיך לשורה הבאה, וכן הלאה.

חשוב מאוד להבין את העיקרון.

UNION ALL

UNION ALL הינה פונקציה יעילה ושכיחה ביותר.

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

בדוגמא שלנו, אנחנו מעדכנים את הנתונים על אנשי הדאטה אחת לחודש, ושומרים אותם בתור טבלאות נפרדות בדאטה בייס. כדי לאחד ביניהם, אנחנו נשתמש ב-UNION ON.


טבלאות sql עמודות month, first name, last name, data role, domain תרגול UNION ALL

לפנינו table1 ,table2. אם נרצה לאחד ביניהם, נשתמש בפקודה הנ״ל.

שאלה: כמה שורות נקבל?

SELECT

Month,

First_name,

Last_name,

Data_role,

domain

FROM table1

UNION ALL

SELECT

Month,

First_name,

Last_name,

Data_role,

domain

FROM table2

נקודות חשובות:

ישנן טכניקות מתקדמות מאוד לשימוש ב-UNION כדי לפתור בעיות.

אחת מהן, היא איחוד מקורות של נתונים ממקומות שונים.

דוגמא:

נניח ותרצו לאחד בין דו״ח ביצועי שיווק לאתר שלכם, עם דו״ח רכישות באתר.

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

CTE/ WITH

אחת הדרכים הכי נוחות לניהול קוד, היא שימוש ב-WITH.

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

דוגמא:

SELECT AVG(salary) salary,table1.country_code,country

FROM table1

JOIN table2

ON table1.country_code = table2.country_code

WHERE salary >30000

GROUP BY table1.country_code,country

איך SQL עובד מאחורי הקלעים?

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

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

אז, אלו הכללים:

  • FROM
  • JOIN
  • WHERE
  • GROUP BY
  • ORDER BY
  • WINDOWS FUNCTIONS

דוגמא:

SELECT

data_role,

AVG([salary]),

FROM Data_salaries

GROUP BY data_role

HAVING data_role =’VP’

VS

SELECT

data_role,

AVG([salary]),

FROM Data_salaries

where data_role =’VP’

GROUP BY data_role

נגיד, ויש לנו טבלה של מאה מיליון שורות, ויש לנו עשר שורות שעונות לתנאי VP.

מה קורה בשאילתה הראשונה?

  1. קודם כל, מתבצעת פעולת ה-FROM.
  2. לאחר מכן, GROUP BY.
  3. ולבסוף, אנחנו מסננים את התוצאות.

מה קורה בשאילתה השנייה?

  1. קודם כל, מתבצעת פעולת ה-FROM.
  2. לאחר מכן ה-WHERE.
  3. לבסוף, GROUP BY.

להלן הסבר על שתי הטבלאות:

אנחנו קוראים את הטבלה, מסננים את הטבלה ממאה מיליון שורות רק לעשר, ואז מיישמים את פעולת GROUP BY.

בעוד שבטבלה הראשונה, GROUP BY מיושמת על מאה מיליון שורות, נראה לנו די ברור שהטבלה הראשונה הרבה פחות יעילה, כי היא מבצעת פעולה לא הכרחית על כל הטבלה.

שימו לב, הכרת הפעולות ושינון הסדר, חיוני מאוד על מנת להתקדם לשלב הבא בלהיות SQL Master.

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

Practice makes perfect 🙂