SQL - JOIN

• 조인 ( join )
  • 상호 관련성을 갖는 두 개 이상의 테이블로부터 새로운 테이블을 생성하는데 사용된다

 

이퀴조인 ( EQUI ) = 등가조인

• where 절에서 '=' 연산자 사용
• 테이블 연결 후 출력 행을 각 테이블의 특정 열에 일치한 데이터를 기준으로 선정하는 방식
• 내부조인 ( inner join ), 단순조인 (simple join)으로 부르기도 한다

• natural join  :  자연 조인
  • 등가 조인을 대신해 사용할수 있다
  • 열의 이름이 같을 때 사용 가능
  • ( 2쌍 이상이 존재한다면 쓸 수 없다)

-- STUDENT, ENROL 합쳐서 다 출력
SELECT STUDENT.STU_NO, STU_NAME, STU_DEPT
FROM STUDENT, ENROL
WHERE STUDENT.STU_NO = ENROL.STU_NO;

-- natural join
SELECT STU_NO, STU_NAME, ENR_GRADE 
FROM STUDENT NATURAL JOIN ENROL;

 

• join ~ using
  • 테이블의 열의 이름이 같은것이 2개 이상일 때

SELECT STU_NO, STU_NAME, ENR_GRADE FROM STUDENT NATURAL JOIN ENROL;
-- JOIN ~ USING
SELECT STU_NO, STU_NAME, ENR_GRADE FROM STUDENT JOIN ENROL USING(STU_NO);

 

 

NON-EQUI 조인

• 조인 조건에 '=' 외의 비교연산자를 사용한 조인 

SELECT STU_NAME, ENR_GRADE,
       SUB_NO AS 과목번호
    FROM STUDENT NATURAL JOIN ENROL
  WHERE ENR_GRADE BETWEEN 80 AND 90;

 

SELF JOIN - 자체조인

• 자기 자신 테이블과의 조인이 요구될 때, 자기 자신과의 조인이 사용됨
• FROM 절에 같은 테이블의 이름이 두개 존재함 
  • 이 때, 테이블의 별칭을 사용하여 조인을 구하게 됨

-- 상급자사원번호 = 사원번호 같은것
SELECT A.EMPNO AS 사번, A.ENAME AS 사원이름,
       B.EMPNO AS 상급자사번, B.ENAME AS 상급자사원이름
    FROM EMP1 A, EMP1 B
  WHERE A.MGR = B.EMPNO;

 

OUTER JOIN - 외부조인

• 조인 조건을 만족하지 않은 행들도 질의 결과에 포함하기 위핸 조인
• 2개 이상의 테이블이 조인될 때 한쪽 테이블에는 해당하는 데이터가 있으나
• 다른 테이블에는 없는 경우 그 데이터는 출력되지 않는 문제를 해결하기 위한 조인
• RIGHT OUTER JOIN
  • 조인문의 오른쪽에 있는 테이블의 모든 결과를 가져온 후
  • 왼쪽 테이블의 데이터를 매칭하고, 매칭되는 데이터가 없으면 NULL 표시한다

-- ENROL 테이블의 모든 정보, 과목이름
SELECT A.*, 
       SUB_NAME AS 과목명
    FROM ENROL A, SUBJECT B
  WHERE A.SUB_NO(+) = B.SUB_NO
ORDER BY 1;

-- 과목테이블도 다 출력(외부 조인)
SELECT A.*, SUB_NAME
    FROM ENROL A RIGHT OUTER JOIN SUBJECT B
  ON A.SUB_NO = B.SUB_NO
ORDER BY 1;

 

 

 

 

 

 

 

-- JOIN
-- 부서번호가 같을 때 테이블을 조인해서 출력하겠다
SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;
-- STUDENT, ENROL 합쳐서 다 출력
SELECT STUDENT.STU_NO, STU_NAME, STU_DEPT
FROM STUDENT, ENROL WHERE STUDENT.STU_NO = ENROL.STU_NO;
-- NATURAL JOIN
SELECT STU_NO, STU_NAME, ENR_GRADE FROM STUDENT NATURAL JOIN ENROL;
-- JOIN ~ USING
SELECT STU_NO, STU_NAME, ENR_GRADE 
FROM STUDENT JOIN ENROL USING(STU_NO);
select student.stu_no, stu_name, stu_dept, enr_grade from student join enrol on student.stu_no=enrol.stu_no;

-- 학생테이블과 수강테이블을 NATURAL JOIN 하시오
SELECT * FROM STUDENT NATURAL JOIN ENROL;
-- 과목이름과 학번, 점수를 검색하시오 (NATURAL JOIN)
SELECT SUB_NAME, STU_NO, ENR_GRADE
    FROM SUBJECT 
    NATURAL JOIN ENROL;
