06-30 (수) ANSI조인_LEFT OUTER JOIN...

※ 개발 수업 정리글입니다. 꾸준히 정리할 예정이고 틀린 부분이 있다면 언제든지 댓글 환영입니다.

---

JOIN 함수  테이블 합치기

오라클조인		ANSI조인
조인조건 생략or잘못된 경우	cartesian product	Cross 조인	조인조건 생략or잘못된 경우
반드시 일치하는 데이터만 조회	Equi 조인	Natural 조인	반드시 일치하는 데이터만 조회
		USING조인	명시적으로 일치하는 데이터만 조회
일치하지 않아도 범위 포함되면 조회	Non-Equi 조인	ON조인	일치하지 않아도 범위 포함되면 조회
일치하지 않아도 전부 표시하는 조인	Outer 조인	LEFT | RIGHT | FULL OUTER 조인	일치하지 않아도 전부 표시하는 조인
자신의 테이블과 조인	Self 조인	Self 조인	자신의 테이블과 조인

---

 

ANSI조인

-JOIN은 원래 INNER  JOIN이지만 INNER 안 넣는게 깔끔

 

■ ANSI조인   Cross 조인 = cartesian product

    ㄴ일치하는 데이터만 반환하지 않고, (테이블1의 행 개수 *곱한 테이블2의 행 개수)한 결과로 반환

    ㄴ아래 예제 더보기

 

 

■ ANSI조인  Natural 조인

-Equi 조인과 동일, 같은 이름을 가진 컬럼에 기반하여 조회

 무조건 1개이상 공통컬럼 있어야하고, 2개이상이면 오류는 아니지만 2개의 공통컬럼 값이 서로 같은 것만 조회됨.   FROM 테이블에 별칭 쓸 수 있음

 

③ SELECT 테이블1.컬럼 , 테이블2.컬럼

① FROM  테이블1 NATURAL JOIN 테이블2

② WHERE 조건 ~ ;

 

■ ANSI조인  JOIN   USING (컬럼) 절 

-Equi 조인과 동일, 명시적으로 일치하는 데이터만 조회

SELECT  컬럼

FROM   테이블1

JOIN    붙을 테이블2

USING   ( 공통컬럼 있을때만 가능!!! )

WHERE ~

*USING절 이하는 별칭 없이 사용해야함

  ex) USING ( 그냥 e ) 안됨!!!  => USING ( ename ) 꼭 이렇게, e.ename도 가능

​

 

 

 

■ ANSI조인  JOIN   ON ~ 절

-Non-Equi 조인과 동일, 반드시 일치하지 않는, 즉 값이 다를 때, ON절 사용

SELECT   컬럼

FROM    테이블1

JOIN      붙을 테이블2

ON         조인조건

WHERE(=AND) 조건절~          ----되도록 WHERE 쓰기

AND 조건절~

ex) ON 테이블1.컬럼1 = 테이블2.컬럼2

 

★3개 이상의 테이블 조인도 가능★

SELECT 컬럼, 컬럼, ...

FROM 테이블1

JOIN  붙을 테이블2   ON 조인조건

JOIN  붙을 테이블3   ON 조인조건   ;

Natural조인이든 SELF조인이든 OUTER조인이든 뭐든가능

 

 

 

■ ANSI조인 self 조인

ex) SELECT *

FROM EMP E

JOIN DEPT D                      ----1번째 JOIN

ON E.DEPTNO  =  D.DEPTNO

JOIN SALGRADE G

ON E.SAL BETWEEN G.LOSAL AND G.HISAL ;  ----2번째 JOIN

 EMP, DEPT, SALGRADE 총3개 테이블 조인

 

 

 

■ ANSI조인   LEFT | RIGHT | FULL    OUTER  조인 ★★★ 정말 많이 사용함!!!

    ㄴ값의 일치 여부와 상관없이 모두 출력

SELECT   테이블1.컬럼, 테이블2.컬럼

FROM     테이블1

{ LEFT|RIGHT|FULL }  OUTER JOIN 테이블2

ON        T1.공통컬럼=T2.공통컬럼  

또는

USING (공통컬럼)

WHERE 조건절~

ex)

 

 

 

 

★★★실습 문제 (오라클조인, ANSI조인 모두 구현해보기)★★★

