SQL / (Oracle/ANSI) JOIN, 심화문제, SUBQUERY

Cheol·2023년 5월 15일
sql

SQL

목록 보기
5/7
post-thumbnail

edu day 5

JOIN

ORACLE JOIN


OUTER JOIN

: 조건을 만족하지 않아도 결과값에 포함시키는 조인 방법으로 (+) 연산자를 사용한다.
(<--> 조인 조건에 일치하는 데이터만 조회하는 조인은 Inner조인이라고 한다.)

SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 , 테이블2
WHERE 테이블1.공통컬럼 = 테이블2.공통컬럼 (+);

(+) 연산자를 지정하면 내부적으로 한 개 이상의 널(NULL)을 가진 행이 생성되고 이렇게 생성된 널(NULL)행들과 데이터를 가진 테이블의 행들이 조인하게 되어 조건이 일치하지 않아도 결과 값에 포함이 된다.
! (+)연산자를 동시에 사용할 수는 없으며, 반드시 하나의 테이블에만 지정해야 된다.

  • 관리자가 없는 사원정보도 모두 포함하여 사원의 이름과 담당 관리자의 사원번호를 조회
SELECT E.ENAME 사원명, E.EMPNO 사번, M.ENAME 관리자명, M.EMPNO 관리자번호 FROM EMP E, EMP M WHERE e.MGR = m.empno(+);
  • 관리자의 관리자 명 출력
SELECT E.ENAME 사원, M.ENAME 관리자, MM.ENAME 관리자의관리자 FROM EMP E, EMP M, EMP MM WHERE E.MGR = M.EMPNO AND M.MGR = MM.EMPNO;
  • 관리자가 없는 사원정보도 모두 포함하여 관리자의 관리자 명까지 출력
SELECT E.ENAME 사원, M.ENAME 관리자, MM.ENAME 관리관리자 FROM EMP E, EMP M, EMP MM WHERE E.MGR = M.EMPNO(+) AND M.MGR = MM.EMPNO(+);

ANSI JOIN

: 조인 조건을 WHERE절에 명시하지 않고 다른 방법을 통하여 기술하며 검색조건을 지정하는 경우에 WHERE절을 사용한다.


Natural JOIN

: Oracle JOIN의 Equi JOIN과 기능이 동일하다. 조인에 참여하는 테이블은 반드시 한 개의 공통 컬럼이 있어야 되며, 만약 두 개 이상의 공통 컬럼이 있다면 엉뚱한 실행결과가 출력된다.

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

공통 컬럼인 DEPTNO를 SELECT할 때 앞에 테이블 이름이나 별칭을 지정해줄 수 없다. -> 공통 컬럼이기 때문

! E.DEPTNO 또는 D.DEPTNO가 아닌 그냥 DEPTNO로 써야한다.


CROSS JOIN
: 조인할 때 일치하는 데이터만 반환하지 않고 조인하는 각 테이블 의 행 개수를 서로 곱한 결과가 반환된다. 데이터로서의 가치는 없다.


USING(컬럼) 절 (중요!!)

: NATURAL 조인 사용시 만약 두 개 이상의 공통 컬럼이 있다면 USING절을 사용하여 명시적으로 어떤 컬럼으로 조인할 지를 지정할 수 있다.

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

SELECT * FROM EMP JOIN DEPT USING(DEPTNO);

ON 절

: 만약 NON-Equi JOIN이나 임의의 조건으로 조인할 경우에는 ON절을 사용해야 된다. 동등/비동등 형식.
또한 TABLE JOIN할때 WHERE을 안써줬기 때문에 조건을 추가 할때는 WHERE을 써준다.

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

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

주의!! USING절은 공통컬럼이 하나만 생성되고 ON절은 공통컬럼이어도 각 테이블마다의 컬럼이 생기기 때문에 컬럼에 별칭을 지정해야 한다.

