[오라클] JOIN / 서브쿼리

Join (조인)

둘 이상의 테이블을 연결해서 데이터를 검색하는 방법

연결하려면 테이블들이 적어도 하나의 컬럼을 공유하고 있어야 한다.

이 공유하고 있는 컬럼을 PK 또는 FK값으로 사용

---

종류

1. INNER JOIN : 내부조인 -> 교집합
2. LEFT / RIGHT JOIN : 부분집합
3. OUTER JOIN : 외부조인 -> 합집합 (오라클은 OUTER 조인이 있지만, MYSQL은 없어서 LEFT조인 + RIGHT 조인)

 

1. INNER JOIN : 교집합, 공통적인 부분만 SELECT 된다.

 

2. LEFT JOIN : 조인기준 왼쪽에 있는부분 다 SELECT 된다. (공통적인 부분 + LEFT에 있는거만)

 

2.1. 조인기준 왼쪽에 있는거만 SELECT 된다. (공통적인 부분 + LEFT에 있느거만)

LEFT가 가지고 있는 것 중 공통적인 부분을 제외한 값

그냥 LEFT JOIN 값 중에서 WHERE 조건으로 NULL인 값을 조회

 

3. RIGHT JOIN : 조인기준 오른쪽에 있는 거 다 SELECT 된다. (공통적인 부분 + RIGHT에 있는거만)

 

3.1.조인기준 오른쪽에 있는거만! SELECT 된다. (공통적인 부분 + RIGHT에 있는거만)

RIGHT가 가지고 있는 것 중 공통적인 부분을 제외한 값

그냥 RIGHT JOIN 값 중에서 WHERE 조건으로 NULL인 값을 조회

 

4. OUTER JOIN : A테이블 + B테이블이 가지고 있는거 둘다 SELECT

 

4.1. 공통적인 부분을 제외한 값

FULL OUTER가 가지고 있는 것 중 공통적인 부분을 제외한 값

 

자연조인(NATURAL JOIN)

- 조건절 없이 양쪽에 해당하는 컬럼을 적어줌으로써 그 컬럼에 자동으로 등가조인이 실시

- 서로 동일한 컬럼 앞에 ALIAS 된 테이블의 별칭을 적어주면 에러 발생

   (동일한 컬럼이 두개 이상이여도 상관없습니다.)

- 등가조인인 ' = ' 쓸때 동일한 컬럼과 송석이 두번 명시해주어야 하기 때문에 이 중복을 제거하기 위해 사용

 

ANSI CROSS JOIN

 위에서 설명한 JOIN은 오라클에서만 사용이 가능한 JOIN 방법이다. ANSI JOIN은 ANSI(미국표준협회)에서 표준화한 JOIN 방법으로 Oracle, MySQL, MS-SQL 등에서 공통적으로 사용이 가능하다. 사용하는 목적은 동일하나 형식이 다르다. ANSI CROSS JOIN은 [ select 컬럼명 from 테이블1 cross join 테이블2; ] 의 형식을 사용한다.

select * from emp cross join dept;

 

JOIN 문법 예제

-- * 각 JOIN 의 역할, 예제 쿼리 1개씩(ANSI SQL 문법대로 작성)
-- OUTER JOIN (LEFT, RIGHT, FULL OUTER JOIN) 
-- INNER JOIN, NATURAL JOIN, CROSS JOIN
-- Non-Equi Join

-- EQUI JOIN 학생 테이블과 부서 테이블을 EQUI JOIN 하여 학번, 이름, 학과번호, 소속학과 이름, 학과 위치를 출력하여라.
SELECT E.EMPNO
     , E.ENAME
     , E.SAL
     , D.DEPTNO
     , D.DNAME
FROM EMP E JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO);

-- NON-EQUI JOIN
SELECT *
FROM EMP, SALGRADE;
-- INNER JOIN

-- ANSI OUTER JOIN(LEFT)
SELECT E.EMPNO
     , E.ENAME
     , E.JOB
     , E.DEPTNO
     , D.DNAME
FROM EMP E LEFT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;

-- ANSI OUTER JOIN(RIGHT)
SELECT E.EMPNO
     , E.ENAME
     , E.JOB
     , E.DEPTNO
     , D.DNAME
FROM EMP E RIGHT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;

-- ANSI OUTER JOIN(FULL)
SELECT E.EMPNO
     , E.ENAME
     , E.JOB
     , E.DEPTNO
     , D.DNAME
FROM EMP E FULL OUTER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;

-- ANSI INNER JOIN
SELECT *
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO = E.DEPTNO;

-- 1. Clara가 근무하는 지역은 어디인가
SELECT E.ENAME, D.LOC
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE ENAME = 'Clara';

-- ANSI NATURAL JOIN
-- EMP 테이블과 DEPT 테이블의 DEPTNO로 JOIN
SELECT DEPTNO
     , E.ENAME
     , D.DNAME
