-Equl Join(동등조인)
JOIN : 둘 이상의 테이블을 연결하여 데이터를 검색하는 방법
보통 둘 이상의 행들의 공통된 값 PRIMARY KEY 및 FOREIGN KEY 값을 사용하여 조인
두개의 테이블을 SELECT 문장 안에서 조인하려면 적어도 하나의 컬럼이 그 두 테이블 사이에서 공유되어야함.
Cartesian Product(카티션 곱) : 검색하고자 했던 데이터뿐 아니라 조인에 사용된 테이블들의 모든 데이터가 반환되는 현상
1.조인 조건을 정의하지 않았을 경우
2.조인 조건이 잘못된 경우
SELECT * FROM emp,dept; --12개의 컬럼과 4개의 컬럼이 합쳐지면서 48개의 반복된 컬럼들을 가진 테이블이 생김.
[Oracle 전용]
동등 조인(Equi Join) : 조건절에 = 조건에 의하여 조인이 이루어짐.
--WHERE 조건에 항상 이퀄 (=)을 쓴다고 생각해도 된다.
SELECT * FROM emp,dept
WHERE emp.deptno = dept.deptno; -- .은 하위요소로 접근한다 라는 뜻(JAVA와 비슷하게 .을 찍어서 접근함)
SELECT emp.ename,dept.dname FROM emp,dept
WHERE emp.deptno = dept.deptno;
테이블에 알리아스 부여하기
SELECT e.ename, d.dname FROM emp e ,dept d
WHERE e.deptno = d.deptno;
컬럼명을 호출할 때 테이블명 또는 테이블 알리아스를 생략 (알리아스 부르는걸 생략하는것)
SELECT ename,dname FROM emp e, dept d --ename은 emp에만 있고 dname은 dept에만 있으므로 생략가능. dept같이 둘 다 있는 경우는 알리아스를 사용해야됨 생략불가능.
WHERE e.deptno = d.deptno;
추가적인 조건 명시하기
ALLEN이 근무하는 부서의 이름과 사원 이름을 같이 출력하시오.
SELECT e.ename,d.dname FROM emp e, dept d
WHERE e.deptno = d.deptno AND e.ename = 'ALLEN';
-- 조인조건 추가적인 조건
3000이상 4000이하 급여를 받는 사원의 이름과 급여, 부서명을 출력하시오.
SELECT e.ename, e.sal,d.dname FROM emp e, dept d
WHERE e.deptno = d.deptno AND e.sal BETWEEN 3000 AND 4000 ;
-Non Equi Join(비동등 조인)
비동등 조인(Non Equi Join) : 테이블이 어떤 column도 join할 테이블의 column에 일치하지 않을 때 사용하고
조인 조건은 동등(=) 이외의 연산자를 갖음.
(between and, is null in)
--비동등 조인은 무조건 같은 종류의 데이터여야 된다.
사원이름,급여,급여등급 구하기 (emp,salgrade 테이블 이용);
SELECT e.ename, e.sal , s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;
-SELF JOIN
SELF JOIN : 동일한 테이블 조인
사원 이름과 해당 사원의 관리자 이름 구하기(관리자가 없는 사원 제외)
SELECT e.ename 사원이름, m.ename 관리자이름 FROM emp e, emp m
WHERE e.mgr = m.empno;
--관리자가 없는 KING 사원은 결과에 나오지않음 (누락된 행이 발생)
-Outer JOIN(외부조인)
--↑ 위에 self join 중 누락된 행을 보이고 싶을 때 사용하는 방법
외부 조인(Outer JOIN) : Equi Join 문장들의 한 가지 제약점은 그것들이
조인을 생성하려 하는 두 개의 테이블의 두 개 컬럼에서 공통된 값이
없다면 테이블로부터 데이터를 반환하지 않는다는 것.
정상적으로 조인 조건을 만족하지 못하는 행들을 보이기 위해 Outer Join을 사용
누락된 행을 표시하기 위해서 누락된 행이 있는 테이블의 반대 테이블의 조인 조건에 + 를 명시
SELECT DISTINCT(e.deptno), d.deptno
FROM emp e, dept d
WHERE e.deptno (+)= d.deptno; --누락은 dept d 에서 되었고 (+)는 e.deptno에다가 함
사원이름과 해당 사원의 관리자 이름 구하기 (관리자가 없는 사원도 표시)
SELECT e.ename 사원이름, m.ename 관리자이름
FROM emp e , emp m
WHERE e.mgr= m.empno (+) ; --emp e에 있는 사원이름 KING이 누락 된 것임으로 반대인 m.empno에 (+)를 명시한다.
-실습문제
[실습문제]
1.모든 사원의 이름,부서번호,부서이름을 표시하시오.(emp,dept)
SELECT e.ename,d.deptno, d.dname FROM emp e ,dept d
WHERE e.deptno = d.deptno;
2.업무가 MANAGER인 사원의 정보를 이름,업무,부서명,근무지 순으로 출력하시오.(emp,dept)
SELECT e.ename,e.job,d.dname,d.loc FROM emp e, dept d
WHERE e.deptno = d.deptno AND e.job = 'MANAGER';
3.커미션을 받고 급여가 1,600이상인 사원의 사원이름,급여,부서명,근무지를 출력하시오.
SELECT e.ename, e.sal, d.dname, d.loc FROM emp e, dept d
WHERE e.deptno = d.deptno AND e.comm IS NOT null AND e.sal >=1600;
4.근무지가 CHICAGO인 모든 사원의 이름,업무,부서번호 및 부서이름을 표시하시오.
SELECT e.ename,e.job,d.deptno,d.dname FROM emp e , dept d
WHERE e.deptno = d.deptno AND d.loc = 'CHICAGO';
5.근무지별로 근무하는 사원의 수가 5명 이하인 경우, 인원이 적은 도시 순으로 정렬하시오.(근무인원이 0명인 곳도 표시)
SELECT d.loc,COUNT(e.empno) emp_member FROM emp e, dept d
WHERE e.deptno(+) = d.deptno GROUP BY d.loc
HAVING COUNT(e.empno) <=5 -- 알리아스가 불가능
ORDER BY emp_member ASC ;
6.사원의 이름 및 사원 번호를 관리자의 이름과 관리자 번호와 함께 표시하고 각각의 열레이블은
employee,emp#,manager,mgr# 로 지정하시오(관리자가 없는 사원 미출력)
SELECT e.ename employee , e.empno "emp#" , m.ename manager , m.empno "mgr#" FROM emp e , emp m
WHERE e.mgr = m.empno;
7.관리자보다 먼저 입사한 모든 사원의 이름 및 입사일을 관리자의 이름 및 입사일과 함께 표시하고 열 레이블을 각각
employee,emp hired, manager,mgr hired로 지정하시오.(관리자가 없는 사원 미출력)
SELECT e.ename employee,e.hiredate "emp hired", m.ename manager, m.hiredate "mgr hired" FROM emp e , emp m
WHERE e.mgr = m.empno AND m.hiredate >e.hiredate;
- Inner Join(내부조인)
[표준SQL]
내부 조인(Inner Join)
SELECT emp.ename ,dept.deptno
FROM emp INNER JOIN dept --Oracle에선 ,(쉼표)를 썼음
ON emp.deptno = dept.deptno; --Oracle에선 WHERE을 썼음
SELECT emp.ename ,dept.deptno
FROM emp JOIN dept --INNER라고 쓰는걸 생략가능
ON emp.deptno = dept.deptno;
알리아스를 사용한 내부 조인
SELECT e.ename, d.dname
FROM emp e JOIN dept d
ON e.deptno = d.deptno;
두개의 테이블의 공통 컬럼은 알리아스를 생략할 수 없음.
SELECT ename, dname,d.deptno --ename과 dname에는 테이블에 따로 있기 때문에 알리아스를 안써도 되지만 deptno는 둘 다 있기 때문에 둘 중 하나의 테이블에서만 써야됨으로 명시를 해줘야됨.
FROM emp e JOIN dept d
ON e.deptno = d.deptno;
ON절은 JOIN 조건을 명시하고 WHERE에 부가 조건을 명시
SELECT e.ename , d.dname
FROM emp e JOIN dept d
ON e.deptno = d.deptno --JOIN 조건
WHERE e.ename = 'ALLEN'; --부가 조건
만약 조인 조건에 사용된 컬럼의 이름이 같다면 다음과 같이
USING 절을 사용하여 조인 조건을 정의할 수 있음
SELECT *
FROM emp e JOIN dept d
ON e.deptno = d.deptno;--deptno행이 deptno와 deptno_1 까지 2개가 나온다.
SELECT *
FROM emp e JOIN dept d
USING (deptno); --USING절을 쓰면 depono행이 2개가 아니라 1개로 나옴
[주의] USING(컬럼명) USING 절에 명시한 컬럼명을 호출할 때는 테이블명 또는 알리아스를 명시해서 호출불가
SELECT e.ename,d.deptno,d.dname--에러 d.deptno는 둘이 하나로 합쳐졌기 떄문에 에러남.
FROM emp e JOIN dept d
USING (deptno);
SELECT e.ename,deptno,d.dname --에러안나는 코드
FROM emp e JOIN dept d
USING (deptno);
SELECT ename,deptno,dname --알리아스 자체를 안넣어도됨.
FROM emp JOIN dept --알리아스 자체를 안넣어도됨.
USING(deptno);
SELECT e.ename, deptno
FROM emp e JOIN dept d
USING (deptno)
WHERE e.ename = 'ALLEN';
- Self Join
SELF JOIN
사원이름과 해당 사원의 관리가 이름 구하기(관리자가 없는 사원은 제외)
SELECT e.ename name, m.ename manager_name
FROM emp e JOIN emp m
ON e.mgr = m.empno; --서로 명칭이 다르기 떄문에 USING 사용불가
- Outer Join
외부조인(Outer Join)
누락된 행의 방향 표시
SELECT DISTINCT(e.deptno), d.deptno
FROM emp e RIGHT OUTER JOIN dept d --누락된 행의 방향 표시 (오른쪽의 값이 누락되었기 떄문에)
ON e.deptno = d.deptno;
사원이름과 해당 사원의 관리자 이름 구하기(관리자 없는 사원도 표시)
SELECT e.ename name, m.ename manager_name
FROM emp e LEFT OUTER JOIN emp m--누락된 행의 방향 표시 (왼쪽의 값이 누락되었기 떄문에)
ON e.mgr = m.empno;
- 실습문제
[실습문제]
1.모든 사원의 이름.부서번호,부서이름,근무지를 표시하시오.(emp,dept)
SELECT e.ename, e.deptno, d.dname , d.loc
FROM emp e JOIN dept d
ON e.deptno = d.deptno;
SELECT e.ename, deptno, d.dname , d.loc
FROM emp e JOIN dept d
USING (deptno);
2.업무가 SALESMAN인 사원의 정보를 이름,업무,부서명,근무지 순으로 출력하시오.(emp,dept)
SELECT e.ename , e.job , d.dname,d.loc
FROM emp e JOIN dept d
ON e.deptno = d.deptno
WHERE e.job = 'SALESMAN';
3.커미션이 책정된 사원들의 사원번호,이름,연봉,급여+커미션,급여등급을 출력하는데 각각의 컬럼명을
"사원번호","이름","연봉","실급여","급여등급"으로 출력하시오. 단 커미션이 null인 것은 제외하고 출력하시오.(emp,salgrade, 실급여로 급여등급 구하기)
SELECT e.empno AS "사원번호", e.ename AS "이름", e.sal*12 AS "연봉",(e.sal + NVL(e.comm, 0)) AS "실급여", s.grade AS "급여등급"
FROM emp e JOIN salgrade s
ON ((e.sal + NVL(e.comm, 0)) BETWEEN s.losal AND s.hisal)
WHERE e.comm IS NOT null; --쓰지않아도 같은 값이 나옴
SELECT e.empno AS "사원번호", e.ename AS "이름", e.sal*12 AS "연봉",e.sal + e.comm AS "실급여", s.grade AS "급여등급"
FROM emp e JOIN salgrade s
ON e.sal+e.comm BETWEEN s.losal AND s.hisal;
4.10번부서에서 근무하는 사원들의 부서번호,부서이름,사원이름,월급,급여등급을 출력하시오.(emp,dept,salgrade)
[Oracle 전용]
SELECT d.deptno, d.dname, e.ename,e.sal,s.grade
FROM emp e , dept d, salgrade s
WHERE e.deptno = d.deptno
AND e.sal BETWEEN s.losal AND s.hisal
AND e.deptno =10;
[표준 SQL]
SELECT d.deptno, d.dname, e.ename,e.sal,s.grade
FROM emp e JOIN dept d
ON e.deptno = d.deptno
JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal
WHERE e.deptno = 10;
