Hong JunYi

Hong JunYi

준이의 개발 블로그

공부한 것들 기록하는 공간

SQL문 연습 - 3 / DML - JOIN

Date:     Updated:

카테고리:

태그:

img

DML - JOIN

1. JOIN

  • JOIN은 2개의 릴레이션에서 연관된 튜플들을 결합하여, 하나의 새로운 릴레이션을 반환한다.
  • JOIN은 일반적으로 FROM 절에 기술하지만, 릴레이션이 사용되는 곳 어디에나 사용할 수 있다.
  • JOIN은 크게 INNER JOIN과 OUTER JOIN으로 구분된다.

2. INNER JOIN

INNER JOIN은 일반적으로 EQUI JOIN과 NON-EQUI JOIN으로 구분된다.

  • 조건이 없는 INNER JOIN을 수행하면 CROSS JOIN과 동일한 결과를 얻을 수 있다.
    • CROSS JOIN (교차 조인) : 교차 조인은 조인하는 두 테이블에 있는 튜플들의 순서쌍을 결과로 반환한다.
    • 교차 조인의 결과로 반환되는 테이블의 행의 수는 두 테이블의 행 수를 곱한 것과 같다.
  • EQUI JOIN
    • EQUI JOIN은 JOIN 대상 테이블에서 공통 속성을 기준으로 ‘=’ (equal) 비교에 의해 같은 값을 가지는 행을 연결하여 결과를 생성하는 JOIN 방법이다.
    • EQUI JOIN에서 JOIN조건이 ‘=’일 때 동일한 속성이 두 번 나타나게 되는데, 이 중 중복된 속성을 제거하여 같은 속성을 한번만 표기하는 방법을 NATURAL JOIN이라고 한다.

    • EQUI JOIN에서 연결 고리가 되는 공통 속성을 JOIN속성이라고 한다.

    • WHERE절을 이용한 EQUI JOIN의 표기 형식
      SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
  FROM 테이블명1, 테이블명2, ...
  WEHRE 테이블명1.속성명 = 테이블명2.속성명;
    • NATURAL JOIN절을 이용한 EQUI JOIN의 표기형식
      SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
  FROM 테이블명1 NATURAL JOIN 테이블명2;
    • JOIN ~ USING절을 이용한 EQUI JOIN의 표기형식
      SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
  FROM 테이블명1 JOIN 테이블명2 USING(속성명);

📜 학생 테이블 :

학번 이름 학과코드 선배 성적
15 고길동 com   83
16 이순신 han   96
17 김선달 com 15 95
19 아무개 han 16 75
37 박치민   17 55

📜 학과 테이블 :

학과코드 학과명
com 컴퓨터
han 국어
eng 영어

📜 성적등급 테이블 :

등급 최저 최고
A 90 100
B 80 89
C 60 79
D 0 59

EQUI JOIN 예제

학생 테이블과 학과 테이블에서 ‘학과코드’값이 같은 튜플을 JOIN하여 ‘학번’, ‘이름’, ‘학과코드’, ‘학과명’을 출력하는 SQL문을 작성하시오

SELECT 학번, 이름, 학생.학과코드, 학과명 
FROM 학생, 학과
WHERE 학생.학과코드 = 학과.학과코드;

또는

SELECT 학번, 이름, 학생.학과코드, 학과명 
FROM 학생 NATURAL JOIN 학과;

또는

SELECT 학번, 이름. 학생.학과코드, 학과명
FROM 학생 JOIN 학과 USING(학과코드);
  • NON-EQUI JOIN
    • NON-EQUI JOIN은 JOIN 조건에 ‘=’이 아닌 나머지 비교 연산자, 즉 <, >, <>, ≥, ≤ 연산자를 사용하는 JOIN 방법이다.
    • 표기형식
      SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
  FROM 테이블명1, 테이블명2, ...
  WHERE (NON-EQUI JOIN 조건);

NON-EQUI 예제

학생 테이블과 성적등급 테이블을 JOIN하여 각 학생의 ‘학번’, ‘이름’,’성적’,’등급’ 을 출력하는 SQL문을 작성하시오

SELECT 학번, 이름, 성적, 등급 
FROM 학생, 성적등급
WHERE 학생.성적 BETWEEN 성적등급.최저 AND 성적등급.최고;

