Inner Join
Join된 테이블에서 on조건에 해당하는 것만을 조회
Join된 테이블에서 on조건에 해당하는 것만을 조회
Select BusinessEntityID, A.DepartmentID, Name, GroupName
From HumanResources.EmployeeDepartmentHistory As A Inner Join HumanResources.Department As B
On A.DepartmentID = B.DepartmentID
Order By DepartmentID
From HumanResources.EmployeeDepartmentHistory As A Inner Join HumanResources.Department As B
On A.DepartmentID = B.DepartmentID
Order By DepartmentID
Left Join
Left Outer Join은 두개 이상의 Table을 조인하여 Data를 조회하되 왼쪽(Left)에 있는 Table의 내용은 모두 가져옴.
On조건에 부합하는 것은 정상적으로 조회하고 조건에 부합하지 않는 경우에는 Null로 표시
Select A.BusinessEntityID, B.AddressID, B.AddressTypeID
From Person.Person As A Left Join Person.BusinessEntityAddress As B
On A.BusinessEntityID = B.BusinessEntityID
Order By BusinessEntityID
From Person.Person As A Left Join Person.BusinessEntityAddress As B
On A.BusinessEntityID = B.BusinessEntityID
Order By BusinessEntityID
Right Outer Join
Left Outer Join과 반대로 오른쪽에 조인된 Table을 기준으로 모든 Data를 가져옴
Select A.ProductID, A.StandardCost, B.Name
From Production.ProductCostHistory As A Right Outer Join Production.Product As B
On A.ProductID = B.ProductID
From Production.ProductCostHistory As A Right Outer Join Production.Product As B
On A.ProductID = B.ProductID
Full Outer Join
Full Outer Join은 간단히 말해 Left Outer Join과 Right Outer Join을 합한 것
양쪽 Table의 모든 Data를 다 가져옴. 표시할 수 없는 값은 null
Select ProductID, ProductDescriptionID, StandardCost, Description
From Production.ProductCostHistory Full Outer Join Production.ProductDescription
On ProductID = ProductDescriptionID
From Production.ProductCostHistory Full Outer Join Production.ProductDescription
On ProductID = ProductDescriptionID
Cross Join
왼쪽 Table 한행당 오른쪽 Table전체를 그리고 다시 왼쪽 Table 한행당 오른쪽 Table 전체... 이런한 방법으로 Join된 결과를 표시
Select A.ProductID, A.Name, StartDate, EndDate, B.StandardCost
From Production.Product As A Cross Join Production.ProductCostHistory As B
Order By A.ProductID
From Production.Product As A Cross Join Production.ProductCostHistory As B
Order By A.ProductID
Where 절을 이용한 Table Join
두개 이상의 Table을 Join하는데는 굳이 Join문을 이용하지 않고도 Where절을 이용하는 방법
Select ProductID, ProductDescriptionID, StandardCost, Description
From Production.ProductCostHistory, Production.ProductDescription
Where ProductID = ProductDescriptionID
From Production.ProductCostHistory, Production.ProductDescription
Where ProductID = ProductDescriptionID
Prev