-- USING절과 ON절
SELECT ENAME, E.DEPTNO, DNAME FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO WHERE ENAME='SMITH';
SELECT ENAME, DEPTNO, DNAME FROM EMP JOIN DEPT USING (DEPTNO) WHERE ENAME = 'SMITH';
  • 3개의 테이블 JOIN
SELECT * FROM EMP E JOIN DEPT D USING(DEPTNO) JOIN SALGRADE S ON e.sal BETWEEN S.LOSAL AND S.HISAL;
  • 10번 부서에 속한 직원의 부서명, 이름, 월급등급, 월급
SELECT DNAME 부서명, ENAME 이름, grade 월급등급, SAL 월급 FROM EMP E JOIN DEPT D USING(DEPTNO) JOIN salgrade S ON E.SAL BETWEEN S.LOSAL AND S.HISAL WHERE DEPTNO = 10;
  • 이름이 SMITH의 관리자와 관리자의 관리자출력
SELECT E.ENAME 사원이름, M.ENAME 관리자이름, MM.ENAME 관리자의관리자이름 FROM EMP E JOIN EMP M ON E.MGR = M.EMPNO JOIN EMP MM ON M.MGR = MM.EMPNO WHERE E.ENAME = 'SMITH';

LEFT OUTER / RIGHT OUTER / FULL OUTER JOIN

: Oracle JOIN에서는 (+)연산자를 사용하고 반드시 한 쪽 테이블에서만 사용할 수 있었다. ANSI JOIN에서의 OUTER조인은 LEFT | RIGHT | FULL 키워드를 이요하며 한 쪽 테이블 또는 양쪽 테이블 지정이 모두 가능하다.

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

LEFT OUTER JOIN은 LEFT로 지정된 테이블 1의 데이터를 테이블2의 조인조건 일치 여부와 상관없이 모두 출력한다는 의미이다. RIGHT도 테이블 2의 데이터를 테이블1의 조인조건 일치 여부와 상관없이 모두 출력한다는 의미이고, FULL OUTER JOIN은 LEFT OUTER JOIN 결과와 RIGHT OUTER JOIN 결과를 합친 결과이다.


ANSI JOIN 실습 문제

  • 부서 테이블과 사원테이블에서 사번, 사원명 , 급여 , 부서명을 검색하시오. 단, 급여가 2000
    이상인 사원에 대하여 급여기준으로 내림차순 정렬할 것
SELECT EMPNO, ENAME, SAL, DNAME FROM EMP E JOIN DEPT D USING(DEPTNO) WHERE SAL >= 2000 ORDER BY 3 DESC;
  • 부서 테이블과 사원 테이블에서 사번, 사원명, 업무, 급여 , 부서명을 검색하시오. 단, 엄무가
    Manager이며 급여가 2500 이상인 사원에 대하여 사번을 기준으로 오름차순 정렬할 것.
SELECT EMPNO, ENAME, JOB, SAL, DNAME FROM EMP E JOIN DEPT D USING(DEPTNO) WHERE JOB = 'MANAGER' AND SAL >=2500 ORDER BY 1;
  • 사원 테이블과 급여 등급 테이블에서 사번, 사원명, 급여, 등급을 검색하시오. 단, 등급은 급여
    가 하한값과 상한값 범위에 포함되고 등급이 4이며 급여를 기준으로 내림차순정렬할 것.
SELECT EMPNO 사번, ENAME 사원명, SAL 급여, GRADE 등급 FROM EMP E JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL WHERE s.grade = 4 ORDER BY 3 DESC;
  • 사원 테이블에서 사원명, 해당 사원의 관리자명, 해당 사원의 관리자의 관리자명을 검색하시오
    위 결과에서 상위 관리자가 없는 모든 사원의 이름도 사원명에 출력되도록 수정하시오
--1
SELECT E.ENAME 사원명, M.ENAME 관리자명, MM.ENAME 관리자의관리자명 FROM EMP E JOIN EMP M ON  E.MGR = M.EMPNO JOIN EMP MM ON M.MGR = MM.EMPNO;
--2
SELECT E.ENAME 사원명, M.ENAME 관리자명, MM.ENAME 관리자의관리자명 FROM EMP E LEFT JOIN EMP M ON  E.MGR = M.EMPNO LEFT JOIN EMP MM ON M.MGR = MM.EMPNO;
  • 커미션을 받는 사원의 이름, 커미션, 부서이름 출력하시오.
