[Oracle] join

 

테이블 join

• 두개 이상의 테이블을 연결하여(=) 하나의 테이블처럼 출력할 때 사용
• 등가 조인 
  
  • *(아래는 예시)
  • SELECT 테이블1.컬럼 ,  테이블2.컬럼 ... (선택할 테이블의 필드들)
  • FROM 테이블1, 테이블2  (사용할 테이블들)
  • WHERE 조인조건 (ex. 테이블1별칭.id = 테이블2별칭.id ) ('=' 을 조건으로 하는 조인)
  • AND 일반조건
    
    • from절에 테이블 별명을 사용할 경우, SQL문 전체에 별명으로 사용해야 함
• 비등가 조인
  • 부등호가 포함된 조인조건을 이용하는 조인 (조인 조건에 '=' 이 없는 경우)
• 자체 조인
  • 자기 참조를 하여, 동일 테이블 간의 조인 방법을 사용하는 것
  • *(아래는 예시)
  • SELECT 별명1.컬럼, 별명2.컬럼, ...
  • FROM 테이블 별명1, 테이블 별명2, ...
  • WHERE 조인 조건
  • AND 일반 조건
• 외부 조인
  • 보여주고 싶은쪽에 (+)을 이용
  • 두 테이블 간 조인 실행 시, 어느 한쪽의 열이 null이어도 강제로 출력하는 방식

-- from절에 여러 테이블 선언하기 (테이블을 하나인것처럼 합침)
-- 중복의 문제가 생김
SELECT *
FROM emp, DEPT
ORDER BY EMPNO


-- 테이블의 별칭 생성
SELECT *
FROM emp e1, emp e2;

SELECT *
FROM emp e, SALGRADE s --e,s 는 별칭
WHERE e.sal >= s.losal AND s.hisal <= e.sal

-- 사진 1-1
SELECT e.*, d.*
FROM emp e, dept d
WHERE e.DEPTNO = d.DEPTNO  --e 테이블과 d 테이블 내의 DEPTNO 값이 같은 경우를 한줄로 매치해줌



--외부조인
-- (+) 가 없는 것은 모든 내용을 출력 (null이어도 일단 출력은 함)
-- (+) 가 있는 것은 표시할 내용이 없으면 null로 표시
SELECT e1.empno, e1.ename, e1.MGR, e2.EMPNO, e2.ENAME, e2.MGR 
FROM emp e1, emp e2
WHERE e1.mgr = e2.EMPNO(+)
ORDER BY e1.EMPNO

1-1

 

 

 

 

표준 문법으로 배우는 조인

 

NATURAL JOIN

• 등가조인 중 하나
• 두 테이블 간에 컬럼이 동일한 컬럼과 값을 가졌다면, 해당 데이터들 열만이 모두 조회됨
  • 만약 특정 컬럼이 따로 표시되어있지 않은 경우,
  • 둘 중에 공통 컬럼과 값인 부분의 열만 한 테이블로 합쳐 조회됨
• *(아래는 예시)
  • SELECT 컬럼, 컬럼, ...
  • FROM 테이블1
  • NATURAL JOIN 테이블2
  • [NATURAL JOIN 테이블3] ...
  • WHERE 검색 조건;

 

JOIN ~ USING

• 자연 조인은 사용하는 테이블들의 동일한 이름, 형식이 두가지 이상인 경우 사용이 불가능 해짐.
• 자연 조인과는 다르게,
  • 같은 이름을 가진 컬럼들 중에서도 특정 컬럼을 따로 선택할 수 있음
  • 그 컬럼 내의 값이 두 테이블 모두 같은 값을 갖고있을때 불러와짐
• 따라서 이 조인문을 구사함
• *(아래는 예시)
  • SELECT 컬럼, 컬럼, ...
  • FROM 테이블1
  • JOIN 테이블2 USING(조인 컬럼)  //테이블2 내에서 조인컬럼이 동일한 것들
  • [JOIN 테이블3 USING(조인 컬럼)] ...
  • WHERE 검색 조건;

 

 

Join ~ on

• 등가 조인은 using 절, 비등가 조인은 on 절을 이용
• FROM 테이블명1
• JOIN 테이블명2 ON (조인 조건식)

 

 

 

join outer

-- 테이블 별로 방향 나누기
-- 왼쪽이 null이더라도 오른쪽은 꼭 출력해달라는 뜻 (dept 테이블이 오른쪽에 있으니 해당됨)
SELECT e.deptno, e.job, d.dname, d.loc
FROM emp e
	right OUTER JOIN dept d ON (e.DEPTNO  = d.DEPTNO)
    
-- 오른쪽이 null이더라도 왼쪽은 꼭 출력해달라는 뜻 
SELECT e.deptno, e.job, d.dname, d.loc
FROM emp e
	left OUTER JOIN dept d ON (e.DEPTNO  = d.DEPTNO)

 

 

