조인및 서브쿼리 > 기술자료

• 목록

1. 조인(JOIN)
 
(1) 설명
하나의 테이블로는 원하는 컬럼정보를 참조할 수 없는 경우 관련된 테이블을 논리적으로 결합하여 원하는 컬럼을 참조하는 방법을 조인(JOIN)이라 한다.
 
(2) 조건
논리적으로 결합되는 2개 이상의 테이블에는 반드시  '공통 컬럼'이 존재해야하며 이 공통 컬럼은 동일한 데이터 타입과 공통된 데이터를 포함해야 한다.

SQL> SELECT emp.empno, dept.dname FROM emp, dept WHERE emp.deptno=dept.deptno;
SQL> SELECT e.empno, d.dname FROM emp e, dept d WHERE e.deptno=d.deptno;
SQL> SELECT e.empno, d.dname FROM emp e JOIN dept d ON e.deptno=d.deptno;
 
 
2. CROSS 조인 ( CARTESIAN 조인 )
2개 이상의 테이블이 조인될 때 where절에 의해 공통되는 컬럼에 의한 결합이 발생하지 않는 경우 즉, 테이블 전체 행의 전체컬럼이 조인에 사용되는 조인 따라서, 모든 데이터가 검색결과가 된다.
교차 조인 (Cross Join) 은 말 그대로 두 개의 테이블을 그냥 붙였다는 개념이다. 아무런 조건이나 제한 사항 없이 양쪽은 있는 그대로 연결만 했다는 개념으로 생각을 하면 되겠다.
합집합(Union) 이라는 개념으로 생각해도 큰 무리가 없다.
http://www.statwith.pe.kr/TABLE_JOIN/functions007.htm

SQL> SELECT e.empno, e.ename, d.dname FROM emp e, dept d;
SQL> SELECT e.empno, e.ename, s.grade FROM emp e, salgrade s;
 

3. NATURAL 조인 ( EQUI 조인 ) - 가장 일반적 WHERE절에 사용된 공통된 컬럼들이 동등 연산자(equals, =)에 의해 비교되는 조인
[1] 형태1 ***** 중요 형태 *****
SQL> SELECT e.empno, e.ename, d.dname FROM emp e, dept d  WHERE e.deptno=d.deptno;

[2] 형태2
SQL> SELECT empno, ename, dname FROM emp NATURAL JOIN dept;
SQL> SELECT empno, ename, dname FROM emp NATURAL JOIN dept WHERE deptno=20;

[3] 형태3 - ( 오라클 9i부터 적용 )
SQL> SELECT e.empno, e.ename, d.dname FROM emp e JOIN dept d USING (deptno);
SQL> SELECT e.empno, e.ename, d.dname FROM emp e JOIN dept d USING (deptno) WHERE deptno=30;

[4] 형태4
SQL> SELECT e.empno, e.ename, d.dname FROM emp e JOIN dept d ON e.deptno=d.deptno;
 

4. SELF 조인
참조해야 할 컬럼이 자신의 테이블에 있는 다른 컬럼인 경우 사용하는 조인
SQL> SELECT e1.empno, e1.ename, e2.empno FROM emp e1, emp e2 WHERE e1.empno=e2.empno;
SQL> SELECT e1.empno, e1.ename, e2.empno FROM emp e1 JOIN emp e2 ON e1.empno=e2.empno;

< sqlForSelfJoin.sql 먼저 실행 가정 >
#문제 : 취업반이면서 국어와 미술을 듣는 학생 출력!

#답1 - (논리 연산자를 이용)
SQL> SELECT DISTINCT name FROM hacksa WHERE dept='취업반' AND (subject='국어' OR subject='미술');
#답2 - (self join 이용1)
SQL> SELECT a.name FROM hacksa a, hacksa b WHERE a.name=b.name AND a.dept='취업반' AND a.subject='국어' AND b.subject='미술';
#답3 - (self join 이용2)
SQL> SELECT a.name FROM hacksa a JOIN  hacksa b ON a.name=b.name WHERE a.dept='취업반' AND a.subject='국어' AND b.subject='미술';


5. OUTER 조인
한쪽 테이블에는 해당하는 데이터가 존재하는 데 다른 쪽 테이블에는 데이터가 존재하지 않을 경우 모든 데이터를 출력하게 하는 조인

[1] 형태1
SQL> SELECT e.empno, e.ename, d.dname FROM emp e, dept d WHERE e.deptno(+)=d.deptno; //17개

[2] 형태2
SQL> SELECT e.empno, e.ename, d.dname FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno; //17개

## 생각해 보세요.. ##
SQL> SELECT e.empno, e.ename, d.dname FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno; //15개
SQL> SELECT e.empno, e.ename, d.dname FROM dept d LEFT OUTER JOIN emp e ON e.deptno=d.deptno; //17개


5-1. RIGHT : 오른쪽 테이블을 기준
오른쪽 테이블을 기준으로 외부 조인 한다. 왼쪽 테이블에 일치하는 값이 없어도 오른쪽 테이블의 데이터는 모두 가져 온다. 이때 왼쪽에 없는 데이터는 null 로 대치 된다.

SQL> SELECT t1.no, t1.name, t2.no, t2.name FROM t1 LEFT OUTER JOIN t2 ON t1.no=t2.no;
http://www.statwith.pe.kr/TABLE_JOIN/functions006.htm


