오라클[sql] JOIN /
JOIN
▶ 조인 : 테이블간의 기준값을 정하여 2개 이상의 테이블을 하나의 테이블로 보이게끔 작성하는 기술
▶ 하나의 테이블에서 있던 값을 가져오는 작업
CREATE TABLE dept
AS SELECT * FROM scott.dept;
SELECT * FROM emp; -- 사원 정보 테이블
SELECT * FROM dept; -- 부서 정보 테이블
-- 부서 정보가 변경이 되었을 경우 부서 테이블만 변경하도록 수정
-- 사원 정보가 변경이 되어도 부서정보와는 무관하게 변경하도록 설계
-- 데이터가 여러곳에 흩어져있기 때문에 사용자가 원하는 데이터를 모두 찾으려고 하면 여러테이블을 모두 조회
▶ join 조인 기술 : 여러 테이블의 흩어져 있는 정보중에 필요한 정보만 가지고 와서 가상의 테이블처럼 결과를 보여주는 기술
▶ 사용자 별의 부서이름
SELECT * FROM emp, dept ORDER BY empno, dname; -- 2개의 테이블을 호출 : 56개의 행이 출력
-- emp 테이블의 한 행에 dept 테이블의 데이터가 모두 연결
-- emp : 14개의 dept : 4개 >> emp 한줄에 dept 4줄 연결
-- 테이블 2개를 출력하려고 하는데 2개의 테이블에 대한 정보를 정확하게 작성하지 않았기 때문에
-- DB가 아무의미없이 연결
>> 두 테이블의 공통적인 부분을 명시하여서 올바른 조인을 해야함 > WHERE 조건절을 통해
-- A테이블과 B테이블간에 기준값을 정하여 하나이상의 테이블을 합치는 기술 >> 내부조인 외부조인
-- 조인 대상이 되는 테이블의 공통적으로 존재하는 컬럼들과 동일한 조건(=) 연결 > 원하는 데이터를 출력
▶ 내부조인 : 서로 공통된 컬럼에 같은 값을 가질 경우에만 출력
▶ 외부조인 : 서로 공통된 컬럼에 다른 값을 가지더라도 출력
ORA-00918 에러
▶ SELECT deptno FROM emp, dept WHERE emp.deptno = dept.deptno; -- 에러
-- ORA-00918: 열의 정의가 애매합니다
-- deptno : 둘 다 포함되어있는 컬럼
-- 어떤테이블에 작성된 deptno 라는 것을 지칭해줘야함
-- > 테이블명.컬럼명으로 호출 > 테이블명이 점점 길어진다면 데이터 호출할때 문장이 너무 길어집니다.
-- > 테이블명을 별칭으로 선언
▶ SELECT emp.deptno, dept.deptno FROM emp, dept WHERE emp.deptno = dept.deptno;
▶ SELECT e.deptno, d.deptno FROM emp e, dept d WHERE e.deptno = d.deptno;
▶ SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno;
▶ SELECT e.empno, e.ename, e.deptno, d.dname, d.loc AS "지역"
FROM emp e, dept d -- emp 테이블의 별칭 e / dept 테이블의 별칭 d : 쿼리문내에서만 사용
WHERE e.deptno = d.deptno
AND e.deptno = 10;
▶ 테이블의 별칭 작성 권장
▶ WHERE 문에서 가장 우선적인 조건식으로 작성, 다른 조건은 2번째 조건식으로 작성
내부조인 / INER JOIN / Natural JOIN
▶ 내부조인 : 조인 대상인 두 테이블에 있는 컬럼중에 조인기준으로 선정한 컬럼 내에 같은 값을 가진 행만 조회하는 기법
표준화 선언방법 / ANSI 조인
SELECT e.empno, e.ename, d.deptno, d.dname
FROM emp e INNER JOIN dept d
ON e.deptno = d.deptno;
▶ WHERE 를 뒤에 쓸 수 있어서 좀더 유연하게 조건을 쓸 수 있음
USING
▶ 조인 대상 테이블끼리 같은 컬럼명을 사용할 경우에만 사용
▶ USING에 쓴 컬럼명은 더이상 테이블명을 붙이지 않아도 된다 > 컬럼을 합쳐준다라고 생각
SELECT e.empno, e.ename, deptno, d.dname
FROM emp e INNER JOIN dept d
USING (deptno);
Natural JOIN
▶ 동일한 타입, 동일한 컬럼명 > Natural JOIN 표현 가능
SELECT e.empno, e.ename, deptno, d.dname
FROM emp e NATURAL JOIN dept d;
공통적인 컬럼의 값을 가지고 있지 않을 경우 / 가지고 있을 경우
INSERT INTO emp(empno, ename)
VALUES (9999, ' 홍길동');
INSERT INTO emp(empno, ename, deptno)
VALUES (1111, ' 고길동', 40);
▶ 홍길동 데이터를 추가 : deptno 가 없음 (부서번호 미존재) > 내부조인하면 결과 > deptno 없으니까 출력 안됨
▶ 고길동 데이터를 추가 : deptno 40번 > 내부조인 결과 > 연결할 테이블에 deptno 값이 있으면 출력됨
SELECT e.empno, e.ename, e.deptno, d.dname, d.loc AS "지역"
FROM emp e, dept d
WHERE e.deptno = d.deptno;
외부조인 / OUTER JOIN
▶ 외부조인 : 내부조인의 결과 + 기준테이블에만 있는 데이터도 포함하여 조회
▶ 내부조인을 하면 두 테이블내에 공통된 값이 없다면 결과가 줄어듭니다. ( 데이터를 모두 출력하지 않기 때문에 훼손)
▶ 한 테이블에 공통된 컬럼값이 없더라도(null 이라도) 검색
▶ (+) : 해당데이터가 존재하지 않더라도 무시하고 조인을 참여해라 라는 뜻
▶ 기준이 아닌 테이블에 데이터가 없어도 기준테이블의 데이터는 모두 출력하는 것이 외부조인, (+) 위치에 따라 기준테이블이 설정
▶ 내부조인에서는 기준이 되는 컬럼은 null 허용되지 않는데
▶ 외부조인에서는 기준이 되는 컬럼에 null 허용이 됩니다. > (+) 쓴 테이블에는 데이터가 null 이 허용된다 생각.
SELECT e.empno, e.ename, e.deptno, d.dname, d.loc AS "지역"
FROM emp e, dept d
WHERE e.deptno = d.deptno(+);
▶ WHERE 왼쪽.컬럼명 = 오른쪽.컬럼명 >> (+) 기호를 이용하여 조인의 기준(방향) 선택
▶ LEFT : 왼쪽테이블에는 있고, 오른쪽테이블에 없는 값을 출력
▶ RIGHT : 오른쪽테이블에는 있고, 왼쪽테이블에는 없는 값을 출력
▶ FULL : 둘다 상관없이 없는 값을 출력
표준화 선언방법 / ANSI 조인
▶ LEFT OUTER JOIN
▶ 왼쪽테이블이 기준테이블(모든데이터 출력), (+) 반대로 작성
SELECT e.empno, e.ename, d.deptno, d.dname
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno = d.deptno;
▶DELETE FROM emp WHERE empno = 1111; -- 고길동(부서번호 40) 삭제
SELECT e.empno, e.ename, d.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno;
▶ dept 테이블은 모두 출력 됨
▶ RIGHT OUTER JOIN
▶ JOIN 오른쪽에 작성된 테이블이 기준 테이블로 선언(모든테이터 출력), 왼쪽테이블은 내부조인된 데이터만 출력
SELECT e.empno, e.ename, d.deptno, d.dname
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno;
▶ 이런 문법은 없음 > 양쪽에 (+) 쓰는 경우 >> 에러
SELECT e.empno, e.ename, d.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno(+);
▶현재 문법만 가능 > 양쪽다 null 인경우도 다 출력됨
SELECT e.empno, e.ename, d.deptno, d.dname
FROM emp e FUll OUTER JOIN dept d
ON e.deptno = d.deptno;
