SQL - Join, 서브쿼리

 

🎯 SQL의 Join과 서브쿼리를 알아본다.

 

 

 

JOIN

검색하고자 하는 컬럼이 여러개의 테이블에 분산되어있는 경우 여러 테이블을 연결해서 필요한 데이터를 조회하는 방법

 

EMP테이블과 DEPT 테이블에는 DEPTNO 라는 공통컬럼이 존재한다.

여기서 EMP 테이블의 DEPTNO를 외래키라고 하고,  DEPT 테이블의 DEPTNO를 기본키라고 한다.

외래키(Foreign Key)는 그 자체로 정보를 갖고있는 건 아니고, 다른 테이블에 찾아가서 참조할 값을 말한다.

참조 당하는 dept 테이블을 부모테이블/master테이블 이라하고 참조하는 emp 테이블을 자식테이블/slave 테이블 이라고 한다.

마스터 테이블의 기본키 PK(D.DEPTNO)가 자식 테이블의 참조키,외래키FK(E.DEPTNO)가 된다.

 

 

join 종류

• 오라클 조인 (오라클 8i)
• ANSI 조인 (오라클 9i 이후)

 

 

 

오라클조인

오라클에서만 사용 가능. 여러 테이블을 연결하는 조인 조건을 where절에 명시한다.

 

 

카타시안 프로덕트Catasian Product

모든 가능한 행들의 조인. 조건을 생략할 경우 발생

일치하는 데이터만 반환하지 않고 조인하는 각 테이블의 행 개수를 서로 곱한 결과를 반환한다. 따라서 유효한 데이터로 사용되지 못한다.

WHERE 절에 조건 명시 안하고 그냥 SELECT ~ FROM 형태로만 쿼리문을 작성하면 결과로 곱집합이 형성된다.

 

 

Equi 조인 = 이퀄조인 이너조인

SELECT 검색할 컬럼명 명시
FROM 사용할 두개의 테이블 명 명시
WHERE 동등연산자(=) 사용해서 '조인조건' 명시

조건에서 = 사용하여 공통적으로 존재하는 컬럼의 값들이 정확하게 일치하는 행을 연결하여 데이터를 반환한다.

다른말로 단순조인, 내부조인(이너쪼인)이라고도 한다.

 

공통컬럼 사용 시 모호성을 피하기 위해서 중복된 컬럼은 조인한 테이블에서 어떤 걸 사용할지 골라줘야 된다. 

이걸 선택해주지 않으면 오류가 난다. 공통 컬럼이 어느 테이블에 속하는지 구분할 수 있게 명시적으로 테이블명을 적어주면 오류를 피할 수 있다.

 

SELECT ENAME, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

셀렉트 절에 사용된 컬럼은 양쪽 테이블에 모두 존재하는 컬럼이 아니다. 공통점 없음. 이때는 컬럼명 앞에 테이블명을 지정하지 않아도 된다.

FROM절에서도 테이블명에 별칭을 붙일 수 있다. 별칭을 지정했을 경우 WHERE절과 SELECT절엔 테이블명이 아닌 별칭을 사용해야 된다.

 

사원번호가 7900인 사람을 찾으라는 검색조건을 추가해줌

 

WHERE절 다음은 조인조건을 먼저 명시하고 AND나 OR 연산자를 사용해 검색조건을 명시한다.

 

부서별로 2005년 이전에 입사한 직원들의 인원 수 출력하기

 

 

Non-Equi조인

두 테이블 간의 공통컬럼이 없는 경우, where 절에 동등연산자 이외의 비교연산자를 사용하는 조인 방법.

SELECT ENAME, SAL, GRADE
FROM EMP E, SALGRADE S
WHERE SAL BETWEEN LOSAL AND HISAL;

예를들어 등급테이블을 이용한 방법(겹치는 컬럼 없음, 급여를 기준에 따라 5등급으로 구분한 테이블)이 있는데,

각 사원의 급여가 몇 등급인지 파악하려면 그 데이터가 일치하는지 비교문으로 작성할 수 있다. 여기서는 where 절에 비교문으로 between a and b를 사용했다. 이걸 부등호 연산자로 표시하면 sal ≥ losal and sal ≤ hisal; 로 바꿔쓸 수 있다.