5-2. LEFT  : 왼쪽 테이블을 기준
왼쪽 테이블을 기준으로 외부 조인 한다. 오른쪽 테이블에 인치하는 값이 없어도 왼쪽 테이블의 데이터는 모두 가져 온다.
이때 오른쪽에 없는 데이터는 null 로 대치 된다. 외부조인은 여집합(Complement) 의 개념이다.

SQL> SELECT t1.no, t1.name, t2.no, t2.name FROM t1 RIGHT OUTER JOIN t2 ON t1.no=t2.no;
http://www.statwith.pe.kr/TABLE_JOIN/functions005.htm


5-3. FULL  : 두 테이블 모두를 기준
오른쪽 외부조인과 왼쪽 외부조인을 합친 개념이다. 왼쪽/오른쪽 테이블의 값이 일치하든 일치 하지 않든 모두 가져오는 조인이다.
다만 Cross join 과 다른 점은 Full join 은 빈 값을  null 로 리턴한다는 것이고 Crosss join 은 양쪽 테이블을 그냥 단순히 연결시켜 준다는 점이다.

SQL> SELECT t1.no, t1.name, t2.no, t2.name FROM t1 FULL OUTER JOIN t2 ON t1.no=t2.no;
http://www.statwith.pe.kr/TABLE_JOIN/functions004.htm

 

6. 서브 쿼리(Sub Query)   
 
(1) 설명
   하나의 SQL문장절에 부속된(포함된) 또 다른 SELECT문장 따라서, 두 번 질의를 해야 얻을 수 있는 결과를 한번의 질의로 해결이 가능케하는 쿼리  
 
(2) 용어 설명
    <1> MAIN-QUERY 또는 OUTER-QUERY
    <2> SUB-QUERY 또는 INNER-QUERY
   
(3) 특징
    <1> 괄호를 묶어야 한다.
    <2> 실행 순서는 대부분 SUB-QUERY가 먼저 수행되고, MAIN-QUERY가 실행된다.
    <3> SUB-QUERY는 MAN-QUERY의 다음 부분에 위치된다.
       - SELECT/DELECT/UPDATE 문장의 FROM절/WHERE절
       - INSERT문장의 INTO절
       - UPDATE문장의 SET절
    <4> SUB-QUERY는 ORDER BY절을 사용할 수 없다.
      ( 예외 : SELECT/DELETE/UPDATE문장의 FROM절 )


7, 단일행 SUB-QUERY ( 연산자 : =, <, >, <=, >= ) SUB-QUERY의 실행결과가 하나의 컬럼, 하나의 행만을 리턴해 주는 경우의 쿼리
      SUB> SELECT job FROM emp WHERE empno=7900;
      SQL> SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE empno=7900);
      error1> SELECT ename, sal FROM emp WHERE sal=(SELECT sal FROM emp WHERE deptno=10);
      error2> SELECT ename, sal FROM emp WHERE sal=(SELECT sal, comm FROM emp WHERE empno=7369);
      error3> SELECT sal FROM emp WHERE sal>AVG(sal);
      SQL> SELECT ename, sal FROM emp WHERE sal>(SELECT AVG(sal) FROM emp);
   
   
8. 복수행 SUB-QUERY
( 연산자 : IN, ANY, ALL[>:가장큰값, <:가장작은값, =:값이한개일때],  EXISTS )
SUB-QUERY의 실행결과가 여러개의 행을 리턴해주는 쿼리

SUB> SELECT sal FROM emp WHERE deptno=10;
SQL> SELECT ename, sal FROM emp WHERE sal IN(SELECT sal FROM emp WHERE deptno=10);
SQL> SELECT ename, sal FROM emp WHERE sal=ANY(SELECT sal FROM emp WHERE deptno=10);
SUB> SELECT sal FROM emp WHERE job='MANAGER';
SQL> SELECT job, sal FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE job='MANAGER');
SQL> SELECT job, sal FROM emp WHERE sal<ALL(SELECT sal FROM emp WHERE job='MANAGER');
SQL> SELECT job, sal FROM emp WHERE sal=ALL(SELECT sal FROM emp WHERE job='PRESIDENT');
SUB> SELECT * FROM emp WHERE deptno=10;
SQL> SELECT dname, deptno FROM dept WHERE EXISTS(SELECT * FROM emp WHERE deptno=10);
 

9. 복수컬럼 SUB-QUERY( 연산자 : IN )
==>SUB-QUERY의 실행결과가 여러개의 컬럼 AND 여러개의 행을 리턴해 주는 쿼리

SUB> SELECT sal, comm FROM emp WHERE deptno=30;
SQL> SELECT ename, deptno, comm FROM emp WHERE (sal, comm) IN (SELECT sal, comm FROM emp WHERE deptno=30);
SUB> SELECT sal, NVL(comm, -1) FROM emp WHERE deptno=30;
SQL> SELECT ename, deptno, NVL(comm, -1) FROM emp WHERE (sal, NVL(comm, -1)) IN (SELECT sal, NVL(comm, -1) FROM emp WHERE deptno=30);


10. 상호관련 SUB-QUERY
MAIN-QUERY절에 사용된 테이블이 SUB-QUERY절에 다시 재사용
error> SELECT AVG(sal) FROM emp e2 WHERE e2.deptno=e1.deptno;
SQL> SELECT e1.empno, e1.sal FROM emp e1 WHERE sal>(SELECT AVG(sal) FROM emp e2 WHERE e2.deptno=e1.deptno);



관련자료
http://blog.daum.net/jeromek/46
http://www.statwith.pe.kr/TABLE_JOIN/functions005.htm