JOIN

JOIN

PK (Primary Key) 

부모값에서 변하면 안되는 KEY. 보통은 테이블을 봤을때 nullable(NO). 즉 반드시 값이 들어가야하는 표시가 있는 것중에 있다.

FK (Foreign Key)

부모의 PK이 자식의 일반 Column에 전이되었을때. 항상 PK를 참조한다

주로 테이블을 JOIN할때 부모의 PK, 자식의 FK를 조인하는 편이다. 포린키 있는쪽이 자식이라고 생각하자!

 

 

 

INNER JOIN

가장 많이 사용되는 조인문. 테이블간에 연결 조건을 모두 만족하는 행을 검색시 사용

자식테이블을 기준으로 모든 레코드수를 가져온다. ex)자식레코드수가 10이면, inner join의 수도 10개

일치하는것은 다 가지고 온다. 양쪽에서 아무데서나 가져온다.
만약 부모,자식테이블 둘다에 중복된 이름이라면 어디의 컬럼명인지 기술해줘야한다 ex) d.deptno 

SELECT 컬럼명, 컬럼명 ..
FROM 테이블명1(별칭)   [INNER] JOIN  테이블명2(별칭)
ON 테이블명1.PK 컬럼명[부모]  =  테이블명2.FK 컬럼명[자식]

 

ex) ....emp : 부모      dept : 자식

// ON 이하 ~ ON e.deptno = d.deptno; 가 조건문이다.
SELECT ename,dname,d.deptno,job FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

 

deptno 는 emp, dept 전부에 있기때문에 구분해주기 위해 d.deptno라고 붙여주었으며,
이 쿼리문을 통해 emp테이블에 dept테이블의 정보를 함께 가지고 출력할수있게 된다. 

 

 

위의 조인문에 WHERE을 붙여서 조건을 줄수도있고, 조인문 바로 뒤에 AND 로 묶은후 직접적으로 조건을 줄 수도있다

SELECT ename,dname,d.deptno,job FROM emp e INNER JOIN dept d ON e.deptno=d.deptno
WHERE sal > 3000;

=

SELECT ename,dname,d.deptno,job FROM emp e INNER JOIN dept d ON e.deptno=d.deptno AND sal > 3000;

 

 

 

 

 

 

OUTER JOIN

두 테이블에 조인 조건이 정확히 일치 하지 않더라도  어느 한쪽 테이블에서 결과값을 모두 가져온다

관계형데이터베이스에서는 부모에 없는 데이터를 입력할수없다.(full outer join이 그래서 의미없음)
단,OUTER JOIN을 이용하면 자식에는 없지만 부모에 있는 데이터는 입력할수있다.
따라서 명시되는 자식값보다 null이 반환된 레코드수가 있다면 그걸로 데이터값을 조회하기에 좋다

 

* OUTER JOIN은 반드시 방향을 명시하여야한다
자식을 기준으로 OUTER JOIN하면 의미가없다
=부모를 기준으로 OUTER JOIN한다.

만약 부모가 왼쪽에 있다면 LEFT, 오른쪽에 위치한다면 RIGHT이다.

LEFT OUTER JOIN : 왼쪽 기준으로 왼쪽테이블에서 다 가지고 온다

RIGHT OUTER JOIN : 오른쪽 기준으로 오른쪽에서 다 가지고 온다

FULL OUTER JOIN : 관계형데이터에서는 의미없음

 

--자식이 emp, 부모가 dept 인경우 
SELECT empno,ename,sal,d.deptno,dname FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno ;


//충원이 되지않은 부서값(부모값)이 있기때문에 자식수는 14개지만, 
조인문을 통해 반환되는건 15개의 레코드가 반환된다. 
따라서 null값인 pk를 이용해 부서명등을 알아낼수 있다. 
위의 조인문을 출력했을시 EMPNO가 NULL값인 레코드가 반환되므로 그걸 이용한다. 
SELECT d.deptno, dname, loc FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno WHERE empno IS NULL; 
--> deptno=40 인 부서에 인원이 충원되지 않았음을 알 수있다.

 

 

 

 

SELF JOIN

자기자신과 자기자신을 JOIN (결국은 테이블이 한개)

같은 테이블안에 이미 정보가 있다면 셀프조인을 진행하면 된다 (예제>emp의 직원/매니저 매칭보기)

 

 

SELECT E1.ename||'의 매니저는 '||E2.ename||'입니다' FROM EMP E1 JOIN EMP E2 ON E1.MGR = E2.EMPNO;
--같은 테이블을 조인하면서 MGR과 EMPNO를 매칭시켜 
--매니저의 이름을 찾는 쿼리문이다.

 

 

 

 

SUBQUERY 서브쿼리

• 다른 하나의 SQL문안에 기술된 SELECT문을 말한다. 반드시 괄호로 묶어야함
• 서브쿼리만을 단독 실행시 실행이 되어야한다
• 두 종류의 연산자가 서브쿼리에 사용된다
  1) (단일행 연산자) : >, <, =, >=, <=, <>, != 
  2) (복수행 연산자) : IN(= or 의미), NOT IN
