Join
- 여러 테이블의 데이터를 이용해서 결과를 만드는 것을 조인이라 한다.
- 어떤 테이블의 값을 기준으로 다른 테이블에 있는 row의 컬럼을 찾아오는 것을 의미
- 일반적인경우 pk(primary key : 기본키)와 fk(foreign key : 외래키)의 관계에 의해서 조인이 성립됨
- from절에 모든 테이블을 정의
- 조인을 하기 위해서 조인조건을 정의해야 한다.
- 이는 어떤 컬럼을 기준으로 비교해야하는지 명시하지 않으면 테이블의 모든 레코드가 대응되어 출력된다.
이를 카티션 프로덕트(카티션곱)라 한다. - 컬럼의 모호성을 피하기 위해서 테이블을 함께 명시한다.
- 테이블.컬럼명
- 테이블에 alias를 정의하고 사용할 수 있다.
- 조인조건은 기본키와 외래키 관계를 갖는 컬럼을 이용해서 정의(연산자로 정의)
- 조인조건은 where절에 정의
- where절에 조건으로 사용하고 select절에 사용하지 않아도 조인조건을 정의해야한다.
- 조인조건과 다른조건은 and조건이어야 한다.
- 직접적으로 사용하고 있지 않은 테이블이라도 결과를 만들기 위해서 필요한데이터들이 연관성이 있으면 모두 조인 조건으로 정의해야한다
- 모든 조인조건은 and조건이어야 한다.
- 직접적으로 사용하고 있지 않은 테이블이라도 결과를 만들기 위해서 필요한 데이터들이 연관성이 있으면 모두 조인조건으로 정의해야 한다.
조인연습 미션1
- 부서별 인원수를 출력하세요
[출력형식]
부서명 인원수
SQL> select d.department_name,count(e.employee_id)
from employees e, departments d
where e.department_id=d.department_id
group by d.department_name;
- mysql에선 ||사용못함 거기선 concat사용
- select concat(concat(concat(concat(concat(first_name,' '), last_name),'의 연봉은 '),salary),' 입니다.' ) as 결과
concat 버전
SQL> select first_name||' '||last_name||'의 연봉은'||salary||'입니다.'as 결과
from employees e, departments d
where e.department_id=d.department_id and
and department_name='IT'
order by salary;
SQL> select employee_id,first_name,job_title,department_name
from employees e, departments d, locations l, jobs j
where e.department_id=d.department_id and
d.location_id = l.location_id and
e.job_id =j.job_id
and l.city='Seattle';
조인연습미션2
select job_title job, sum(salary) 급여
from employees e, jobs j
where j.job_id=e.job_id
and job_title not like '%Representative%'
group by job_title
having sum(salary)>30000
order by sum(salary);->job_title not like '%Representative%' 이부분 다시보기.
select d.department_name 부서명, count(e.employee_id) 인원수
from employees e, departments d
where e.department_id=d.department_id
and to_char(hire_date,'YYYY')<2005
group by d.department_name;-
hire_date<'2005/01/01' 이렇게 표현해도됨
-
group by에 department_id하나만 명시할시 select절에도 department_id만 쓸 수 있음. select절에 쓰는 건 무조건 group by로 묶여야 하므로 두 개를 group by절에 추가해야함.
select e.department_id 부서번호, d.department_name 부서명, count(e.employee_id) 인원수,
max(e.salary) 최고급여,min(e.salary) 최저급여, trunc(avg(e.salary)) 평균급여, sum(e.salary) 급여총액
from departments d, employees e
where e.department_id=d.department_id
group by e.department_id, d.department_name
having count(e.employee_id)>=3
order by 인원수 desc;insert는 원본데이터 바꾸는 데이터 조작어 DML-> 테이블에 자료추가하려고..
SQL> conn scott/tiger
Connected.
SQL> set linesize 300;
SQL> set pagesize 100;
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 83/01/12 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
14 rows selected.
SQL> insert into emp values(7777,'RM',null,null,sysdate,500,null,null);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 83/01/12 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
7777 RM 24/04/09 500
15 rows selected.
SQL> insert into emp values(8888,'슈가',null,null,sysdate,500,null,null);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 83/01/12 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
7777 RM 24/04/09 500
8888 슈가 24/04/09 500
16 rows selected.
[조인종류-오라클]
1. Equi JOIN : 등가조인
- 여러 테이블에서 정확하게 일치하는 값이 있는 경우에만 찾아온다.
- = 연산자를 이용해서 조인조건을 정의
- 기본키와 외래키 관계의 키를 이용해서 조인한다.
- inner join
SQL> select d.dname, e.ename
2 from emp e,dept d
3 where e.deptno = d.deptno;
DNAME ENAME
---------------------------- --------------------
RESEARCH SMITH
SALES ALLEN
SALES WARD
RESEARCH JONES
SALES MARTIN
SALES BLAKE
ACCOUNTING CLARK
RESEARCH SCOTT
ACCOUNTING KING
SALES TURNER
RESEARCH ADAMS
SALES JAMES
RESEARCH FORD
ACCOUNTING MILLER
14 rows selected.
=> 새로 추가한 RM,슈가는 deptno가 없어서 출력되지않는모습
2. Outer JOIN : 외부조인
- join조건을 만족하지 않는 경우에도 모든 데이터를 다 볼 수 있는 조인
- (+)연산자를 조인조건의 컬럼 뒤에 추가해서 사용
->확장하고 싶은 쪽의 반대편에 (+) 붙이기 - 조인조건에 null이 있어도 결과를 보려고 할 때 사용하는 조인방식
1) Left Outer Join
- 왼쪽 테이블의 모든 행을 출력하고 오른쪽 테이블에서 조건에 만족하는 값을 출력
- 만약에 오른쪽 테이블에 값이 없으면 null로 채운다.
- 오른쪽에 (+)표시한다.
SQL> select ename,dname
2 from emp e, dept d
3 where e.deptno=d.deptno(+);
ENAME DNAME
-------------------- ----------------------------
MILLER ACCOUNTING
KING ACCOUNTING
CLARK ACCOUNTING
FORD RESEARCH
ADAMS RESEARCH
SCOTT RESEARCH
JONES RESEARCH
SMITH RESEARCH
JAMES SALES
TURNER SALES
BLAKE SALES
MARTIN SALES
WARD SALES
ALLEN SALES
슈가
RM
16 rows selected.
2) Right Outer Join
- 오른쪽 테이블의 모든 행을 출력하고 왼쪽 테이블에서 조건에 만족하는 값을 출력
- 만약에 왼쪽 테이블에 값이 없으면 null로 채운다.
- 왼쪽에 (+)표시한다.
SQL> select ename,dname
2 from emp e, dept d
3 where e.deptno(+)=d.deptno;
ENAME DNAME
-------------------- ----------------------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING
INSA
OPERATIONS
16 rows selected.
3. Non-equi JOIN
- = 연산자가 아닌 다른 연산자를 이용해서 조인
- 정확하게 일치하는 값을 이용해서 조인하지 않고 사이값만 가지고 조인할 수 있다.
SQL> select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL> select e.ename,e.sal,g.losal,g.hisal,g.grade
2 from emp e,salgrade g
3 where e.sal>=g.losal and e.sal<=g.hisal;
ENAME SAL LOSAL HISAL GRADE
-------------------- ---------- ---------- ---------- ----------
SMITH 800 700 1200 1
ADAMS 1100 700 1200 1
JAMES 950 700 1200 1
WARD 1250 1201 1400 2
MARTIN 1250 1201 1400 2
MILLER 1300 1201 1400 2
ALLEN 1600 1401 2000 3
TURNER 1500 1401 2000 3
JONES 2975 2001 3000 4
BLAKE 2850 2001 3000 4
CLARK 2450 2001 3000 4
SCOTT 3000 2001 3000 4
FORD 3000 2001 3000 4
KING 5000 3001 9999 5
14 rows selected.
where e.sal>=g.losal and e.sal<=g.hisal;를
where e.sal between g.losal and g.hisal;로 표현할 수도 있음
4. self Join
- 하나의 테이블을 두 개의 테이블로 정의해서 조인
- 한 테이블을 from 절에 두 개의 테이블로 정의해야 하므로 두 번 명시한다.
그러나 서로 다른 테이블로 인식해야 하므로 alias를 다른 alias를 이용해서 정의한다.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 83/01/12 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
7777 RM 24/04/09 500
8888 슈가 24/04/09 500
16 rows selected.
미션_관리자별로 관리자하는 직원의 수를 출력하기
SQL> select e.empno 사원번호, e.ename 사원명,m.empno 관리자번호, m.ename 관리자명
2 from emp e, emp m
3 where e.mgr = m.empno;
사원번호 사원명 관리자번호 관리자명
---------- -------------------- ---------- --------------------
7902 FORD 7566 JONES
7788 SCOTT 7566 JONES
7900 JAMES 7698 BLAKE
7844 TURNER 7698 BLAKE
7654 MARTIN 7698 BLAKE
7521 WARD 7698 BLAKE
7499 ALLEN 7698 BLAKE
7934 MILLER 7782 CLARK
7876 ADAMS 7788 SCOTT
7782 CLARK 7839 KING
7698 BLAKE 7839 KING
7566 JONES 7839 KING
7369 SMITH 7902 FORD
13 rows selected.
SQL> select mgr, count(empno)
2 from emp
3 group by mgr;
MGR COUNT(EMPNO)
---------- ------------
7839 3
3
7782 1
7698 5
7902 1
7566 2
7788 1
7 rows selected.미션_관리자명 인원수의 형식으로 출력
select m.ename 관리자명,count(e.empno) 인원수
from emp e, emp m
where e.mgr=m.empno
group by m.ename;
관리자명 인원수
-------------------- ----------
JONES 2
FORD 1
CLARK 1
SCOTT 1
BLAKE 5
KING 3
6 rows selected.
미션_오늘 배운 조인연습
1. FIRST_NAME의 두 번째 문자열에 "t"(소문자)가 포함된 사원의 사원번호, 이름(FIRST_NAME),관리자 이름(FIRST_NAME)을 출력하고 관리자가 없는 경우에는 "관리자 없음"이라고 출력되도록 작성하세요.
select e.employee_id, e.first_name,nvl(m.first_name,'관리자 없음') 관리자명
from employees e, employees m
where e.manager_id=m.employee_id(+)
and e.first_name like '_t%'=>외부조인 기억안나서 (+)쓰지않아서 출력되지않았음
2. 각 부서별로 평균급여(salary)을 급여가 높은 순서대로 출력하세요.단, 급여는 소수이하 자리수는 반올림하여 나타내며 급여가 5000이상인 데이터만 조회합니다.
select department_name, floor(avg(salary)) 평균연봉
from departments d, employees e
where e.department_id=d.department_id
group by department_name
having floor(avg(salary)) >5000
order by floor(avg(salary)) desc;3. 직원중 현재시간 기준으로 근무 개월수가 20년(12*20개월) 보다 많은(초과) 사람의 first_name, salary, hire_date, department_name 을 출력하세요.
select first_name,salary,hire_date, department_name
from departments d, employees e
where e.department_id=d.department_id
and months_between(sysdate,hire_date)>240;4. 각 직책별로 직원수를 출력하세요. 단, 해당 직원이 없는 job_title에 대해서도 모두 출력합니다.(해당 job_title이 없다고 하더라도 추후에 발생시 적용될 수 있도록 작성하세요.)
select job_title, count(employee_id)
from jobs j, employees e
where j.job_id=e.job_id(+)
group by job_title;본 포스팅은 멀티캠퍼스의 멀티잇 백엔드 개발(Java)의 교육을 수강하고 작성되었습니다.
