מבנה שאילתת Join:
SELECT Column list
FROM tbl1 [alias] [join type] JOIN tbl2 [alias]
ON Join Criteria
[Join Type] – יש שני סוגים: INNER JOIN ו- OUTER JOIN .
[alias] – לטבלה עם שם ארוך אפשר לקבוע שם חלופי לטבלה.
אם פונים לשדה עם שם זהה בשתי טבלאות שונות, חייב לציין לפני הפנייה את שם הטבלה. אפשר לעשות גם על ידי ה-alias שלה. בכל מקרה רצוי לציין את שם הטבלה לפני שם העמודה ליעילות.
On – התנאי לשילוב בין העמודות.
ברוב המקרים הקריטריון לחיבור יהיה העמודה המשותפת בין הטבלאות בקשר של PK ו FK.
INNER Join
ה-JOIN הבסיסי לשילוב מידע בין שתי טבלאות. דוגמא, שליפה של שמות המוצרים והתאמה של שם הקטגוריה המתאימה.
SELECT p.ProductName, c.CategoryName
FROM Products p INNER JOIN Categories c
On p.CategoryID = c.CategoryID
אפשר לסנן את החיבור עם פעולת where.
SELECT P.ProductName, O.UnitPrice, O.Quantity
FROM Products P JOIN [Order Details] O
ON O.ProductID = P.ProductID
where Quantity = 9
ORDER BY O.UnitPrice
שילוב יותר מ – 2 טבלאות
SELECT Column list
FROM tbl1 [alias] [join type] JOIN tbl2 [alias]
ON Join Criteria
JOIN tbl3 [alias]
ON Join Criteria
נשרשר את פקודת ה-join. למשל:
Select c.CategoryName , p.ProductName , s. CompanyName
From Categories c Join Products p
On c .CategoryId = p. CategoryId
Join Suppliers s
On s.SupplierID = p.SupplierID
Outer join
שאילתה המחזירה את כל הנתונים מטבלה אחת ואת המשלימים לה מהטבלה האחרת.
בתוצאה של שאילתת join רגילה מופיעות אך ורק רשימות המקיימות את התנאי. לדוגמה, אם הגדרנו שאילתת join בין מחלקות לעובדים ויש מחלקה שאין לה עובדים היא לא תופיע בתוצאה.
בכדי להציג את כל הרשומות, כולל אלו שהוחסרו ב- join הרגיל, נשתמש ב- outer join.
כיוון התנאי ב- outer join קובע את הטבלה ממנה ילקחו כל הרשומות כולל אלו שאינן עונות על תנאי הקישור בין הטבלאות. רשומות להן אין רשומה מתאימה בטבלה השניה יופיעו בתוצאה עם ערכי Null תחת העמודות השייכות לטבלה השניה.
יש שלושה תתי סוגים של Outer Join : Left , Right ו Full
Left Outer Join
ב – Left Outer Join השרת יחזיר את כל הרשומות מהטבלה השמאלית בסעיף ה – From ורק את הרשומות המתאימות להן מהטבלה הימנית בסעיף ה From .
בדוגמא נקבל את כל רשימת הלקוחות, גם אלה שאין להם הזמנה. אם היינו עושים inner join היינו מקבלים את הלקוחות שיש להם הזמנה בלבד.
select c.customerid, o.orderid
from customers c left join orders o
on c.customerid = o.customerid
Right Outer Join
ב- Right Outer Join העקרון דומה אלא שהפעם השרת יחזיר את כל הרשומות מהטבלה שמופיעה בצד ימין של סעיף ה From
בדוגמא הזאת נקבל את רשימת כל ההזמנות, כי אין הזמנה שלא משוייכת ללקוח.
select c.customerid,o.orderid
from customers c right join orders o
on c.customerid=o.customerid
Full Outer Join
ב Full Outer Join – השרת יחזיר את כל הרשומות משתי הטבלאות. ישדך רשומות להן יש רשומה מתאימה בטבלה השניה. רשומות להן אין רשומה מתאימה בטבלה השניה יופיעו בתוצאה עם ערכי Null תחת העמודות השייכות לטבלה השניה.
Cross join
זהו Join אשר מציג תוצאות של כל הצירופים האפשריים בין השורות של שתי הטבלאות.כלומר כל רשומה מהטבלה הראשונה תהיה משולבת עם כל רשומה מהטבלה השנייה.
למשל אם יש לנו מוצר עם צבעים ואנחנו רוצים לייצר רשימה של כל המוצרים עם כל הצבעים, נשתמש בפקודה הזאת.
SELECT *
FROM colors
cross join products
Self join
לעיתים קיים צורך לבצע צירוף של רשימות מטבלה לרשימות מאותה טבלה.
לדוגמה: נניח כי היתה לנו טבלת עובדים אשר עבור כל עובד בין שאר הנתונים היה נשמר עבורו שמו ,מספר העובד שלו ומספר העובד של המנהל שלו, אילו היינו רוצים להדפיס את שמות העובדים ושמות מנהליהם היינו צריכים לפנות פעמיים לאותה טבלה פעם אחת עבור לקיחת שמות העובדים ופעם שניה עבור לקיחת שמות המנהלים שהם כמובן גם כן עובדים ולכן יופיעו באותה הטבלה – במקרה מסוג זה היינו משתמשים ב- self join .
Select w.LastName + ' ' + w.FirstName as "worker",
r.LastName + ' ' + r.FirstName as "Reports To"
from Employees w join Employees r
On w.ReportsTo = r.EmployeeId
תרגול Join – שאלות
- הצג את שם המוצר מתוך טבלת products ואת שם הקטגוריה שלו מתוך טבלת categories.
- הצג את שם המוצר מתוך טבלת products ואת שם החברה של הספק שלו מתוך טבלת suppliers.
- הצג את מספר ההזמנה מתוך טבלת orders ואת שם החברה של הלקוח מתוך טבלת customers, עבור החברות ששמן מתחיל באות 'a'.
- הצג מתוך טבלת region את תיאור התחום, RegionDescription, ומתוך טבלת Territories את תיאור האיזור, TerritoryDescription.
- הצג את שם המוצר ומחיר המוצר מתוך טבלת products, ואת שם הקטגוריה שלו מתוך טבלת categories, עבור המוצרים שמחירם גבוה מ – 5.
- הצג את מספר המוצר , מחיר המוצר , ואת מספר הספק מתוך טבלת products, ואת שם הקטגוריה שלו מתוך טבלת categories, עבור המוצרים שמספר הספק שלהם הוא 3.
- הצג את מספר המוצר , מחיר המוצר, ומספר הספק מתוך טבלת products ואת שם הקטגוריה שלו מתוך טבלת categories, עבור המוצרים שיש להם בשם הקטגוריה את האות a.
- הצג את שם המוצר מתוך טבלת products, את שם הקטגוריה מתוך טבלת categories, ואת שם החברה של הספק מתוך טבלת suppliers.
- הצג את שם המוצר מתוך טבלת products את תיאור הקטגוריה מתוך טבלת categories, ואת עיר הספק מתוך טבלת suppliers, כאשר שם העיר של הספק היא Tokyo או London.
- הצג את מספר המוצר מתוך טבלת products, את תיאור הקטגוריה מתוך טבלת categories ואת הארץ מתוך טבלת suppliers כאשר האות הראשונה של הארץ מתחילה באות a.
- הצג את שם החברה של הלקוח מתוך טבלת customers ואת מספר ההזמנה מתוך טבלת orders עבור הלקוחות , גם אלו ללא הזמנות.
- הצג מספר הזמנה, OrderID, תאריך הזמנה, OrderDate, וכתובת למשלוח ShipAddress, מטבלת ORDERS. ובנוסף את קוד הלקוח CustomerID, שם הלקוח CompanyName, ומספר הטלפון שלו Phone, מטבלת Customers. יש לכלול בתוצאה רק הזמנות שבוצעו בשנת 1996, ורק לקוחות שקוד הלקוח שלהם, CustomerID, מתחיל באותיות C או A.
- חזור על השאלה הקודמת, הוסף לה גם את עמודות שם פרטי FirstName ושם משפחה LastName מטבלת employees. תן שמות מתאימים לעמודות. סדר את התוצאות עם תאריך ההזמנה בסדר יורד.
- הצג את שם משפחתו של העובד ושם משפחתו של מנהלו, בסס את תשובתך על סמך הקשר הפנימי בתוך טבלת Employees בין מספר המנהל ומספר העובד. הצג גם את העובדים ללא המנהלים.
- הצג מטבלת המוצרים, בעזרת join את מספר המוצר ProductID, שם המוצר ProductName ומחירו UnitPrice עבור מוצרים שעולים יותר ממוצר בשם Alice Mutton.
תרגול Join – תשובות
-- ex1
select p.ProductName, c.CategoryName
from products p inner join Categories c
on p.CategoryID = c.CategoryID
-- ex2
select p.ProductName, s.ContactName
from products p inner join Suppliers s
on p.SupplierID = s.SupplierID
-- ex3
select
ord.OrderID,
cust.CompanyName
from orders ord inner join customers cust
on ord.CustomerID = cust.CustomerID
where cust.CompanyName like 'a%'
-- ex4
select
reg.RegionDescription,
ter.TerritoryDescription
from region reg inner join Territories ter
on reg.RegionID = ter.RegionID
-- ex5
select
p.ProductName,
p.UnitPrice,
c.CategoryName
from products p inner join categories c
on p.CategoryID = c.CategoryID
where p.UnitPrice > 50
-- ex6
select
p.ProductID,
p.UnitPrice,
p.SupplierID,
c.CategoryName
from products p inner join categories c
on p.CategoryID = c.CategoryID
where p.SupplierID = 3
-- ex7
select
p.ProductID,
p.UnitPrice,
p.SupplierID,
c.CategoryName
from products p inner join categories c
on p.CategoryID = c.CategoryID
where c.CategoryName like '%a%'
-- ex8
select
p.ProductName,
c.CategoryName,
s.CompanyName
from products p inner join categories c
on p.CategoryID = c.CategoryID
JOIN Suppliers s
on p.SupplierID = s.SupplierID
-- ex9
select
p.ProductName,
c.[Description],
s.City
from products p inner join categories c
on p.CategoryID = c.CategoryID
JOIN Suppliers s
on p.SupplierID = s.SupplierID
where s.City = 'London' or s.City = 'Tokyo'
-- ex10
select
p.ProductName,
c.[Description],
s.Country
from products p inner join categories c
on p.CategoryID = c.CategoryID
JOIN Suppliers s
on p.SupplierID = s.SupplierID
where s.Country like 'a%'
-- ex11
select
c.CompanyName,
o.OrderID
from customers c left join orders o
on c.CustomerID = o.CustomerID
-- ex12
select
o.OrderID,
o.OrderDate,
o.ShipAddress,
c.CustomerID,
c.CompanyName,
c.Phone
from orders o inner join Customers c
on c.CustomerID = o.CustomerID
where o.OrderDate > '1996-01-01' and o.OrderDate < '1997-01-01'
and (c.CustomerID like 'a%' or c.CustomerID like 'd%')
-- ex13
select
o.OrderID,
o.OrderDate,
o.ShipAddress,
c.CustomerID,
c.CompanyName,
c.Phone,
e.FirstName,
e.LastName
from orders o inner join Customers c
on c.CustomerID = o.CustomerID
join Employees e
on e.EmployeeID = o.EmployeeID
where o.OrderDate > '1996-01-01' and o.OrderDate < '1997-01-01'
and (c.CustomerID like 'a%' or c.CustomerID like 'd%')
order by o.OrderDate desc
-- ex14
select
emp.LastName,
manager.LastName
from Employees emp left join Employees manager
On emp.ReportsTo = manager.EmployeeId