3개의 테이블을 조인할 땐 이퀄조인과 논이퀄조인이 같이 쓰일 수 있다.

 

 

self조인

자기자신의 테이블을 참조. 테이블의 별칭을 사용하여 가상의 테이블을 생성한다.

SELECT E.ENAME, M.ENAME
FROM EMP E, EMP M
WHERE E.MGR = M.EMPNO;

사원의 관리자 번호로 관리자를 조회하고싶으면 계속 emp테이블을 뒤져야한다. 셀프 조인 필요

같은 테이블이 하나 더 존재하는 것처럼 생각할 수 있도록 반드시 테이블 별칭을 사용해야 한다.

 

 

Outer 조인

조인조건에 만족하지 못하는 행들도 전부 출력한다.

누락된 자료(NULL)가 있으면 누락된것까지 추가해서 보여준다

조인시킬 값이 없는 조인측에 (+)를 붙여준다. 테이블 중에 한번만 사용 가능.

 

조인의 실행결과는 반드시 조인 조건에 만족하는 데이터만 조회하며 조인조건에 위배되면 누락된다.

조건에 일치하는 데이터만 조회하는 조인을 INNER 조인이라 하고 위의 이퀄, 논이퀄, 셀프조인이 모두 이너조인이다.

 

 

사원과 담당 관리자의 이름을 누락된 부분까지 출력

 

 

부서 40번에 매칭된 사원정보는 null로 출력

 

EMP 테이블과 DEPT 테이블에서 DEPT 테이블에 있는 모든 자료를 사원번호, 이름, 업무, EMP 테이블의 부서번호, DEPT 테이블의 부서번호,부서명,근무지를 출력하기

 

 

사원명과 관리자명, 그리고 관리자의 관리자명을 출력하기

 

 

 

ansi조인

조인 형식이 FROM 절에서 지정된다.

조인조건을 WHERE절에 쓰지 않고 다른 방법을 통해 작성한다.

WHERE절은 검색조건을 지정할 때 사용.

 

 

cross 조인

SELECT EMPNO, ENAME, DNAME
FROM DEPT CROSS JOIN EMP;

오라클 조인의 카타시안이랑 동일한 조건 없는 조인 방법이다.

프롬에 조인할 테이블을 모두 나열하지 않고,

프롬 다음에 크로스 조인 절을 만들어준다.

 

 

natural 조인

SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1 NATURAL JOIN 테이블2
[WHERE 검색조건];

이퀄조인과 동일하다. 같은 이름을 가진 컬럼에 기반.

양쪽 테이블에 반드시 1개만의 공통컬럼명만 있어야한다. 2개 이상 있으면 엉뚱한 결과를 가져온다.

FROM 절에서 NATURAL JOIN 키워드를 사용해 조인 조건을 적어준다.

WHERE은 검색조건을 지정하는 경우에 사용한다.

이렇게 하면 조인 조건과 검색조건을 분리하여 가독성을 높일 수 있다.

 

SELECT EMPNO, ENAME, DEPTNO
FROM EMP NATURAL JOIN DEPT
WHERE DEPTNO = 30;

조인에 참여하는 테이블에 반드시 한개의 공통컬럼이 있어야 정확한 결과가 반환되며,

두 개 이상의 공통컬럼이 있다면 두 공통컬럼값 모두 만족해야해서 적은 결과가 출력된다.

공통컬럼 사용시 별칭 사용할 수 없다. 오라클이 자동으로 공통 컬럼을 찾아주기 때문에 별칭 사용 시 에러 발생.

 

 

using 절

SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1 [INNER] JOIN 테이블2 USING(공통컬럼)
[WHERE 검색조건];

natural join은 오라클이 공통컬럼을 자동으로 찾아주는데 using은 내가 직접 공통컬럼을 명시해주는 방법이다.

공통컬럼이 여러개인 경우에 내가 직접 명시적으로 어떤 컬럼을 기준으로 조인할 건지 지정한다. natural join과 같이 공통컬럼에는 별칭을 사용할 수 없다.

오라클 조인의 이퀄조인과 동일한 실행결과를 반환한다.

 

 

