[DB][SQL][국비교육] Day 24

Ga02·2023년 1월 31일

국비교육

목록 보기

23/82

🔍 조인, JOIN

💡 제일 중요 💡
두개 이상의 테이블을 한꺼번에 SELECT(조회)하는 방법

조인하려는 테이블들의 모든 조합(모든 행 + 모든 컬럼의 조합)을 조회 👉🏻 행들의 곱만큼, 컬럼들의 합만큼의 결과 집합이 생성

emp TB - 15rows 8 cols
dept TB - 4rows 3clos

SELECT * FROM emp, dept;
➡ 60rows(15x4) 11cols(8+3)

의미있는 데이터를 추출하기 위한 조인조건을 설정 👉🏻 Primary Key와 Foreign Key를 이용하여 조인조건으로 설정하는 경우가 많음

--공통컬럼인 deptno를 조인조건으로
SELECT empno, ename, E.deptno, dname  
FROM emp E, dept D
WHERE E.deptno = D.deptno;

➰ 데이터 무결성, Data Integrity !!각 테이블의 제약조건 보기

프로그램 실행 중 데이터가 정확하게 일관성있게 유지되는 성격

Primary Key, PK, 주키, 기본키
테이블 내 각 행(데이터)을 서로 구분하기 위한 식별데이터로 적용된 컬럼
emp ➡ empno / dept ➡ deptno
Foreign Key, FK, 외래키, 참조키
다른 테이블의 컬럼을 참조하고 있는 컬럼 👉🏻 참조 대상은 참조테이블의 Primary Key
dept.deptno(PK) 👉🏻참조👉🏻 emp.deptno(FK)
기본키 무결성 원칙
- 기본키 컬럼은 테이블에서 고유한 값(중복데이터 x)을 가져야함 👉🏻 UNIQUE
- 기본키 컬럼은 NULL값을 가질 수 없음 👉🏻 NOT NULL
  ➡ emp.empno / dept.deptno
참조 무결성 원칙
- 외래키컬럼(FK)은 참조 대상 테이블(dept)의 기본키 컬럼(deptno)에 존재하는 값만 사용할 수 있음 👉🏻 NULL값 가능
  ➡ emp.deptno(FK)는 dept.deptno(dept의 PF)에 존재하는 값만 가능

-- emp 테이블에 empno, deptno값만 삽입
INSERT INTO emp(empno, deptno)  
VALUES ( 9999, 80 );
👉🏻 dept.deptno에서 80을 찾을 수 없으므로 emp.deptno에 추가할 수 없음 / parent key not found

🔍 JOIN 문법 종류

ANSI 표준 문법
ANSI 기구가 표준으로 지정하여 대부분의 DBMS에서 사용할 수 있는 표준적인 방법
오라클 전용 문법
오라클 DB에서만 사용할 수 있는 조인 문법

➰ EQUI JOIN, 등가 조인 / 오라클 JOIN

FROM절 : join대상테이블들을 ,(콤마)로 나열 👉🏻 공통으로 가지는 컬럼으로 조인
WHERE절 : equal(=, 등가)비교하는 조인조건을 넣음

SELECT empno, ename, E.deptno, dname
FROM emp E, dept D
WHERE E.deptno = D.deptno   --조인조건
    AND empno < 7800		--일반조회조건
ORDER BY empno;

➰ INNER JOIN, 내부 조인 / ANSI 표준 구문

조인조건에 따라 EQUI JOIN과 같은 결과를 얻을 수 있음

FROM절 : 테이블을 하나만 적음 (➡ 오라클 등가조인과의 차이점)
INNER JOIN절 : 조인할 테이블 👉🏻 INNER 생략 가능
ON절 : 조인조건 / USING : ON대신 사용 가능
WHERE절 : 일반 조회조건

SELECT empno, ename, E.deptno, dname
FROM emp E
INNER JOIN dept D
ON E.deptno = D.deptno   --조인조건
WHERE empno < 7800        --일반조회조건
ORDER BY empno;

