03 :

다중 테이블 조회 학습 목표 둘 이상의 테이블을 통합하여 조작하는 기법을 이해한다.

조인을 이해하고, 이를 이용한 쿼리를 작성할 수 있다.

하위 쿼리를 이해하고, 이를 이용한 쿼리를 작성할 수 있다.

유니온을 이해하고, 이를 이용한 쿼리를 작성할 수 있다.

Chapter

03

다중 테이블 조회 1. 조인 2. 하위 쿼리 3. 유니온

개요

Chapter

03

1. 조인

 조인(join): 두 개 이상의 테이블들을 합성한 후, 여기서 유용한 정보를 추출하는 연산  대부분 관계를 맺은 테이블 간에 조인을 수행하고 이를 통해 통합적인 정보를 제공  크로스 조인, 자연 조인, 내부 조인, 외부 조인, 자체 조인 등이 있음  너무 많은 테이블끼리 조인을 수행할 때는 성능을 떨어뜨리므로 주의해야 함(시스템에 따라 좌우되기는 하지만, 보통 4∼5개 정도까지 의 테이블 사이의 조인은 크게 문제되지 않는다.) 예제 1 - Test1DB 생성 및 초기 데이터를 삽입하는 스크립트를 생성

Chapter

03

크로스 조인 1. 조인

 크로스 조인(cross join, 상호 조인): 두 테이블을 서로 곱하는 개념의 연산으로, 일명 카티션 곱(Cartesian product)이라 함  자연 조인(natural join): 조인 테이블에 같은 이름의 열이 두 개 이상 나타나지 않도록 하는 조인  조인 테이블의 행 수는 두 테이블의 행 수를 곱한 값이 되며 열 수는 두 테이블의 열 수를 더한 값이 됨

Chapter

03

크로스 조인 1. 조인

 buyer 테이블과 orders 테이블의 모든 가능한 행들의 쌍을 연결하여 조인 테이블(buyer * orders 테이블)을 만든다.

Chapter

03

크로스 조인 1. 조인

 Transact-SQL에서 크로스 조인을 수행하는 구문 두 가지 • 두 경우 모두 조인 테이블의 행을 제약하는ON 절 또는 WHERE 절이 없는 것에 유의할 것 • ①은 SQL-92(1장 참고)형식이고 ②는 구식 형식인데, 가능하면 ①을 사용할 것을 권함

Chapter

03

크로스 조인 1. 조인

예제 2

1 USE Test1DB; 2 GO 3 SELECT * 4 FROM buyer 5 CROSS JOIN orders;

Chapter

03

내부 조인 1. 조인

 내부 조인(inner join): 각 테이블에서 비교 연산자에 의한 조인 조건을 만족하는 행들만 조인 테이블에 포함시켜주는 조인으로, 대부분의 조인이 이에 해당됨  조인 테이블의 행 수는 자식 테이블(orders)의 행 수와 같다.

Chapter

03

내부 조인 1. 조인

 내부 조인의 구문 • 구문 ②는 구식 내부 조인 구문으로, ON 절을 사용할 수 없고 조인 조건과 일반 행 제약 조건을 WHERE 절에서 통합해서 지정해야 하므로 정교하지 못하다. 따라서 구문 ②는 가급적 사용하지 않는 것이 좋다.

Chapter

03

내부 조인 1. 조인

 구문 설명 • INNER: 생략할 수 있다(대부분의 조인이 내부 조인이다).

