האנטומיה של שאילתת SQL

[yossiz]

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

הקדמה ל-SQL ולמדריך

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

שפת SQL הינה שפה דקלרטיבית. כלומר, אתה לא אומר למנוע ה-DB מה לעשות. אתה רק מתאר לו מה אתה רוצה לקבל ממנו והוא כבר מחליט לבד מה לעשות.
כתוצאה מכך, אפשר לראות בשאילתת SQL 3 שכבות זו לפנים מזו.
א) שכבת התחביר. זו המעטפה, הקליפה, אני לא רוצה להתייחס לזה יותר מדי. (חבל שאי אפשר לחבר שאילתה חוקית מבלי להכיר את התחביר... ועוד יותר חבל שהתחביר משתנה ממנוע למנוע... נקדיש לזה קטע קצר להלן).
ב) שכבת השאילתה הלוגית. זו הבקשה שמסתתרת מתחת למילים של השאילתה. אני רוצה להתמקד בשכבה זו. כאשר אתה מייצר שאילתה, אתה קודם כל חושב על שאילתה לוגית, אח"כ אתה מתרגם את זה למילים בשכבת התחביר.
ג) שכבת הביצוע. זו הפעולות שאותם המנוע יבצע. לא תמיד מה שביקשת ובסדר שביקשת הוא מה שהמנוע יבצע. הרשות ניתנת למנוע לבצע איזה פעולה שהיא רוצה ובסדר שהיא רוצה ובתנאי שהתוצאה יהיה אותה תוצאה של הפעולה הלוגית שביקשת. אני לא הולך לדבר על נושא זו מכיון שבינתיים אני לא מכיר אותה. נושא זה נחוץ עבור מיטוב ביצועי השאילתה.

טיפת תחביר

