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

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

 

תחביר EXISTS:

(שאילתא)EXISTS.

ביטוי זה יחזיר true כאשר השאילתא תחזיר ערך ויחזיר false כאשר השאילתא לא תחזיר ערך. כמובן, הערך המוחזר יתהפך, כאשר נשים NOT לפני ה EXISTS.

לטכניקה זאת יש מספר יתרונות בולטים:

1.שאילתות עם exists הם ברוב המקרים הרבה יותר מהירות. הסיבה לכך היא שכפי שתראו בדוגמאות הבאות, שיטת הפעולה מבחינת בסיס הנתונים היא הרבה יותר יעילה מאשר שימוש בטכניקות מקבילות. במקום ליצור cursors ענקיים ולהשוות בינהם כמו לדוגמא בintersect, בסיס הנתונים עובד רשומה רשומה ורק עליה מקבל החלטה האם להציג אותה או לאו.

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

3.שאילתות SQL שבהם משתמשים בexists הרבה יותר ברורות ומובנות משאר שאילתות שנכתבו עם טכניקות אחרות.

4. exists הוא פטנט שימושי מאד למגוון של מצבים שקשה או אף בלתי אפשרי להתמודד באמצעים אחרים.

כדי להדגים שאילתות עם EXISTS, נשתמש במסד הנתונים NORTHWIND של SQL SERVER, אך רעיונות אלו יעבדו באותה מידה גם בORACLE ובבסיסי נתונים אחרים.

בDB זה, טבלת ORDERS מתארת את הכותרת (HEADER) של ההזמנה הכוללת את קוד הלקוח ותאריך ההזמנה, וטבלת ORDER DETAIL מתארת את פרטי ההזמנה, כגון קוד מוצר וכמות. כדי לשלוף למשל, את כל ההזמנות, תאריך ההזמנה שלהם, הלקוח שלהם והמוצרים בהזמנה, יש להריץ שאילתא לדוגמא:
(הערה: כל הדוגמאות המופיעות במאמר זה נכתבו ב SQL תקני )

SELECT o.CustomerID,o.OrderDate,od.OrderID,  od.ProductID, od.quantity
 FROM Orders o ,  [Order Details] od 
 WHERE  o.OrderID = od.OrderID 

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

 


דוגמא א:

מהם מספרי ההזמנה אשר מכילים את מוצר 28 וגם את מוצר 39.

 SELECT distinct orderid 
      FROM   Orders o 
      WHERE exists (select 1 from [order details] od 
                    where o.orderid=od.orderid and od.productid=28) 
            and exists (select 1 from [order details] od 
                where o. orderid=od.orderid and od.productid=39)

הסבר:

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

משפט הexists הראשון אומר, לכל מספר הזמנה (orderid), לך לטבלת פרטי הזמנה (order detail) ובדוק האם יש בהזמנה זאת מוצר מספר 28. ברגע שישנה הזמנה כזאת השאילתא תחזיר ערך. במקרה זאת קבענו את ערך הקבוע 1 שיוחזר , אך ערך זה שרירותי ואין לא חשיבות. הדבר החשוב מבחינתנו שברגע שיוחזר ערך משפט הexists יקבל true.

במשפט הexists השני עשינו את אותו הדבר בדיוק, רק עם מספר הזמנה 39.


דוגמא ב:

מי הם הלקוחות שבאף הזמנה שלהם לא היה מוצר 11.

 
SELECT distinct o.CustomerID 
FROM   Orders o  
WHERE  not exists (select 1 from [order details] od 
                   where o.orderid=od.orderid and od.productid= 11 
                   ) 

הסבר: שוב, אנחנו רואים כאן שאילתא המבוסס על טבלת Orders בלבד. לכל שורה בטבלת ה Orders, השאילתא בודקת האם יש ללקוח בשורה (שדה customerid) הזמנה עם מוצר 11. מספיק שישנה הזמנה כזאת, הexists מחזיר true, לכן ה not exists יחזיר false והשורה של הלקוח הזה תושמט.


דוגמא ג:

מצא את כל הלקוחות מטבלת orders אשר אין להם אפילו הזמנה אחת מטבלת  order_details.

SELECT distinct o.CustomerID 
FROM   Orders o 
WHERE  NOT EXISTS (SELECT 1 FROM [ORDER DETAILS] od 
                   where o.orderid=od.orderid) 

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


דוגמא ד:

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

select distinct customerid  from orders o 
where exists 
     (select 1  
      from orders o_alias  
      where  o.customerid=o_alias.customerid                                   
      and datediff(day,o.orderdate,o_alias.orderdate)>500)
     ) 

הסבר:

במקרה זה אנו צריכים להשתמש בalias של טבלת orders. הפעולה המתבצעת היא לכל שורה בorders, בדוק האם ללקוח שמופיע בשורה ישנה הזמנה נוספת שיש פעם בינה לבין ההזמנה פער של לפחות 500 יום. זאת הסיבה שהקישור בין הטבלה לבין הalias שלה מתבצע באמצעות שדה customerid.

 


דוגמא ה:

מצא את כל הלקוחות אשר לאחר שקנו את מוצר 28 קנו את מוצר 59.

SELECT distinct o. CustomerID FROM Orders o 
WHERE    exists (select 1 
                 from [Order Details ]od, [Order Details]od_alias, orders o_alias  
                 where  o.OrderID = od.OrderID   
                 and o.customerid=o_alias. customerid       
                 and o_alias.orderid=od_alias. orderid  
                 and (od_alias.productid=59 and od. productid=28 )  
                 and o_alias.orderdate>o. orderdate ) 

הסבר:

דוגמא זאת דומה  לדוגמא ד, אלא שמעט יותר מורכבת. מכיוון שאנו שואלים על המוצרים אנחנו צריכים קישור לטבלת order details ובדומה לדוגמא הקודמת יש לנו התייחסות ל2 הזמנות, לכן אנחנו צריכים גם alias לטבלת orders וגם לטבלת order details.

3 השורות הראשונות של משפט הwhere הפנימי, מחברות בין טבלאות אלו. השורה הרביעית, מגדירה את המוצר הראשון (28) וכן את המוצר השני (59). השורה החמישית והאחרונה,  "דואגת" שהמוצר השני אכן הוזמן אחרי הראשון.

 


 

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

 

 

חדשות dwh.co.il

BI&BigData

07 אפריל 2020

bi\dwh in israel

רשימת קורסים

קורס ה Bo Designer Master הוא קורס ייחודי שפותח בא ...
קורס ה SQL Wiz הוא קורס מיוחד המלמד חסר נסיון בSQL ...
בקורס זה אנו נלמד כיצד לפתח דוחות באמצעות הWebinte ...
בקורס זה אנו נלמד כיצד לפתח דוחות באמצעות הWebinte ...
הSDK של הקליינט של הBusiness Objects מרחיב את האפש ...
בסדנא זאת, אנו נלמד על כלים מיוחדים עבור הDesigner ...
קורס ה Deski\Client-Business Objects, נועד לאפשר פ ...
תנו לנו לייק לקבלת עדכונים שוטפים