פקודת WHERE מורכבת בשפת ה SQL מאפשרת לנו לסנן שורות באמצעות תנאים מתקדמים.
במדריך SQL זה נלמד את צורות הסינון המורכבות ונראה כיצד ניתן לתת מענה להשוואות מול מספר רב של ערכים וסינונים מורכבים אשר תנאי השוואה פשוטים לא יכלו לבצע.
בחלק זה אנו נכיר את האופרטורים: IN, BETWEEN, LIKE, AND, OR. (מומלץ לקרוא את הפוסט הקודם – תנאי השוואה פשוטים לפני קריאת פוסט זה)
אופרטור IN
מאפשר לנו לבצע השוואת עמודה מול מספר ערכים בו זמנית.
WHERE שם_עמודה IN (ערך,ערך,ערך …)
השוואה מול מספרים
העובדים אשר מספר מחלקתם שווה ל 50, 80 או 90 :
WHERE department_id IN (50, 80, 90)
השוואה מול מחרוזות
העובדים אשר שם משפחתם הוא לוי, כהן או קדם :
WHERE last_name IN (‘Levi’, ‘Cohen’, ‘Kedem’)
v ערכי המחרוזות חייבים להיות בתוך גרשיים ('מחרוזת').
v מבחינת גודל האותיות (LEVI מול Levi) חייבת להיות תאימות מלאה בין הערך בתוך העמודה לבין הערך שאותו כתבנו בתוך ה IN.
השוואה מול תאריכים
העובדים אשר תאריך העסקתם הוא 01.01.1990 או 07.09.1982 :
WHERE hire_date IN (’07-SEP-1982’, ’01-JAN-1990’)
! חובה לכתוב את הערך התאריכי בתוך גרשיים ('ערך תאריכי') ובפורמט התקין.
אופרטור NOT
v אופרטור NOT מאפשר לנו להחזיר את התוצאות ההפוכות מאלו שאופרטור IN החזיר.
v את אופרטור NOT נכתוב לפני IN.
העובדים אשר מספר מחלקתם שווה ל- 50, 80 או 90 :
WHERE department_id IN (50, 80 ,90)
העובדים אשר מספר מחלקתם לא שווה ל- 50, 80 או 90 :
WHERE department_id NOT IN (50, 80 ,90)
! חייבת להיות תאימות בין סוג העמודה לסוג הערכים בתוך הסוגריים.
אופרטור BETWEEN
מאפשר לנו לבדוק טווח ערכים עבור עמודה מסויימת.
WHERE שם_עמודה BETWEEN ערך_תחתון AND ערך_עליון
בדיקת טווח מול מספרים
העובדים אשר שכרם בטווח בין 5000 ל- 8000 :
WHERE salary BETWEEN 5000 AND 8000
בדיקת טווח מול מחרוזות
העובדים אשר שמם הפרטי בטווח האותיות בין A ל- G (כמו ספר טלפונים) :
WHERE first_name BETWEEN ‘A’ AND ‘G’
v ערכי המחרוזות חייבים להיות בתוך גרשיים ('מחרוזת').
v מבחינת גודל האותיות (LEVI מול Levi) חייבת להיות תאימות מלאה בין הערך בתוך העמודה לבין הערך שאותו כתבנו בתוך ה- BETWEEN. למשל, אם כתבנו את טווח האותיות A–G וישנו עובד ששמו הפרטי הוא avi, אותו עובד לא יוצג.
בדיקת טווח מול תאריכים
העובדים אשר תאריך העסקתם נע בין 01.01.1990 ל- 01.01.2000 :
WHERE hire_date BETWEEN ‘01.01.1990’ AND ‘01.01.2000’
חובה לכתוב את הערך התאריכי בתוך גרשיים ('ערך תאריכי') ובפורמט התקין.
! באופרטור BETWEEN הערך הנמוך תמיד יופיע לפני הערך הגבוה.
! אופרטור BETWEEN כולל ערכים קיצוניים (Inclusive). לדוגמא, כאשר נרצה להציג את העובדים אשר שכרם נע בין 5000 ל- 9000 אנו נקבל את כל אותם עובדים כולל אלו ששכרם שווה ל- 5000 ול- 9000.
אופרטור NOT
v מאפשר לנו להחזיר את התוצאות ההפוכות מאלו שאופרטור BETWEEN החזיר.
v את אופרטור NOT נכתוב לפני BETWEEN.
העובדים אשר שכרם בטווח בין 5000 ל- 8000 :
WHERE salary BETWEEN 5000 AND 8000
העובדים אשר שכרם לא בטווח בין 5000 ל- 8000 :
WHERE salary NOT BETWEEN 5000 AND 8000
אופרטור LIKE
מאפשר לנו לאתר מחרוזות על-סמך תבנית (Pattern) מסויימת.
WHERE שם_עמודה LIKE ‘תבנית_מסויימת’
לעיתים נרצה לחפש אינפורמציה בטבלה שלנו שלא על סמך הערך המדוייק של השדה שבתוך העמודה, אלא לפי תבנית, לדוגמא:
v כל העובדים אשר שמם מתחיל ב- 'M' (עמודת שם פרטי).
v כל הלקוחות אשר הנייד שלהם מתחיל ב- 054 (עמודת מספר סלולרי).
v הרכבים אשר לוחית הזיהוי שלהם מסתיימת ב- 86 (עמודת מספר לוחית זיהוי).
v הודעות הפורום בהן מופיעה המילה 'למכור' (עמודת הודעה).
מבנה התבנית
תבנית LIKE יכולה להיות מורכבת מהסימנים הבאים :
v % – מסמל כל מספר של תווים (0 עד אין סוף).
v _ – מסמל תו בודד.
הדוגמאות הבאות ימחישו את סימני התבנית :
WHERE last_name LIKE ‘%a’
‘%a’ – כל השמות אשר מסתיימים באות 'a'. התבנית למעשה אומרת, לא משנה כמה אותיות תהיינה בתחילת המילה (כל מספר של תווים), אך התבנית חייבת להסתיים באות a.
ערכים לדוגמא : ‘Akiva’, ‘Shapira’, ‘Bouskila’.
! באופן תיאורטי גם שם משפחה שערכו ‘a’ היה נכלל פה משום ש- % מסמל כל מספר של תווים (גם 0).
WHERE last_name LIKE ‘A%’
‘A%’ – כל השמות אשר מתחילים באות 'a'. התבנית למעשה אומרת, המילה חייבת להתחיל ב- ‘a’ לאחר מכן לא משנה מספר האותיות (כל מספר של תווים).
ערכים לדוגמא : ‘Avraham‘ , ‘Akiva’ , ‘Avidor’.
! לשים לב, כי בשל הרגישות לגודל האות שם משפחה כגון ‘avidor’ לא יתקבל בחיפוש (החיפוש שלנו מוגדר על 'A' גדולה) .
! באופן תיאורטי גם שם משפחה שערכו ‘A’ היה נכלל פה משום ש- % מסמל כל מספר של תווים (גם 0).
WHERE last_name LIKE ‘%a%’
‘%a%’ – כל השמות אשר מכילים את האות 'a'. התבנית למעשה אומרת, המילה יכולה להתחיל בכל מספר של תווים, לאחר מכן צריך להופיע התו 'a' ולאחר מכן שוב יכולים להופיע כל מספר של תווים.
ערכים לדוגמא : ‘Avraham‘ , ‘Strauss’ , ‘Gilboa’ , ‘Harel’.
! לשים לב כי בשל הרגישות לגודל האות שם משפחה כגון ‘StrAuss’ לא יתקבל בחיפוש (החיפוש שלנו מוגדר על 'a' קטנה).
! באופן תיאורטי גם שם משפחה שערכו ‘a’ היה נכלל פה משום ש- % מסמל כל מספר של תווים (גם 0).
WHERE last_name LIKE ‘_a%’
‘_a%’ – כל השמות אשר האות ‘a’ היא האות השניה בהם. התבנית למעשה אומרת, המילה יכולה להתחיל בתו בודד כלשהו ( _ מסמל תו בודד) לאחר מכן תופיע האות ‘a’ ולאחר מכן כל מספר של תווים.
ערכים לדוגמא : ‘Harel’ , ‘Tamari’ , ‘Dagan’.
אופרטור NOT
v מחזיר את התוצאות ההפוכות ממה שאופרטור LIKE החזיר.
v נכתב לפני LIKE.
מחזיר את כל אלו שיש בשמם את האות ‘a’ :
WHERE last_name LIKE ‘%a%’
מחזיר את כל אלו שאין בשמם את האות ‘a’ :
WHERE last_name NOT LIKE ‘%a%’
IS NULL / IS NOT NULL
ערך NULL מציין שדה אשר חסר בו ערך. הערך NULL לא שווה לאפס (0) וגם לא שווה
לרווח (‘ ’) יש לראותו כחלל ריק. משום שהערך NULL לא שווה לאף ערך, לא ניתן לבצע עליו השוואות בעזרת האופרטורים '=' או '<>'.
הדוגמאות הבאות לא תקינות.
העובדים אשר אינם מקבלים עמלה :
WHERE commission_pct = NULL (לא תקין)
העובדים אשר מקבלים עמלה :
WHERE last_name &lt;&gt; NULL (לא תקין)
כדי להתמודד עם השוואה מול ערכי NULL אנו צריכים להשתמש באופרטורים:
v IS NULL – המקבילה לפעולה NULL =
v IS NOT NULL – המקבילה לפעולה NULL <>
העובדים אשר אינם מקבלים עמלה :
WHERE commission_pct IS NULL
העובדים אשר מקבלים עמלה :
WHERE last_name IS NOT NULL
האופרטורים AND ו- OR
מטרת אופרטורים אלו היא לאפשר לנו לבצע סינון על סמך מספר רב של תנאים, לדוגמא :
שם העובד |
שכר |
מספר מחלקה |
חיה |
10000 |
90 |
דוד |
9000 |
80 |
יוסי |
8000 |
70 |
אופיר |
7000 |
70 |
עמית |
5000 |
70 |
רם |
4000 |
40 |
v AND מציין כי כל התנאים חייבים להתקיים.
העובדים אשר שכרם גבוה מ- 6000 וגם מספר מחלקתם 70.
העובדים אשר עונים על שני תנאים אלו הם: אופיר ויוסי (2 עובדים סה"כ).
v OR מציין כי לפחות אחד מהתנאים חייב להתקיים.
העובדים אשר שכרם גבוה מ- 6000 או מספר מחלקתם 70 :
o אצל רם – אף אחד מהתנאים לא מתקיים.
o אצל עמית מספר המחלקה הוא 70 – תנאי אחד מתקיים.
o אצל יוסי ואופיר שכרם גבוה מ- 6000 ומספר מחלקתם 70 – שני התנאים מתקיימים.
o אצל חיה ודוד השכר גבוה מ- 6000 – תנאי אחד מתקיים.
o 5 עובדים עונים לפחות על תנאי אחד.
! משום שאופרטור OR מתקיים גם כאשר רק חלק מהתנאים מתקיימים, אופרטור OR מחזיר יותר ערכים.
העובדים אשר שכרם גבוה מ- 6000 וגם מספר מחלקתם 70 :
WHERE salary &gt; 6000 AND Department_id = 70
העובדים אשר שכרם גבוה מ- 6000 או מספר מחלקתם 70 :
WHERE salary &gt; 6000 OR Department_id = 70
! משפט WHERE מופיע פעם אחת בלבד, לאחר פקודת OR או AND אנו נכתוב את התנאי הנוסף ללא ציון נוסף של פקודת WHERE.
סדר קדימויות בין תנאים
פקודת AND קודמת לפקודת OR (כשם שפעולת הכפל קודמת לפעולת החיבור )
WHERE Job_Title = ‘DBA’ OR Job_Title = ‘Manager’ AND Salary &gt; 8000
במשפט WHERE זה בחרנו להציג את אלו אשר :
(תנאי ראשון) התפקיד שלהם הוא DBA.
או
(תנאי שני) התפקיד שלהם הוא Manager וגם הם מרוויחים מעל 8000.
שינוי סדר הקדימויות בין תנאים
כאשר נרצה לשנות את סדר הקדימויות בין OR ל- AND נשתמש בסוגריים (כשם שסוגריים משנים את סדר הקדימויות בין חיבור לכפל).
WHERE (Job_Title = ‘DBA’ ORSalary &gt; 8000) AND Last_name LIKE ‘%a%’
במשפט WHERE זה בחרנו להציג את אלו אשר :
(תנאי ראשון) התפקיד שלהם הוא DBA או השכר שלהם גבוה מ 8000.
וגם
(תנאי שני) שם המשפחה שלהם מכיל את האות a.