[오라클 SQL] 조인 (Join)

조인 JOIN

• INNER JOIN
  • EQUI JOIN
  • NATURAL JOIN
  • CROSS JOIN
  • SELF JOIN
  • NON-EQUI JOIN
• OUTER JOIN
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN

 

EQUI JOIN

• 컬럼 값들이 정확하게 일치하는 경우에 사용
• 명시적 표현으로 사용하는 것이 좋다.

-- 형식 1 (묵시적 표현)
SELECT [테이블명1.]컬럼명, [테이블명2.]컬럼명 ....
FROM 테이블명1, 테이블명2
WHERE 테이블명1.컬럼명 = 테이블명2.컬럼명  [AND 조건]

SELECT b.bCode, bName, bPrice, b.pNum, pName, sDate, s.cNum, cName, qty, bPrice*qty amt
FROM book b, pub p, dsale d, sale s, cus c
WHERE b.pNum = p.pNum AND b.bCode=d.bCode ANd d.sNum=s.sNum AND s.cNum=c.cNum


-- 형식 2 (명시적 표현)
SELECT [테이블명1.]컬럼명, [테이블명2.]컬럼명 ....
FROM 테이블명1
[ INNER ] JOIN 테이블명2 ON 테이블명1.컬럼명 = 테이블명2.컬럼명

SELECT b.bCode, bName, bPrice, b.pNum, pName, sDate, s.cNum, cName, 
			qty, bPrice*qty amt
FROM book b 
JOIN pub p ON b.pNum = p.pNum
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON s.cNum = c.cNum; 

-- 형식 3
-- 속도가 느리므로 잘 사용하지 않는다.
SELECT 컬럼명, 컬럼명
FROM 테이블명1
JOIN 테이블명2 USING (컬럼명1)
JOIN 테이블명3 USING (컬럼명2);

-- 판매된 책코드(bCode), 책이름(bName): 가장 많이 판매된 책 코드
 -- book(bCode, bName), dsale(bCode, qty)
SELECT bCode, bName FROM (
    SELECT b.bCODE, bName, RANK() OVER(ORDER BY SUM(qty) DESC) rank
    FROM book b
    JOIN dsale d ON b.bCode=d.bCode
    GROUP BY  b.bCODE, bName
)WHERE rank=1;


-- 판매된 책코드(bCode), 책이름(bName): 1~3등
SELECT bCode, bName FROM (
    SELECT b.bCODE, bName, RANK() OVER(ORDER BY SUM(qty) DESC) rank
    FROM book b
    JOIN dsale d ON b.bCode=d.bCode
    GROUP BY  b.bCODE, bName
)WHERE rank<=3;


-- 작년의 판매 현황
SELECT b.bCode, bName, bPrice, b.pNum, pName, sDate, s.cNum, cName, qty, bPrice*qty amt
FROM book b 
JOIN pub p ON b.pNum = p.pNum
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON s.cNum = c.cNum
WHERE TO_CHAR(sDate,'YYYY') = TO_CHAR(SYSDATE,'YYYY')-1;
-- WHERE TO_CHAR(sDate,'YYYY') = TO_CHAR(SYSDATE-(INTERVAL '1' YEAR),'YYYY');


-- 이전달 고객번호, 고객명, 년도, 판매금액합: 고객번호, 년도 오름차순
SELECT s.cNum 고객번호, c.cName 고객이름, TO_CHAR(sDate,'YYYY-MM')전달, SUM(bPrice*qty)판매금액합
FROM book b 
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON s.cNum = c.cNum
WHERE TO_CHAR(sDate,'YYYY-MM') = TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYY-MM')
GROUP BY s.cNum, cName, TO_CHAR(sDate,'YYYY-MM')
ORDER BY 고객번호;

-- INTERVAL 쓰면 안됨. 아래 예시 코드 참고

SELECT TO_DATE('20220320', 'YYYYMMDD') - (INTERVAL '1' MONTH) FROM dual;
        -- 20220320
-- SELECT TO_DATE('20220331', 'YYYYMMDD') - (INTERVAL '1' MONTH) FROM dual;
        -- 에러. 
SELECT ADD_MONTHS(TO_DATE('20220331', 'YYYYMMDD'), -1)FROM dual;
        -- 2/28 또는 2/29

 

 

NATURAL JOIN

• EQUI JOIN에서 동일한 속성의 중복 제거하여 한 번만 표기
• 테이블 별칭 주면 에러

-- 형식
SELECT 컬럼명, 컬럼명 ....
FROM 테이블명1
NATURAL JOIN  테이블명2

SELECT bCode, bName, bPrice, pNum, sDate, cNum, cName, qty, bPrice*qty amt 
FROM BOOK
NATURAL JOIN pub
NATURAL JOIN dsale
NATURAL JOIN sale
NATURAL JOIN cus;

 

CROSS JOIN

• 카티션 곱(Cartisian Product) 반환

SELECT p.pNum, pName, bCode, BNAME
FROM pub p
CROSS JOIN book b;

 

