賢爸知識分享站

介紹 Inner Join  Left Join   Right Join  Cross Join  的使用

======== 新增 Empord 資料庫 ========

Create Database EmpOrd

======== 新增 Table Emp ========

Create Table Emp(

EmpId int primary key,

EmpName nvarchar(50))

======== 新增 Ord Table ======== 

Create Table Ord(

OrdId int primary key,

EmpId int,

Amount int)

======== Insert 資料 ======== 

Insert Into Emp(EmpId, EmpName)

Values(1, N'Peter'),

(2, N'John'),

(3, N'Alice')

======== Insert 資料 ======== 

Insert Into Ord(OrdId, EmpId, Amount)

Values(1, 1, 5000),

(2, 1, 4200),

(3, 2, 3800),

(4, 3, 6500)

======== 列印結果 ======== 

Select * From Emp

Select * From Ord

======== Inner Join 使用 ======== 

Select o.OrdId, o.EmpId, e.EmpName, o.Amount

From Emp e

Inner Join Ord o

   on e.EmpId = o.EmpId

Select e.EmpId, e.EmpName, SUM(o.Amount) Total

From Emp e

Inner Join Ord o

   on e.EmpId = o.EmpId

Group By e.EmpId, e.EmpName

======== Left Join 使用 ========

Select e.EmpId, e.EmpName, SUM(o.Amount) as Total

From Emp e

Left Join Ord o

   on e.EmpId = o.EmpId

Group By e.EmpId, e.EmpName

Order By Total

======== Right Join 使用 ========

Select * From Emp e

Right Join Ord o

   on e.EmpId = o.EmpId

======== Full Join 使用 ======== 

Select * From Emp e

Full Join Ord o

   on e.EmpId = o.EmpId

======== Cross Join 使用 ======== 

Select * From Emp A

Cross Join Emp B