29. 조인(Join)

-- 조인(Join) : Left 조인

-- 카테고리테이블설계

Create Table dbo.Categories

(

       CategoryID Int Identity(1, 1) Not Null Primary Key, -- 카테고리번호

       CategoryName VarChar(25) Not Null  -- 카테고리명

)

Go

 

Insert Categories Values('가전')  -- 1번카테고리

Go

Insert Categories Values('컴퓨터') -- 2번카테고리

Go

Insert Categories Values('서적')

Go

 

--상품테이블설계

Create Table dbo.Products

(

       ProductID Int Identity(1, 1) Not Null Primary Key,   -- 상품고유번호

       ModelName VarChar(25) Not Null,        -- 상품명

       SellPrice Int Null,                    -- 가격

       CategoryID Int Null                    -- 카테고리(1,2,3)

)

Go

 

--[!] 외래키따로지정: 외부에서

Alter Table dbo.Products

Add Foreign Key(CategoryID) References Categories(CategoryID)

Go

 

-- 가전, 냉장고, 100

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

Go

 

-- 컴퓨터, 노트북, 200

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

Go

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

Go

 

-- 상품평(코멘트) 테이블설계

Create Table dbo.Reviews

(

       ReviewID Int Identity(1, 1) Primary Key,      -- 일련번호

       ProductID Int References Products(ProductID), -- 외래키

       Comment VarChar(3850)                         -- 내용

)

Go

Insert Into Reviews Values(1, '냉장실에서얼음이얼어요...')

Insert Into Reviews Values(2, '브랜드가모닝글로리')

 

 

 

Select * From Categories

Select * From Products

 

 

-- 상품리스트출력: 카테고리명, 상품명, 판매가

--[1] SQL Server 전용문법

Select CategoryName, ModelName, SellPrice

From Categories, Products

Where Categories.CategoryID = Products.CategoryID

Go

 

--[2] ANSI-SQL 공통문법

Select CategoryName, ModelName, SellPrice

From Categories Join Products

       On Categories.CategoryID = Products.CategoryID

Go

 

--[3] 상세표시: Join은기본적으로Inner Join이다.

Select

       Categories.CategoryName,

       Products.ModelName,

       Products.SellPrice

From Categories Inner Join Products

       On Categories.CategoryID = Products.CategoryID

Go

 

--[4] 축약표시-> SQL Server전용문법이다.

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

From Categories c, Products p

Where c.CategoryID = p.CategoryID

Go

 

/*

위의[1], [2], [3], [4]는모두똑같은구문이다. 표현방식은다르지만실행결과는모두같다.

*/

 

Select * From Products

Select * From Reviews

 

-- 상품명, 코멘트

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

Select p.ModelName, r.Comment

From Products p Inner Join Reviews r

       On p.ProductID = r.ProductID

Go

 

-- Left Outer Join : 왼쪽테이블의모든목록은출력

Select p.ModelName, r.Comment

From Products p  Left Outer Join Reviews r

       On p.ProductID = r.ProductID

Go

 

 

-- 3개테이블조인

-- 카테고리명, 상품명, 판매가, 코멘트

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

From

       Categories c

             Join Products p

                    On c.CategoryID = p.CategoryID

                    Join Reviews r

                           On p.ProductID = r.ProductID

Go          

 

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

From Categories c, Products p, Reviews r

Where

       c.CategoryID = p.CategoryID

       And

       p.ProductID = r.ProductID

Go