테이블 JOIN

서현서현·2022년 2월 21일
0

DB, SQL

목록 보기
15/27
post-thumbnail

📌 테이블 조인

  • 관계형 db의 핵심연산
  • 두개 이상의 테이블에서 공통의 컬럼(외래키, 관계:Relationship)을 이용하여 연산 수행
  • 필요한 자료가 여러 테이블에 분산저장 되어있는 경우 사용
  • 사용된 테이블의 수가 N개일때 조인 조건이 N-1개 이상이어야 함
  • 분류
    • 내부조인 (inner join) vs 외부조인(outer join)
    • 일반조인 vs ANSI 조인
    • 동등조인 vs 비동등조인
    • 셀프조인
    • Cartisian product ..etc
(일반 조인문 형식)
SELECT 컬럼LIST
FROM 테이블명1 [별칭1], 테이블명2 [별칭2] [,테이블명3 [별칭3],...]
WHERE [별칭1|테이블명1.]컬럼명 관계연산자 [별칭2|테이블명2.]컬럼명
[AND [별칭2|테이블명2.]컬럼명] 관계연산자 [별칭3|테이블명3.]컬럼명
[AND 일반조건];

📌 1) Cartesion Product

  • 조인조건이 없거나 조인조건을 잘못 기술한 경우
  • 모든 가능한 랭들의 조합을 결과로 반환
  • 최악의 경우 (조인조건이 없는 경우) M행 N열로 구성됨 A테이블과 S행 R열로 구성된 B테이블이 카타시안 곱을 수행하면 결과는 M*S 행 N+R 열이 됨
  • 반드시 필요한 경우가 아니면 수행하지 말아야함
  • ANSI JOIN의 CROSS JOIN에 해당
(일반 Cartesion Product 사용형식)
SELECT 컬럼LIST
FROM 테이블명1 [별칭1], 테이블명2 [별칭2] [,테이블명3 [별칭3],...];

(ANSI Cartesion Product 사용형식)
SELECT 컬럼LIST
FROM 테이블명1 [별칭1]
CROSS JOIN 테이블명2 [별칭2]
[CROSS JOIN 테이블명3 [별칭3],...];

(EX1)

SELECT COUNT(*)
FROM CART, PROD;
SELECT 207*74 FROM DUAL
// CART테이블이 207행 PROD테이블이 74열.
// 1300얼마개의 행이 나옴.

FROM절에 테이블 두개 이상이면 JOIN

// ANSI CROSS JOIN 사용
SELECT *
FROM CART
CROSS JOIN PROD;
SELECT COUNT(*)
FROM CART, PROD, BUYPROD;
// 2267064개의 행이 선택된다.
// ANSI CROSS JOIN 사용
SELECT COUNT*
FROM CART
CROSS JOIN PROD
CROSS JOIN BUYPROD;

📌 2. EQUI JOIN

  • 조인조건에 ‘=’연산자가 사용된 조인문
  • 대부분의 JOIN이 동등조인
  • ANSI에서는 INNER JOIN에 해당
(ANSI INNERJOIN 사용형식)
SELECT 컬럼LIST
FROM 테이블명1[별칭1]
INNER JOIN 테이블명2[별칭] ON(조인조건 [ AND 일반조건])
INNER JOIN 테이블명3[별칭] ON(조인조건 [ AND 일반조건])
							.
							.
[WHERE 일반조건]
- '테이블명1'괴 '테이블명2'가 '조인조건1'로 JOIN되면
- '테이블명3'은 '테이블명1'과 '테이블명2'의 조건으로 조잉걍경과와 조인됨
- '일반조건1'은 '테이블명2'에 관련된 조건이며,
- '일반조건2'는 '테이블3'에 관련된 조건
= 'WHERE 일반조건n'은 모든 테이블이공통인 조건 기술

(EX1) 사원테이블에서 부서번호 60,90,100에 속한 사원들의 사원번호, 사원명, 부서명, 입사일을 조회하시오