using을 사용해 공통컬럼을 명시한다

 

 

on 절

SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1 [INNER] JOIN 테이블2 ON 조인조건
[WHERE 검색조건];

non-equi 조인 또는 임의 조건으로 조인시 사용

조인할 컬럼을 명시하기 위해서 사용한다.

복잡한 조건의 조인이 가능하다. (서브쿼리, AND/OR연산자, EXIST, IN 연산자)

FROM에서 JOIN 키워드 앞뒤로 조인할 테이블명 지정하고 ON 절에서 조인조건을 명시한다

 

SELECT ENAME, DNAME, E.DEPTNO
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;

별칭 사용할 수 있고 ON절에 이퀄조건도 쓸수 있다. WHERE 절을 사용해 검색조건을 지정한다.

 

 

3개 테이블 조인하기 // 사원정보, 부서정보 및 월급 등급 조회

 

사원테이블과 부서 테이블을 ON 절 사용해 조인, 나중에 추가로 등급 테이블과 조인.

비교연산자 사용하는 논이퀄조인의 경우 ON절을 사용하면 된다.

 

 

SELECT E.ENAME 사원명, D.DNAME 부서명, S.GRADE 등급
FROM EMP E JOIN DEPT D
USING(DEPTNO)
           JOIN SALGRADE S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL;

공통컬럼에는 ON절 대신 USING 절 사용 가능.

SELECT EMPNO, ENAME, DNAME, SAL
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
AND SAL IN (800);

샐러리가 800인 사원의 정보 출력하기

 

 

OUTER 조인

LEFT / RIGHT / FULL

SELECT 테이블1.컬럼, 테이블2.컬럼
FORM 테이블1 LEFT|RHINGT|FULL OUTER JOIN 테이블2
ON 조인조건 | USING(컬럼)
[WHERE 검색조건];

좌측/우측에 기술한 테이블의 모든 행들을 조인조건 일치 여부와 상관없이 모두 출력할 수 있다.

FULL OUTER JOIN 은 LEFT JOIN과 RIGHT JOIN 의 결과를 합집합으로 처리한 결과와 동일하다.

 

SELECT E.ENAME 사원명, M.ENAME 관리자명
FROM EMP E LEFT OUTER JOIN EMP M
ON E.MGR = M.EMPNO;

OUTER JOIN을 사용해 사원들의 매니저 정보 출력하기

 

 

 

서브쿼리

하나의 SELECT 만으로 원하는 데이터를 조회할 수 없을때, SELECT 두번 날릴꺼 한꺼번에 해버리자!

select문 안에 또 하나의 select문을 중첩하는 것이 서브쿼리이며 바깥 쪽 SELECT문을 메인쿼리라고 부른다.

서브쿼리는 반드시 괄호 사용

메인쿼리에서 서브쿼리 컬럼을 사용할 수 없다

SELECT,FROM,WHERE,HAVING,ORDER BY,UPDATE,INSERT INTO절에도 사용될 수 있다.

서브쿼리에는 GROUP BY는 쓰여도 ORDER BY 는 사용불가

 

 

스콧과 같은 부서 사람들 찾기

 

1. EMP테이블에서 스콧의 부서번호를 찾는다 (서브쿼리)

2. EMP 테이블에서 1번에서 찾은 부서번호를 가진 사람의 이름을 출력한다. (메인쿼리)

 

조회된 서브쿼리의 결과값이 단일행인지 복수행인지에 따라 연산자를 다르게 사용해야 한다.

 

 

단일행 서브쿼리

서브쿼리 실행 결과가 한개의 행을 반환한다

단일행 연산자 사용 (= , > , < , >= , <= , != 와 같은 비교연산자 )

그룹함수도 단일행 리턴

EMP 테이블에서 사원번호가 7521의 업무와 같고 급여가 사원번호가 7934보다 많은 사원의 사원번호, 이름, 담당업무,입사일자, 급여를 출력

 

 

복수행 서브쿼리

서브쿼리의 실행 결과가 여러개의 행을 반환한다.

복수행 연산자를 사용해야함. (IN, ANY, ALL, EXISTS)

사용할 수 있는 연산자가 달라지기 때문에 구분해야 한다.

 

 

