------------------------------ -- 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