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

Presentation on theme: "다중 테이블 조회 둘 이상의 테이블을 통합하여 조작하는 기법을 이해한다."— Presentation transcript:

1 다중 테이블 조회 둘 이상의 테이블을 통합하여 조작하는 기법을 이해한다.
조인을 이해하고, 이를 이용한 쿼리를 작성할 수 있다. 하위 쿼리를 이해하고, 이를 이용한 쿼리를 작성할 수 있다. 유니온을 이해하고, 이를 이용한 쿼리를 작성할 수 있다.

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

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

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

5 1. 조인 크로스 조인 buyer 테이블과 orders 테이블의 모든 가능한 행들의 쌍을

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

7 1. 조인 크로스 조인 예제 2 1 USE Test1DB; 2 GO 3 SELECT * 4 FROM buyer
5 CROSS JOIN orders; 예제 2

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

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

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

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

12 1. 조인 내부 조인 - 테이블 별칭을 사용 - 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; 예제 3

13 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 ON b.buyer_id = o.buyer_id;

14 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 ON o.buyer_id = b.buyer_id 5 INNER JOIN product AS p ON o.product_id = p.product_id; 예제 5

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

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

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

18 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 ON p.product_id = o.product_id;

19 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 ON p.product_id = o.product_id AND p.product_name <> '수박';

20 1. 조인 외부 조인 - 구식 외부 조인 문을 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 <> '수박'; 예제 8

21 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 <> '수박‘;

22 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 <> '수박‘;

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

24 1. 조인 외부 조인 - 호환성 수준을 원상 복구(여기서 100은 SQL Server 2008을 가리킴) 1 ALTER DATABASE Test1DB 2 SET COMPATIBILITY_LEVEL = 100; 예제 10

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

26 1. 조인 외부 조인 예제 11 'a.SalesOrderDetailID',
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 ON a.SalesOrderID = b.SalesOrderID AND a.SalesOrderDetailID < b.SalesOrderDetailID 7 ORDER BY a.SalesOrderID, a.SalesOrderDetailID, b.SalesOrderDetailID; 예제 11

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

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

29 2. 하위 쿼리 단순 하위 쿼리 - 하위 쿼리를 연산식으로 사용 1 SELECT SalesOrderDetailID, UnitPriceDiscount, UnitPriceDiscount - ( 2 SELECT AVG(UnitPriceDiscount) FROM SalesLT.SalesOrderDetail 3 ) AS 'UnitPriceDiscount의 평균값과의 차이' 4 FROM SalesLT.SalesOrderDetail; 예제 12

30 2. 하위 쿼리 단순 하위 쿼리 - 하위 쿼리를 테이블이 오는 곳에 사용 1 SELECT a.ProductCategoryID, a.Name 2 FROM ( 3 SELECT * FROM AdventureWorksLT.SalesLT. ProductCategory 4 ) AS a 5 WHERE a.ParentProductCategoryID IS NULL; 예제 13

31 2. 하위 쿼리 상관 하위 쿼리 상관 하위 쿼리(correlated subquery): 외부 쿼리와 하위 쿼리가
상호 연관된, 복잡한 형태의 쿼리 1 SELECT DISTINCT SalesOrderID, UnitPrice AS '최고 단가' 2 FROM SalesLT.SalesOrderDetail AS a 3 WHERE UnitPrice = ( 4 SELECT MAX(UnitPrice) FROM SalesLT.SalesOrderDetail AS b WHERE a.SalesOrderID = b.SalesOrderID 7 ); 1 SELECT SalesOrderID, SalesOrderDetailID, UnitPrice 2 FROM SalesLT.SalesOrderDetail; 예제 14 예제 15

32 2. 하위 쿼리 단순 하위 쿼리 [예제 14]와 [예제 15]의 실행 결과를 대조하면서 처리 절차를 이해해보자.
➊ a의 첫 번째 행부터 시작한다(예: SalesOrderID=71774, SalesOrderDetailID=110562, UnitPrice= ). ➋ a의 SalesOrderID 값(예: 71774)을 하위 쿼리로 넘긴다. ➌ b의 SalesOrderID가 외부 쿼리에서 넘겨받은 값(예: 71774)인 행들(2행) 중에서 UnitPrice의 최댓값(예: )을 찾아서(4행) 외부 쿼리로 돌려준다. ➍ a는 UnitPrice의 값이 하위 쿼리에서 돌려받은 최댓값(예: ) 과 일치하는지를 판단한다(3행).