SELECT empno, ename, E.deptno, dname
FROM emp E
JOIN dept D
USING E.deptno = D.deptno   --조인조건
WHERE empno < 7800        --일반조회조건
ORDER BY empno;

➰ NON-EQUI JOIN, 비등가 조인

EUQI JOIN과 문법은 같지만 조인 조건의 비교연산자가 equal(=)이 아닌 조인

<오라클 ver.>
SELECT empno, ename, sal, grade
FROM emp, salgrade
WHERE sal BETWEEN losal AND hisal
ORDER BY grade, sal, empno;


<ANSI ver.>
SELECT empno, ename, sal, grade
FROM emp
INNER JOIN salgrade
ON sal BETWEEN losal AND hisal
ORDER BY grade, sal, empno;

➰ SELF JOIN, 자체 조인

하나의 테이블이 자기 자신 테이블과 조인하는 것

--  Alias만 다른 동일한 테이블 두개
SELECT * FROM emp EMPLOYEE;
SELECT * FROM emp MANAGER;



<오라클 ver.> 
SELECT E.empno, E.ename
    , M.ename manager
FROM emp E, emp M
WHERE E.mgr = M.empno
ORDER BY empno;


<ANSI ver.>
SELECT E.empno, E.ename
    , M.ename manager
FROM emp E
INNER JOIN emp M
ON E.mgr = M.empno
ORDER BY empno;

➰ CROSS JOIN, 상호 조인

두개 이상의 테이블의 조합으로 조회가능한 모든 경우의 수 조회 👉🏻 카테시안 곱을 얻을 때 사용하는 조인 구문
카테시안 곱, Cartesian Product : 데이터 집합 간 모든 조합 ➡ 조인조건을 붙이지 않은 조인과 같은 결과

<오라클 ver.>
SELECT * FROM emp, dept;


<ANSI ver.>
SELECT * FROM emp CROSS JOIN dept;

➰ NATURAL JOIN, 자연 조인

조인조건을 설정하지 않아도 EQUI JOIN(등가조인)으로 자동 조인을 수행

조인조건이 되는 컬럼을 자동으로 판단함 👉🏻 조인컬럼을 조회 결과의 첫 컬럼으로
개발자가 조인 조건의 컬럼을 마음대로 제어하는데 어려움이 있음

SELECT * FROM emp
NATURAL JOIN dept;

➰ OUTER JOIN, 외부 조인

조인 조건에 사용한 컬럼의 값이 한 쪽 테이블에만 존재할 경우 사용하는 조인 구문

조인 조건으로 비교할 수 없는 데이터를 '외부' 데이터로 인식, 이 외부데이터를 포함하여 조인하는 것을 외부 조인이라 함
오라클 전용구문에서는 조인 조건에 (+)연산자를 추가해서 적용 👉🏻 테이블을 조인했을 때 데이터가 부족한 쪽 컬럼에 외부조인 연산자를 붙여서 조인 결과에 NULL값이 추가되도록 설정
FULL OUTER JOIN 👉🏻 ANSI 표준 구문으로만 제공됨
조인테이블 양쪽에 모두 NULL값을 추가해서 외부 데이터를 보여줘야할 때 사용

▶ 오라클 외부 조인 👉🏻 ANSI외부 조인으로 변환

FROM절의 테이블 나열 순서를 조인 조건의 컬럼순서와 같은 순서로 둬야 함

조인 조건의 왼쪽 컬럼에 (+)연산자가 붙어있으면 RIGHT OUTER JOIN으로 변환
조인 조건의 오른쪽 컬럼에 (+)연산자가 붙어있으면 LEFT OUTER JOIN으로 변환

--  회사의 부서번호
SELECT deptno FROM dept;
--  사원들의 부서번호
SELECT DISTINCT deptno FROM emp;    --현재 40번 부서 없음


<오라클 ver.>
SELECT empno, ename, D.deptno, dname
FROM emp E, dept D
WHERE E.deptno(+) = D.deptno
ORDER BY deptno, empno;

