דוגמאות קוד – SQL Server / Join Methods

------------------------------
-- JOIN (INNER) - EQUI
------------------------------

SELECT productName , unitPrice , categoryName
FROM products JOIN categories
ON   categoryID = categoryID 

SELECT products.productName , products.unitPrice , categories.categoryID , categories.categoryName
FROM products JOIN categories
ON   products.categoryID = categories.categoryID 

SELECT products.productName , products.unitPrice , products.UnitsInStock , categories.categoryName
FROM products JOIN categories
ON   products.categoryID = categories.categoryID 

SELECT pro.productName , pro.unitPrice , pro.UnitsInStock , cat.categoryName
FROM products pro JOIN categories cat
ON   pro.categoryID = cat.categoryID
WHERE pro.unitPrice > 50
ORDER BY pro.productName DESC 

SELECT pro.productName , sup.CompanyName , cat.CategoryName
FROM products pro JOIN suppliers sup
ON   pro.supplierID = sup.supplierID
				  JOIN categories cat
ON   pro.categoryID = cat.CategoryID
WHERE pro.productName LIKE '%a%' 

--------------------------------
-- JOIN (INNER) - NON EQUI
--------------------------------

CREATE TABLE price_range
(min_price int,
 max_price int,
 price_range varchar(25))

 INSERT INTO price_range VALUES
 (0,20,'A') , (21,40,'B') , (41,60,'C') , (61,80,'D'),
 (81,100,'E')

 SELECT *
 FROM price_range 

 SELECT pro.productName , pro.UnitPrice , rang.min_price , rang.max_price , rang.price_range
 FROM products pro JOIN  price_range rang
 ON pro.UnitPrice BETWEEN rang.min_price AND rang.max_price

 -------------------------------
 -- OUTER JOIN
 -------------------------------

 USE ACDB 

 SELECT cust.customer_id , cust.first_name , cust.last_name , cust.pack_id ,  pack.speed , pack.monthly_payment
 FROM customers cust JOIN packages pack
 ON cust.pack_id = pack.pack_id

 SELECT cust.customer_id , cust.first_name , cust.last_name , cust.pack_id ,  pack.speed , pack.monthly_payment
 FROM customers cust LEFT OUTER JOIN packages pack
 ON cust.pack_id = pack.pack_id

 SELECT cust.customer_id , cust.first_name , cust.last_name , cust.pack_id ,  pack.speed , pack.monthly_payment
 FROM customers cust RIGHT OUTER JOIN packages pack
 ON cust.pack_id = pack.pack_id

 SELECT cust.customer_id , cust.first_name , cust.last_name , cust.pack_id ,  pack.speed , pack.monthly_payment
 FROM customers cust FULL OUTER JOIN packages pack
 ON cust.pack_id = pack.pack_id

-----------------------------------------
-- SELF JOIN
-----------------------------------------

SELECT * FROM employees 

SELECT employeeID , lastName , reportsTO
FROM employees 

SELECT emp.lastName AS 'EmployeeName' , mng.lastName AS 'ManagerName'
FROM employees emp JOIN employees mng
ON   emp.reportsTo = mng.employeeID 

SELECT emp.lastName AS 'EmployeeName' , mng.lastName AS 'ManagerName'
FROM employees emp LEFT OUTER JOIN employees mng
ON   emp.reportsTo = mng.employeeID