FROM EMP E NATURAL JOIN DEPT D;

-- ANSI CROSS JOIN
SELECT *
FROM EMP CROSS JOIN DEPT;

 

-- * 서브 쿼리 예제 쿼리 1개씩
-- 단일행 서브쿼리
-- 다중행 서브쿼리
-- 다중컬럼 서브쿼리
-- 인라인뷰
-- 스칼라 서브쿼리
-- 집합쿼리(UNION, UNION ALL, INTERSECT, MINUS)

-- 서브쿼리사용
-- 전체 사원 평균 급여보다 낮은 급여를 받는 사원의 명단을 추출해야 한다면??
-- 단일행 서브쿼리
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL < (SELECT ROUND(AVG(SAL)) FROM EMP);

SELECT DNAME
FROM DEPT
WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'TOM');

 

-- TOM의 급여와 동일하거나 급여를 더 많이 받는 사원과 급여를 출력하는 쿼리문
SELECT ENAME, SAL
FROM EMP
WHERE SAL >= (SELECT SAL FROM EMP WHERE ENAME = 'TOM')
AND ENAME != 'TOM';

 

-- 다중행 서브쿼리
-- 연봉을 3000이상 받는 사원이 소속된 부서와 동일한 부서에서 근무하는 사원들의 정보를 출력하는 쿼리문
SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO IN ( SELECT DISTINCT DEPTNO FROM EMP WHERE SAL >= 3000 );

-- 다중컬럼 서브쿼리
-- 부서번호 20번 최대급여자보다 급여가 높은 사원을 출력하라
SELECT ENAME, SAL
FROM EMP
WHERE SAL > ALL(SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 20);

 

-- 부서번호 20번 최대급여자보다 급여가 작은 사원을 출력하라
SELECT ENAME, SAL
FROM EMP
WHERE SAL < ANY(SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 20);

-- 10번 부서에 가장 작은 급여자보다 작게 받는 급여자를 출력하라
SELECT ENAME, SAL
FROM EMP
WHERE SAL < ALL(SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 10);

-- 직무별 최대급여자의 사원내역을 출력하라
SELECT JOB, MAX(SAL)
FROM EMP
GROUP BY JOB;

 

SELECT ENAME, TRUNC(SAL), JOB
FROM EMP
WHERE (JOB, SAL) IN (SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB);

-- 10번 부서원들과 보너스가 같은 사원을 검색하라
SELECT COMM FROM EMP WHERE DEPTNO = 20;

 

-- 인라인뷰
-- 인라인뷰는 FROM절에 오는 SUBQUERY이다. FROM절에서 원하는 데이터를 조회하여 가상의 집합을 만들어 조인을 수행하거나 가상의 집합을 다시 조회 할 대 사용한다.

-- EMP테이블에서 자신이 속한 부서평균급여보다 급여가 많은 사원의 이름, 급여, 부서코드를 출력하세요
-- 서브쿼리방법

SELECT E.ENAME, E.SAL, E.DEPTNO
FROM EMP E

WHERE SAL > (SELECT AVG(SAL) FROM EMP E1 WHERE E.DEPTNO = E1.DEPTNO);

 

-- 인라인뷰 방법
SELECT E.ENAME, E.SAL, E.DEPTNO

FROM EMP E, (SELECT DEPTNO, AVG(SAL) SALAVG FROM EMP GROUP BY DEPTNO) E1

WHERE E.DEPTNO = E1.DEPTNO

AND E.SAL > E1.SALAVG;

 

-- 스칼라 서브쿼리
SELECT D.DEPTNO,
       (SELECT MIN(EMPNO)
       FROM EMP
       WHERE DEPTNO = D.DEPTNO) EMPNO
FROM DEPT D
ORDER BY D.DEPTNO;

 

-- 집합쿼리
-- UNION : 합집합 ( UNION ALL : 공통원소 두번씩 다 포함한 합집합)
-- INTERSECT : 교집합 ( MINUS : 차집합 )
-- UNION은 두 테이블의 결합을 나타내며, 결합시키는 두 테이블의 중복되지 않은 값들을 반환합니다.

-- UNION
SELECT DEPTNO
FROM EMP
UNION
SELECT DEPTNO
FROM DEPT;

-- UNION ALL
SELECT DEPTNO FROM EMP
UNION ALL
SELECT DEPTNO FROM DEPT
ORDER BY DEPTNO ASC;

-- INTERSECT ( 두 행의 집합중 공통된 행을 반환)
SELECT DEPTNO FROM EMP
INTERSECT
SELECT DEPTNO FROM DEPT;

-- MINUS는 첫번째 SELECT문에 의해 반환되는 행중에서 두번째 SELECT문에 의해 반환되는 행에 존재하지 않는 행들을 보여줍니다.
SELECT DEPTNO FROM DEPT
MINUS
SELECT DEPTNO FROM EMP;