26.MS_SQL 2008 - Join_Left(조인문) [2]

--[!]외래키따로지정: 외부에서참조가능(외래키는따로밖에지정한다.)

Alter Table dbo.Products

Add Foreign Key(CategoryID) References Categories(CategoryID)

Go

 

--가전, 냉장고, 100

Insert Products Values('냉장고', 100, 1)

Go

--컴퓨터, 노트북

Insert Products Values('노트북', 200, 2)

Go

Insert Products Values('데스크톱', 150, 3)

Go

--애러

--Insert Products Values('데스크톱', 150, 4) CateGoryID가1~3까지인데벗어나서애러

--Go

 

--상품리스트출력: 카테고리명, 상품명, 판매가(Join문사용)

--[1] SQL Server 전용문법

Select CategoryName, ModelName, SellPrice

From Categories, Products

Where Categories.CategoryID = Products.CategoryID

Go

--[2] ANSI-SQL 공통문법 (1과는같은문법)

Select CategoryName, ModelName, SellPrice

From Categories Join Products

       On Categories.CategoryID = Products.CategoryID

Go

 

--[3]상세표시(1과와같은문법= 추천)

Select

       Categories.CategoryName,

       Products.ModelName,

       Products.SellPrice

From Categories Inner Join Products

On Categories.CategoryID = Products.CategoryID

 

--[4] 축약표시(4번문법상으로잘못됐어요. 쓴다음에from문에서정의)

Select c.CategoryName, p.ModelName, p.SellPrice

From Categories c, Products p

Where c.CategoryID = p.ProductID

Go

 

 

select *From Products

select *From Reviews

 

--상품명, 코멘트

--기본조인(Inner Join)은매치되는결과만가져온다. 데스크톱누락

Select p.ModelName, r.Comment

From Products p Join Reviews r --Products를줄여서p로Reviews를줄여서r로

       On p.CategoryID = r.ProductID

Go

 

--Left Outer Join : 왼쪽테이블의모든목록은출력(Null값도출력)

Select p.ModelName, r.Comment

From Products p Left Outer Join Reviews r --Products를줄여서p로Reviews를줄여서r로

       On p.CategoryID = r.ProductID

Go

---

26Join(2).sql