3. OUTER JOIN

OUTER JOIN은 릴레이션에서 JOIN 조건에 만족하지 않는 튜플도 결과로 출력하기 위한 JOIN 방법으로, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN이 있다.

  • LEFT OUTER JOIN : INNER JOIN의 결과를 구한 후, 우측 항 릴레이션의 어떤 튜플과도 맞지 않는 좌측 항의 릴레이션에 있는 튜플들에 NULL 값을 붙여서 INNER JOIN의 결과에 추가한다.
    • 표기 형식
      SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
  FROM 테이블명1 LEFT OUTER JOIN 테이블명2
  ON 테이블명1.속성명 = 테이블명2 속성명;      -- WHERE이 아니라 ON임 (주의)
    
  또는
    
  SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
  FROM 테이블명1, 테이블명2
  WHERE 테이블명1.속성명 = 테이블명2.속성명(+);
    
  -- LEFT OUTER JOIN 일 때는 조건문의 우측에 (+) 기호를 붙이고,
  -- RIGHT OUTER JOIN 일 때는 조건문의 좌측에 (+) 기호를 붙인다.
  • RIGHT OUTER JOIN
    • INNER JOIN의 결과를 구한 후, 좌측 항 릴레이션의 어떤 튜플과도 맞지 않는 우측 항의 릴레이션에 있는 튜플들에 NULL 값을 붙여서 INNER JOIN의 결과에 추가한다.
    • 표기형식의 큰 틀은 LEFT OUTER JOIN 과 동일함.
  • FULL OUTER JOIN
    • LEFT OUTER JOIN과 RIGHT OUTER JOIN을 합쳐 놓은 것이다.
    • INNER JOIN의 결과를 구한 후, 좌측항의 릴레이션의 튜플들에 대해 우측 항의 릴레이션의 어떤 튜플과도 맞지 않는 튜플들에 NULL 값을 붙여서 INNER JOIN의 결과에 추가한다.
    • 그리고 유사하게 우측 항의 릴레이션의 튜플들에 대해 좌측 항의 릴레이션의 어떤 튜플과도 맞지 않는 튜플들에 NULL 값을 붙여서 INNER JOIN의 결과에 추가한다.
    • 표기 형식
      SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
  FROM 테이블명1 FULL OUTER JOIN 테이블명2
  ON 테이블명1.속성명 = 테이블명2.속성명;

OUTER JOIN 예제 1

학생 테이블과 학과 테이블에서 ‘학과코드’ 값이 같은 튜플을 JOIN 하여 ‘학번’, ‘이름’, ‘학과코드’, ‘학과명’을 출력하는 SQL 문을 작성하시오. 이때, ‘학과코드’가 입력되지 않은 학생도 출력하시오.

SELECT 학번, 이름, 학생.학과코드, 학과명 
FROM 학생 LEFT OUTER JOIN 학과
ON 학생.학과코드 = 학과.학과코드;

또는

SELECT 학번, 이름, 학생.학과코드, 학과명
FROM 학생, 학과
WHERE 학생.학과코드 = 학과.학과코드(+);

⚠️ INNER JOIN을 하면 ‘학과코드’가 입력되지 않은 “박치민”은 출력되지 않는다. 그러므로 JOIN 구문을 기준으로 왼쪽 테이블, 즉 (학생)의 자료는 모두 출력되는 LEFT JOIN을 사용한 것.

OUTER JOIN 예제 2

학생 테이블과 학과 테이블에서 ‘학과코드’값이 같은 튜플을 JOIN하여 ‘학번’,‘이름’, ‘학과코드’, ‘학과명’을 출력하는 SQL을 작성하시오. 이때, ‘학과코드’가 입력 안 된 학생이나 학생이 없는 ‘학과코드’도 모두 출력하시오.

SELECT 학번, 이름, 학과.학과코드, 학과명
FROM 학생 FULL OUTER JOIN 학과
ON 학생.학과코드 = 학과.학과코드;

-- 학생테이블에서 학과코드를 가져오게 되면 eng가 표시되지않는다.


1. 일반 형식

