2026. 1. 30. 16:41ㆍ대우개발원 수업 내용/Database (sql)
[ 조인의 종류 ]
▶ 조인이란? : 테이블(혹은 뷰)간의 관계를 맺는 방법
- 조인의 종류
- 조인 연산자에 따른 구분 : 동등 조인, 안티 조인
- 조인 대상에 따른 구분 : 셀프 조인
- 조인 조건에 따른 구분 : 내부 조인, 외부 조인, 세미 조인, 카타시안 조인(CATASIAN PRODUCT)
- 기타 : ANSI 조인
[ 내부 조인과 외부 조인 ]
▶ 내부조인 - 동등 조인(EQUI JOIN)
● 가장 기본적이고 일반적인 조인 방법
● WHERE 절에서 등호(‘=’)연산자를 사용해 2개 이상의 테이블이나 뷰를 연결한 조인 조인조건
● 컬럼 단위로 조인조건 기술
● SELECT *
FROM TAB1 a, TAB2 b
WHERE a.col1 = b.col1
....
-- 동등 조인
SELECT a.employee_id, a.emp_name, a.department_id, b.department_name
FROM employees a,
departments b
WHERE a.department_id = b.department_id;
▶ 내부조인 - 세미 조인(SEMI JOIN)
● 서브쿼리를 사용해 서브쿼리에 존재하는 데이터만 메인 쿼리에서 추출하는 조인
● WHERE 절에서 IN 과 EXISTS 연산자를 사용한 조인방법
● SELECT *
FROM TAB1 a
WHERE EXISTS ( SELECT 1
FROM TAB2 b
WHERE a.col1 = b.col1
....
);● SELECT *
FROM TAB1 a
WHERE a.col1 IN ( SELECT b.col1
FROM TAB2 b
WHERE ....
....
);
● 세미 조인은 서브쿼리에 존재하는 메인쿼리 데이터가 여러 건 존재하더라도
최종 반환되는 메인쿼리 데이터에는 중복되는 건이 없다 일반 조인과의 차이점
▶ 내부조인 - 안티 조인(ANTI JOIN)
● 서브쿼리 테이블에는 없는, 메인쿼리 테이블의 데이터만 추출하는 조인 방법
● WHERE 절에서 NOT IN 과 NOT EXISTS 연산자를 사용한 조인방법
● SELECT *
FROM TAB1 a
WHERE NOT EXISTS ( SELECT 1
FROM TAB2 b
WHERE a.col1 = b.col1
....
);-- 세미 조인
SELECT department_id, department_name
FROM departments a
WHERE EXISTS ( SELECT *
FROM employees b
WHERE a.department_id = b.department_id
AND b.salary > 3000)
ORDER BY a.department_name;
--위의 세미조인을 IN 연산자로 바꾸면
SELECT department_id, department_name
FROM departments a
WHERE a.department_id IN ( SELECT b.department_id
FROM employees b
WHERE b.salary > 3000)
ORDER BY department_name;
동등조인은 중복된 값이 발생
-- 동등 조인
SELECT a.department_id, a.department_name
FROM departments a, employees b
WHERE a.department_id = b.department_id
AND b.salary > 3000
ORDER BY a.department_name;
[ 내부조인과 외부조인 ]
▶ 내부조인 - 안티 조인(ANTI JOIN)
● 서브쿼리 테이블에는 없는, 메인쿼리 테이블의 데이터만 추출하는 조인 방법
● WHERE 절에서 NOT EXISTS 과 NOT IN 연산자를 사용한 조인방법
SELECT *
FROM TAB1 a
WHERE NOT EXISTS ( SELECT 1
FROM TAB2 b
WHERE a.col1 = b.col1
....
);NOT EXISTS
-- 건수만 조회
-- NULL인 데이터를 인정하기 떄문에 106이 아닌 107이 나옴
SELECT count(*)
FROM employees a
WHERE NOT EXISTS ( SELECT 1
FROM departments c
WHERE a.department_id = c. department_id
AND manager_id IS NULL ) ;
● SELECT *
FROM TAB1 a
WHERE a.col1 NOT IN ( SELECT b.col1
FROM TAB2 b
WHERE ….
….
);NOT IN
-- 안티 조인
-- 데이터를 조회
SELECT a.employee_id, a.emp_name, a.department_id, b.department_name
FROM employees a, departments b
WHERE a.department_id = b.department_id
AND a.department_id NOT IN ( SELECT department_id
FROM departments
WHERE manager_id IS NULL ) ;
내부조인과 외부조인
▶ 내부조인 - 셀프 조인(SELF JOIN)
● 서로 다른 두 테이블이 아닌 동일한 한 테이블을 사용해 조인
SELECT *
FROM TAB1 a, TAB1 b
WHERE a.col1 = b.col1
....
;SELECT 컬럼명 AS 별칭-- 셀프 조인
-- 같은 부서(20번 부서)에 속한 직원들끼리 2명씩 짝을 지어 조회
SELECT a.employee_id, a.emp_name, b.employee_id, b.emp_name, a.department_id
FROM employees a,
employees b
WHERE a.employee_id < b.employee_id
AND a.department_id = b.department_id
AND a.department_id = 20;
-- 셀프 조인
-- 같은 부서(80번 부서)에 속한 직원들끼리 2명씩 짝을 지어 조회
SELECT a.employee_id, a.emp_name, b.employee_id, b.emp_name, a.department_id
FROM employees a,
employees b
WHERE a.employee_id < b.employee_id
AND a.department_id = b.department_id
AND a.department_id = 80;
[ 내부조인과 외부조인 ]
▶ 외부조인 - 외부 조인(OUTER JOIN)
● 일반 조인을 확장한 개념
● 조인 조건에 만족하는 데이터 뿐만 아니라, 어느 한 쪽 테이블에 조인 조건에 명시된 컬럼에 값이 없거나(NULL 이더라도)
해당 로우가 아예 없더라도 데이터를 모두 추출
● 조인조건에서 데이터가 없는 쪽 테이블의 컬럼 끝에 (+)를 붙인다
● 조인조건이 여러 개일 경우 모든 조인조건에 (+)를 붙여야 한다
=> - (+ )는 조인 조건에 맞는 값이 없어도 NULL을 채워서 결과를 보여주라는 의미
● 한 번에 한 테이블에만 외부 조인
A와 B 테이블을 외부 조인으로 연결했다면, 동시에 A와 C 테이블에 외부 조인을 걸 수는 없다.
● (+)연산자가 붙은 조건과 OR를 같이 사용할 수 없다
● (+)연산자가 붙은 조건에는 IN 연산자를 같이 사용할 수 없다. 단 IN절에 포함되는 값이 1개인 때는 사용 가능하다.
SELECT a.department_id, a.department_name, b.job_id, b.department_id
FROM departments a,
job_history b
WHERE a.department_id = b.department_id;
-> 일반조인, 결과는 10건SELECT a.department_id, a.department_name, b.job_id, b.department_id
FROM departments a,
job_history b
WHERE a.department_id = b.department_id(+);
-> 외부조인, 결과는 31건-- 외부 조인
-- job_history에 없는 데이터가 조회가 안된다
SELECT a.department_id, a.department_name, b.job_id, b.department_id
FROM departments a,
job_history b
WHERE a.department_id = b.department_id;
-- job_history에 없는 데이터도 조회가 된다
SELECT a.department_id, a.department_name, b.job_id, b.department_id
FROM departments a,
job_history b
WHERE a.department_id = b.department_id (+) ;
-- 외부 조인 조건을 썼지만 내부 조인 조건을 덧붙여서 결국 내부 조인처럼 동작
SELECT a.employee_id, a.emp_name, b.job_id, b.department_id
FROM employees a,
job_history b
WHERE a.employee_id = b.employee_id (+)
AND a.department_id = b.department_id;
-- 위의 코드를 외부조인으로 바꾸는것
SELECT a.employee_id, a.emp_name, b.job_id, b.department_id
FROM employees a,
job_history b
WHERE a.employee_id = b.employee_id (+)
AND a.department_id = b.department_id(+);
내부조인과 외부조인
▶ 외부조인 - 카타시안 조인 (CATASIAN PRODUCT)
● 조인조건이 없는 조인
● FROM 절에 A와 B, 2개의 테이블을 명시했을 경우, 추출되는 데이터는
A 테이블 데이터 건수 * B 테이블 데이터 건수
-- 카타시안 조인
SELECT a.employee_id, a.emp_name, b.department_id, b.department_name
FROM employees a,
departments b;
[ ANSI SQL ]
1. ANSI SQL 정의
- ANSI는 American National Standards Institute(미국 표준 협회)의 약자이다.
- ANSI SQL은 다양한 DBMS(Oracle, MySQL 등)에서 공통적으로 사용할 수 있는 표준 SQL 문법이다.
2. ANSI SQL 사용 예시
-- ORACLE 방식
SELECT a.name, b.name
FROM STUDENTS a, TEACHERS b
WHERE a.class_no = b.class_no;
-- ANSI 방식
SELECT a.name, b.name
FROM STUDENTS a JOIN TEACHERS b
ON a.class_no = b.class_no;
- 위 두 개의 쿼리는 같은 결과값을 출력하게 된다.
3. ANSI SQL의 장점
- 표준 SQL 문법이기 때문에 대부분의 DBMS에서 사용 가능하다.
- JOIN 절과 WHERE 절이 분리되어 있어 쿼리 분석이 직관적이다.
4. ANSI SQL의 단점
- DBMS 내부 함수 등을 사용할 경우 ANSI 문법만으로는 쿼리 작성이 불가능한 경우가 있다.
[ ANSI 조인 ]
▶ ANSI SQL 문법을 사용한 조인
▷ ANSI 내부조인
●
SELECT A.컬럼1, A.컬럼2, B.컬럼1, B.컬럼2 ...
FROM 테이블 A
INNER JOIN 테이블 B
ON (A.컬럼1 = B.컬럼1) -- 조인 조건
WHERE ....
;
● 내부조인의 경우 FROM 절에 INNER JOIN을 명시
● 조인조건은 ON 절에 명시
● 테이블 간 조인조건 외의 다른 조건은 WHERE 절에 명시한다.
-- ANSI 조인
SELECT a.employee_id, a.emp_name, b.department_id, b.department_name
FROM employees a,
departments b
WHERE a.department_id = b.department_id
AND a.hire_date >= TO_DATE('2003-01-01','YYYY-MM-DD');
SELECT a.employee_id, a.emp_name, b.department_id, b.department_name
FROM employees a
INNER JOIN departments b
ON(a.departmeNt_id = b.department_id)
WHERE a.hire_date >= TO_DATE('2003-01-01','YYYY-MM-DD');
[ ANSI 조인 ]
▷ ANSI 외부조인
●
SELECT A.컬럼1, A.컬럼2, B.컬럼1, B.컬럼2 ...
FROM 테이블 A
LEFT(RIGHT) [OUTER] JOIN 테이블 B
ON (A.컬럼1 = B.컬럼1) -- 조인 조건
WHERE ....
;
● FROM 절에 LEFT(RIGHT) [OUTER] JOIN 을 명시, 조인조건은 ON 절에 명시
● 일반 외부조인에서는 기존 테이블과 대상 테이블(데이터가 없는 테이블)에서 대상 테이블 쪽 조인 조건에 (+)를 붙였지만,
ANSI 외부 조인은 FROM 절에 명시된 테이블 순서에 입각해 먼저 명시된 테이블 기준으로 LEFT 혹은 RIGHT을 붙인다
● OUTER는 생략 가능
-- ANSI 외부조인
SELECT a.employee_id, a.emp_name, b.job_id, b.department_id
FROM employees a,
job_history b
WHERE a.employee_id = b.employee_id (+)
AND a.department_id = b.department_id(+);
-- 위의 코드를 ANSI코드로 변환
SELECT a.employee_id, a.emp_name, b.job_id, b.department_id
FROM employees a
LEFT OUTER JOIN job_history b
ON( a.employee_id = b.employee_id
and a.department_id = b.department_id);
SELECT a.employee_id, a.emp_name, b.job_id, b.department_id
FROM job_history b
RIGHT OUTER JOIN employees a
ON( a.employee_id = b.employee_id
and a.departme nt_id = b.department_id);
[ ANSI 조인 ]
▶ ANSI CROSS 조인
●
SELECT A.컬럼1, A.컬럼2, B.컬럼1, B.컬럼2 ...
FROM 테이블 A
CROSS JOIN 테이블 B
WHERE ....
;
● 카타시안 조인을 ANSI 조인에서는 CROSS 조인이라 함
● FROM 절에 CROSS JOIN 명시
-- CROSS 조인
SELECT a.employee_id, a.emp_name, b.department_id, b.department_name
FROM employees a,
departments b;
-- 위의 코드를 ANSI코드로 변환
SELECT a.employee_id, a.emp_name, b.department_id, b.department_name
FROM employees a
CROSS JOIN departments b;
[ ANSI 조인 ]
▶ ANSI FULL OUTER 조인
● 일반적인 외부 조인은 두 테이블 중 어느 한 테이블에 조인 조건에 만족하는 데이터가 없더라도 두 테이블 데이터 모두 조회되는 조인 ➔ 한 쪽 이빨이 빠진 형태
● 한 쪽 테이블이 아닌 두 테이블 모두 데이터가 없는 경우, 두 쪽 테이블 모두 이빨이 빠진 형태의 결과가 추출되는 외부조인이 FULL OUTER 조인이다.
● 조인조건을 기준으로 두 테이블 중 어느 한 쪽에만 데이터가 있으면 조회된다
●
SELECT a.emp_id, b.emp_id
FROM hong_a a
FULL OUTER JOIN hong_b b
ON (a.emp_id = b.emp_id);
-- ANSI FULL OUTER 조인
SELECT a.emp_id, b.emp_id
FROM lee_a a,
lee_b b
WHERE a.emp_id(+) = b.emp_id(+); //오류
--오류가 나기때문에 ANSI FULL OUTER 조인을 사용
SELECT a.emp_id, b.emp_id
FROM lee_a a
FULL OUTER JOIN lee_b b
ON (a.emp_id = b.emp_id);
[ 서브쿼리 ]
한 SQL 문장 안에서 보조로 사용되는 또 다른 SELECT문
● 메인 쿼리를 기준으로 보조 역할을 한다
● SELECT, FROM, WHERE절 뿐만 아니라, INSERT, UPDATE, MERGE, DELETE 문에서도
서브쿼리를 사용할 수 있다
서브쿼리의 종류
● 메인쿼리와의 연관성에 따라
- 연관성 없는(Noncorrelated) 서브쿼리
- 연관성 있는 서브쿼리
● 형태에 따라
- 일반 서브쿼리 (SELECT 절)
- 인라인 뷰 (FROM 절)
- 중첩쿼리 (WHERE 절)
연관성 없는 서브쿼리
● 메인쿼리와의 연관성이 없는 서브쿼리 메인 테이블과 조인 조건이 걸리지 않음
● 유형1
SELECT count(*)
FROM employees
WHERE salary >= ( SELECT AVG(salary)
FROM employees );
● 유형2
SELECT count(*)
FROM employees
WHERE department_id IN ( SELECT department_id
FROM departments
WHERE parent_id IS NULL);
● 유형3
SELECT employee_id, emp_name, job_id
FROM employees
WHERE (employee_id, job_id ) IN ( SELECT employee_id, job_id
FROM job_history);연관성 있는 서브쿼리
● 메인쿼리와의 연관성이 있는 서브쿼리 메인 테이블과 조인 조건이 걸려있다
● 유형1
SELECT a.department_id, a.department_name
FROM departments a
WHERE EXISTS ( SELECT 1
FROM job_history b
WHERE a.department_id = b.department_id );
● 유형2
-- 전체 평균 급여보다 높은 급여를 받는 직원이 한 명이라도 존재하는 부서만 조회
SELECT a.department_id, a.department_name
FROM departments a
WHERE EXISTS ( SELECT 1
FROM employees b
WHERE a.department_id = b.department_id
AND b.salary > ( SELECT AVG(salary)
FROM employees )
);● 유형3
SELECT a.employee_id,
( SELECT b.emp_name
FROM employees b
WHERE a.employee_id = b.employee_id) AS emp_name,
a.department_id,
( SELECT b.department_name
FROM departments b
WHERE a.department_id = b.department_id) AS dep_name
FROM job_history a;
-- 부모 부서가 90인 부서들만 대상으로, 각 부서멸 평균 급여를 계산
SELECT department_id, AVG(salary)
FROM employees a
WHERE department_id IN(SELECT department_id
FROM departments
WHERE parent_id = 90)
GROUP BY department_id;
-- 부모 부서가 90인 하위 부서들의 직원 급여를 각 부서별 평균 급여로 업데이트
UPDATE employees a
SET a.salary = ( SELECT sal
FROM ( SELECT b.department_id, AVG(c.salary) as sal
FROM departments b,
employees c
WHERE b.parent_id =90
AND b.department_id = c.department_id
GROUP BY b.department_id) d --인라인뷰, 부모부서가 90 부서의 부서별 평균 조회
WHERE a.department_id = d.department_id )
WHERE a.department_id IN ( SELECT department_id --조건 서브쿼리
FROM departments
WHERE parent_id = 90 );
ROLLBACK;
-- 부모 부서가 90인 부서들만 대상으로, 각 부서별 최소 급여와 최대 급여를 조회
SELECT department_id, MIN(salary), MAX(salary)
FROM employees a
WHERE department_id IN ( SELECT department_id
FROM departments
WHERE parent_id = 90)
GROUP BY department_id;
-- 부모 부서가 90인 하위 부서들의 직원 급여를 각 부서별 평균 급여로 업데이트
MERGE INTO employees a
USING ( SELECT b.department_id, AVG(c.salary) as sal
FROM departments b,
employees c
WHERE b.parent_id =90
AND b.department_id = c.department_id
GROUP BY b.department_id ) d
ON ( a.department_id = d.department_id)
WHEN MATCHED THEN
UPDATE SET a.salary = d.sal;
ROLLBACK;
인라인 뷰
● FROM절에 사용하는 서브쿼리를 말한다
SELECT a.employee_id,
a.emp_name,
b.department_id,
b.department_name
FROM employees a,
departments b,
(
SELECT AVG(c.salary) AS avg_salary
FROM departments b,
employees c
WHERE b.parent_id = 90 -- 기획부
AND b.department_id = c.department_id
) d
WHERE a.department_id = b.department_id
AND a.salary > d.avg_salary;ora_user_quiz
-----------------------------------------------------------------------------
--DB06 26/1/30
-----------------------------------------------------------------------------
--chap06_ex1
--1. 101번 사원에 대해 아래의 결과를 산출하는 쿼리를 작성해 보자.
-----------------------------------------------------------------------------------------
--사번 사원명 job명칭 job시작일자 job종료일자 job수행부서명
-----------------------------------------------------------------------------------------
SELECT a.employee_id "사번",
a.emp_name "사원명",
b.job_id "job명칭",
b.start_date "job 시작일자",
b.end_date "job 종료일자",
c.department_name "job 수행부서명"
FROM employees a,
job_history b,
departments c,
jobs d
WHERE a.employee_id = b.employee_id
and b.department_id = c.department_id
and b.job_id = d.job_id
and a.employee_id = 101;
--2. 아래의 쿼리를 수행하면 오류가 발생한다. 오류의 원인은 무엇인가?
--select a.employee_id, a.emp_name, b.job_id, b.department_id
--from employees a,
--job_history b
--where a.employee_id = b.employee_id(+)
--and a.department_id(+) = b.department_id;
select a.employee_id, a.emp_name, b.job_id, b.department_id
from employees a,
job_history b
where a.employee_id = b.employee_id(+)
and a.department_id = b.department_id(+);
--3. 다음의 쿼리를 ANSI 문법으로 변경해 보자.
SELECT a.department_id, a.department_name
FROM departments a, employees b
WHERE a.department_id = b.department_id
AND b.salary > 3000
ORDER BY a.department_name;
---------------------------------------------------
SELECT a.department_id, a.department_name
FROM departments a
INNER JOIN employees b
ON a.department_id = b.department_id
WHERE b.salary > 3000
ORDER BY a.department_name;
--4. 다음은 연관성 있는 서브쿼리이다. 이를 연관성 없는 서브쿼리로 변환해 보자.
SELECT a.department_id, a.department_name
FROM departments a
WHERE EXISTS ( SELECT 1
FROM job_history b
WHERE a.department_id = b.department_id );
SELECT a.department_id, a.department_name
FROM departments a
WHERE a.department_id IN ( SELECT department_id
FROM job_history );
ex_user
-----------------------------------------------------------------
-----------------------------------------------------------------
-- chap06_ex2
-- 26/1/30
-----------------------------------------------------------------
-----------------------------------------------------------------
--1. Equi(동등)조인을 사용하여 SCOTT 사원의 부서 번호와 부서이름을 출력하세요.
SELECT a.ename, a.dno, b.dname
FROM employee a, department b
WHERE a.dno = b.dno
AND a.ename = 'SCOTT';
--2. Inner 조인과 on연산자를 사용하여 사원이름과함께 그 사원이
--소속된 부서이름과 지역명을 출력하세요.
SELECT a.ename, b.dname, b.loc
FROM employee a
INNER JOIN department b
on a.dno = b.dno;
--3. INNER 조인 Using 연산자를 사용하여 10번 부서에 속하는
--모든 담당업무의 고유 목록을 부서의 지역명을 포함하여 출력하세요.
SELECT dno, job, loc
FROM employee
INNER JOIN department
USING (dno)
WHERE dno = 10 ;
--4. Equi(동등) 조인과 Wild카드를 사용해서 이름에 A가 포함된 모든 사원의 이름과 부서명을 출력하세요.
SELECT a.ename, b.dname
FROM employee a, department b
WHERE a.dno = b.dno
AND a.ename LIKE '%A%';
--5. Self Join을 사용하여 사원의 이름및 사원 번호를 관리자 이름 및 관리자 번호와 함께 출력하세요.
--각 열의 별칭은 사원이름(Employee) 사원번호(emp#) 관리자이름(Manager) 관리자번호(Mgr#)
SELECT a.ename AS Employee,
a.eno AS emp#,
b.ename AS Manager,
b.eno AS Mgr#
FROM employee a, employee b
WHERE a.manager = b.eno;
--6. Self조인을 사용하여 지정한 사원(SCOTT)의 이름, 부서번호, 지정한 사원과
--동일한 부서에서 근무하는 사원을 출력하세요
--각 열의 별칭은 이름, 부서번호, 동료로 지정하세요
SELECT a.ename as "이름", a.dno as "부서번호", b.ename as "동료"
FROM employee a, employee b
WHERE a.dno = b.dno
AND a.ename = 'SCOTT'
AND b.ename <> 'SCOTT';
--7. Self 조인을 사용하여 WARD 사원보다 늦게 입사한 사원의 이름과 입사일을 출력하세요.,
SELECT a.ename, a.hiredate
FROM employee a, employee b
WHERE a.hiredate > b.hiredate
AND b.ename = 'WARD'
ORDER BY a.hiredate;
--8. Self조인을 사용하여 관리자보다 먼저 입사한 모든 사원의 이름 및 입사일을
--관리자의 이름 및 입사입과 함께 출력하세요.
--각 열의 별칭은 사원이름(Ename) 사원입사일(HIERDATE) 관리자 이름 (Ename) 관리자 입사입(HIERDATE)로 출력하세요.
SELECT a.ename as "Ename",
a.hiredate as "HIREDATE",
b.ename as "Ename",
b.hiredate as "HIREDATE"
FROM employee a, employee b
WHERE a.manager = b.eno
AND a.HIREDATE <= b.hiredate;
'대우개발원 수업 내용 > Database (sql)' 카테고리의 다른 글
| Database 10일차 (0) | 2026.02.04 |
|---|---|
| Database 9일차 (9) | 2026.02.03 |
| Database 7일차 (0) | 2026.01.29 |
| Database 6일차 (0) | 2026.01.28 |
| Database 5일차 (26) | 2026.01.27 |