• 서브쿼리는 연산자 오른쪽에 기술되어야한다
• 단일행 서브쿼리 -> 단일행 연산자 / 다중행 서브쿼리 -> 복수행 연산자
  값이 항상 동일하게 단일 = 단일 인지 꼭 확인하자. 아니면 에러 뜬다
• SELECT, FROM, WHERE절등에 위치할 수 있다.

SELECT *
FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp) //쿼리안의 쿼리, ()로 감싸짐,
|| WHERE sal IN (SELECT sal FROM emp);

 

//서브쿼리 예제

//서브쿼리 : 각 직업에서 최고연봉을 받는 사람이 누구인지 찾는 쿼리문

SELECT ename,sal,job FROM emp
WHERE (sal,job) IN (SELECT MAX(sal),job FROM EMP GROUP BY job);

 

 

//서브쿼리 : 최소연봉을 받는 사람을 서브쿼리형태로 

SELECT * FROM emp WHERE sal = (SELECT MIN(sal) FROM emp);

 

 

//서브쿼리 : 가장최소연봉을 받는사람을 1.5배 연봉인상한다면?

SELECT ENAME,SAL,JOB,SAL*1.5"인상된연봉" FROM EMP WHERE SAL = (SELECT MIN(SAL) FROM EMP);

 

 

// 서브쿼리(복수연산자) : 최소연봉, 최대연봉을 받는 사람이 누군지 ?

SELECT ename,sal,job FROM emp
WHERE sal IN ((SELECT MAX(sal) FROM emp),(SELECT MIN(sal) FROM emp))
ORDER BY sal;

 

 

// 각 직업별 최대연봉을 받는 사람과 직업정보 (JOIN이 들어감)

SELECT ename,sal,d.deptno,hiredate,dname 
from emp e JOIN dept d ON e.deptno=d.deptno
WHERE (d.deptno,sal) IN (SELECT deptno, MAX(sal) FROM emp GROUP BY deptno);

 

 

 

 

TOP 쿼리

얻어진 결과에서(정렬될것) 위에서부터 순서대로 몇 개만 가져오는 경우에 사용.

그러나 limit~ top~ 같은 메소드가 mySQL이나 다른데는 있는데, 오라클에는 없어서 서브쿼리를 이용해 만든다

 

데이터가 입력된 순서대로 부여되거나, 서브쿼리에 의해 생성된 테이블(FROM절등) 레코드 순서에 의해

행번호(ROWNUM)가 내부적으로 부여된다.

 

 

 

서브쿼리 이용

(=위에서부터 순차적으로 가져올때 사용)

 

Select * From (
  Select * From 테이블명 Order By PK컬럼명 Desc
)
Where rownum <= 3 ORDER BY PK컬럼명 Desc;

예시 ) 연봉순으로 나열후(오름차순) 상위 5명을 골라내는 쿼리문

SELECT * FROM (SELECT * FROM emp ORDER BY sal DESC) WHERE ROWNUM <= 5;

 

 

특정구간에 있는 레코드사용

(=특정 구간을 뽑아내고 싶다면 이렇게 해야한다)

//예시 : 연봉순으로 정렬후, 5~10번째 있는 사람들을 출력하라 
SELECT * FROM (SELECT t.*,ROWNUM r FROM(SELECT * FROM emp ORDER BY sal) t)
WHERE r BETWEEN 5 AND 10; 

 

1. 가장 안쪽에 들어갈 쿼리문 : 특정컬럼(보통 PK)를 기준으로 정렬(ORDER BY)후 별칭부여
   ex)  (SELECT * FROM emp ORDER BY sal) t  //t로 별칭한 정렬된 쿼리문
2. <1번째 쿼리문>앞에 작성하는 두번째 쿼리문 : 별칭.* , ROWNUM 별칭 부여
   ex) (SELECT t.*, ROWNUM r FROM ( 1번째 쿼리문 ) t ) 
3. 구간적어주기 : 최종 WHERE (rownum 별칭) BETWEEN 5 AND 10; 으로 원하는 구간을 부여
   ex) SELECT * FROM (두번째 쿼리문 r FROM ( 1번째쿼리문) t ) WHERE r BETWEEN 5 AND 10 ;
   //5~10까지의 구간을 출력하는 쿼리문

예시 1) 부서번호 30인사람들을 연봉순으로 나열한후, 이름, 연봉, 직업, 부서번호, 부서명을 출력하되 2~4번째 있는 사람만 출력하라 (JOIN 필요)

SELECT ename,sal,job,deptno,dname
FROM (SELECT t.*,ROWNUM r FROM(SELECT E.*,dname FROM emp e JOIN dept d ON e.deptno=d.deptno 
WHERE d.deptno=30 ORDER BY sal DESC)t)
WHERE r BETWEEN 2 AND 4;