-
1. 조인 (Join, ⋈)
1) 두 릴레이션의 공통 속성을 기준으로 속성 값이 같은 튜플을 수평으로 결합하는 연산임
2) 조인을 수행하기 위해서는 두 릴레이션의 조인에 참여하는 속성이 서로 동일한 도메인으로 구성되어야 함
3) 조인 연산의 결과는 공통 속성 값이 동일한 튜플만 반환함
4) 조인 연산 구분
- 기본연산
- Theta join (세타 조인 θ)
- 조인에 참여하는 두 릴레이션의 속성 값을 비교하여 조건을 만족하는 튜플만 반환함
- 세타조인의 조건은 (=, <>, <=, >=) 중 하나가 됨
- Equi join (동등 조인)
- 세타 조인에서 = 연산자를 사용한 조인을 말함
- 내부 조인(inner join)
- ex) 고객과 고객의 주문 사항을 모두 보이시오
- 확장된 조인 연산 : 외부 조인 (outer join)
- 기본 조인 시 조인에 실패한 튜플을 모두 보여주되 값이 없는 대응 속성에는 NULL 값을 채워서 반환
- 조인에 의해 사라진 정보를 보전하기 위한 조인 연산자의 확장 버전
- 조인 결과를 구한 후 한 릴레이션에서 제외된 모든 튜플을 조인 결과 릴레이션에 추가함
- 왼쪽 외부 조인 (left outer join) : 왼쪽 릴레이션의 튜플을 추가
- 오른쪽 외부 조인 (right outer join) : 오른쪽 릴레이션의 튜플을 추가
- 완전 외부 조인 (full outer join) : 모든 릴레이션의 튜플을 추가
2. Join이 필요한 이유
1) 필요한 정보(상품명, 유저 정보, 카테고리 정보 등)가 각기 다른 테이블에 분산 저장되어 있을 때, 이를 하나의 테이블로 가져와 보기좋게 데이터를 추출하기 위해서
2) join의 문법
SELECT 컬럼명 FROM 테이블명 a
JOIN 테이블명2 b ON a.컬럼명 = b.컬럼명3. Join의 원리
1) 두 릴레이션 속성 값을 비교하여 조건을 만족하지 않는 행이 삭제됨
2) left outer join에서 왼쪽 릴레이션에서 해당 행이 그대로 남음. 단, 오른쪽 릴레이션에서는 아무 정보도 가져오지 못함
4. Join의 유형과 상황 내용 정리
유형 Inner Join Left Join Right Join Full Join
조인 두 개 테이블에 모두 왼쪽 테이블을 기준으로 Left Join과 반대 모든 값이 합쳐짐
결과 존재하는 행만 남음 오른쪽 테이블을 붙임
-> 오른쪽 테이블에 조인되는
값이 없는 경우 null로 표기됨
필요상황 두 개 테이블에 조인키가 한 개 이상의 테이블에 조인
빠짐없이 있을 때 시에 null값이 있거나
(ex: 주문테이블에 상품 테이블1에 있는 값이 테이블2
테이블을 조인하는 경우) 에는 없을 때
(ex: 주문테이블에 유저
테이블을 조인하는 경우)
5. Join 주의사항(가장 많이 하는 실수)
1) WHERE절 실수 case
- "WHERE" 문구가 맨 앞에 오는지 확인하기
2) ON절 실수 case
- ON절에 =, >=, >, <, <=, BETWEEN 연산자가 있는지 반드시 확인하기
/* * 조인 : 두 개 이상 테이블에 SQL 질의 * 1. Customer 테이블을 Orders 테이블과 조건 없이 연결 * - Customer와 Orders 테이블의 조인 결과 튜플의 개수는 고객이 5명이고 주문이 10개 * => 5*10 = 50개 튜플 */ SELECT * FROM CUSTOMER c , ORDERS o ; -- 2. 고객과 고객의 주문에 관한 데이터를 모두 보이시오 SELECT * FROM CUSTOMER c , ORDERS o WHERE c.CUSTID = o.CUSTID; SELECT * FROM CUSTOMER c JOIN ORDERS o ON c.CUSTID = o.CUSTID; -- 3. 고객과 고객의 주문에 관한 데이터를 고객번호 순으로 정렬하여 보이시오 SELECT * FROM CUSTOMER c , ORDERS o WHERE c.CUSTID = o.CUSTID ORDER BY c.CUSTID; -- 4. 고객의 이름과 고객이 주문한 도서의 판매가격을 보이시오 SELECT c.NAME , o.SELLPRICE FROM CUSTOMER c , ORDERS o WHERE c.CUSTID = o.CUSTID; -- 5. 고객별로 주문한 모든 도서의 총 판매액을 구하고, 고객별로 정렬하시오 SELECT NAME "고객", SUM(o.SELLPRICE) "총 판매액" FROM CUSTOMER c , ORDERS o WHERE c.CUSTID = o.CUSTID GROUP BY c.NAME ORDER BY c.NAME; -- 6. 고객의 이름과 고객이 주문한 도서의 이름을 구하시오. SELECT name "이름", bookname "도서" FROM CUSTOMER c , ORDERS o , BOOK b WHERE c.CUSTID = o.CUSTID AND o.BOOKID = b.BOOKID; SELECT c.name "이름", b.bookname "도서" FROM ORDERS o JOIN CUSTOMER c ON o.CUSTID = c.CUSTID JOIN BOOK b ON o.BOOKID = b.BOOKID; -- 7. 가격이 20,000원인 도서를 주문한 고객의 이름과 도서의 이름을 구하시오 SELECT name "이름", bookname "도서" FROM CUSTOMER c , ORDERS o , BOOK b WHERE c.CUSTID = o.CUSTID AND o.BOOKID = b.BOOKID AND o.SELLPRICE = 20000; -- 8. 도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 판매가격을 구하시오 SELECT name, sellprice FROM CUSTOMER c LEFT OUTER JOIN ORDERS o ON c.CUSTID = o.CUSTID;/* * 1) 조인 * - 2개 이상의 테이블에 정보 중 사용자가 필요한 집합에 맞게 가상의 테이블처럼 만들어서 결과를 보여주는 것 * * 2) 조인의 종류 * - INNER 조인 : 특정 컬럼을 기준으로 정확히 매칭된 집합을 출력함 * INNER 조인은 대표적인 조인의 종류임 * - OUTER 조인 : 특정 컬럼을 기준으로 정확히 매칭된 집합을 출력하지만 * 한 쪽의 집합은 모두 출력하고 다른 한 쪽의 집합은 매칭되는 컬럼의 값만 출력 */ DROP TABLE basket_a; CREATE TABLE basket_a( id NUMBER PRIMARY KEY, fruit varchar2(100) NOT null ); DROP TABLE basket_b; CREATE TABLE basket_b( id NUMBER PRIMARY KEY, fruit varchar2(100) NOT null ); SELECT * FROM BASKET_A ba; SELECT * FROM BASKET_B bb; INSERT INTO basket_A (id, fruit) VALUES (1, 'Apple'); INSERT INTO basket_A (id, fruit) VALUES (2, 'Orange'); INSERT INTO basket_A (id, fruit) VALUES (3, 'Banana'); INSERT INTO basket_A (id, fruit) VALUES (4, 'Cucumber'); commit; insert into basket_b (id, fruit) values (1, 'Orange'); insert into basket_b (id, fruit) values (2, 'Apple'); insert into basket_b (id, fruit) values (3, 'Watermelon'); insert into basket_b (id, fruit) values (4, 'Pear'); commit; -- Inner 조인 select a.id, a.fruit, b.id, b.fruit from basket_a a inner join basket_b b on a.fruit = b.fruit; -- Left Outer 조인 select ba.ID, ba.FRUIT, bb.ID, bb.FRUIT from basket_a ba LEFT OUTER JOIN BASKET_B bb ON ba.FRUIT = bb.FRUIT; -- Right Outer 조인 select ba.ID, ba.FRUIT, bb.ID, bb.FRUIT from basket_b bb LEFT OUTER JOIN BASKET_a ba ON ba.FRUIT = bb.FRUIT; -- Left Outer 조인 - Left Only select ba.ID, ba.FRUIT, bb.ID, bb.FRUIT from basket_a ba LEFT OUTER JOIN BASKET_B bb ON ba.FRUIT = bb.FRUIT WHERE bb.ID IS null;-- 데이터 탐색 -- a) 주문 테이블 SELECT * FROM "ONLINE_ORDER" oo; -- b) 상품 테이블 SELECT * FROM ITEM i; -- c) 카테고리 테이블 SELECT * FROM CATEGORY c; -- d) 유저 테이블 SELECT * FROM "USER_INFO" ui; -- 상품별 매출액 집계 후, 매출액 높은 순으로 정렬하시오 SELECT i.id, i.ITEM_NAME "상품명", SUM(gmv) "매출액" FROM "ONLINE_ORDER" o INNER JOIN ITEM i ON o.itemid = i.id GROUP BY i.id, i.ITEM_NAME ORDER BY SUM(gmv) DESC ; -- 카테고리별 매출액 집계 후, 매출액 높은 순으로 정렬하시오 SELECT C.cate1, sum(gmv) "GMV" FROM "ONLINE_ORDER" oo JOIN ITEM i ON oo.ITEMID = i.ID JOIN CATEGORY c ON i.CATEGORY_ID = c.ID GROUP BY c.CATE1 ORDER BY gmv DESC; SELECT c.CATE1 , c.CATE2 , c.CATE3, sum(gmv) "GMV" FROM "ONLINE_ORDER" oo JOIN ITEM i ON oo.ITEMID = i.ID JOIN CATEGORY c ON i.CATEGORY_ID = c.ID GROUP BY c.CATE1 , c.CATE2 , c.CATE3 ORDER BY gmv DESC; -- 구매고객의 성연령별 매출액 높은 순으로 정렬하시오 SELECT ui.GENDER, ui.AGE_BAND, SUM(gmv) "GMV" FROM "ONLINE_ORDER" oo JOIN USER_INFO ui ON oo.USERID = ui.USERID GROUP BY ui.GENDER, ui.AGE_BAND ORDER BY gmv DESC;-- 일부 매출액이 누락된 게 있음 SELECT ui.GENDER , ui.AGE_BAND , sum(GMV) GMV FROM ONLINE_ORDER oo INNER JOIN USER_INFO ui ON oo.USERID = ui.USERID GROUP BY ui.GENDER , ui.AGE_BAND ORDER BY 1, 2; -- 매출액이 누락되지 않게 수정 SELECT ui.GENDER , ui.AGE_BAND , sum(GMV) GMV FROM ONLINE_ORDER oo LEFT JOIN USER_INFO ui ON oo.USERID = ui.USERID GROUP BY ui.GENDER , ui.AGE_BAND ORDER BY 1, 2; SELECT * FROM ONLINE_ORDER oo JOIN ITEM i ON oo.ITEMID = i.ID LEFT JOIN CATEGORY c ON i.CATEGORY_ID = c.ID WHERE c.CATE1 = '셔츠'; SELECT * FROM ONLINE_ORDER oo LEFT JOIN USER_INFO ui ON oo.USERID = ui.USERID;