SELECT EMPLOYEE_ID AS 사원번호, 
			 EMP_NAME AS 사원명, 
			 DEPARTMENT_NAME AS 부서명, 
			 HIRE_DATE AS 입사일
FROM HR.EMPLOYEES, HR.DEPARTMENTS
WHERE DEPARTMENT_ID =DEPARTMENT_ID
	AND DEPARTMENT_ID IN (60,90,100);

ORA-00918: column ambiguously defined
00918. 00000 - "column ambiguously defined"

모호한 이름때문에 오류

테이블 별칭이 필요

SELECT A.EMPLOYEE_ID AS 사원번호, 
			 A.EMP_NAME AS 사원명, 
			 B.DEPARTMENT_NAME AS 부서명, 
			 A. HIRE_DATE AS 입사일
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
WHERE A. DEPARTMENT_ID = B. DEPARTMENT_ID -- 조인조건
	AND A.DEPARTMENT_ID IN (60,90,100); -- 일반조건

(ANSI INNER JOIN FORMAT)

SELECT A.EMPLOYEE_ID AS 사원번호, 
			 A.EMP_NAME AS 사원명, 
			 B.DEPARTMENT_NAME AS 부서명, 
			 A. HIRE_DATE AS 입사일
FROM HR.EMPLOYEES A
INNER JOIN HR.DEPARTMENTS B ON (A. DEPARTMENT_ID = B. DEPARTMENT_ID -- 조인조건
	AND A.DEPARTMENT_ID IN (60,90,100));

(EX2) 2005년 6월 회원별 매출집계를 조회하시오

Alias 는 회원번호, 회원명, 구매금액합계

SELECT A.CART_MEMBER AS 회원번호, 
        B.MEM_NAME AS 회원명, 
        SUM(C.PROD_PRICE*A.CART_QTY) AS 구매금액합계
FROM CART A, MEMBER B, PROD C
WHERE A.CART_NO LIKE '200506%' -- 일반조건
AND A.CART_MEMBER = B.MEM_ID -- 조인조건
AND C.CART_PROD = C.PROD_ID -- 조인조건
GROUP BY A.CART_MEMBER,B.MEM_NAME;

외부조인이였다면 많은쪽 기준이므로 CART가 아니라 MEMBER테이블을 봤어야함 (6월 구매자 기준이므로 MEMBER테이블에 버리는 데이터가 더 많으므로 더 큼)

SELECT A.CART_MEMBER AS 회원번호, 
        B.MEM_NAME AS 회원명, 
        SUM(C.PROD_PRICE*A.CART_QTY) AS 구매금액합계
FROM MEMBER B
INNER JOIN CART A ON (A.CART_MEMBER=B.MEM_ID)
INNER JOIN PROD C ON(A.CART_PROD = C.PROD_ID AND A.CART_NO LIKE '200506%')
GROUP BY A.CART_MEMBER, B.MEM_NAME;

(EX3) 사원테이블에서 미국 의외의 지역에 위치한 부서에서 근무하는 직원수를 조회하시오

Alias는 부서코드, 부서명, 인원수

SELECT A.DEPARTMENT_ID AS 부서코드, 
				B. DEPARTMENT_NAME AS 부서명, 
				COUNT(*) AS 인원수
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B, HR.LOCATIONS C
WHERE C.COUNTRY_ID !='US'
AND A.DEPARTMENT_ID=B.DEPARTMENT_ID
AND B.LOCATION_ID = C.LOCATION_ID
GROUP BY A.DEPARTMENT_ID, B.DEPARTMENT_NAME
ORDER BY 1;

(ANSI INNER JOIN FORMAT)

SELECT A.DEPARTMENT_ID AS 부서코드, 
        B. DEPARTMENT_NAME AS 부서명, 
        COUNT(*) AS 인원수