-- -- 과목이름과 학번, 점수를 검색하시오 (JOIN ~ USING)
SELECT SUB_NAME, STU_NO, ENR_GRADE
    FROM SUBJECT 
    JOIN ENROL USING(SUB_NO);

-- 점수가 70점 이상인 학생 이름을 검색 (이큐조인)
SELECT STU_NAME
    FROM STUDENT, ENROL
WHERE STUDENT.STU_NO = ENROL.STU_NO
    AND ENR_GRADE >= 70;
-- 점수가 60점 이상인 학생 이름 J.U (JOIN ~ USING)
SELECT STU_NAME
    FROM STUDENT
    JOIN ENROL USING (STU_NO)
WHERE ENR_GRADE >= 60;
-- 70점 이하인 학생 (NATURAL JOIN)
SELECT STU_NAME
    FROM STUDENT
    NATURAL JOIN ENROL
WHERE ENR_GRADE <= 70;
-- 강종영 교수가 강의하는 과목을 수강하는 학생 이름 (이큐조인)
SELECT STU_NAME
    FROM STUDENT, SUBJECT, ENROL
WHERE STUDENT.STU_NO=ENROL.STU_NO
    AND ENROL.SUB_NO=SUBJECT.SUB_NO
    AND SUB_PROF='강종영';
-- 컴퓨터개론을 수강하는 학생들의 학번과 이름 (이큐조인)
SELECT STUDENT.STU_NO, STU_NAME
    FROM STUDENT, SUBJECT, ENROL
WHERE STUDENT.STU_NO = ENROL.STU_NO
    AND ENROL.SUB_NO = SUBJECT.SUB_NO
    AND SUB_NAME = '컴퓨터개론';
    
    
-- 1. ADAMS사원이 근무 중인 부서이름과 지역을검색하라.(equi join)
SELECT D.DNAME, D.LOC
    FROM EMP1 E, DEPT1 D
  WHERE E.DEPTNO = D.DEPTNO
    AND E.ENAME = 'ADAMS';

-- 2. 급여가 2000이상인 사원들의 사원명과 지역을검색하라. (natural join)
SELECT ENAME, LOC
    FROM EMP1 NATURAL JOIN DEPT1
  WHERE SAL >= 2000;

-- 3. 2번을equi join으로 바꿔라.
SELECT E.ENAME, D.LOC
    FROM EMP1 E, DEPT1 D
  WHERE E.DEPTNO = D.DEPTNO
    AND E.SAL >= 2000;

-- 4. 급여가 1000 이상 2000 이하인 사원들의 사원번호, 사원이름, 부서이름을 사원번호순으로 검색하라. (join using)
SELECT EMPNO, ENAME, DNAME, SAL
    FROM EMP1 JOIN DEPT1 USING (DEPTNO)
  WHERE SAL BETWEEN 1000 AND 2000
ORDER BY EMPNO;

-- 5. 사원직무가 SALESMAN이면서 CHICAGO 지역에 근무 중인 사원명을 검색하라.
SELECT ENAME, JOB, LOC
    FROM EMP1 NATURAL JOIN DEPT1
  WHERE JOB = 'SALESMAN'
    AND LOC = 'CHICAGO';

-- 6. NEW YORK이나 DALLAS 지역에 근무하는 사원들의 사원번호와 사원이름을 사원번호 순으로 검색하라. (equi join)
SELECT E.EMPNO, E.ENAME, D.LOC
    FROM EMP1 E, DEPT1 D
  WHERE E.DEPTNO = D.DEPTNO
    AND D.LOC IN ('NEW YORK', 'DALLAS')
ORDER BY E.EMPNO;

-- 7. 부서이름이 ACCOUNTING 이거나, 지역이 CHICAGO 인 사원의 사원번호와 사원이름을 검색하라. (equi join)
SELECT  E.EMPNO, E.ENAME, 
        D.DNAME AS 부서이름, D.LOC AS 지역
    FROM EMP1 E, DEPT1 D
  WHERE E.DEPTNO = D.DEPTNO
    AND (D.DNAME = 'ACCOUNTING' OR D.LOC = 'CHICAGO');

-- 8. NEW YORK 이나 DALLAS 지역에 근무하는 사원들의 사원번호와 사원이름을 사원번호 순으로 검색하라. (natural join)
SELECT EMPNO, ENAME, LOC AS 지역
    FROM EMP1 NATURAL JOIN DEPT
  WHERE LOC IN ('NEW YORK', 'DALLAS')
ORDER BY EMPNO;