Inner Join

---使用北風資料庫
use Northwind

/*
使用INNER JOIN
*/

---客戶訂單
Select CustomerID,CompanyName, ---From dbo.Customers
OrderDate,RequiredDate,ShippedDate ---From dbo.Orders
from dbo.Customers inner join dbo.Orders
on CustomerID=CustomerID ---A.PK=B.FK

-設TABLE別名(AS)
Select C.CustomerID,CompanyName, ---From dbo.Customers
OrderDate,RequiredDate,ShippedDate ---From dbo.Orders
from dbo.Customers as C inner join dbo.Orders as O
on C.CustomerID=O.CustomerID

-----省略 AS , INNER
Select C.CustomerID,CompanyName, ---From dbo.Customers
OrderDate,RequiredDate,ShippedDate ---From dbo.Orders
from dbo.Customers C join dbo.Orders O
on C.CustomerID=O.CustomerID
------------------------------------------------------------------------------
Select O.OrderID,OrderDate, ---From dbo.Orders
P.ProductID,ProductName,P.UnitPrice as '訂價', ---From dbo.Products
D.UnitPrice as '售價',Quantity,Discount ---From dbo.Order Details
from dbo.Orders O join dbo.[Order Details] D on O.OrderID=D.OrderID
join dbo.Products P on P.ProductID=D.ProductID

--找出售價低於目前訂價訂單及其明細
Select O.OrderID,OrderDate, ---From dbo.Orders
P.ProductID,ProductName,P.UnitPrice as '訂價', ---From dbo.Products
D.UnitPrice as '售價',Quantity,Discount ---From dbo.Order Details
From dbo.Orders O join dbo.[Order Details] D on O.OrderID=D.OrderID
join dbo.Products P on P.ProductID=D.ProductID
where P.UnitPrice>D.UnitPrice
-------------------------------------------------------------------------
Select O.OrderID,OrderDate, ---From dbo.Orders
P.ProductID,ProductName,P.UnitPrice as '訂價', ---From dbo.Products
D.UnitPrice as '售價',Quantity,Discount ---From dbo.Order Details
From dbo.Orders O join dbo.[Order Details] D on O.OrderID=D.OrderID
join dbo.Products P on P.ProductID=D.ProductID
where P.UnitPrice>D.UnitPriceOrder by 訂價 desc--只有Order by 可用欄位別名，Where不可用欄位別名
-------------------
Select O.OrderID,OrderDate, ---From dbo.Orders
C.CustomerID,CompanyName, ---From dbo.Customers
E.EmployeeID,FirstName+' '+LastName as '員工', ---from dbo.Employees
P.ProductID,ProductName,P.UnitPrice as '訂價', ---From dbo.Products
D.UnitPrice as '售價',Quantity,Discount, ---From dbo.Order Details
D.UnitPrice*Quantity*(1-Discount) as '小計'
from dbo.Orders O join dbo.[Order Details] D on O.OrderID=D.OrderID
join dbo.Products P on P.ProductID=D.ProductID
join dbo.Customers C on C.CustomerID=O.CustomerID
join dbo.Employees E on E.EmployeeID=O.EmployeeID