SELECT ENAME 이름, COMM 커미션, DNAME 부서이름 FROM EMP E JOIN DEPT D USING(DEPTNO) WHERE COMM IS NOT NULL AND COMM != 0;
  • 자신의 관리자 보다 연봉(sal)을 많이 받는 사원의 이름과 연봉을 출력하시오.
SELECT E.ENAME 이름, E.SAL*12 연봉 FROM EMP E JOIN EMP M ON E.MGR = M.EMPNO WHERE E.SAL > M.SAL;
  • 직원 중 현재시간 기준으로 근무 개월 수가 30년(12 * 30개월) 보다 많은 사람의 이름, 급여 ,
    입사일 , 부서명을 출력하시오.
SELECT ENAME 이름, SAL 급여, HIREDATE 입사일, DNAME 부서명 FROM EMP E JOIN DEPT D USING(DEPTNO) WHERE (SYSDATE - HIREDATE)/365 > 30;
SELECT ENAME, SAL, HIREDATE, DNAME FROM EMP E JOIN DEPT D USING(DEPTNO) WHERE MONTHS_BETWEEN(SYSDATE, HIREDATE)/12 > 30;
  • 각 부서별로 1982년 이전에 입사한 직원들의 인원수를 출력하시오.
SELECT DNAME 부서명, COUNT(*) 인원수 FROM EMP E JOIN DEPT D USING(DEPTNO) WHERE TO_CHAR(HIREDATE,'YY') < 82 GROUP BY DNAME;


SELECT 실습 문제 (TB)

  • '학' 자가 들어간 계열의 소속 하고가가 몇 개 있는지 계열별, 학과수를 출력하시오. 단 학과 수가 많은 순으로 정렬하시오
SELECT category, COUNT(*) FROM TB_DEPARTMENT GROUP BY CATEGORY HAVING category LIKE '%학%'ORDER BY 2 DESC;
  • 법학과 교수 중 가장 나이가 작은 사람부터 이름을 확인 하자. 단 조인을 사용하기(USING절로 처리) 
SELECT s.professor_name, s.professor_ssn FROM TB_PROFESSOR S JOIN TB_DEPARTMENT D USING(DEPARTMENT_NO) WHERE d.department_name='법학과' ORDER BY s.professor_ssn DESC;
  • 과목이름과 과목의 학과이름을 출력하는 SQL을 작성하시오(USING절 사용)-TB_CLASS, TB_DEPARTMENT
SELECT CLASS_NAME, DEPARTMENT_NAME FROM TB_CLASS C JOIN TB_DEPARTMENT D USING(DEPARTMENT_NO) ORDER BY 2;
  • CLASS_NAME 과 PROFESSOR_NAME을 출력하시오
SELECT CLASS_NAME, PROFESSOR_NAME FROM TB_CLASS C JOIN tb_CLASS_professor P USING(class_no) JOIN TB_PROFESSOR CP USING(PROFESSOR_NO);
  • CATEGORY가 '인문사회'인 CLASS 이름과 교수의 이름, CATEGORY를 출력
SELECT CLASS_NAME, PROFESSOR_NAME, CATEGORY FROM TB_CLASS C JOIN TB_CLASS_PROFESSOR CP USING(CLASS_NO) JOIN TB_PROFESSOR P USING(PROFESSOR_NO) JOIN TB_DEPARTMENT D ON c.department_no = D.department_no WHERE CATEGORY='인문사회';

SUBQUERY