SELF JOIN

• 한 테이블에서 2개의 속성 연결하여 EQUI JOIN

-- 저자 테이블 
SELECT a1.bCode, a1.aName, a2.aName
FROM AUTHOR a1
JOIN AUTHOR a2 ON a1.bCode = a2.bCode
ORDER BY bCode;


-- 저자가 두 명 이상인 책 
SELECT bCode, BNAME
FROM BOOK
WHERE bCode IN (
SELECT a1.bCode
FROM AUTHOR a1
JOIN AUTHOR a2 ON a1.bCode = a2.bCode AND a1.ANAME > a2.ANAME
);

 

NON-EQUI JOIN

• 어떤 컬럼값도 일치하지 않을 때 사용
• '=' 이외의 연산자 사용
• 사용 빈도 매우 낮음

-- 형식
SELECT [테이블명1.]컬럼명, [테이블명2.]컬럼명 ....
FROM 테이블명1, 테이블명2..
WHERE (non-equi-join 조건)

-- EQUI JOIN
SELECT s.sNum, bCode, cNum, sDate, qty
FROM sale S
JOIN dsale d ON s.sNum = d.sNum;

-- NON-EQUI JOIN
SELECT s.sNum, bCode, cNum, sDate, qty
FROM sale S
JOIN dsale d ON s.sNum > 10;

 

 

---

OUTER JOIN

• 조인 조건 만족하지 않는 데이터를 처리하는 JOIN
• 어느 한 쪽의 데이터 모두 가져옴

 

LEFT OUTER JOIN

• 왼쪽에 표기된 데이터 기준으로 OUTER JOIN 수행
• 왼쪽의 테이블 결과 모두 가져온 후 오른쪽 테이블의 데이터 매칭
• 매칭되는 데이터 없는 경우 NULL 로 표시

-- 형식1
SELECT [테이블명1.]컬럼명, [테이블명2.]컬럼명
FROM 테이블명1, 테이블명2
WHERE 테이블명1.컬럼명=테이블명2.컬럼명(+);
-- 헷갈리므로 잘 사용하지 않음

-- 형식2 (명시적 표현)
SELECT [테이블명1.]컬럼명, [테이블명2.]컬럼명
 FROM 테이블명1
LEFT OUTER JOIN 테이블명2 ON 테이블명1.컬럼명=테이블명2.컬럼명;

SELECT b.bCode, bName, sNum, qty
FROM BOOK b
LEFT OUTER JOIN dsale d ON b.bCode = d.bCode;

 

RIGHT OUTER JOIN

• 오른쪽에 표기된 데이터 기준으로 OUTER JOIN 수행
• 오른쪽 테이블 결과 모두 가져온 후 왼쪽 테이블의 데이터 매칭
• 매칭되는 데이터 없는 경우 NULL 로 표시

-- 형식1
SELECT [테이블명1.]컬럼명, [테이블명2.]컬럼명
FROM 테이블명1, 테이블명2
WHERE 테이블명1.컬럼명(+)=테이블명2.컬럼명;
-- 헷갈리므로 잘 사용하지 않음

-- 형식2
SELECT [테이블명1.]컬럼명, [테이블명2.]컬럼명
FROM 테이블명1
RIGHT OUTER JOIN 테이블명2 ON 테이블명1.컬럼명=테이블명2.컬럼명;

-- RIGHT OUTER JOIN: 형식2. 
SELECT b.bCode, bName, sNum, qty
FROM dsale d
RIGHT OUTER JOIN book b ON d.bCode = b.bCode;


-- 한 권도 판매 되지 않은 책
-- book(bCode, bName), dsale(bCode, sNum, qty)
SELECT b.bCode, bName
FROM BOOK b
LEFT OUTER JOIN dsale d ON b.bCode = d.bCode
WHERE d.bCode IS NULL;

 

FULL OUTER JOIN

• 왼쪽, 오른쪽 테이블 모든 값 읽어 JOIN 수행
• LEFT, RIGHT OUTER JOIN 결과를 합집합 처리한 것과 동일
• UNION 기능과 같으므로 중복되는 데이터는 삭제 (UNION ALL X)

-- 형식
SELECT [테이블명1.]컬럼명, [테이블명2.]컬럼명
FROM 테이블명1 FULL OUTER JOIN 테이블명2 ON 테이블명1.컬럼명=테이블명2.컬럼명;

-- Cname이 NULL인 데이터 존재
SELECT sNum, sDate, s.cNum, m.cNum, cName, userId
FROM sale s
FULL OUTER JOIN member m ON s.cNum=m.cNum
FULL OUTER JOIN cus c ON c.cNum=s.cNum;

-- Cname이 NULL인 데이터 없음
SELECT sNum, sDate, s.cNum, m.cNum, cName, userId
FROM cus c
FULL OUTER JOIN member m ON c.cNum=m.cNum
FULL OUTER JOIN sale s ON c.cNum=s.cNum;