총합 예제

-- Q1
-- sal이 2000초과인 사원들의 부서 정보, 사원정보를 출력하자
SELECT d.deptno, d.DNAME , e.empno, e.ename, e.sal
FROM emp e, dept d
WHERE e.sal > 2000
AND e.DEPTNO = d.DEPTNO -- 중복제거
ORDER BYdeptno




-- Q2
-- 부서별 평균 급여, 최대 급여, 최소 급여, 사원수 출력
SELECT
	d.deptno,
	d.dname,
	floor(avg(e.sal)) AS avg_sal,
	max(e.sal) AS avg_sal,
	min(e.sal) AS min_sal,
	count(*) AS cnt
FROM emp e, dept d
WHERE d.DEPTNO <= 30
AND e.DEPTNO = d.DEPTNO -- 중복제거
GROUP BY d.DEPTNO, d.dname
ORDER BY d.DEPTNO 


-- 위랑 같은 결과값
SELECT
	d.deptno,
	d.dname,
	trunc(avg(e.sal)) AS avg_sal,
	max(e.sal) AS avg_sal,
	min(e.sal) AS min_sal,
	count(*) AS cnt
FROM emp e
	LEFT OUTER JOIN dept d ON (e.DEPTNO = d.DEPTNO)
GROUP BY
	d.DEPTNO, d.DNAME
ORDER BY d.DEPTNO




-- Q3
-- 모든 부서, 사원 정보를 부서번호, 사원 이름순으로 정렬하여 출력
SELECT
	d.deptno,
	d.dname,
	e.empno,
	e.ename,
	e.job,
	e.sal
FROM emp e
	right OUTER JOIN dept d ON (e.DEPTNO = d.DEPTNO)
ORDER BY d.deptno, e.ename
	



-- Q4
-- 모든 부서, 사원, 급여 등급, 각 사원의 직속 상관의 정보를
-- 부서 번호, 사원 번호 순서로 정렬
SELECT *
FROM 

SELECT
	d.deptno,
	d.dname,
	e.empno,
	e.ename,
	e.mgr,
	e.sal,
	e.DEPTNO  AS deptno_1,
	s.losal AS losal,
	s.hisal AS hisal,
	s.grade AS grade,
	e2.mgr AS mgr_empno,
	e2.ename AS mgr_ename
FROM dept d
	left OUTER JOIN emp e ON (e.DEPTNO = d.DEPTNO) --중복 제거
	LEFT OUTER JOIN SALGRADE s ON (e.sal >= s.losal AND e.sal < s.hisal)
	LEFT OUTER JOIN emp e2 ON (e.mgr = e2.empno)
ORDER BY d.DEPTNO, e.EMPNO

 

 

 

서브쿼리

• 어떤 조건에 따라서 변할수있는 데이터의 값을 비교하는 것

-- jones씨와 같은 job을 갖고있는 사람을 모두 출력
-- 단일행
SELECT *
FROM emp
WHERE job = (
	SELECT job
	FROM emp
	WHERE ename='JONES')

	
    
-- 다중행
-- 부서별로 최대 급여 출력
SELECT max(sal) FROM emp
GROUP BY DEPTNO



-- in : 메인쿼리의 데이터가 서브쿼리의(in 뒤가 서브쿼리임)
-- 결과 중 하나라도 일치한다면 true
SELECT * FROM EMP
WHERE sal in (SELECT max(sal) FROM emp
	GROUP BY DEPTNO)
	
	
    
-- any, some 연산자
-- 서브쿼리의 결과가 둘 중 값 하나만 조건식에 맞아 떨어지면 출력 대상이 됨
SELECT * FROM EMP
WHERE sal > any (SELECT max(sal) FROM emp
	GROUP BY DEPTNO)

-- with절로 서브쿼리 만들기
-- 별칭으로 저장후 씀
WITH e10 AS (
	SELECT * FROM emp WHERE deptno = 10)
SELECT *
FROM
	e10,
	dept d
WHERE e10.deptno = d.DEPTNO 
	



-- emp의 모든 사원을 대상으로
-- 한명당 그 한명에 해당하는 부서의 최소 급여와 비교
SELECT *
FROM emp e1
WHERE sal > (
	SELECT min(sal)
	FROM emp e2
	WHERE e2.DEPTNO = e1.DEPTNO)
ORDER BY DEPTNO, sal;



-- select 안의 select 문 넣기
SELECT
	empno,
	deptno,
	(SELECT dname FROM dept d
	WHERE d.deptno = e.DEPTNO) sub_dname
FROM emp e

 

 

 

 

출처&참고

https://keep-cool.tistory.com/41