33 2. 하위 쿼리 단순 하위 쿼리 ➎ 과정 ➍의 판단 결과가TRUE면 이 행의 열SalesOrderID(예: 71774), SalesOrderDetailID((예: ), UnitPrice(예: )) 값들 을 결과 집합에 포함시킨다. UnitPrice 중 동일한 최댓값이 두 개 이상 있을 경우에는 1행의 DISTINCT에 의해 한 행만 결과 집합에 포함된다. ➏ a의 다음 행(예: SalesOrderID=71774, SalesOrderDetailID=110563, UnitPrice= )에 대해서 ➋~➎를 반복한다. UnitPrice 중 동일한 최댓값이 두 개 이상 있을 경우에는 1행의 DISTINCT에 의해 한 행만 결과 집합에 포함된다.

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

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

36 2. 하위 쿼리 단순 하위 쿼리 예제 16 - 한 번이라도 주문을 받은 제품들의 목록만 조회하는 쿼리다.
- 3∼7행의 하위 쿼리가 한 행이라도 결과 집합을 돌려주면(즉 orders 테이블에 존재하는 product_id이면) EXISTS 테스트가 성공하고, 이 product_id를 가진 product 테이블의 행은 최종적인 결과 집합에 포함된다. 예제 16

37 2. 하위 쿼리 단순 하위 쿼리 예제 16 1 SELECT * 2 FROM Test1DB.dbo.product AS p
3 WHERE EXISTS ( 4 SELECT 1 FROM Test1DB.dbo.orders AS o WHERE p.product_id = o.product_id 7 );

38 2. 하위 쿼리 단순 하위 쿼리 - NOT EXISTS를 사용 - 한 번도 주문을 받지 못한 제품들의 목록만 조회하는 쿼리 예제 17 1 SELECT * 2 FROM Test1DB.dbo.product AS p 3 WHERE NOT EXISTS ( 4 SELECT 1 FROM Test1DB.dbo.orders AS o WHERE p.product_id = o.product_id 7 );

39 2. 하위 쿼리 조인으로의 변환 대부분의 상관 하위 쿼리는 조인으로 변환할 수 있음 상관 하위 쿼리의 단점
JOIN에 비해 처리가 느리다. 루프 개념과 내·외부 쿼리 간의 연관 관계 등으로 인해 처리 과정을 이해하기 어렵다.

40 2. 하위 쿼리 조인으로의 변환 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; 예제 18

41 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 ON a.SalesOrderID = b.SalesOrderID 5 GROUP BY a.SalesOrderID, a.UnitPrice 6 HAVING a.UnitPrice = MAX(b.UnitPrice) 7 ORDER BY a.SalesOrderID; 예제 18

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

43 3. 유니온 개요 구문 설명 select_statement의 열들은 개수가 일치해야 하고, 대응되는 열들의
데이터 형식은 서로 호환성이 있어야 한다. 최종적인 결과 집합의 열 제목은 첫 번째 select_statement의 것을 따르므로, 첫 번째 select_statement의 열에는 반드시 열 이름을 부여해야 한다(인위적으로 만들어진 열이라면 열 제목을 붙인다). 중복된 행은 제거된다. 모든 행을 포함시키고 싶으면 ALL을 사용한다. 기본적으로 결과 집합은 첫 번째 열 값으로 정렬된다. 이 순서를 바꾸려면 ORDER BY 절을 사용해야 한다. ORDER BY 절에서는 첫 번째 select_statement의 열 이름을 사용한다.

44 3. 유니온 유니온 예제 20 - 둘 이상의 SELECT 문을 유니온 연산자로 합성
- 기본적으로 결과 집합은 첫 번째 열(Name) 값으로 정렬됨 1 SELECT FirstName + ' ' + LastName AS Name, Address 2 FROM AdventureWorks.Person.Contact 3 UNION 4 SELECT ReviewerName, Address 5 FROM AdventureWorks.Production.ProductReview; 예제 20

45 3. 유니온 유니온 예제 21 - [예제 20]의 결과 집합 행들의 테이블을 확인
1 SELECT FirstName + ' ' + LastName AS Name, 'Contact' AS source, Address 2 FROM AdventureWorks.Person.Contact 3 UNION 4 SELECT ReviewerName, 'ProductReview' AS source, Address 5 FROM AdventureWorks.Production.ProductReview; 예제 21