2007. 5. 7. 09:43
Lecture/Beginning SQL Server 2005 Programming
1. JOIN : 두 테이블의 정보를 결합하여 하나의 결과 집합인 "가상" 테이블을 만드는 것
2. INNER JOIN
1) 구문
3. OUTER JOIN
1) 구문
Initial Customer 10.50 NULL
Volume Discount 6.70 NULL
Customer Discount 5.00 Bookbeat
3) RIGHT OUTER JOIN
2. INNER JOIN
1) 구문
SELECT <선택 목록>
FROM <첫 번째 테이블>
<조인 유형> <두번째 테이블>
[ON <조인 조건>]
2) 간단한 JOINFROM <첫 번째 테이블>
<조인 유형> <두번째 테이블>
[ON <조인 조건>]
SELECT p.ProductID, s.SupplierID, p.ProductName, s.CompanyName
FROM Products p
INNER JOIN Suppliers s
ON p.SupplierID = s.SupplierID
WHERE p.ProductID < 4
3) 복잡한 JOINFROM Products p
INNER JOIN Suppliers s
ON p.SupplierID = s.SupplierID
WHERE p.ProductID < 4
SELECT au_lname + ', ' + au_fname AS "Author", t.title
FROM authors a
JOIN titleauthor ta
ON a.au_id = ta.au_id
JOIN titles t
ON t.title_id = ta.title_id
FROM authors a
JOIN titleauthor ta
ON a.au_id = ta.au_id
JOIN titles t
ON t.title_id = ta.title_id
3. OUTER JOIN
1) 구문
SELECT <SELECT 목록>
FROM <왼쪽 테이블>
<LEFT|RIGHT> [OUTER] JOIN <오른쪽 테이블>
ON <조인조건>
2) LEFT OUTER JOINFROM <왼쪽 테이블>
<LEFT|RIGHT> [OUTER] JOIN <오른쪽 테이블>
ON <조인조건>
SELECT discounttype, discount, s.stor_name
FROM discounts d
LEFT OUTER JOIN stores s
ON d.stor_id = s.stor_id
FROM discounts d
LEFT OUTER JOIN stores s
ON d.stor_id = s.stor_id
Initial Customer 10.50 NULL
Volume Discount 6.70 NULL
Customer Discount 5.00 Bookbeat
3) RIGHT OUTER JOIN
SELECT discounttype, discount, s.stor_name
FROM discounts d
RIGHT OUTER JOIN stores s
ON d.stor_id = s.stor_id
FROM discounts d
RIGHT OUTER JOIN stores s
ON d.stor_id = s.stor_id
※ 출처: Beginning SQL Server 2005 Programming