: JOIN은 하나 이상의 테이블에서 원하는 데이터를 조회할 때 사용하는 방법이고, 서브쿼리는 하나의 SELECT만으로 원하는 데이터를 조회할 수 없을 때 사용하는 방법으로 여러 개의 SELECT 문장을 하나로 합쳐서 하나의 실행 가능한 SQL문장을 만들어 원하는 데이터를 조회한다.
1. 서브쿼리가 실행되고 2. 메인쿼리가 실행된다.
=> 서브쿼리를 먼저 작성하고 메인쿼리를 작성하면 편하다.

SELECT select_list
FROM 테이블
WHERE 컬럼명 연산자 (SELECT select_list
FROM 테이블);

  • SMITH의 급여보다 많은 사람들 이름, 급여 출력
SELECT ENAME, SAL FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'SMITH');
  • SMITH와 같은 부서에 있는 사람들 출력
SELECT ENAME, DEPTNO FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'SMITH');
  • RESEARCH 부서에서 근무하는 직원 이름, 부서 번호 (JOIN 사용 불가능)
SELECT ENAME, DEPTNO FROM EMP WHERE DEPTNO = (SELECT deptno FROM DEPT WHERE DNAME='RESEARCH');
  • 월급 평균보다 더 많은 사원명, 월급
SELECT ENAME, SAL FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP);

서브쿼리 종류

: 오라클에서 사용 가능한 서브쿼리는 서브 쿼리가 실행되어 반횐된 행의 개수에 따라서 단일행 서브쿼리와 복수행 서브쿼리로 구분된다.

단일행 서브쿼리

: 대표적으로 기본키를 이용하거나 MAX,MIN,SUM과 같은 그룹함수를 사용하여 검색하는 경우로서 반드시 단일행 연산자를 사용하여 메인 쿼리와 연산되어야 된다.

  • 10번 부서의 최소 월급보다 월급을 적게 받는 사원의 이름, SAL
SELECT ENAME, SAL FROM EMP WHERE SAL < (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 10);
  • CLARK보다 빨리 입사한 사람들의 이름과 입사일
SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE < (SELECT HIREDATE FROM EMP WHERE ENAME = 'CLARK');

복수행 서브쿼리

: 서브쿼리가 실행되어 반환되는 결과가 하나 이상의 행일 때 사용하는 서브쿼리이다. 반드시 복수형 연산자와 함께 사용해야 된다.

IN 연산자
: 서브쿼리 반환값이 복수이고 메인 쿼리와 동등 연산자 방식으로 비교할 때 사용하는 연산자

  • 이름이 SMITH와 WARD와 급여가 같은 사람들 출력
    SELECT ENAME, SAL FROM EMP WHERE SAL IN (SELECT SAL FROM EMP WHERE ENAME IN ('SMITH', 'WARD'));
  • 급여가 3000이상이 있는 부서의 부서번호, 부서원, 급여 출력
SELECT DEPTNO, ENAME, SAL FROM EMP WHERE DEPTNO IN (SELECT DISTINCT DEPTNO FROM EMP WHERE SAL >= 2000);

ALL 연산자
: ALL연산자는 복수행 서브쿼리에서 > 또는 <같은 비교 연산자를 사용하고자 할 때 사용된다. > 또는 < 같은 연산자는 단일행 연산자이기 때문에 복수행 연산자에서 ALL 키워드 없이 사용하면 에러가 발생한다.

  • 부서가 30인 사람들 중 급여를 가장 많이 받는 사람보다 급여를 더 받는 사람들
SELECT ENAME, SAL FROM EMP WHERE SAL > ALL(SELECT SAL FROM EMP WHERE DEPTNO = 30);
  • 부서가 30인 사람들 중 급여를 가장 적게 받는 사람보다 급여를 더 적게 받는 사람들
SELECT ENAME, SAL FROM EMP WHERE SAL < ALL(SELECT SAL FROM EMP WHERE DEPTNO = 30);

ANY 연산자
: 조건이 하나 이상만 만족하면 된다.

  • 부서가 30인 사람의 급여의 최소값보다 큰 사람들 출력
SELECT ENAME, SAL FROM EMP WHERE SAL > ANY(SELECT SAL FROM EMP WHERE DEPTNO = 30);
  • 부서가 30인 사람의 급여의 최대값보다 작은 사람들 출력