--1.오라클조인
SELECT EMPNO,ENAME,JOB,DNAME FROM DEPT, EMP WHERE DEPT.DEPTNO=EMP.DEPTNO ORDER BY 1;
--1.ANSI조인
SELECT EMPNO,ENAME,JOB,DNAME FROM DEPT JOIN EMP USING(DEPTNO) ORDER BY 1;
SELECT EMPNO,ENAME,JOB,DNAME FROM DEPT JOIN EMP ON DEPT.DEPTNO=EMP.DEPTNO ORDER BY 1;

--2.오라클조인
SELECT EMPNO,ENAME,SAL,DNAME FROM DEPT, EMP WHERE DEPT.DEPTNO=EMP.DEPTNO
AND SAL>=2000 ORDER BY 3 DESC;
--2.ANSI조인
SELECT EMPNO,ENAME,SAL,DNAME FROM DEPT JOIN EMP USING(DEPTNO) WHERE SAL>=2000 ORDER BY 3 DESC;
SELECT EMPNO,ENAME,SAL,DNAME FROM DEPT JOIN EMP ON DEPT.DEPTNO=EMP.DEPTNO WHERE SAL>=2000 ORDER BY 3 DESC;

 

--3.오라클조인
SELECT EMPNO,ENAME,JOB,SAL,DNAME FROM DEPT, EMP WHERE DEPT.DEPTNO=EMP.DEPTNO
AND JOB='MANAGER' AND SAL>=2500 ORDER BY 1 ;
--3.ANSI조인
SELECT EMPNO,ENAME,JOB,SAL,DNAME FROM DEPT JOIN EMP USING(DEPTNO) WHERE JOB='MANAGER' AND SAL>=2500 ORDER BY 1 ;
SELECT EMPNO,ENAME,JOB,SAL,DNAME FROM DEPT JOIN EMP ON DEPT.DEPTNO=EMP.DEPTNO WHERE JOB='MANAGER' AND SAL>=2500 ORDER BY 1 ;

 

--4.오라클조인
SELECT EMPNO,ENAME,SAL,GRADE FROM SALGRADE, EMP WHERE SAL BETWEEN LOSAL AND HISAL AND GRADE=4 ORDER BY 3 DESC;
--4.ANSI조인
SELECT EMPNO,ENAME,SAL,GRADE FROM SALGRADE JOIN EMP ON SAL BETWEEN LOSAL AND HISAL WHERE GRADE=4 ORDER BY 3 DESC;

 

--5.오라클조인
SELECT EMPNO,ENAME,SAL,GRADE FROM DEPT,EMP,SALGRADE 
WHERE DEPT.DEPTNO=EMP.DEPTNO AND SAL BETWEEN LOSAL AND HISAL ORDER BY 4 DESC;
--5.ANSI조인
SELECT EMPNO,ENAME,SAL,GRADE FROM DEPT JOIN EMP USING(DEPTNO) JOIN SALGRADE ON SAL BETWEEN LOSAL AND HISAL ORDER BY 4 DESC;
SELECT EMPNO,ENAME,SAL,GRADE FROM DEPT D JOIN EMP E ON D.DEPTNO=E.DEPTNO JOIN SALGRADE ON SAL BETWEEN LOSAL AND HISAL ORDER BY 4 DESC;

 

--6.오라클조인
SELECT M.ENAME 사원, N.ENAME 관리자 FROM EMP M,EMP N WHERE M.EMPNO=N.MGR;
--6.ANSI조인
SELECT M.ENAME 사원, N.ENAME 관리자 FROM EMP M JOIN EMP N ON M.EMPNO=N.MGR;

 

--7.오라클조인
SELECT M.ENAME 사원, N.ENAME 관리자,O.ENAME "관리자의 관리자" FROM EMP M,EMP N,EMP O WHERE M.MGR=N.EMPNO AND N.MGR=O.EMPNO;
--7.ANSI조인
SELECT M.ENAME 사원, N.ENAME 관리자,O.ENAME "관리자의 관리자" FROM EMP M JOIN EMP N ON M.MGR=N.EMPNO JOIN EMP O ON N.MGR=O.EMPNO;

 

--8.오라클조인 (+)

SELECT M.ENAME 사원, N.ENAME 관리자,O.ENAME "관리자의 관리자"

FROM EMP M,EMP N,EMP O

