Oracle/ Natural절 & USING절을 이용한 조인

<자연조인 (Natural Join)>

 

• 동일한 타입과 이름을 가진 칼럼을 조인 조건으로 이용하는 방법
• 보다 간단히 표현가능 
• 등가조인

 

SELECT 컬럼, ... 
FROM 테이블 1 
NATURAL JOIN 테이블 2 
WHERE 일반조건

 

• 여러 테이블에 존재하는 동일 컬럼을 기술 할 시, 이전 조인 문장과 다르게 테이블 이름을 생략하고 컬럼명만 표기한다.
• 반드시 두 테이블 간에 조인할 수 있는 동일 이름, 동일 타입을 가진 컬럼이 필요하다.
• 조인에 이용되는 칼럼은 별도로 명시하지 않아도 자동으로 조인에 사용
• 동일한 이름을 가졌더라도 데이터 타입이 다를 시엔 에러 
• 조인 조건을 쓸 필요가 없다.

 

--자연조건으로 각사원의 근무부서를 검색한다.
SELECT eno, ename, dno, dname
FROM emp
NATURAL JOIN dept;

--자연조인을 사용해서 광주에서 근무하는 직원의 명단을 검색
--부서명과 부서번호도 검색

SELECT loc, dno, dname, eno, ename
FROM emp
NATURAL JOIN dept
WHERE loc='광주';

 

 

 

 

<USING절을 이용한 JOIN>

 

• 두 테이블에 공톨된 칼럼이 2개 이상일 경우
• emp1: eno, name, dno / dept1: dno, name, loc

SELECT eno, loc
FROM dept1
NATURAL JOIN emp1;

SELECT eno, loc
FROM dept1, emp1
WHERE dept1.dno=emp1.dno
AND dept1.name=emp1.name;

-> 선택된 레코드가 없다고 뜸
내추럴 조인은 편리하지만 명확한 칼럼 값이 있을때만 쓸 수 있음

 

• 이럴 때 USING절을 이용하여 명확하게 JOIN을 구사할 수 있다.

SELECT 칼럼, . . . 
FROM 테이블1 
JOIN 테이블2 USING (조인칼럼) 
JOIN 테이블3 USING (조인칼럼), . . . 
WHERE 일반조건;

 

• NATURAL과 USING은 함께 이용할 수 없다.
• 조인에 사용되지 않는 동일 명칭의 칼럼은 컬럼명앞에 테이블명을 기술한다.

--USING절로 각사원의 근무 부서를 검색한다.
SELECT eno, ename, dno, dname
FROM emp
JOIN dept USING (dno);

--USING절로 광주에서 근무하는 직원의 명단을 검색한다. 부서번호 부서명 포함.
SELECT eno, ename, dno, dname, loc
FROM emp
JOIN dept USING (dno)
WHERE loc='광주';




--화학과 1학년 학생들의 유기화학 점수를 검색한다.
SELECT major, syear, sno, sname, cno, cname, result
FROM student
JOIN score USING (sno)
JOIN course USING (cno)
WHERE syear=1
AND major='화학'
AND cname='유기화학';

--내추럴조인으로도 가능
SELECT major, syear, sno, sname, cno, cname, result
FROM student
NATURAL JOIN score 
NATURAL JOIN course 
WHERE syear=1
AND major='화학'
AND cname='유기화학';

 

 

 

 

 

 

<Exercise>

--1.송강교수가 강의하는 과목을 검색
SELECT pno, pname, cno, cname
FROM professor
JOIN course USING (pno)
WHERE pname='송강';


--2. 화학관련 과목을 강의하는 교수를 검색
SELECT pno, pname, cno, cname
FROM professor
JOIN course USING (pno)
WHERE cname LIKE '%화학%';


--3. 학점이 2학점인 과목과 이를 강의하는 교수 검색
SELECT pno, pname, cno, cname, st_num
FROM professor
JOIN course USING (pno)
WHERE st_num=2;


--4. 화학과 교수가 강의하는 과목 검색
SELECT section, pno, pname, cno, cname
FROM professor
JOIN course USING (pno)
WHERE section='화학';


--5. 화학과 1학년의 기말고사 성적 검색
SELECT DISTINCT major, syear, sno, sname, cno, cname, result
FROM student
JOIN score USING (sno)
JOIN course USING (cno)
WHERE major='화학'
AND syear=1;


--6. 일반화학과목의 기말고사 점수 검색
SELECT DISTINCT syear, sno, sname, cno, cname, result
FROM student
JOIN score USING (sno)
JOIN course USING (cno)
WHERE cname='일반화학';


--7. 화학과 1학년 학생의 일반화학 기말고사 점수를 검색
SELECT DISTINCT major, syear, sno, sname, cno, cname, result
FROM student
JOIN score USING (sno)
JOIN course USING (cno)
WHERE major='화학'
AND cname='일반화학'
AND syear=1;


--8. 화학과 1학년 학생이 수강하는 과목을 검색
SELECT DISTINCT major, syear, sno, sname, cno, cname
FROM student
JOIN score USING (sno)
JOIN course USING (cno)
WHERE major='화학'
AND syear=1;