התחביר של שאילתת SQL נראה כך:
SELECT <עמודות...>
FROM <טבלאות...>
WHERE <תנאים>
GROUP BY <ביטוי>
HAVING <תנאים>
ORDER BY <ביטוי>
OFFSET <מספר>
LIMIT <מספר>
(נראה לי שבשני החלקים הנ"ל יש שינויים בין המנועים)
; (לסיום המשפט)

כמה נקודות (בלתי אפשרי להקיף פה את הכל, אני רק כותב כמה נקודות):

• השפה הינה case insensitive, (לא מבדילה בין אותיות גדולות לקטנות). אפשר לכתוב SeLecT גם כן.
• שמות הטבלאות והעמודות הם בברירת מחדל case insensitive. תבדקו את התיעוד של המנוע שלכם לגבי יצירת עמודות שהם case sensitive ואיזכורם. ב-postgres אם שם העמודה הינה case sensitive צריך להזכיר את שם העמודה והטבלה בתוך גרשיים (לא ככה: "Table.Column" וגם לא ככה: 'Table.Column' או ככה: 'Table'.'Column' אלא ככה: ‎"Table"."Column"‎ - כן כן, מגעיל... בשנות השמונים או השבעים לא ידעו לעשות תחבירים אלגנטיים כנראה...).
• לא כל החלקים חיוביים. בעיקרון אפשר להשמיט את הכל חוץ מה-SELECT. כל השאר הם אופצינאליים לפחות בחלק מהמקרים. חלק מהם הם חיוביים במקרים מסויימים. (עזרתי מאוד, לא? הכללים הם הגיוניים ולא קשים לניחוש אחרי שאתה מכיר את המבנה של השאילתה שנרחיב עליו בקטע הבא).

השאילתה הלוגית

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

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

סדר הפעולות היא כזאת:

• FROM - בחירת טבלה גולמית (כולל חיבורים עם טבלאות אחרות).
• WHERE - סינון השורות
• GROUP BY - קיפול של כמה שורות לתוך שורה אחת
• HAVING - שוב סינון על השורות המקופלות
• SELECT - בחירת עמודות מתוך התוצאה של השלבים הקודמים (כולל יצירת עמודות חדשות שמחושבות מהתוצאה של השלבים הקודמים)
• DISTINCT - מחיקת כפלויות
• UNION - חיבור תשובות משאילתה אחרת
• ORDER BY - מיון השורות
• OFFSET דילוג לשורה מסויימת
• LIMIT - מחיקת השורות מה-limit והלאה.

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

עכשיו נפרט קצת על כל שלב.

FROM - בחירת טבלה גולמית

כמה נקודות:

• שלב זה אופציונאלית. אם משמיטים את השלב הכוונה היא שאנחנו רוצים שהמנוע יחזיר טבלה חדשה יש מאין. צריך להשמיט גם את כל השלבים עד שלב ה-SELECT כי כולם הם פעולות על הטבלה הגולמית - שלא קיימת במקרה זו.
  אפשר להשתמש ב-SELECT ערטילאית כמו סוג של echo או console.log כדי לקבל את הערך של ביטוי. לדוגמה SELECT 20+4;‎ מחזיר טבלה של 1×1 עם הערך 24. או SELECT sqlite_version();‎ ב-sqlite מחזיר טבלה של 1×1 עם הערך של הגירסה של המנוע.
• ה-FROM מקבלת כל ביטוי שמחזיר טבלה.
  ביטויים שמחזירים טבלאות:
  • שם של טבלה, לדוגמה FROM "SomeTable"‎
  • פונקציה שמחזירה טבלה, לדוגמה FROM generate_series(1,100)‎ (הפונקציה generate_series ב-postgres מחזירה טבלה - [תיעוד])
  • שאילתה שמחזירה טבלה, דוגמה: FROM (SELECT * FROM "SomeTable")
  • ביטוי שמחזירה טבלה משולב עם עוד ביטוי שמחזירה טבלה באמצעות JOIN

JOIN - חיבור כמה טבלאות ביחד

אפשר לחבר כמה טבלאות ביחד על ידי פעולת JOIN.
התחביר של JOIN:

• <טבלה> (ביטוי שמחזיר טבלה, כנ"ל)
• JOIN (עם פירוט של סוג ה-JOIN)
• <טבלה> (ביטוי שמחזיר טבלה, כנ"ל)
• ON <תנאי> (או USING <עמודה>)

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

יופי, קיבלנו את הטבלה הגולמית, עכשיו נעבור לסינון

WHERE - סינון שורות

התחביר של WHERE:

• WHERE <תנאי>

זהו!
מהו התנאי? זה ביטוי שמחזיר ערך בוליאני (TRUE או FALSE).
המנוע מעביר את כל השורות דרך התנאי ומוחקת את השורות שלהם הביטוי מחזירה FALSE. כאשר בתוך הביטוי אפשר להזכיר את הערכים של השורה שמועברת דרכה.
דוגמאות:

• WHERE 1 - תמיד מחזיר TRUE
• WHERE "SomeColumn" = 25‎‎
• WHERE some_condition() AND another_condition OR something_else()‎

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

אפשר להשמיט לגמרי את ה-WHERE ואז לא מתבצעת סינון

GROUP BY - קיפול כמה שורות לתוך שורה אחת

התחביר:
GROUP BY <ביטוי שמחזיר עמודה>
הפעולה: פעולה זו מקפלת כמה שורות לתוך שורה אחת, מעתה ולהלן לא תוכל לקבל את הערך של שורה ספציפית מתוך השורות המקופלות אלא ערך של פונקציה שמסכמת את כל הפריטים ביחד. לדוגמה סך כולל על ידי פונקציית SUM או ממוצע על ידי פונקציית AVG. או הכי גדול או הכי קטן על ידי פונקציות MIN ו-MAX.

HAVING - שוב סינון

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

SELECT - בחירת או יצירת עמודות לטבלה הסופית

סוף סוף קיבלנו את הדאטה הגולמית שאותה אנחנו רוצים להציג, אחרי כל המניפלציות. בשלב זה אתה מגדירים את העמודות שאנחנו רוצים להציג מתוך הדאטה.
אפשר לבחור עמודות קיימות, או אפשר לייצר עמודות חדשות.
לדוגמה: SELECT "SomeColumn", "AnotherColumn", "SomeColumn" * 50 / 2 - בחרנו שתי עמודות קיימות, ויצרנו עמודה שלישית שמחושבת מתוך העמודה הראשונה.

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

DISTINCT - מחיקת כפילויות

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

UNION - חיבור לתשובות של שאילתה אחרת

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

ORDER BY - מיון

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

LIMIT ו-OFFSET

אחרי שיש לנו את כל הדאטה ממויין אפשר להגביל את התשובה לחלק קטן מכלל השורות על ידי LIMIT ו-OFFSET.

קרדיטים:

• https://jvns.ca/blog/2019/10/03/sql-queries-don-t-start-with-select/
• https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-true-order-of-sql-operations/

מקוה שלמדתם משהו.