SELECT [PREDICATE] [테이블명.]속성명 [AS 별칭][, [테이블명.]속성명, ...]
[, 그룹함수(속성명) [AS 별칭]]
[, WINDOW 함수 OVER (PARTITION BY 속성명1, 속성명2 ... ORDER BY 속성명3, 속성명4, ...)[AS 별칭]]
FROM 테이블명[, 테이블명, ...]
[WHERE 조건]
[GROUP BY 속성명, 속성명, ...]
[HAVING 조건]
[ORDER BY 속성명 [ASC | DESC]];
  • 그룹함수 : GROUP BY 절에 지정된 그룹별로 속성의 값을 집계할 함수를 기술함
  • WINDOW 함수 : GROUP BY 절을 이용하지 않고 속성의 값을 집계할 함수를 기술함
    • PARTITION BY : WINDOW 함수의 적용범위가 될 속성을 지정함
    • ORDER BY : PARTITION 안에서 정렬 기준으로 사용할 속성을 지정함
  • GROUP BY절 : 특정 속성을 기준으로 그룹화하여 검색할 때 사용한다. 일반적으로 GROUP BY 절은 그룹 함수와 함께 사용됨
  • HAVING 절 : GROUP BY와 함께 사용되며, 그룹에 대한 조건을 지정함


2. 그룹함수

그룹함수는 GROUP BY절에 지정된 그룹별로 속성의 값을 집계할 때 사용된다.

함수 기능
COUNT(속성명) 그룹별 튜플 수를 구하는 함수
SUM(속성명) 그룹별 합계를 구하는 함수
AVG(속성명) 그룹별 평균을 구하는 함수
MAX(속성명) 그룹별 최대값을 구하는 함수
MIN(속성명) 그룹별 최소값을 구하는 함수
STDDEV(속성명) 그룹별 표준편차를 구하는 함수
VARIANCE(속성명) 그룹별 분산을 구하는 함수
ROLLUP(속성명, 속성명, …) - 인수로 주어진 속성을 대상으로 그룹별 소계를 구하는 함수
- 속성의 개수가 N개이면, N+1 레벨까지, 하위 레벨에서 상위 레벨 순으로 데이터가 집계됨.  
CUBE(속성명, 속성명, … ) - ROLLUP과 유사한 형태지만, CUBE는 인수로 주어진 속성을 대상으로 모든 조합의 그룹별 소계를 구함
- 속성의 개수가 N개이면, 2의 N제곱 레벨까지, 상위레벨에서 하위 레벨 순으로 데이터가 집계됨.  


3. WINDOW 함수

  • WINDOWS 함수는 GROUP BY 절을 이용하지 않고 함수의 인수로 지정한 속성의 값을 집계한다.
  • 함수의 인수로 지정한 속성이 집계할 범위가 되는데, 이를 윈도우(WINDOW)라고 부른다.
  • WINDOW 함수
    • ROW_NUMBER() : 윈도우별로 각 레코드에 대한 일련번호를 반환한다.
    • RANK() : 윈도우별로 순위를 반환하며, 공동 순위를 반영한다.
    • DENSE_RANK() : 윈도우별로 순위를 반환하며, 공동순위를 무시하고 순위를 부여한다.


4. WINDOW 함수 이용 검색

  • GROUP BY 절을 이용하지 않고 함수의 인수로 지정한 속성을 범위로 하여 속성의 값을 집계한다.

<상여금>

부서 이름 상여내역 상여금
기획 홍길동 연장근무 100
기획 일지매 연장근무 100
기획 최준호 야간근무 120
기획 장길산 특별근무 90
인터넷 강건달 특별근무 90
인터넷 서국현 특별근무 90
인터넷 박인식 연장근무 30
편집 김선달 특별근무 80
편집 황종근 연장근무 40
편집 성춘향 야간근무 80
편집 임꺽정 야간근무 80
편집 황진이 야간근무 50


예제 1

상여금 테이블에서 ‘상여내역’ 별로 ‘상여금’에 대한 일련 번호를 구하시오. (단, 순서는 내림차순이며, 속성명은 ‘NO’로 할 것)

SELECT 상여내역, 상여금, ROW_NUMBER() OVER(PARTITION BY 상여내역 ORDER BY 상여금 DESC)
AS NO 
FROM 상여금;


