דוגמאות קוד – 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

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…