SELECT ENAME, SAL FROM EMP WHERE SAL < ANY(SELECT SAL FROM EMP WHERE DEPTNO = 30);

EXISTS 연산자
: 서브쿼리에서 실행된 결과가 하나라도 존재 하는지 여부를 확인할 때 사용하는 복수행 연산자이다.
만일 서브쿼리에서 검색된 결과가 하나라도 없으면 메인쿼리에 전달된 값이 FLASE이기 때문에 메인 쿼리가 실행되지 않는다. 검색된 결과가 있으면 값이 TRUE이기 때문에 출력된다.

  • 커미션을 받는 사원이 한 명이라도 있으면 모든 사원 정보를 출력하는 SQL문
SELECT ENAME, EMPNO, SAL FROM EMP WHERE EXISTS(SELECT EMPNO FROM EMP WHERE COMM IS NOT NULL);

다중 컬럼 서브쿼리

: 서브쿼리에서 여러 개의 컬럼값을 검색하여 메인쿼리의 조건절과 비교하는 서브쿼리이다. 메인쿼리의 조건절은 서브쿼리의 컬럼과 일대일 매칭이 되어야 한다.
컬럼을 쌍으로 묶는 pairwise방식이 있고 컬럼별로 나누어 비교하고 나중에 AND연산으로 처리하는 unpairwise방식이 있다.

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

--> 서브쿼리를 나눌 수도 있음
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);

인라인 뷰(in-line view)
: 서브쿼리는 메인쿼리의 WHERE절에 사용된 서브쿼리이다. 하지만 FROM절에도 서브쿼리는 사용할 수 있는 서브쿼리를 '인라인 뷰'라고 부른다.

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

FROM 절에 서브쿼리를 지정하고 별칭(alias)을 설정한다. 일반적으로 FROM절에는 테이블명이 와야하지만 서브쿼리가 하나의 가상 테이블 형태로 사용될 수 있다.

--> 총 컬럼 수가 11개
SELECT E.DEPTNO, SUM(SAL), AVG(SAL), COUNT(*) FROM EMP E, dept D WHERE e.deptno = D.DEPTNO GROUP BY E.DEPTNO ORDER BY 1;
--> 총 컬럼 수가 7개
SELECT * FROM (SELECT DEPTNO, SUM(SAL), AVG(SAL), COUNT(*) FROM EMP GROUP BY DEPTNO) E, DEPT D WHERE E.DEPTNO = D.DEPTNO ORDER BY 1;

--> 훨씬 적은 수의 데이터로 조인에 참여할 수 있다.


실습 문제

  • 사원 테이블에서 MILLER 보다 늦게 입사한 사원의 사번, 이름, 입사일을 검색하시오.
SELECT EMPNO, ENAME, HIREDATE FROM EMP WHERE HIREDATE < (SELECT HIREDATE FROM EMP WHERE ENAME = 'MILLER');
  • 사원 테이블에서 부서별 최대 급여를 받는 사원들의 사번, 이름, 부서코드, 급여를 검색하시오.
-- 3가지 방법
SELECT EMPNO, ENAME, DEPTNO, SAL FROM EMP WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);
SELECT EMPNO, ENAME, DEPTNO, SAL FROM EMP WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO);
SELECT EMPNO, ENAME, DEPTNO, SAL FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM EMP GROUP BY DEPTNO) AND SAL IN(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);
  • ‘SMITH’의 급여등급과 같은 등급의 사원이름과 등급을 검색하시오
SELECT ENAME, GRADE FROM EMP E JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL WHERE GRADE = (SELECT GRADE FROM EMP E JOIN SALGRADE S ON E.SAL BETWEEN LOSAL AND HISAL WHERE ENAME = 'SMITH');
profile
Cheol
이전 포스트

SQL / 그룹, JOIN 함수

다음 포스트

SQL / DML(트랜잭션, INSERT... 등), DDL(CREATE)

0개의 댓글