예제2

상여금 테이블에서 ‘상여내역’별로 ‘상여금’에 대한 순위를 구하시오. (단, 순서는 내림차순이며, 속성명은 ‘상여금순위’로 하고, RANK() 함수를 이용할 것)

SELECT 상여내역, 상여금 RANK() OVER(PARTITION BY 상여내역 ORDER BY 상여금 DESC)
AS "상여금순위"
FROM 상여금;


5. 그룹 지정 검색

GROUP BY절에 지정한 속성을 기준으로 자료를 그룹화하여 검색한다.


예제1

상여금 테이블에서 ‘부서’별 ‘상여금’의 평균을 구하시오

SELECT 부서, AVG(상여금) AS "평균상여금"
 FROM 상여금
GROUP BY 부서;


예제2

상여금 테이블에서 부서별 튜플 수를 검색하시오.

SELECT 부서, COUNT(부서) AS "부서별인원수"
FROM 상여금
GROUP BY 부서;


예제3

상여금 테이블에서 ‘상여금’이 100 이상인 사원이 2명 이상인 ‘부서’의 튜플 수를 구하시오

SELECT 부서, COUNT(*) AS "사원수"
FROM 상여금
WHERE '상여금' >= 100 
GROUP BY 부서
HAVING COUNT(*) >= 2;


예제4

상여금 테이블의 ‘부서’, ‘상여내역’, 그리고 ‘상여금’에 대해 부서별 상여내역별 소계와 전체 합계를 검색하시오. (단, 속성명은 ‘상여금합계’로 하고, ROLLUP 함수를 사용할 것)

SELECT 부서, 상여내역, SUM(상여금) AS "상여금합계"
FROM 상여금
GROUP BY ROLLUP(부서, 상여내역);


예제 5

상여금 테이블의 ‘부서’, ‘상여내역’, 그리고 ‘상여금’에 대해 부서별 상여내역별 소계와 전체 합계를 검색하시오. (단, 속성명은 ‘상여금합계’로 하고, CUBE 함수를 사용할 것)

SELECT 부서, 상여내역, SUM(상여금) AS "상여금합계"
FROM 상여금
GROUP BY CUBE(부서, 상여내역);


6. 집합 연산자를 이용한 통합 질의

집합 연산자를 사용하여 2개 이상의 테이블의 데이터를 하나로 통합한다.

표기형식

SELECT 속성명1, 속성명2, ...
FROM 테이블명
UNION | UNION ALL | INTERSECT | EXCEPT
SELECT 속성명1,  속성명2, ...
FROM 테이블명
[ORDER BY 속성명 [ASC | DESC]];
  • 두 개의 SELECT문에 기술한 속성들은 개수와 데이터 유형이 서로 동일해야 한다.

  • 집합 연산자의 종류 (통합 질의의 종류)

    집합 연산자 설명 집합종류
    UNION - 두 SELECT문의 조회 결과를 통합하여 모두 출력함  
    - 중복된 행은 한 번만 출력함 합집합  
    UNION ALL - 두 SELECT문의 조회 결과를 통합하여 모두 출력함  
    - 중복된 행도 그대로 출력함 합집합  
    INTERSECT - 두 SELECT문의 조회 결과 중 공통된 행만 출력함 교집합
    EXCEPT - 첫 번째 SELECT문의 조회 결과에서 두 번째 SELECT문의 조회 결과를 제외한 행을 출력함 차집합



<사원> 테이블  
사원 직급
김형석 대리
홍영선 과장
류기선 부장
김현천 이사


<직원> 테이블  
사원 직급
신원섭 이사
이성호 대리
홍영선 과장
류기선 부장


예제 1

사원테이블과 직원테이블을 통합하는 질의문을 작성하시오. (단, 같은 레코드가 중복되어 나오지 않게 하시오.)

SELECT * FROM 사원
UNION 
SELECT * FROM 직원;


예제 2

사원 테이블과 직원 테이블에 공통으로 존재하는 레코드만 통합하는 질의문을 작성하시오.

SELECT * FROM 사원
UNION ALL
SELECT * FROM 직원;

info-process 카테고리 내 다른 글 보러가기

댓글 남기기

최근 글 5 개