תתי שאילתות – Subqueries

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

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

  • השאילתה הראשית תקרא Main Query תתי השאילתות יקראו Sub – Query.
  • ניתן לכתוב מספר תתי שאילתות עבור שאילתה ראשית אחת.
  • ניתן לשלב תת שאילתה אחרי: Where , Having ו- From.
  • התת שאילתה תמיד מורצת לפני השאילתה הראשית.
  • תת שאילתה תיכתב בסוגריים.
  • תת שאילתה תופיע בצד הימני של אופרטור ההשוואה.
  • אין להשתמש במשפט order by בתת שאילתה.
  • תת השאילתה תחזיר עמודה אחת אליה תבוצע ההשוואה.
  • תת השאילתא תחזיר ערך מאותו ה DATA TYPE של העמודה אליה היא מושוות בשאילתא החיצונית.
  • תת השאילתה חייבת להחזיר מספר ערכים בהתאם לאופרטור אליו היא מושוות בשאילתה החיצונית.

Single row subquery

תת שאילתה המחזירה ערך בודד. האופרטורים לשאילתא מסוג זה הינם: =, ><, >, =>, <, =<.

דוגמא: הצגת המחירים ושמות המוצר בעלי עלות גבוהה מזו של מוצר מספר 2 מטבלת products.

על מנת להציג את המוצרים שעלותם גבוהה מזו של מוצר 2, צריך למצוא את העלות של מוצר מספר 2. את זה מבצעים בתת השאילתא.

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

SELECT 
    ProductName, 
    UnitPrice 
FROM Products 
WHERE UnitPrice > (SELECT UnitPric FROM Products WHERE productid = 2)

Multiple row subquery

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

האופרטורים לשאילתא מסוג זה הינם: NOT, IN, ANY, ALL.

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

NOT IN ההפך מ IN. שונה מכל אחד מהערכים שמחזירה תת השאילתא.

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

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

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

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

דוגמא ל-IN

דוגמה זו מחזירה את ה-id ושמות המוצרים אשר הוזמנו מהם יותר מ- 1200 פריטים.

SELECT 
    ProductID,
    ProductName
FROM products 
WHERE ProductID IN(SELECT ProductID FROM [order details]
GROUP BY ProductID 
HAVING sum(Quantity)>1200)

דוגמא ל-ALL​

דוגמה זו מחזירה את פרטי המוצרים אשר מחירם מעל הערך המקסימלי שהחזירה תת השאילתא.

תת השאילתא מחזירה את מחירי המוצרים בקטגוריה מספר 7, והשאילתא הראשית תחזיר את המוצרים היקרים ממחירי המוצרים בקטגוריה הזאת.

SELECT 
    productid, 
    productname, 
    unitprice 
FROM products 
WHERE unitprice > ALL(SELECT unitprice FROM products WHERE categoryid = 7)

הרחבות לנושא תת השאילתא

שימוש בשאילתא במשפט From.

דוגמא:

ציון מספר הפריטים בהזמנה הגדולה ביותר.

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

בטבלת order details יש כל פריט שמשוייך לכל הזמנה. נקבץ את כל הפריטים תחת אותו מספר הזמנה, נבדוק מה הכמות שהוזמנה מהפריט ונסכום את כל כמות הפריטים שבהזמנה הזאת.

קבלנו שורות עם כמויות.

SELECT 
    sum(quantity) as s_quantity 
FROM [order details] 
GROUP BY orderId

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

SELECT 
    max(s.s_quantity) 
FROM (SELECT sum(quantity) as s_quantity FROM [order details] 
    GROUP BY orderId) s

שימוש באופרטור EXISTS

אופרטור EXISTS בודק האם ערך מופיע ברשימת הערכים החוזרת מתת-שאילתה או לא. מחזיר ערך בוליאני.

דוגמא:

הדוגמה מחזירה את ה-id של כל הלקוחות הקיימים במאגר הלקוחות אך לא קיימת להם כרגע הזמנה במאגר ההזמנות.

SELECT 
    customerid 
FROM customers 
WHERE not exists(SELECT customerid FROM orders 
    WHERE customerid=customers.customerid)