FROM HR.EMPLOYEES A
INNER JOIN HR.DEPARTMENTS B ON(A.DEPARTMENT_ID=B.DEPARTMENT_ID)
INNER JOIN HR.LOCATIONS C ON (B.LOCATION_ID = C.LOCATION_ID)
WHERE C.COUNTRY_ID != 'US'
GROUP BY A.DEPARTMENT_ID,B.DEPARTMENT_NAME
ORDERBY 1;

(EX4) 2005년도 거래처별 매입액 합계를 조회하시오

Alias 거래처코드, 거래처명, 매입금액합계

SELECT A.BUYER_ID AS 거래처코드, 
        A.BUYER_NAME AS 거래처명, 
        SUM(C.PROD_COST*B.BUY_QTY) AS 매입금액합계
FROM BUYER A, BUYPROD B, PROD C
WHERE B.BUY_PROD = C.PROD_ID
AND C.PROD_BUYER = A.BUYER_ID
AND EXTRACT(YEAR FROM BUY_DATE)=2005
GROUP BY A.BUYER_ID,A.BUYER_NAME
ORDER BY 1;

BUYER와 BUYPROD는 공통컬럼이 없으므로 모두 연관된 PROD테이블을 이용한다.

(ANSI FORMAT)

SELECT A.BUYER_ID AS 거래처코드, 
        A.BUYER_NAME AS 거래처명, 
        SUM(C.PROD_COST*B.BUY_QTY) AS 매입금액합계
FROM BUYER A // A와 직접 JOIN 가능한건 PROD니까 PROD를 먼저 이너조인
INNER JOIN PROD C ON (C.PROD_BUYER=A.BUYER_ID)
INNER JOIN BUYPROD B ON (B.BUY_PROD = C.PROD_ID AND
            EXTRACT (YEAR FROM BUY_DATE)=2005)
    GROUP BY A.BUYER_ID, A.BUYER_NAME
    ORDER BY 1;

(EX5) 2005년도 거래처별 매출집계를 조회하시오

Alias는 거래처코드,거래처명,매출합

SELECT A.BUYER_ID AS 거래처코드,
        A.BUYER_NAME AS 거래처명,
        SUM(B.CART_QTY*C.PROD_PRICE) AS 매출액합계
   FROM BUYER A, CART B, PROD C
  WHERE A.BUYER_ID = C.PROD_BUYER
    AND C.PROD_ID = B.CART_PROD
    AND B.CART_NO LIKE '2005%'
GROUP BY A.BUYER_ID,A.BUYER_NAME // SELECT절에서 집계함수빼고 그대로 가죠와
ORDER BY 1;

[과제]

  1. ANSI포맷으로 만들어보기

    SELECT A.BUYER_ID AS 거래처코드,
        A.BUYER_NAME AS 거래처명,
        SUM(B.CART_QTY*C.PROD_PRICE) AS 매출액합계
   FROM BUYER A
   INNER JOIN PROD C ON (A.BUYER_ID = C.PROD_BUYER)
   INNER JOIN CART B ON (C.PROD_ID = B.CART_PROD)
   AND B.CART_NO LIKE '2005%'
   GROUP BY A.BUYER_ID,A.BUYER_NAME
ORDER BY 1;

  2. 하나의 쿼리로 매출매입 한번에 구하기

    SELECT BUYER_ID AS 거래처코드, 
       BUYER_NAME AS 거래처명, 
       SUM(BUY_COST*BUY_QTY) AS 매입금액합계, 
       SUM(PROD_PRICE*CART_QTY)매출금액합계
FROM BUYER A, BUYPROD B, PROD C, CART D
WHERE B.BUY_PROD = C.PROD_ID
AND C.PROD_BUYER = A.BUYER_ID
AND C.PROD_ID = D.CART_PROD
AND EXTRACT(YEAR FROM BUY_DATE)=2005
GROUP BY A.BUYER_ID,A.BUYER_NAME
ORDER BY 1;
profile
서현서현
이전 포스트

집계함수

다음 포스트

서브쿼리

0개의 댓글