<RIGHT OUTER JOIN ANSI ver.> 
SELECT empno, ename, D.deptno, dname
FROM emp E
RIGHT OUTER JOIN dept D
ON E.deptno = D.deptno
ORDER BY deptno, empno;


<오라클 ver.>
SELECT empno, ename, D.deptno, dname
FROM emp E, dept D
WHERE E.deptno = D.deptno(+)
ORDER BY deptno, empno;

<LEFT OUTER JOIN ANSI ver.>
SELECT empno, ename, D.deptno, dname
FROM emp E
LEFT OUTER JOIN dept D
ON E.deptno = D.deptno
ORDER BY deptno, empno;


<FULL OUTER JOIN ANSI ver.>
SELECT empno, ename, D.deptno, dname
FROM emp E
FULL OUTER JOIN dept D
ON E.deptno = D.deptno
ORDER BY deptno, empno;

🔍 서브쿼리, Subquery

SQL문장에 포함된 또 다른 SQL(구문쿼리 안에 쿼리를 중첩)
👉🏻 메인쿼리(외부쿼리)와 서브쿼리(내부쿼리)로 지칭

➰ 적용하는 위치에 따른 서브쿼리 종류

서브쿼리 : WHERE절의 조건으로 사용되는 서브쿼리

--  KING의 부서번호 조회
SELECT deptno FROM emp
WHERE ename = 'KING';

--  10번 부서의 정보조회
SELECT deptno FROM emp
WHERE deptno = 10;

--  KING과 부서번호가 같은 부서
SELECT deptno FROM emp
WHERE deptno = (
    SELECT deptno FROM emp
    WHERE ename = 'KING'
);

스칼라 서브쿼리 : SELECT절의 컬럼으로 사용되는 서브쿼리
- 단일 행 또는 단일 열 서브쿼리로 작성
- 상관쿼리, 상호연관 서브쿼리 : 메인쿼리와 서브쿼리의 관계를 이용하여 조회 👉🏻 상호 연관을 가지도록 조건을 부여

SELECT empno, ename, deptno
    , (SELECT dname FROM dept D WHERE E.deptno = D.deptno) dname
    , (SELECT loc FROM dept D WHERE E.deptno = D.deptno) loc
FROM emp E;

인라인 뷰 : FROM절의 조회테이블 대신 사용되는 서브쿼리

--  테이블 조회
SELECT * FROM emp;

--  empno과 ename만 조회
SELECT empno eno, ename FROM emp;

--  서브쿼리 조회(인라인 뷰)
SELECT * FROM (
    SELECT empno eno, ename FROM emp
);

➰ 행 결과 집합 형태에 따른 서브쿼리의 종류

단일 행 서브쿼리 : 결과집합이 하나의 행으로 나오는 서브쿼리
다중 행 서브쿼리 : 결과집합이 여러 행으로 나오는 서브쿼리
👉🏻 단일 행, 다중 행 서브쿼리는 WHERE절에서 사용할 수 있는 연산자가 다름
다중 열 서브쿼리 : 결과집합이 여러 컬럼(열)으로 나오는 서브쿼리

➰ WITH절

메인쿼리에서 인라인뷰를 분리시켜 따로 작성하고 Alias를 적용, 메인쿼리에서 별칭을 사용
👉🏻 인라인뷰때문에 복잡해진 구문을 단순화시켜 작성할 수 있게 도와줌
✔ WITH절과 SELECT구문을 같이 실행해야 적용됨

WITH E AS (
    SELECT deptno, round(avg(sal), 2) avg
    FROM emp
    GROUP BY deptno
    HAVING avg(sal) > (SELECT avg(sal) FROM emp
    )
)

SELECT E.deptno, avg, dname, loc  
--FROM  E, dept D
--WHERE E.deptno = D.deptno
FROM E INNER JOIN dept D ON E.deptno = D.deptno
ORDER BY deptno;