IN 연산자

메인쿼리의 비교조건이 서브쿼리의 결과중에서 하나라도 일치하면 검색 가능.

SELECT * FROM EMP
WHERE SAL IN (SELECT SAL
		FROM EMP
		WHERE ENAME IN ('MILLER', 'SCOTT'));

 

 

ALL연산자

복수행 서브쿼리의 결과가 메인 쿼리의 WHERE 절에서 부등호 조건으로 비교될 때 사용된다.

서브쿼리에서 반환되는 행들 전체에 대한 조건을 만족해야 한다.

서브쿼리에서 반환되는 최댓값과 최솟값을 비교

직업이 세일즈인 사원의 최소 월급보다 적은 월급을 받는 사원들의 정보를 출력하기

 

서브쿼리가 반환된 모든 값보다 작아야 최소월급보다 적은 월급을 받는다는 조건이 일치하게 된다. 그래서 ALL 연산자는 최댓값 최솟값을 기준으로 비교하는데 이용된다.

 

 

ANY 연산자

ALL 연산자와 사용방법이 비슷하다. 

서브쿼리에서 반환되는 행들 전체에 대해 하나의 조건만 만족하면 된다.

서브쿼리에서 반환되는 값들 중 어느 하나의 값보다 크기만 하면 된다.

 

SELECT ENAME, DEPTNO, SAL
FROM EMP
WHERE SAL > ANY (SELECT SAL
		FROM EMP
		WHERE JOB = "SALESMAN";

사원 테이블에서 직업이 세일즈인 사원의 최소 월급보다 많은 월급을 받는 사원의 정보를 출력하기.

 

 

EXISTS 연산자

서브쿼리의 실행결과에 따라 메인쿼리를 실행하거나 실행하지 않는 문장을 작성할 수 있다.

서브쿼리에서 검색된 결과가 하나도 없으면 전달값이 FALSE이기 때문에 쿼리를 실행할 수 없다.

 

SELECT * FORM EMP
WHERE EXISTS ( SELECT EMPNO
		FROM EMP
                WHERE COMM IS NOT NULL );

사원들 중 커미션을 받는 사원이 한 명이라도 있으면 모든 사원 정보 출력하기

 

 

다중컬럼 서브쿼리

서브쿼리에서 여러개의 컬럼값을 검색하여 메인쿼리의 조건절과 비교하는 서브쿼리.

메인쿼리의 조건절에서도 서브쿼리의 컬럼수만큼 지정해야 한다. 일대일 매칭이 되어야함.

 

PAIRWISE

컬럼을 쌍으로 묶어서 동시비교

SELECT DEPTNO, EMPNO, ENAME, SAL
FROM EMP
WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL)
			FROM EMP
			GROUP BY DEPTNO);

 

UNPAIRWISE

컬럼별로 나누어 비교하고 나중에 AND 연산 처리

SELECT DEPTNO, EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO
		FROM EMP
		GROUP BY DEPTNO)
AND SAL IN (SELECT MAX(SAL)
	    FROM EMP
	    GROUP BY DEPTNO);

 

 

인라인뷰

SELECT column_list
FROM (서브쿼리) alias
WHERE 조건식;

FROM 뒤에 테이블명이 안오고 서브쿼리가 하나의 가상테이블을 반환하는 형태로 사용되는 경우.

테이블 2개를 조인했기 때문에 검색해봐야 할 데이터 범위가 아주 늘어난다. 근데 인라인뷰로 필요한 행과 컬럼만으로 구성된 집합을 재정의하면 훨씬 적은 수의 데이터가 조인에 참여하기 때문에 더 효율적으로 쿼리문을 처리할 수 있다.

 

SELECT E.DEPTNO, TOTAL_SUM , TOTAL_AVG, CNT
FROM (SELECT DEPTNO, SUM(SAL) TOTAL_SUM, AVG(SAL) TOTAL_AVG, COUNT(*) CNT
        FROM EMP
        GROUP BY DEPTNO ) E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

사원 테이블과 부서 테이블에서 부서별 월급 총합과 평균 그리고 부서별 인원수를 출력하기

 

 

😜 NEXT : SQL 조인과 서브쿼리의 실습문제 풀어보기