테이블 조인
-
관계형 데이터베이스의 주요 연산중 하나
-
다수개의 테이블에 분산된 자료를 테이블 간에 설정된 관계를 이용하여 참조하는 연산
-
구분
. 일반조인 , ANSI 조인
. 내부조인 -조인조건을 만족하는 애들만, 외부조인-가짓수가 더많은 애들만
. 동등조인=, 세미조인, 안티조인, 카타시안조인 등- 일반조인과 ANSI 조인
- 일반조인은 DBMS 회사별로 제공되는 조인 형식
- ANSI조인: 미국표준위원회에서 제정한 (89년 기준안) 조인문 형식
- 내부조인
1) 동등조인(Equi-JOIN)
- 조인조건에 '=' 연산자가 사용된 조인 - 가장 일반적인 조인 형식
(사용형식:일반조인)
SELECT 컬럼list
FROM 테이블명1 [별칭1], 테이블명2 [별칭2] [,테이블명3 [별칭3],...]
WHERE 조인조건
[AND 일반조건]
_ . '조인조건' : 사용된 테이블들 간 동일한 데이터를 가진 컬럼명을 '='연산자로 연결함
. 조인조건의 갯수는 적어도 테이블의 수-1개 이상이어야함
. '일반조건' 과 '조인조건'의 기술순서는 수행순서와 관계없음._
(사용형식:ASNI 조인)
SELECT 컬럼list
FROM 테이블명1 [별칭1]
INNER JOIN 테이블명2 [별칭2] ON(조인조건1 [AND 일반조건1])
--1테이블과 2테이블은 이너조인수행 = 공통조인 필수로 가지고 있어야함.
[INNER JOIN 테이블명3 [별칭3] ON(조인조건2 [AND 일반조건2])]
-- 테이블1,2의 조인결과와 테이블3이 조인되어짐
:
[WHERE 일반조건n]
. '테이블명1'과 '테이블명2'는 반드시 직접 조인 가능해야함
. '테이블명3'은 '테이블명1', '테이블명2' 조인 결과와 조인
. '일반조건1'은 '테이블명1', '테이블명2'에만 관련된 조건
. '일반조건n'은 모든 테이블에 공통적으로 적용되는 조건사용예) 사원테이블에서 2005년 이후 입사한 사원들을 조회하시오
Alias 는 사원번호, 사원명, 부서명, 입사일이다.
(일반조인)SELECT A.EMPLOYEE_ID AS 사원번호,
A.EMP_NAME AS 사원명,
B.DEPARTMENT_NAME AS 부서명,
A.HIRE_DATE AS 입사일
FROM HR.EMP A, HR.DEPT B
WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
AND EXTRACT(YEAR FROM A.HIRE_DATE) >=2005 --일반조건
ORDER BY 3; (ANSI조인) SELECT A.EMPLOYEE_ID AS 사원번호,
A.EMP_NAME AS 사원명,
B.DEPARTMENT_NAME AS 부서명,
A.HIRE_DATE AS 입사일
FROM HR.EMP A
INNER JOIN HR.DEPT B ON(A.DEPARTMENT_ID=B.DEPARTMENT_ID
AND EXTRACT(YEAR FROM A.HIRE_DATE) >=2005)
ORDER BY 3;사용예) 미국내에 있는 부서별 인원수를 조회하시오 --COUNTRY_ID가 US
Alias는 부서코드, 부서명, 인원수
(일반조인) SELECT B.DEPARTMENT_ID AS 부서코드, --A로하면?
A.DEPARTMENT_NAME AS 부서명,
COUNT(*) AS 인원수
FROM HR.DEPT A, HR.EMP B, HR.LOCATIONS C
WHERE B.DEPARTMENT_ID=A.DEPARTMENT_ID
AND A.LOCATION_ID=C.LOCATION_ID
AND C.COUNTRY_ID='US'
GROUP BY B.DEPARTMENT_ID, A.DEPARTMENT_NAME
ORDER BY 1; (ANSI조인)SELECT B.DEPARTMENT_ID AS 부서코드,
A.DEPARTMENT_NAME AS 부서명,
COUNT(*) AS 인원수
FROM HR.DEPT A
INNER JOIN HR.EMP B ON(A.DEPARTMENT_ID=B.DEPARTMENT_ID )
INNER JOIN HR.LOCATIONS C ON(A.LOCATION_ID=C.LOCATION_ID)
WHERE C.COUNTRY_ID='US'
GROUP BY B.DEPARTMENT_ID, A.DEPARTMENT_NAME
ORDER BY 1;사용예) 2005년도 5월 거래처별 매입현황을 조회하시오
Alias는 거래처코드, 거래처명, 매입수량, 매입금액이다.
SELECT C.BUYER_ID AS 거래처코드,
C.BUYER_NAME AS 거래처명,
SUM(A.BUY_QTY) AS 매입수량,
SUM(A.BUY_QTY*B.PROD_COST) AS 매입금액
FROM BUYPROD A, PROD B, BUYER C
WHERE A.BUY_PROD=B.PROD_ID
AND B.PROD_BUYER=C.BUYER_ID
AND A.BUY_DATE BETWEEN TO_DATE('20050501') AND TO_DATE('20050531')
GROUP BY C.BUYER_ID, C.BUYER_NAME
ORDER BY 1;
(ANSI조인)
SELECT C.BUYER_ID AS 거래처코드,
C.BUYER_NAME AS 거래처명,
SUM(A.BUY_QTY) AS 매입수량,
SUM(A.BUY_QTY*B.PROD_COST) AS 매입금액
FROM BUYPROD A
INNER JOIN PROD B ON( A.BUY_PROD=B.PROD_ID)
INNER JOIN BUYER C ON(B.PROD_BUYER=C.BUYER_ID)
WHERE A.BUY_DATE BETWEEN TO_DATE('20050501') AND TO_DATE('20050531')
GROUP BY C.BUYER_ID, C.BUYER_NAME
ORDER BY 1;** 재고수불테이블을 생성하시오
CREATE TABLE REMAIN(
REMAIN_YEAR CHAR(4) NOT NULL,
--기본키는 기본적으로 NOT NULL 때문에 NOT NULL 안써도됨
PROD_ID VARCHAR2(10),
REMAIN_J_00 NUMBER(5) DEFAULT 0 ,
REMAIN_I NUMBER(5)DEFAULT 0 ,
REMAIN_O NUMBER(5) DEFAULT 0 ,
REMAIN_J_99 NUMBER(5) DEFAULT 0 ,
REMAIN_DATE DATE ,CONSTRAINT pk_remain PRIMARY KEY(REMAIN_YEAR,PROD_ID),
CONSTRAINT fk_remain_prod FOREIGN KEY(PROD_ID)
REFERENCES PROD(PROD_ID));
** 생성된 재고수불테이블에 다음 자료를 입력하시오
1)년도: 2005
2)상품코드: PROD테이블의 상품코드
3)기초재고수량: PROD테이블의 적정재고(PROD_PROPERSTOCK)
4)기말재고: 기초재고
5)갱신일자: 2005년 1월 1일
INSERT INTO REMAIN(REMAIN_YEAR,PROD_ID,REMAIN_J_00,REMAIN_J_99,REMAIN_DATE)
SELECT '2005', PROD_ID, PROD_PROPERSTOCK, PROD_PROPERSTOCK, TO_DATE('20050101')
FROM PROD;
-- 서브쿼리 쓰는 INSERT절은 VALUES 생략 SELECT * FROM REMAIN;
UPDATE REMAIN SET REMAIN_J_00=0, REMAIN_J_99=0, REMAIN_I=0, REMAIN_O=0;
COMMIT;
사용예) 2005년도 1~4월 제품별 매입현황을 조회하시오
Alias는 상품코드, 매입수량이다.
SELECT BUY_PROD AS BID,
SUM(BUY_QTY) AS CNT
FROM BUYPROD
WHERE BUY_DATE BETWEEN TO_DATE('20050101') AND TO_DATE('20050331')
GROUP BY BUY_PROD;사용예)위 예에서 조회된 자료를 이용하여 재고수불테이블을 갱신하시오
UPDATE REMAIN A
SET (A.REMAIN_I, A.REMAIN_J_99,A.REMAIN_DATE) =
(SELECT B.CNT, B.CNT, TO_DATE('20050430')
FROM (SELECT BUY_PROD AS BID,
SUM(BUY_QTY) AS CNT
FROM BUYPROD
WHERE BUY_DATE BETWEEN TO_DATE('20050101') AND TO_DATE('20050430')
GROUP BY BUY_PROD) B
WHERE A.PROD_ID=B.BID)
WHERE REMAIN_YEAR ='2005'
AND PROD_ID IN(SELECT DISTINCT BUY_PROD
FROM BUYPROD
WHERE BUY_DATE BETWEEN TO_DATE('20050101') AND TO_DATE('20050430'));
SELECT * FROM REMAIN;
COMMIT;
사용예) 2005년도 4월 제품별 매출현황을 조회하시오
Alias 상품코드, 매출수량
SELECT CART_PROD AS CID,
SUM(CART_QTY) AS CMT
FROM CART
WHERE CART_NO LIKE '200504%'
GROUP BY CART_PROD;
(재고수불테이블 UPDATE)
UPDATE REMAIN A
SET (A.REMAIN_O, A.REMAIN_J_99,A.REMAIN_DATE) =
(SELECT A.REMAIN_O+B.CMT, A.REMAIN_J_99-B.CMT, TO_DATE('20050430')
FROM (SELECT CART_PROD AS CID,
SUM(CART_QTY) AS CMT
FROM CART
WHERE CART_NO LIKE '200504%'
GROUP BY CART_PROD) B
WHERE A.PROD_ID=B.CID)
WHERE REMAIN_YEAR ='2005'
AND PROD_ID IN(SELECT DISTINCT CART_PROD
FROM CART
WHERE CART_NO LIKE '200504%');
SELECT * FROM REMAIN;
COMMIT;
삐약..뺙뺙