WHERE M.MGR=N.EMPNO(+) AND N.MGR=O.EMPNO(+);

--8.ANSI조인 LEFT OUTER

SELECT M.ENAME 사원, N.ENAME 관리자,O.ENAME "관리자의 관리자"

FROM EMP M

LEFT OUTER JOIN EMP N ON M.MGR=N.EMPNO

LEFT OUTER JOIN EMP O ON N.MGR=O.EMPNO;

​​

--9.오라클조인
SELECT DNAME, ENAME FROM DEPT D,EMP E WHERE D.DEPTNO=E.DEPTNO AND E.DEPTNO=20;
--9.ANSI조인
SELECT DNAME, ENAME FROM DEPT D JOIN EMP E USING(DEPTNO) WHERE E.DEPTNO='20';

ㄴ아 원래 DEPTNO가 문자데이터네... 엥 근데 왜 다른 SQL은 작땀표 안해도 나오는거지...
SELECT DNAME, ENAME FROM DEPT D JOIN EMP E ON D.DEPTNO=E.DEPTNO AND E.DEPTNO=20;

 

--10번 오라클조인
SELECT ENAME, COMM, DNAME FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO AND COMM IS NOT NULL AND COMM!=0 ;
--10번 ANSI조인
SELECT ENAME, COMM, DNAME FROM EMP E JOIN DEPT D USING(DEPTNO) WHERE COMM IS NOT NULL AND COMM!=0 ;
SELECT ENAME, COMM, DNAME FROM EMP E JOIN DEPT D ON E.DEPTNO=D.DEPTNO AND COMM IS NOT NULL AND COMM!=0 ;

--11번 오라클조인
SELECT ENAME, dname FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO AND ENAME LIKE'%A%';
--11번 ANSI조인
SELECT ENAME, dname FROM EMP E JOIN DEPT D USING (DEPTNO) WHERE ENAME LIKE'%A%';
SELECT ENAME, dname FROM EMP E JOIN DEPT D ON E.DEPTNO=D.DEPTNO WHERE ENAME LIKE'%A%';

 

--12번 오라클조인
SELECT ENAME, SAL, HIREDATE, COMM FROM EMP E, DEPT D WHERE E.DEPTNO=d.deptno AND LOC='DALLAS' AND SAL>=1500;
--12번 ANSI조인
SELECT ENAME, SAL, HIREDATE, COMM FROM EMP E JOIN DEPT D USING (deptno) WHERE LOC='DALLAS' AND SAL>=1500;
SELECT ENAME, SAL, HIREDATE, COMM FROM EMP E JOIN DEPT D ON E.deptno=D.DEPTNO AND LOC='DALLAS' AND SAL>=1500;

 

--13번 오라클조인
SELECT n.ename, N.SAL*12 FROM EMP N, EMP M WHERE N.MGR=M.EMPNO AND N.SAL>M.SAL;
--13번 ANSI조인
SELECT n.ename, N.SAL*12 FROM EMP N JOIN EMP M ON N.MGR=M.EMPNO AND N.SAL>M.SAL;

 

--14번 현재시간으로 30년 하면 12명, 40년 하면 6명 
--오라클조인
SELECT ENAME, SAL, HIREDATE, DNAME FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO  AND (MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)>30;
--ANSI조인
SELECT ENAME, SAL, HIREDATE, DNAME FROM EMP E JOIN DEPT D USING (DEPTNO) WHERE(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)>30;
SELECT ENAME, SAL, HIREDATE, DNAME FROM EMP E JOIN DEPT D ON E.DEPTNO=D.DEPTNO AND(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)>30;

 

--15번 오라클조인
SELECT DNAME 부서명, COUNT(*) "인원수" FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO AND TO_CHAR(HIREDATE, 'YYYY')<1982 GROUP BY DNAME ;
--15번 ANSI조인
SELECT DNAME 부서명, COUNT(*) "인원수" FROM EMP E JOIN DEPT D USING (DEPTNO) WHERE TO_CHAR(HIREDATE, 'YYYY')<1982 GROUP BY DNAME ;
SELECT DNAME 부서명, COUNT(*) "인원수" FROM EMP E JOIN DEPT D ON E.DEPTNO=D.DEPTNO AND TO_CHAR(HIREDATE, 'YYYY')<1982 GROUP BY DNAME ;

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ​