תרגול Subqueries – שאלות

  1. הצג מטבלת Products את שמות המוצרים אשר מחירם נמוך מהמחיר של מוצר מספר 8.
  2. הצג מטבלת Products את שמות המוצרים ומחירם, עבור המוצרים אשר מחירם גבוה יותר ממוצר ששמו Tofu.
  3. הצג מטבלת employees את שמות העובדים ותאריך גיוסם עבור העובדים שגויסו לאחר עובד שמספרו 6.
  4. הצג מטבלת Products את מספר המוצר, שם המוצר ומחיר יחידה עבור המוצרים אשר מחירם גבוה מהמחיר הממוצע ליחידה.
  5. הצג מטבלת Products את שמות המוצרים וכמותם במלאי, עבור המוצרים אשר כמותם במלאי נמוכה יותר מהכמות המינימלית בקטגוריה מספר 5.
  6. הצג מטבלת Products את כל פרטי המוצרים שנמצאים באותה קטגוריה כמו מוצר ששמו Chai. אין להציג בתוצאה הסופית את המוצר chai עצמו.
  7. הצג מטבלת Products את שם המוצר, מחירו ומספר הקטגוריה שלו, עבור המוצרים שמחירם שווה לאלו של קטגוריה מס' 5.
  8. הצג מטבלת Products את שם המוצר ומחירו, עבור המוצרים אשר מחירם גבוה מלפחות אחד המוצרים בקטגוריה מספר 5.
  9. הצג מטבלת Products את שם המוצר ומחירו , עבור המוצרים אשר מחירם גבוה מכל המוצרים של קטגוריה מספר 5.
  10. הצג מטבלת Orders את מספרי ההזמנות ותאריך ביצוע ההזמנות עבור כל ההזמנות שהלקוחות שלהן מ-Germany ,France או Sweden וגם תאריך ההזמנה היה בשנת 1997, יש לשים לב לכמה שורות מחזירה התת – שאילתא.
  11. הצג מטבלת products את שמות המוצרים ואת הקוד שלהם, productID. יש לכלול בתוצאה רק המוצרים שמחירם גדול מהמחיר הממוצע של המוצרים אשר מספר היחידות במלאי, UnitsInStock, גדול מ- 50.
  12. הצג מטבלת products את שמות כל המוצרים אשר שם הקטגוריה שלהם הוא Beverages או Condiments וגם אזור, region, הספק אינו ידוע.
  13. הצג את שמות החברות, CompanyName מטבלת Suppliers, המספקות מוצרים מקטגוריית Beverages, עמודת CategoryName בטבלת Categories.

תרגול Subqueries – תשובות

בתשובות יש קודם את השאילתא הראשונית, תת השאילתא, ואחר כך את השאילתא העיקרית.

-- ex1
select
    UnitPrice
from Products
where ProductID = 8

select
    ProductName 
from Products
where UnitPrice < ( select
                        UnitPrice
                    from Products
                    where ProductID = 8 )

-- ex2
select
    UnitPrice
from Products
where ProductName = 'Tofu'

select
    ProductName,
    UnitPrice
from Products
where UnitPrice > ( select
                        UnitPrice
                    from Products
                    where ProductName = 'Tofu' )

-- ex3
select
    HireDate
from employees
where EmployeeID = 6

select
    FirstName + ' ' + LastName as [Name],
    HireDate
from employees
where HireDate > ( select
                        HireDate
                    from employees
                    where EmployeeID = 6 )

-- ex4
select
    avg(UnitPrice)
from Products

select
    ProductID,
    ProductName,
    UnitPrice
from Products
where UnitPrice > ( select
                        avg(UnitPrice)
                    from Products )

-- ex5                  
select
    min(UnitsInStock)
from Products
where CategoryID = 5

select
    ProductName,
    UnitsInStock
from Products
where UnitsInStock < ( select
                            min(UnitsInStock)
                        from Products
                        where CategoryID = 5 )

-- ex6
select
    CategoryID
from Products
where ProductName = 'Chai'

select *
from Products
where ProductName <> 'Chai' and 
    CategoryID = ( select
                     CategoryID
                     from Products
                     where ProductName = 'Chai' )

-- ex7
select
    UnitPrice
from Products
where CategoryID = 5

select 
    ProductName,
    UnitPrice,
    CategoryID
from Products
where UnitPrice IN (select
                        UnitPrice
                    from Products
                    where CategoryID = 5 )

-- ex8
select
    UnitPrice
from Products
where CategoryID = 5

select 
    ProductName,
    UnitPrice
from Products
where UnitPrice > ANY (select
                        UnitPrice
                    from Products
                    where CategoryID = 5 )

-- ex9
select
    UnitPrice
from Products
where CategoryID = 5

select 
    ProductName,
    UnitPrice
from Products
where UnitPrice > ALL (select
                        UnitPrice
                    from Products
                    where CategoryID = 5 )

-- ex10
select
    CustomerID
from Customers
where Country = 'France' or Country = 'Germany' or Country = 'Sweden'

select
    OrderID,
    OrderDate
from Orders
where Year(OrderDate) = 1997 and CustomerID in 
    (select
        CustomerID
        from Customers
        where Country = 'France' or Country = 
            'Germany' or Country = 'Sweden')

-- ex11
select
    avg(UnitPrice)
from products
where UnitsInStock > 50

select
    ProductName,
    ProductID
from products
where UnitPrice > ( select
                        avg(UnitPrice)
                    from products
                    where UnitsInStock > 50 )

-- ex12
select
    SupplierID
from Suppliers
where Region is null

select
    CategoryID
from Categories
where CategoryName = 'Beverages' or CategoryName = 'Condiments'

select
    ProductName
from products
where 
CategoryID in ( select
                    CategoryID
                from Categories
                where CategoryName = 'Beverages' or 
                      CategoryName = 'Condiments')
and
SupplierID in ( select
                    SupplierID
                from Suppliers
                where Region is null)

-- ex13
select
    CategoryID
from Categories
where CategoryName = 'Beverages'

select
    s.CompanyName
from products p inner join Suppliers s
on p.SupplierID = s.SupplierID
where p.CategoryID  = ( select
                            CategoryID
                        from Categories
                        where CategoryName = 'Beverages' )

ניווט במאמר

מאמרים אחרונים

Weekly Tutorial