비교할 컬럼을 지정하고 그 컬럼 안의 ROW값을 설정하여 ROW값일 경우 출력문자를 출력하는 방법이다.
형식
SELECT 컬럼명, 컬럼명,
CASE 비교컬럼명
WHEN 검색값 THEN 출력문자
WHEN 검색값 THEN 출력문자
ELSE defalut값의 출력문자
END
FROM 테이블명;
예시
-- hr 스키마
SELECT employee_id, first_name, phone_number,
CASE SUBSTR(phone_number,1,3) -- 지역번호
WHEN '515' THEN '서울'
WHEN '590' THEN '부산'
WHEN '650' THEN '공주'
ELSE '기타'
END
FROM employees;
QUERY 안의 QUERY 한 개의 행(ROW, RECORD, 한 사람에 해당하는 데이터)에서 결과 값이 반환되는 QUERY
서브 칼럼이 *이 아닌 이상 서브 칼럼이랑 외부 칼럼이 같아야 한다. 사용 : 테이블이 있는데 걸러내서 산출하고 싶을 때
현재까지 배운 종류
- SELECT 단일 ROW 단일 COLUMN (산출되는 데이터는 한 개, 컬럼도 한 개) - FROM 다중 ROW 다중 COLUMN - WHERE 다중 ROW 다중 COLUMN(제일 많이 사용)
SELECT절
-- hr 스키마
SELECT employee_id, first_name,
(SELECT first_name -- 단일 COLUMN
FROM employees
WHERE employee_id = 100) -- 단일 ROW
FROM employees;
SELECT first_name,
(SELECT SUM(salary) FROM employees),
(SELECT AVG(salary) FROM employees)
FROM employees;
-- 평균월급에 내 월급이 얼마인지 비교
SELECT first_name, salary, (SELECT AVG(salary) FROM employees)
FROM employees
WHERE department_id = 30;
FROM절
-- 100번 부서에 있는 사람만 걸러내서 테이블을 만듯 것
-- 그테이블의 값을 받아낸 것
-- 별칭으로도 내보낼 수 있다.
SELECT EMPNO, NAME ,SAL
FROM ( SELECT employee_id EMPNO, first_name NAME ,salary SAL
FROM employees
WHERE department_id = 100)
WHERE SAL > 8000;
-- 부서번호가 50번, 급여가 6000이상인 사원
SELECT employee_id, first_name, last_name, salary
FROM ( SELECT *
FROM employees
WHERE department_id = 50)
WHERE salary >= 6000;
-- 업무별 급여의 합계, 인원수, 사원명, 월급
SELECT e.employee_id, e.first_name,j.급여합계, j.인원수
FROM employees e, (SELECT job_id, SUM(salary) 급여합계, COUNT(*) 인원수
FROM employees
GROUP BY job_id) j;
WHERE절
-- 평균 급여보다 많은 사원
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees);
-- 부서 90인 사원의 업무명
SELECT job_id, first_name, department_id
FROM employees
WHERE job_id IN (SELECT job_id
FROM employees
WHERE department_id = 90);
-- 부서별로 가장 급여를 적게 받는 사월의 급여와 같은 급여를 받는 사원
SELECT first_name, salary, department_id
FROM employees
WHERE salary IN (SELECT MIN(salary)
FROM employees
GROUP BY department_id);
-- 부서별로 가장 급여를 적게 받는 사원과 급여
-- GROUP으로만 못구할 때 서브쿼리 사용
SELECT first_name, employee_id, salary, department_id
FROM employees
WHERE (department_id, salary) IN (SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id);
-- 50) 모든 사원의 이름, 부서번호, 부서이름을 표시하시오.(emp,dept)
SELECT e.ename, e.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;
-- 51) 업무가 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';
-- 52) 커미션을 받고 급여가 1,600이상인 사원의 사원이름,부서명,근무지를 출력하시오
SELECT e.ename, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND comm IS NOT NULL
AND comm <> 0
AND sal >= 1600;
-- 53) 근무지가 CHICAGO인 모든 사원의 이름,업무,부서번호 및 부서이름을 표시하시오.
SELECT e.ename, e.job,d.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND d.loc = 'CHICAGO';
-- 54) 근무지별로 근무하는 사원의 수가 5명 이하인 경우, 인원이 적은 도시순으로 정렬하시오.(근무 인원이 0명인 곳도 표시)
SELECT d.loc, COUNT(e.empno)
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno
GROUP BY d.loc
HAVING COUNT(e.empno) <= 5
ORDER BY COUNT(e.empno) ASC ;
-- 55) 사원의 이름 및 사원 번호를 관리자의 이름과 관리자 번호와 함께 표시하고
-- 각각의 열 레이블은 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;
-- 56) 관리자보다 먼저 입사한 모든 사원의 이름 및 입사일을 관리자의 이름 및 입사일과
-- 함께 표시하고 열 레이블을 각각 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 e.hiredate < m.hiredate;
--57) 사원의 이름 및 사원번호를 관리자의 이름과 관리자 번호와 함께 표시하고 각각의 열
-- 레이블은 employee, emp#, manager, mgr#로 지정하는데 King을 포함하여 관리자가
-- 없는 모든 사원을 표시하도록 하고 결과를 사원번호를 기준으로 정렬
SELECT e.ename employee, e.empno "emp#" , m.ename manager, m.empno "mgr#"
FROM emp e, emp m
WHERE e.mgr = m.empno(+)
ORDER BY e.empno ASC;
--58) 지정한 부서번호, 사원이름 및 지정한 사원과 동일한 부서에서 근무하는 모든 사원을
-- 표시하도록 질의를 작성하고 부서번호는 department, 사원이름은 employee, 동일한
-- 부서에서 근무하는 사원은 colleague로 표시하시오.(부서번호, 사원이름,동료 순으로 오름차순 정렬)
SELECT a.ename employee, a.deptno department,
b.ename colleague, a.deptno
FROM emp a, emp b
WHERE a.deptno = b.deptno
AND a.empno != b.empno -- 동일사원을 비교하지 않기 위한 조건
ORDER BY a.deptno ASC, a.ename ASC, b.ename ASC;
-- 59) 10번부서에서 근무하는 사원들의 부서번호, 부서이름, 사원이름, 월급, 급여등급을 출력하시오.
SELECT d.deptno, d.dname, e.ename, e.sal, s.grade
FROM emp e, dept d , salgrade s
WHERE e.deptno = d.deptno
AND d.deptno = 10
AND sal BETWEEN s.losal AND s.hisal;
-- 문제1) 사원들의 이름, 부서번호, 부서명을 출력하라
SELECT e.first_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;
-- 문제2) 30번 부서의 사원들의 이름,직업,부서명을 출력하라
SELECT e.first_name, e.job_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.department_id = 30;
-- 문제3) 커미션을 받는 사원의 이름, 직업, 부서번호,부서명을 출력하라
SELECT e.first_name, e.job_id, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.commission_pct IS NOT NULL;
-- 문제4) 지역번호 2500 에서 근무하는 사원의 이름, 직업,부서번호,부서명을 출력하라
SELECT e.first_name, e.job_id, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.location_id = 2500;
-- 문제5) 이름에 A가 들어가는 사원들의 이름과 부서이름을 출력하라
SELECT e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.first_name LIKE '%A%';
-- 문제6) 사원이름과 그 사원의 관리자 이름을 출력하라
SELECT emp.first_name 사원명, mgr.first_name 매니저명
FROM employees emp, employees mgr
WHERE emp.manager_id = mgr.employee_id;
-- 문제7) 사원이름과 부서명과 월급을 출력하는데 월급이 3000 이상인 사원을 출력하라
SELECT e.first_name, d.department_name, e.salary
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.salary >= 3000;
-- 문제8) TJ 이란 사원보다 늦게 입사한 사원의 이름과 입사일을 출력하라
-- cross join
SELECT b.first_name , b.hire_date
FROM employees a, employees b -- a: TJ, b: 사원들
WHERE a.first_name = 'TJ'
AND a.hire_date < b.hire_date;
-- 문제9) 급여가 3000에서 5000사이인 사원의 이름과 소속부서명 출력하라
SELECT e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.salary BETWEEN 3000 AND 5000;
-- 문제10) ACCOUNTING 부서 소속 사원의 이름과 입사일 출력하라
SELECT e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_name = INITCAP('ACCOUNTING');
-- 문제11) 급여가 3000이하인 사원의 이름과 급여, 근무지를 출력하라
SELECT e.first_name, e.salary, d.location_id, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND e.salary <= 3000 ;
-- scott
--19) emp 테이블에서 사원번호, 사원이름, 입사일을 출력하는데 입사일이 빠른 사람순으로 정렬하시오.
SELECT empno, ename, hiredate
FROM emp
ORDER by hiredate;
--20) emp 테이블에서 사원이름, 급여, 연봉을 구하고 연봉이 많은 순으로 정렬하시오.
SELECT ename, sal, sal*12
FROM emp
ORDER BY sal*12 DESC;
--21)10번 부서와 20번부서에서 근무하고 있는 사원의 이름과 부서번호를 출력하는데 이름을 영문자순으로 표시하시오.
SELECT ename,deptno
FROM emp
-- WHERE deptno = 10 OR deptno = 20
WHERE deptno IN(10,20)
ORDER BY ename;
--22) 커미션을 받는 모든 사원의 이름,급여 및 커미션을 커미션을 기준으로 내림차순으로 정렬하여 표시하십시오.
SELECT ename, sal, comm
FROM emp
-- WHERE NVL(comm,0)>0
WHERE comm IS NOT NULL AND comm != 0
ORDER BY comm DESC;
--23) emp테이블의 업무(job)을 첫글자는 대문자 나머지는 소문자로 출력하시오. INITCAP()
--업무(job)의 중복은 제거해주십시오.
SELECT DISTINCT INITCAP(job) FROM emp;
--24) emp테이블에서 사원이름 중 A가 포함된 사원이름을 구하고 그 이름 중 앞에서 3자만 추출하여 출력
SELECT SUBSTR(ename,1,3)
FROM emp
WHERE ename LIKE '%A%';
--25) 이름의 세번째 문자가 A인 모든 사원의 이름을 표시하시오.
SELECT ename
FROM emp
WHERE ename LIKE '__A%';
--WHERE SUBSTR(ename,3,1) = 'A';
--26) 이름이 J,A 또는 M으로 시작하는 모든 사원의 이름(첫 글자는 대문자로, 나머지 글자는 소문자로 표시) 및 이름의 길이를 표시하시오.(열 레이블은 name과 length로 표시)
SELECT ename, INITCAP(ename), LENGTH(ename)
FROM emp
WHERE ename LIKE 'J%'
OR ename LIKE 'A%'
OR ename LIKE 'M%';
--27) 이름의 글자수가 6자 이상인 사원의 이름을 소문자로 이름만 출력하시오
SELECT LOWER(ename)
FROM emp
WHERE LENGTH(ename) >=6;
--28) 이름의 글자수가 6자 이상인 사람의 이름을 앞에서 3자만 구하여 소문자로 출력하시오.
SELECT ename, SUBSTR(LOWER(ename),1,3)
FROM emp
WHERE LENGTH(ename) >=6;
--29) 모든 사원의 이름과 급여를 표시하시오. 급여는 15자 길이로 왼쪽에 $기호가 채워진 형식으로 표기하고 열레이블을 SALARY로 지정하시오.
SELECT ename, sal, LPAD(sal, 15, '$') SALARY FROM emp;
--30) 오늘부터 이번달의 마지막날까지의 남은 날 수를 구하시오.
SELECT TO_NUMBER(TO_CHAR( LAST_DAY(SYSDATE),'YYMMDD'))-TO_NUMBER(TO_CHAR(SYSDATE, 'YYMMDD'))AS 남은날 FROM DUAL;
SELECT LAST_DAY(SYSDATE) - SYSDATE FROM dual;
--31) emp테이블에서 각 사원에 대해 사원번호, 이름, 급여 및 15% 인상된 급여를 정수(반올림)로 표시하시오.
-- 인상된 급여열의 레이블을 New Salary로 지정하시오.
SELECT empno, ename, sal, ROUND(sal*1.15) "New Salary" FROM emp;
--32) emp테이블에서 월급의 4의 배수(mod(sal,4)=0)인 사원의 이름과 월급을 출력하시오.(세자리 단위 쉼표 표시)
SELECT ename, TO_CHAR(sal, '999,999,999')
FROM emp
WHERE mod(sal,4)=0;
-- 33) 각 사원의 이름을 표시하고 근무 달 수(입사일로부터 현재까지의 달수)를 계산하여 열 레이블을 MONTHS_WORKED로 지정하시오.
-- 결과는 정수로 반올림하여 표시하고 근무달 수를 기준으로 오름차순으로 정렬하시오.
-- 적고싶은 날짜가 있을 시 TO_DATE('00000000','YYYYMMDD') 사용
SELECT ename, ROUND(MONTHS_BETWEEN(SYSDATE, hiredate)) as MONTHS_WORKED
FROM emp
ORDER BY MONTHS_WORKED;
-- 34)emp테이블에서 이름, 업무, 근무연차를 출력하시오.
SELECT ename, job,
-- trunc ((( sysdate - hiredate ) + 1 ) / 365 ) AS 근속연수
TRUNC(MONTHS_BETWEEN(SYSDATE, hiredate)/12) AS 근속연수
FROM emp;
-- 35)emp테이블에서 사원이름, 월급, 월급과 커미션을 더한 값을 컬럼명 실급여라고 해서 출력.
-- 단, NULL값은 나타나지 않게 작성하시오. NVL
SELECT ename, sal, comm, sal+comm as 실급여
FROM emp
WHERE comm IS NOT NULL AND comm != 0;
SELECT ename, sal, comm, sal+NVL(comm,0) as 실급여
FROM emp
WHERE comm IS NOT NULL AND comm != 0;
-- 36)월급과 커미션을 합친 금액이 2,000이상인 급여를 받는 사원의 이름,업무,월급,커미션,고용날짜를 출력하시오. 단, 고용날짜는 1980-12-17 형태로 출력하시오.
SELECT ename, job, sal+NVL(comm,0), to_date(hiredate, 'YYYY-MM-DD') AS 고용날짜
FROM emp
WHERE sal + NVL(comm,0) >= 2000;
-- 38)모든 사원의 급여의 최고액, 최저액, 총액 및 평균액을 표시하시오.
-- 열 레이블을 각각 maximum,minimum,sum 및 average로 지정하고
-- 결과를 정수로 반올림하고 세자리 단위로 ,를 명시하시오.
SELECT
TO_CHAR(MAX(sal),'999,999') as maximum ,
TO_CHAR(MIN(sal),'999,999') as minimum,
TO_CHAR(SUM(sal),'999,999') as sum,
TO_CHAR(ROUND(AVG(sal)),'999,999') as average
FROM emp;
-- 39) 업무가 동일한 사원 수를 표시하는 질의를 작성하시오.
SELECT job, count(*)
FROM emp
GROUP BY job;
-- 40) emp테이블에서 30번부서의 사원수를 구하시오.
SELECT count(*)
FROM emp
WHERE deptno = 30
GROUP BY deptno;
-- 41) emp테이블에서 업무별 최고 월급을 구하고 업무,최고 월급을 출력하시오.
SELECT job, MAX(sal)
FROM emp
GROUP BY job;
-- 42) emp테이블에서 20번부서의 급여 합계를 구하고 급여 합계 금액을 출력하시오.
SELECT SUM(sal)
FROM emp
WHERE deptno = 20;
-- 43) emp테이블에서 부서별로 지급되는 총월급에서 금액이 7,000이상인 부서번호, 총월급을 출력하시오.
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal) >= 7000;
-- 44) emp테이블에서 업무별로 사번이 제일 늦은 사람을 구하고 그 결과 내에서 사번이 79로 시작하는 결과만 보여주시오.
SELECT job, MAX(empno)
FROM emp
GROUP BY job
HAVING MAX(empno) LIKE '79%';
-- 45) emp테이블에서 업무별 총월급을 출력하는데 업무가 'MANAGER'인 사원들은 제외하고
-- 총월급이 5,000보다 큰 업무와 총월급만 출력하시오.
SELECT job, SUM(sal)
FROM emp
WHERE job != 'MANAGER'
GROUP BY job
HAVING SUM(sal) > 5000 ;
-- 46)emp테이블에서 업무별로 사원수가 4명 이상인 업무와 인원수를 출력하시오.
SELECT job, COUNT(*)
FROM emp
GROUP BY job
HAVING COUNT(*) >= 4;
-- 47)emp테이블에서 사원수가 5명이 넘는 부서의 부서번호와 사원수를 구하시오.
SELECT deptno, COUNT(*)
FROM emp
GROUP BY deptno
HAVING COUNT(*) >= 5;
-- 48)emp테이블에서 부서별 급여평균을 구할 때 소수점 3자리에서 반올림해서 2자리까지 구하고
-- 부서번호, 급여평균을 출력하시오.
SELECT deptno, ROUND(AVG(sal),2)
FROM emp
GROUP BY deptno;
-- hr 정렬
-- 문제1) EMPLOYEES 테이블에서 입사일자 순으로 정렬하여 사원번호, 이름, 업무, 급여, 입사일자,부서번호를 출력하라.
-- ASC는 안적어도 되지만 가독성을 위해 적어준다.
SELECT employee_id, first_name, job_id, salary, hire_date, department_id
FROM employees
ORDER BY hire_date ASC;
-- 문제2) EMPLOYEES 테이블에서 가장 최근에 입사한 순으로 사원번호, 이름, 업무, 급여, 입사일자,부서번호를 출력하라.
SELECT employee_id, first_name, job_id, salary, hire_date, department_id
FROM employees
ORDER BY hire_date DESC;
-- 문제3) EMPLOYEES 테이블에서 부서번호로 정렬한 후 부서번호가 같을 경우 급여가 많은 순으로 정렬하여
-- 사원번호, 성명, 업무, 부서번호, 급여를 출력하여라.
SELECT employee_id, first_name, job_id, department_id, salary
FROM employees
ORDER BY department_id ASC , salary DESC ;
-- 문제4) EMPLOYEES 테이블에서 첫번째 정렬은 부서번호로 두번째 정렬은 업무로 세번째 정렬은 급여가 많은 순으로 정렬하여
-- 사원번호, 성명, 입사일자, 부서번호, 업무, 급여를 출력하여라.
SELECT employee_id, first_name, hire_date, department_id, job_id, salary
FROM employees
ORDER BY department_id ASC, job_id ASC, salary DESC ;
-- hr 그룹핑
-- 문제1) EMPLOYEES 테이블에서 모든 SALESMAN(SA_)에 대하여 급여의 평균, 최고액, 최저액, 합계를 구하여 출력하여라.
SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE 'SA%';
-- 문제2) EMPLOYEES 테이블에 등록되어 있는 인원수,
-- 보너스가 NULL이 아닌 인원수, 보너스의 평균, 등록되어 있는 부서의 수를 구하여 출력하라.
SELECT COUNT(*), COUNT(commission_pct), ROUND(AVG(commission_pct*salary)),
count(DISTINCT department_id)
FROM employees;
-- 문제3) EMPLOYEES 테이블에서 부서별로 인원수, 평균 급여, 최저급여, 최고 급여, 급여의 합을 구하여 출력하라.
SELECT department_id, COUNT(*), ROUND(AVG(salary),1), MIN(salary), MAX(salary), SUM(salary)
FROM employees
GROUP BY department_id;
-- 문제4) EMPLOYEES 테이블에서 각 부서별로 인원수,급여의 평균, 최저 급여, 최고 급여, 급여의 합을 구하여 급여의 합이 많은 순으로 출력하여라.
SELECT COUNT(*), TRUNC(AVG(salary)), MIN(salary), MAX(salary), SUM(salary) AS 급여의합
FROM employees
GROUP BY department_id
ORDER BY 급여의합 DESC;
-- 문제5) EMPLOYEES 테이블에서 부서별, 업무별 그룹하여 결과를 부서번호, 업무, 인원수, 급여의 평균, 급여의 합을 구하여 출력하여라.
SELECT department_id, job_id, COUNT(*), ROUND(AVG(salary),2), SUM(salary)
FROM employees
GROUP BY department_id, job_id;
-- 문제6) EMPLOYEES 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 구하여 출력하여라
SELECT department_id, COUNT(*), SUM(salary)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 4;
-- 문제7) EMPLOYEES 테이블에서 급여가 최대 10000이상인 부서에 대해서 부서번호, 평균 급여, 급여의 합을 구하여 출력하여라.
SELECT department_id, ROUND(AVG(salary),2), SUM(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) >= 10000;
-- 문제8) EMPLOYEES 테이블에서 업무별 급여의 평균이 10000 이상인 업무에 대해서 업무명,평균 급여, 급여의 합을 구하여 출력하라.
SELECT job_id, AVG(salary), SUM(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary) >= 10000;
-- 문제9) EMPLOYEES 테이블에서 전체 월급이 10000을 초과하는 각 업무에 대해서 업무와 월급여 합계를 출력하라.
-- 단 판매원(SA_)은 제외하고 월 급여 합계로 정렬(내림차순)하라.
SELECT job_id, SUM(salary)
FROM employees
WHERE job_id NOT LIKE 'SA_%'
GROUP BY job_id
HAVING SUM(salary) > 10000
ORDER BY SUM(salary) DESC;
두 개 이상의 테이블을 연결해서 데이터를 검색하는 방법 보통 두개이상의 행(ROW)들의 공통된 값 기본키(PRIMARY KEY), 외래키를 사용해서 JOIN 한다. 현재 테이블의 외래키(model F)를 통해 상대 테이블의 기본키(model P & U)에 연결 목적 : 현재 테이블에 없는 값을 상대 테이블의 정보(칼럼)에서 산출하기 위한 것. 기본키(Primary key) : 테이블에서 중복되지 않는 키 (model P) 외래키(Foreign key) : 다른 테이블에서 기본키이거나 고유키인 경우가 많다. (model F)
JOIN 종류
inner join = 교집합 ★★★★★ (basic)
full outer join = 합집합
cross join outer join ★★★ left right self join ★★★★★
형식
ANSI문법
SELECT 검색컬럼명
FROM 테이블명1 별명1 JOIN 테이블명2 별명2
ON 별명1.외래키 = 별명2.기본키(OR 고유키);
오라클문법
SELECT 검색컬럼명
FROM 테이블명1 별명1, 테이블명2 별명2
WHERE 별명1.외래키 = 별명2.기본키(OR 고유키);
inner join : 기준테이블과 대상 테이블에 매칭하는 필드값이 있는 경우에 검색한다.
참고
2개이상의 테이블도 JOIN 가능하다.
ANSI문법 사용시 FROM절에 INNER 생략 가능하다.
별명은 양쪽 다 가지고 있지 않는 컬럼일 경우 생략가능하다.
예시
-- hr 스키마
-- ansi SQL (표준, MySQL도 가능)
-- 목적 : 부서명 얻기(d.department_name )
SELECT e.employee_id, e.first_name, e.department_id,
d.department_id, d.department_name
FROM employees e INNER JOIN departments d
ON e.department_id = d.department_id; -- 연결
-- oracle (위와 결과값이 같음)
SELECT e.employee_id, e.first_name, e.department_id,
d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id; -- 같을때 연결하라
-- 목적 : JOB_ID == IT_PROG 업무명을 꺼내고 싶다
-- 데이터를 가지고 가서 맞춰보고 상대방의 정보를 가져올 수 있다.
SELECT e.employee_id, e.first_name, e.job_id,
j.job_id, j.job_title
FROM employees e, jobs j
WHERE e.job_id = j.job_id
AND e.job_id = 'IT_PROG';
-- 3개 테이블을 조인
-- NULL은 JOIN 불가!
SELECT e.employee_id, e.first_name,
d.department_name , j.job_title
FROM employees e, departments d, jobs j
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id
AND e.employee_id = 101;
cross join : 2개의 테이블을 아무 조건없이 연결시키는 방법(ALL JOIN)
참고
실행시 1번테이블 * 2번테이블 결과값을 가져온다.
거의 사용하지 않는다.
예시
-- ansi
SELECT e.employee_id, e.first_name, e.department_id,
d.department_id, d.department_name
FROM employees e CROSS JOIN departments d;
-- oracle
SELECT e.employee_id, e.first_name, e.department_id,
d.department_id, d.department_name
FROM employees e, departments d;
full outer join :기준테이블과 대상 테이블에 매칭하는 필드값이 있는 경우모든 필드값으로테이블을 만들어서 데이터를 검색하는 방법
참고
data 전체를 뽑을 때 (간혹 사용)
결과값 : left outer join + right outer join
예시
-- ansi
SELECT e.employee_id, e.first_name, e.department_id,
d.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;
/* oracle : 결론적으론 안됨 == error.
SELECT e.employee_id, e.first_name, e.department_id,
d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id(+); -- left
*/
-- 굳이 사용하겠다면 UNION 사용.
SELECT e.employee_id, e.first_name, e.department_id,
d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+) -- left join
UNION
SELECT e.employee_id, e.first_name, e.department_id,
d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id; -- right join
-- e.department_id, d.department_id null 가져와라
-- ansi
SELECT e.employee_id, e.first_name, e.department_id,
d.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL
OR d.department_id IS NULL;
-- oracle
SELECT e.employee_id, e.first_name, e.department_id,
d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
AND e.department_id IS NULL
UNION
SELECT e.employee_id, e.first_name, e.department_id,
d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id
AND e.department_id IS NULL;
outer(외부) join : 기준테이블과 대상 테이블에 매칭하는 필드값이 있는 경우동일한 필드값이 없는 행도 검색하는 방법
left join(leftouter join) : 좌측테이블(기준 테이블)의 모든 필드값을 가져온다.
참고
실행시 e.department_id의 null의 값도 가져오게 된다.
예시
-- ansi
SELECT e.employee_id, e.first_name, e.department_id,
d.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;
-- oracle
SELECT e.employee_id, e.first_name, e.department_id,
d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);
-- 차집합 (e.department_id의 NULL을 가져와라)
SELECT e.employee_id, e.first_name, e.department_id,
d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
AND e.department_id IS NULL;
right join(right outer join) : 우측 테이블(대상 테이블)의 모든 필드값을 가져온다.
참고
실행시 d.department_id의 null의 값도 가져오게 된다.
예시
-- ansi
SELECT e.employee_id, e.first_name, e.department_id,
d.department_id, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id;
-- oracle
SELECT e.employee_id, e.first_name, e.department_id,
d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;
-- 차집합 (d.department_id NULL을 가져와라)
SELECT e.employee_id, e.first_name, e.department_id,
d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id
AND e.department_id IS NULL;
self join : 자기 자신과 join 한다.
참고
테이블 내 동일한 값이 다른 의미를 가지는 즉, 다른 컬럼에 존재하는 경우
예시
SELECT emp.employee_id, emp.first_name, emp.manager_id,
mgr.employee_id, mgr.first_name
FROM employees emp, employees mgr -- emp:사원, mgr:상사
WHERE emp.manager_id = mgr.employee_id;
-- null값까지 뽑기(self join + left outer join)
SELECT emp.employee_id, emp.first_name, emp.manager_id,
mgr.employee_id, mgr.first_name
FROM employees emp, employees mgr -- emp:사원, mgr:상사
WHERE emp.manager_id = mgr.employee_id(+);