SELECT EMPNO, ENAME, DNAME, SAL, E.DEPTNO

FROM EMP E

JOIN DEPT D

ON E.DEPTNO=D.DEPTNO

AND SAL IN(800);

--위아래 같은거

SELECT EMPNO, ENAME, DNAME, SAL, DEPTNO

FROM EMP E

JOIN DEPT D

USING(DEPTNO)

WHERE SAL IN(800);

​

SELECT E.ENAME 사원, D.ENAME 관리자

FROM EMP E

JOIN EMP D

ON E.MGR=D.EMPNO;

--사번이 7902인 것도 추가하면 아래

SELECT E.ENAME 사원명, M.ENAME 관리자명

FROM EMP E LEFT OUTER JOIN EMP M

ON E.MGR=M.EMPNO

WHERE E.EMPNO=7902;​

★★★★실습문제 끝★★★★★

 

★★★★바로 웤샵 3문제하고 끝★★★★

 

---

 서브쿼리 

조인      : 하나 이상의 테이블에서 원하는 데이터 조회

서브쿼리: 여러 개의 SELECT문장을 하나로 합쳐서 조회

 

(기본문법) 

  *서브쿼리는 WHERE절 말고도 SELECT, FROM, HAVING, ORDER BY, UPDATE, INSERT, DELETE 등에 

   사용가능하지만, 일반적으로는 WHERE절의 서브쿼리

 

(종류)

단일행 서브쿼리 : 서브쿼리 결과가 1개  ➠   = >  >=  < <=   != 비교연산자 사용가능

복수행 서브쿼리 : 서브쿼리 결과가 복수 ➠   IN , ANY , ALL , EXIST 연산자 사용가능

                                         사용가능 연산자 바꿔서 사용 못함┘

예제1) SELECT ENAME, SAL
FROM EMP
WHERE SAL>=
(SELECT SAL  -- CLARK의 SAL은 2450
FROM EMP
WHERE ENAME='CLARK');

2) JOIN 말고 서브쿼리 사용하기
SELECT DNAME
FROM DEPT           
WHERE DEPTNO=   --이건 DEPT의 DEPTNO
(SELECT DEPTNO     --이건 EMP의 DEPTNO
FROM EMP 
WHERE ENAME='SMITH');

3) SELECT ENAME FROM EMP
WHERE DEPTNO=                  --이건 EMP의 DEPTNO
(SELECT DEPTNO FROM DEPT  --이건 DEPT의 DEPTNO
WHERE DNAME='RESEARCH');

■    종류                     사용가능연산자
단일행 서브쿼리   |    =  >  >=  <  <=  !=
복수행 서브쿼리   |    IN, ANY, ALL, EXIST

예제)
SELECT EMPNO, ENAME, JOB, HIREDATE, SAL
FROM EMP
WHERE JOB=
(SELECT JOB
FROM EMP
WHERE EMPNO=7521)
AND SAL>
(SELECT SAL
FROM EMP
WHERE EMPNO=7934);

SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE SAL<
(SELECT AVG(SAL) FROM EMP);

SELECT DEPTNO, MIN(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING MIN(SAL)>(SELECT MIN(SAL) FROM EMP
WHERE DEPTNO=20);

■ HAVING 절에서 서브쿼리 사용
RETURN =>나중 배운다 함

예제)
SELECT JOB, AVG(SAL)--직업을 그룹묶음하고 직업,월급평균을 보이기
FROM EMP
GROUP BY JOB;
--=위아래를 합치기
(SELECT MIN(AVG(SAL))--직업들 중에서 월급 평균이 제일 적은 직업
FROM EMP
GROUP BY JOB);

SELECT JOB, AVG(SAL)--직업을 그룹묶음하고 직업,월급평균~~보여주는데
FROM EMP
GROUP BY JOB;          --그것 중에 서브쿼리와
HAVING AVG(SAL)=        --       = 같은값
(SELECT MIN(AVG(SAL))--직업들 중에서 월급 평균이 제일 적은 직업
FROM EMP
GROUP BY JOB);

예제)
SELECT ENAME, SAL                   --이름과 월급을 골라라
FROM EMP                              --
WHERE SAL>(SELECT AVG(SAL)     --월급이 큰 레코드의,
FROM EMP);                             --EMP의  평균월급보다