• join_condition - 비교 연산자(=, ＜, ＜＞ 등)를 사용하는 연산식 - =`연산자를 가장 많이 사용하고, ＜ 또는 ＞는 자체 조인에서 종종 사용되지만, 다른 비교 연산자는 거의 사용되지 않음 - join_condition은“table1.column1 { = | ＜ | ＞ } table2.column2”의 형식을 띠는데, column1과 column2는 데이터 형식에 호환성이 있어야 하며, 대부분의 경우 부모 테이블의 주 키와 자식 테이블의 외래 키가 사용됨 - 부모 테이블이 복합 주 키를 가질 경우에는 주 키와 외래 키를 구성하는 모든 열들에 대한 비교 연산식을 AND로 연결해야 함

Chapter

03

내부 조인 1. 조인

• 하나 이상의 column - 중복된 이름이 나타나지 않도록 열거해야 하고(즉 자연 조인으로 만든다), 두 테이블에 같은 이름의 열들이 있을 경우에는 table.column의 형식으로 반드시 식별해야 함 - 모든 열을 table.column의 형식으로 식별해주면 가독성이 향상되는데, 이 경우에는 테이블 별칭alias을 사용하는 것이 바람직함 • 세 개 이상의 테이블 간의 조인도 가능하며, 이때는 JOIN 절을 반복해서 나열해줌

Chapter

03

내부 조인 1. 조인

예제 3 - 테이블 별칭을 사용 - 2행에서 first_table은 원래 테이블 이름이고, f는 테이블 별칭 - 3행처럼 AS를 생략할 수도 있지만 가독성은 떨어짐 - 테이블 별칭은 두 자 이상으로 구성할 수도 있지만, 가능하면 한 자로 구성하는 것이 간단명료하고 가독성도 향상됨

1 SELECT f.col1, f.col2, s.* 2 FROM first_table AS f 3 JOIN second_table s 4 ON f.col1 = s.col1;

Chapter

03

내부 조인 1. 조인

예제 4 - [그림 3-2]의 개념을 구현

1 SELECT b.buyer_id, b.buyer_name, o.orders_id, o.product_id, o.qty

2 FROM buyer AS b 3 INNER JOIN orders AS o 4 ON b.buyer_id = o.buyer_id;

Chapter

03

내부 조인 1. 조인

예제 5 - 세 개의 테이블을 내부 조인

1 SELECT o.orders_id, b.buyer_name, p.product_name, o.qty

2 FROM orders AS o 3 INNER JOIN buyer AS b 4 ON o.buyer_id = b.buyer_id

5 INNER JOIN product AS p 6 ON o.product_id = p.product_id;

Chapter

03

외부 조인 1. 조인

 외부 조인(outer join): 조인 조건을 만족하지 않는 행까지 결과 집합에 포함시켜 돌려주는 특수한 조인으로, 집계할 때 종종 사용된다. • 왼쪽 외부 조인, 오른쪽 외부 조인, 완전 외부 조인

Chapter

03

외부 조인 1. 조인

 외부 조인 구문 • 구문 ②는 구식 외부 조인 구문으로, ON 절을 사용할 수 없고 조인 조건과 일반 행 제약 조건을 WHERE 절에서 통합하여 지정해줘야 하므로 부정확해질 수 있다. 이로 인해 잘못된 결과가 나오는 치명적인 문제가 발생할 가능성이 있으므로 가급적 사용하지 않는 것이 좋다.

Chapter

03

외부 조인 1. 조인

 구문 설명 • LEFT는 왼쪽, RIGHT는 오른쪽, FULL은 완전 외부 조인을 지정함 • OUTER는 생략 가능(LEFT, RIGHT,FULL은 생략할 수 없다) • outer_join_condition은“table1.column1 { *= | =* | *=* } table2.column2”의 형식을 띔 • *= 연산자는 왼쪽, =* 연산자는 오른쪽, *=* 연산자는 완전 외부 조 인을 지정함

Chapter

03

외부 조인 1. 조인

예제 6 - [그림 3-3]의 개념을 구현

1 SELECT p.product_id, p.product_name, o.orders_id, o.buyer_id, o.qty

2 FROM product AS p 3 LEFT OUTER JOIN orders AS o 4 ON p.product_id = o.product_id;

Chapter

03

외부 조인 1. 조인

예제 7 - SQL-92 형식의 외부 조인 구문을 사용

1 SELECT p.product_id, p.product_name, o.orders_id, o.buyer_id, o.qty

2 FROM product AS p 3 LEFT OUTER JOIN orders AS o 4 ON p.product_id = o.product_id

5 AND p.product_name <> '

수박

';

Chapter

03

외부 조인 1. 조인

예제 8 - 구식 외부 조인 문을 SQL Server 2008에서 실행하면 오류가 발생함

1 SELECT p.product_id, p.product_name, o.orders_id, o.buyer_id, o.qty

2 FROM product p, orders o 3 WHERE p.product_id *= o.product_id

4 AND p.product_name <> '

수박

';

Chapter

03

외부 조인 1. 조인

예제 9 - 구식 외부 조인 쿼리를 강제로 실행하고 다시 [예제 8]의 SELECT 문을 실행

1 ALTER DATABASE Test1DB 2 SET COMPATIBILITY_LEVEL = 80; 1 SELECT p.product_id, p.product_name, o.orders_id, o.buyer_id, o.qty

2 FROM product p, orders o 3 WHERE p.product_id *= o.product_id

4 AND p.product_name <> '

수박

‘;

Chapter

03

외부 조인 1. 조인

예제 9

1 SELECT p.product_id, p.product_name, o.orders_id, o.buyer_id, o.qty

2 FROM product p, orders o 3 WHERE p.product_id *= o.product_id

4 AND p.product_name <> '

수박

‘;

Chapter

03

외부 조인 1. 조인

 [예제 9]의 결과 집합이 [예제 7]과 다른 이유 • SELECT 문의 4행처럼 WHERE 절의 일부로 포함된 조건식 “p.product_name ＜＞‘수박’”은 예외 없이 적용됨 - 즉 이 조건식에 의해 product_name 열 값이‘수박’인 모든 행은 왼쪽 외부 조인임에도 불구하고 제외된다. 이는 원하는 바가 아니며, 잘못된 결과라고 할 수 있다.

• 결론은 구식 외부 조인 문을 사용해서는 안 된다.

Chapter

03

외부 조인 1. 조인

예제 10 - 호환성 수준을 원상 복구(여기서 100은 SQL Server 2008을 가리킴)

1 ALTER DATABASE Test1DB 2 SET COMPATIBILITY_LEVEL = 100;

Chapter

03

자체 조인 1. 조인

 자체 조인(self join): 자기 자신의 테이블과 하는 조인  자체 조인은 흔하지는 않지만, 같은 테이블의 서로 관련이 있는 행들을 조합해서 추출할 필요가 있을 때 종종 사용됨

Chapter

03

외부 조인 1. 조인

예제 11

1 SELECT a.SalesOrderID, a.SalesOrderDetailID AS 'a.SalesOrderDetailID', 2 b.SalesOrderDetailID AS 'b.SalesOrderDetailID' 3 FROM SalesLT.SalesOrderDetail a 4 INNER JOIN SalesLT.SalesOrderDetail b 5 ON a.SalesOrderID = b.SalesOrderID

6 AND a.SalesOrderDetailID < b.SalesOrderDetailID

7 ORDER BY a.SalesOrderID, a.SalesOrderDetailID, b.SalesOrderDetailID;

Chapter

03

단순 하위 쿼리 2. 하위 쿼리

 하위 쿼리(subquery) : 다른 명령문(SELECT, INSERT, UPDATE 또는 DELETE 문)에 포함된 SELECT 문  단순 하위 쿼리(simple subquery) : 상관(correlated) 하위 쿼리가 아닌 하위 쿼리

Chapter

03

단순 하위 쿼리 2. 하위 쿼리

 하위 쿼리에 대해 유의할 사항 • 복잡한 쿼리를 일련의 논리적 절차로 분리하거나 다른 쿼리의 결과에 의존하는 쿼리를 만들 때 사용함 • 하위 쿼리는 반드시 ( )로 둘러싸야 함 • 하위 쿼리가 단일 값이나 값 목록을 돌려줄 경우, 연산식이 오는 곳에 사용할 수 있고, 테이블이 오는 곳에 사용할 수도 있음 • 특수한 데이터 형식(예: Transact-SQL의 text나 image)의 열을 추출하는 하위 쿼리는 만들 수 없음

Chapter

03

단순 하위 쿼리 2. 하위 쿼리

예제 12 - 하위 쿼리를 연산식으로 사용

1 SELECT SalesOrderDetailID, UnitPriceDiscount, UnitPriceDiscount - ( 2 SELECT AVG(UnitPriceDiscount) FROM SalesLT.SalesOrderDetail

3 ) AS 'UnitPriceDiscount

의 평균값과의 차이

' 4 FROM SalesLT.SalesOrderDetail;

Chapter

03

단순 하위 쿼리 2. 하위 쿼리

예제 13 - 하위 쿼리를 테이블이 오는 곳에 사용

1 SELECT a.ProductCategoryID, a.Name

2 FROM ( 3 SELECT * FROM AdventureWorksLT.SalesLT.

ProductCategory 4 ) AS a 5 WHERE a.ParentProductCategoryID IS NULL;

Chapter

03

상관 하위 쿼리 2. 하위 쿼리

 상관 하위 쿼리(correlated subquery): 외부 쿼리와 하위 쿼리가 상호 연관된, 복잡한 형태의 쿼리 예제 14

1 SELECT DISTINCT SalesOrderID, UnitPrice AS '

최고 단가

' 2 FROM SalesLT.SalesOrderDetail AS a 3 WHERE UnitPrice = ( 4 SELECT MAX(UnitPrice) 5 FROM SalesLT.SalesOrderDetail AS b 6 WHERE a.SalesOrderID = b.SalesOrderID

7 );

예제 15

1 SELECT SalesOrderID, SalesOrderDetailID, UnitPrice 2 FROM SalesLT.SalesOrderDetail;

Chapter

03

단순 하위 쿼리 2. 하위 쿼리

 [예제 14]와 [예제 15]의 실행 결과를 대조하면서 처리 절차를 이해해보자.

➊ a의 첫 번째 행부터 시작한다(예: SalesOrderID=71774, SalesOrderDetailID=110562, UnitPrice=356.898).

➋ a의 SalesOrderID 값(예: 71774)을 하위 쿼리로 넘긴다.

➌ b의 SalesOrderID가 외부 쿼리에서 넘겨받은 값(예: 71774)인 행들(2행) 중에서 UnitPrice의 최댓값(예: 356.898)을 찾아서(4행) 외부 쿼리로 돌려준다.

➍ a는 UnitPrice의 값이 하위 쿼리에서 돌려받은 최댓값(예: 356.898) 과 일치하는지를 판단한다(3행).

Chapter

03

단순 하위 쿼리 2. 하위 쿼리

➎ 과정 ➍ 의 판단 결과가TRUE면 이 행의 열SalesOrderID(예: 71774), SalesOrderDetailID((예: 110562), UnitPrice(예: 356.898)) 값들 을 결과 집합에 포함시킨다. UnitPrice 중 동일한 최댓값이 두 개 이상 있을 경우에는 1행의 DISTINCT에 의해 한 행만 결과 집합에 포함된다.

➏ a의 다음 행(예: SalesOrderID=71774, SalesOrderDetailID=110563, UnitPrice=356.898)에 대해서 ➋ ~ ➎ 를 반복한다. UnitPrice 중 동일한 최댓값이 두 개 이상 있을 경우에는 1행의 DISTINCT에 의해 한 행만 결과 집합에 포함된다.

Chapter

03

단순 하위 쿼리 2. 하위 쿼리

Chapter

03

2. 하위 쿼리 EXISTS와 NOT EXISTS

 EXISTS와 NOT EXISTS 연산자는 하위 쿼리(많은 경우, 상관 하위 쿼리)와 함께 사용되는 특수한 연산자로서, 해당 하위 쿼리가 한 행이 라도 결과 집합을 돌려주는지를 판단할 때 사용됨  EXISTS 하위 쿼리에서는 행이 존재하는지 여부만 파악하면 되므로 SELECT 목록에서 * 또는 임의의 열 목록을 지정할 필요가 없음  EXISTS와NOT EXISTS에 관련된 부분적인 구문

Chapter

03

단순 하위 쿼리 2. 하위 쿼리

예제 16 - 한 번이라도 주문을 받은 제품들의 목록만 조회하는 쿼리다.

- 3∼7행의 하위 쿼리가 한 행이라도 결과 집합을 돌려주면(즉 orders 테이블에 존재하는 product_id이면) EXISTS 테스트가 성공하고, 이 product_id를 가진 product 테이블의 행은 최종적인 결과 집합에 포함된다.

Chapter

03

단순 하위 쿼리 2. 하위 쿼리

예제 16

1 SELECT * 2 FROM Test1DB.dbo.product AS p 3 WHERE EXISTS ( 4 SELECT 1 5 FROM Test1DB.dbo.orders AS o 6 WHERE p.product_id = o.product_id

7 );

Chapter

03

단순 하위 쿼리 2. 하위 쿼리

예제 17 - NOT EXISTS를 사용 - 한 번도 주문을 받지 못한 제품들의 목록만 조회하는 쿼리

1 SELECT * 2 FROM Test1DB.dbo.product AS p 3 WHERE NOT EXISTS ( 4 SELECT 1 5 FROM Test1DB.dbo.orders AS o 6 WHERE p.product_id = o.product_id

7 );

Chapter

03

2. 하위 쿼리 조인으로의 변환

 대부분의 상관 하위 쿼리는 조인으로 변환할 수 있음  상관 하위 쿼리의 단점 • JOIN에 비해 처리가 느리다.

• 루프 개념과 내·외부 쿼리 간의 연관 관계 등으로 인해 처리 과정을 이해하기 어렵다.

Chapter

03

2. 하위 쿼리 조인으로의 변환

예제 18

1 SELECT DISTINCT a.ProductID

2 FROM SalesLT.SalesOrderDetail AS a 3 WHERE a.ProductID IN ( 4 SELECT b.ProductID

5 FROM SalesLT.SalesOrderDetail AS b 6 WHERE a.SalesOrderID <> b.SalesOrderID

7 ) 8 ORDER BY a.ProductID; 9 -->

조인으로 변환 :

10 SELECT DISTINCT a.ProductID

11 FROM SalesLT.SalesOrderDetail AS a 12 INNER JOIN SalesLT.SalesOrderDetail AS b 13 ON a.ProductID = b.ProductID

14 WHERE a.SalesOrderID <> b.SalesOrderID

15 ORDER BY a.ProductID;

Chapter

03

2. 하위 쿼리 조인으로의 변환

예제 18 - [예제 14]를 조인 쿼리로 변환 - 상관 하위 쿼리에서 집계 함수 MAX가 사용되었기 때문에 조인 쿼리에 GROUP BY와 HAVING 절을 부득이하게 사용함

1 SELECT DISTINCT a.SalesOrderID, a.UnitPrice AS

‘ 최고 단가 '

2 FROM SalesLT.SalesOrderDetail AS a 3 JOIN SalesLT.SalesOrderDetail AS b 4 ON a.SalesOrderID = b.SalesOrderID

5 GROUP BY a.SalesOrderID, a.UnitPrice

6 HAVING a.UnitPrice = MAX(b.UnitPrice) 7 ORDER BY a.SalesOrderID;

개요

Chapter

03

3. 유니온

 유니온(UNION): 둘 이상의 SELECT 문들의 결과 집합을 합성해서 하나의 결과 집합으로 만들어주는 연산자  유니온 연산자로 합성된 SELECT 문은 전체적으로 하나의 문이 됨  비슷한 결과 집합을 가지지만 하나의 SELECT 문으로 만들기 힘들 경우에 유니온 연산자를 사용함  유니온 연산자와 관련된 구문

개요

Chapter

03

3. 유니온

 구문 설명 • select_statement의 열들은 개수가 일치해야 하고, 대응되는 열들의 데이터 형식은 서로 호환성이 있어야 한다.

• 최종적인 결과 집합의 열 제목은 첫 번째 select_statement의 것을 따르므로, 첫 번째 select_statement의 열에는 반드시 열 이름을 부여해야 한다(인위적으로 만들어진 열이라면 열 제목을 붙인다).

• 중복된 행은 제거된다. 모든 행을 포함시키고 싶으면 ALL을 사용한다.

• 기본적으로 결과 집합은 첫 번째 열 값으로 정렬된다. 이 순서를 바꾸려면 ORDER BY 절을 사용해야 한다. ORDER BY 절에서는 첫 번째 select_statement의 열 이름을 사용한다.

Chapter

03

유니온 3. 유니온

예제 20 - 둘 이상의 SELECT 문을 유니온 연산자로 합성 - 기본적으로 결과 집합은 첫 번째 열(Name) 값으로 정렬됨

1 SELECT FirstName + ' ' + LastName AS Name, EmailAddress 2 FROM AdventureWorks.Person.Contact

3 UNION 4 SELECT ReviewerName, EmailAddress 5 FROM AdventureWorks.Production.ProductReview;

Chapter

03

유니온 3. 유니온

예제 21 - [예제 20]의 결과 집합 행들의 테이블을 확인

1 SELECT FirstName + ' ' + LastName AS Name, 'Contact' AS source, EmailAddress 2 FROM AdventureWorks.Person.Contact

3 UNION 4 SELECT ReviewerName, 'ProductReview' AS source, EmailAddress 5 FROM AdventureWorks.Production.ProductReview;