sumin

ANSI 조인은 ANSI SQL 문법을 사용한 조인을 말한다. 지금까지 설명했던 모든 조인을 ANSI SQL을 사용해 변환이 가능하다. 기존 문법과 ANSI 조인의 차이점은 조인 조건이 WHERE절이 아닌 FROM 절에 들어 간다는 점이다. 

1. ANSI 내부 조인

기존 문법과 ANSI 내부 조인 문법의 차이는 다음과 같다.

<기존 문법>

<ANSI 문법>

2003년 1월 1일 이후에 입사한 사원번호, 사원명, 부서번호, 부서명을 조회하는 쿼리를 비교해 보자.

◈ 예제

<기존 문법>

<ANSI 문법>

ANSI 내부 조인은 FROM절에서 INNER JOIN 구문을 쓴다. 조인 조건은 ON 절에 명시하고 조인 조건 외의 조건은 기존대로 WHERE 절에 명시한다. 만약 조인 조건 컬럼이 두 테이블 모두 동일하다면 ON 대신 USING 절을 사용할 수 있는데, 이때는 SELECT절에서 조인 조건에 포함된 컬럼명을 테이블명.컬럼명형태가 아닌 컬럼명만 기술해야 한다.

2. ANSI 외부 조인

ANSI 외부 조인도 그 형식은 내부 조인과 비슷하다. 기존 문법과 비교해 보자.

<기존 문법>

<ANSI 문법>

기존 문법에서는 기준 테이블과 대상 테이블(데이터가 없는 테이블)에서 대상 테이블쪽 조인 조건에 (+)를 붙였지만, ANSI 외부 조인은 FROM 절에 명시된 테이블 순서에 입각해 먼저 명시된 테이블 기준으로 LEFT 혹은 RIGHT를 붙이는 점이 다르다.

◈ 예제

<기존 문법>

<ANSI 문법>

위 쿼리는 사원 테이블이 기준이 되어 job_history 테이블을 LEFT OUTER JOIN으로 연결했는데 테이블 순서를 바꾸면 RIGHT OUTER JOIN으로 변경이 가능하다.

또한 외부 조인은 OUTER라는 키워드는 생략이 가능하다. LEFT JOIN 혹은 RIGHT JOIN이라고 명시해도 이는 외부 조인을 의미한다.

3. CROSS 조인

앞에서 WHERE 절에 조인 조건을 명시하지 않은 카타시안 조인이 있었다. 이를 ANSI 조인에서는 CROSS 조인이라고 하며 다음과 같이 작성한다.

◈ 예제

<기존 문법>

<ANSI 문법>

※ 내부 조인 과 외부조인

1. 셀프 조인

셀프란 말에서 알 수 있듯이, 셀프 조인(SELF-JOIN)은 서로 다른 두 테이블이 아닌 동일한 한 테이블을 사용해 조인하는 방법을 말한다.

◈ 예제

사원 테이블을 A, B로 나누어 조인을 하는데, 같은 부서번호를 가진 사원 중 A 사원번호가 B 사원번호보다 작은 건을 조회하는 쿼리다. 사원 테이블에서 부서번호가 20인 건은 단 2건 뿐인데(201과 202) 조건에 의해 결과는 1건만 추출된다.

2. 외부 조인

① 일반 조인

◈ 예제

쿼리는 부서와  job_history 테이블을 부서번호 값을 조건으로 조인한 결과다. 따라서 job_hisotry에 없는 부서는 조회되지 않았다. 그런데 job_hisotry 테이블에는 없더라도 부서 테이블에 있는 모든 부서를 같이 보고 싶다면 외부 조인을 사용하면된다.

② 외부 조인

◈ 예제

10개가 아닌 31개의 결과가 조회되었고 job_history에 없는 부서(10, 30, 40 등)도 모두 조회되었다. 쿼리를 자세히 보면 조인 조건에 (+) 기호가 붙어 있는데 조인 조건에서 데이터가 없는 테이블의 컬럼에 (+) 기호를 붙이는 것이 바로 외부 조인이다. 당연히 10, 30, 40번 부서는 job_history에 데이터가 없으므로 NULL로 출력되었다. 

외부 조인 시 알아야 할 내용을 정리해 보자.

❶ 조인 대상 테이블 중 데이터가 없는 테이블 조인 조건에 (+)를 붙인다

❷ 외부 조인의 조인 조건이 여러 개일 때 모든 조건에 (+)를 붙인다

❸ 한 번에 한 테이블에만 외부 조인을 할 수 있다. 예를 들어, 조인 대상 테이블이 A, B, C 3개이고, A를 기준으로 B 테이블을 외부 조인으로 연결했다면, 동시에 C를 기준으로 B 테이블에 외부 조인을 걸 수는 없다

❹ (+)연산자가 붙은 조건과 OR를 같이 사용할 수 없다

❺ (+)연산자가 붙은 조건에는 IN 연산자를 같이 사용할 수 없다(단 IN절에 포함되는 값이 1개인 때는 사용 가능)

3. 카타시안 조인

카타시안 조인(CATASIAN PRODUCT)은 WHERE 절에 조인 조건이 없는 조인을 말한다. FROM 절에 테이블을 명시했으나 두 테이블 간 조인 조건이 없는 조인이다. 조인 조건이 없으므로 조인이라 말할 수 없을 수도 있지만 FROM 절에 2개 이상 테이블을 명시했으므로 일종의 조인이다. 조인 조건이 없으므로 그 결과는 두 테이블 건수의 곱이다. A 테이블 건수가 n1, B 테이블 건수가 n2라고 한다면, 결과 건수는 ‘n1 * n2’가 된다.

◈ 예제

사원 테이블의 총 건수는 107건이고 부서 테이블의 총 건수는 27건이므로, 107 * 27 = 2,889건이 조회되었다.

조인의 종류

조인의 종류를 열거해 보면 내부 조인, 외부 조인, 동등 조인, 안티 조인, 셀프 조인, 세미 조인, 카타시안 조인(CATASIAN PRODUCT), ANSI 조인이 있다. 8가지 조인 방법이 상대적으로 독립적인 개념은 아니다. 내부 조인의 상대 개념이 외부 조인이며, 외부 조인을 제외한 셀프 조인, 안티 조인 등은 모두 내부 조인에 포함된다. 또한 ANSI 조인은 7가지 조인을 모두 포함한 개념으로 ANSI SQL을 사용한 점만 다를 뿐이며 일반적으로 ANSI 조인이라고 굳이 구분하지는 않는다

• 조인 연산자에 따른 구분: 동등 조인, 안티 조인

• 조인 대상에 따른 구분: 셀프 조인

• 조인 조건에 따른 구분: 내부 조인, 외부 조인, 세미 조인, 카타시안 조인

• 기타: ANSI 조인

※ 내부 조인과 외부 조인

1. 동등 조인

가장 기본이며 일반적인 조인 방법이 바로 동등 조인(EQUI-JOIN)이다. 동등 조인은 WHERE 절에서 등호(‘=’)연산자를 사용해 2개 이상의 테이블이나 뷰를 연결한 조인이다. 등호 연산자를 사용한 WHERE절 조건에 만족하는 데이터를 추출하는 조인이다. 이때 WHERE절에 기술한 조건을 조인 조건이라고 한다.

조인 조건은 컬럼 단위로 기술한다. 예를 들어, A와 B 테이블이 있다고 한다면 두 테이블에서 공통된 값을 가진 컬럼을 등호 연산자로 연결해 조인 조건에 일치하는 조인 조건 결과가 참에 해당되는 두 컬럼 값의 같은 행을 추출하는 것이다.

◈ 예제

.

.

사원과 부서 테이블에 공통적으로 존재하는 부서번호(department_id)를 등호 연산자를 사용해 조회조건에 명시했다. 부서번호 컬럼은 부서 테이블에서 키(Primary Key)에 해당해 필수 값이지만 사원 테이블에서는 필수 값이 아니므로 쿼리 결과는 사원 테이블에서 부서번호 컬럼 값이 있는 건만 추출된다. 사원 테이블의 전체 건수가 107건이지만 부서번호가 없는 사원이 한 건 존재하므로 106건이 조회된 것이다.

2. 세미 조인

세미 조인(SEMI-JOIN)은 서브 쿼리를 사용해 서브 쿼리에 존재하는 데이터만 메인 쿼리에서 추출하는 조인 방법으로 IN과 EXISTS 연산자를 사용한 조인이다. 서브 쿼리에 있는 테이블을 B, 메인 쿼리에 사용된 테이블을 A라고 한다면 세미 조인은 B 테이블에 존재하는 A 테이블의 데이터를 추출하는 조인이다.

① EXISTS 사용

◈ 예제

② IN 사용

◈ 예제

3. 안티 조인

안티 조인(ANTI-JOIN)은 서브 쿼리의 B 테이블에는 없는 메인 쿼리의 A 테이블의 데이터만 추출하는 조인 방법이다. 한쪽 테이블에만 있는 데이터를 추출하는 것이므로 조회 조건에서 NOT IN이나 NOT EXISTS 연산자를 사용한다. 세미 조인과 반대 개념이다.

① NOT IN

◈ 예제

앞의 쿼리는 manager_id 값이 있는 부서에 속한 사원 테이블을 조회한 것이다. 여기서 주의할 점은 “departments b”를 조인한 부분은 해당 사원에 대한 부서명을 가져오기 위한 조인이고 안티 조인은 NOT IN 절 이하의 서브 쿼리에 명시했다.

다음과 같이 NOT EXISTS로도 변환할 수 있다.

② NOT EXISTS

◈ 예제

SELECT문을 실행하면 해당 조건에 맞는 데이터가 조회된다. 예를 들어 사원 테이블에서 부서번호가 30인 건을 조회하는 쿼리를 작성하면 이 조건에 맞는 일련의 데이터가 조회되는데 결과는 30번 부서에 속한 사원정보 데이터 집합이라고 부르기도 한다. 집합 연산자는 데이터 집합을 대상으로 연산을 수행하는 연산자를 말하며 종류로는 UNION, UNION ALL, INTERSECT, MINUS가 있다.

데이터 집합이 대상이므로 집합 연산자를 사용할 때 데이터 집합의 수는 한 개 이상을 사용할 수 있다. 여러 개의 SELECT문을 연결해 또 다른 하나의 쿼리를 만드는 역할을 하는 것이 집합 연산자다. 

1. UNION

UNON은 합집합을 의미한다. 예를 들어, 두 개의 데이터 집합이 있으면 각 집합 원소(SELECT 결과)를 모두 포함한 결과가 반환된다. 예제를 통해 알아 보자.

◈ 예제

exp_goods_asia 테이블을 생성해 한국과 일본의 주요 10대 수출품을 입력했다. 다음 쿼리를 실행해 보자.

위는 한국과 일본의 수출품을 조회한 쿼리다. 그 결과로 각각 하나의 데이터 집합을 반환했다. 국가에 상관없이 모든 수출품을 조회하는데, 단 자동차나 선박과 같이 두 국가가 겹치는 수출품목은 한 번만 조회되도록 하려면 UNION(합집합 개념)을 사용한다.

exp_goods_asia 테이블에는 한국, 일본별로 수출품이 10개씩 총 20건이 등록되어 있는데, 자동차나 선박 등 겹쳐 있는 5개 품목은 단 한 번만 조회되었다. 즉 합집합 개념이 적용되어 데이터가 추출되었다.

2. UNION ALL

UNION ALL은 UNION과 비슷한데 한 가지 다른 것은 중복된 항목도 모두 조회된다는 점이다.

◈ 예제

3. INTERSECT

INTERSECT는 합집합이 아닌 교집합을 의미한다. 데이터 집합에서 공통된 항목만 추출해 낸다.

◈ 예제

4. MINUS

MINUS는 차집합을 의미한다. 즉 한 데이터 집합을 기준으로 다른 데이터 집합과 공통된 항목을 제외한 결과만 추출해 낸다.

◈ 예제

이 쿼리는 한국에는 있지만 일본에는 없는 수출품 목록을 조회한 것이다. 반대로 일본에만 있는 수출품을 뽑아 내려면 데이터 집합 순서를 바꿔주면 된다. MINUS는 먼저 위치한 SELECT문이 기준이 된다.

1. GROUP BY 절

집계 함수는 사원 전체를 기준으로 데이터를 추출했는데 특정 그룹을 묶어서 데이터를 집계할 수도 있다. 이때 사용되는 구문이 GROUP BY 절이다. 그룹으로 묶을 컬럼명이나 표현식을 GROUP BY 절에 명시해서 사용하며 GROUP BY 구문은 WHERE와 ORDER BY절 사이에 위치한다.

기본구문

◈ 예제

사원 테이블에서 각 부서별 급여의 총액을 구했다. 위 결과를 보면 30번 부서에 속한 사원들의 급여를 모두 합하면 24900 임을 알 수 있다. 또 다른 쿼리를 수행해 보자.

kor_loan_status 테이블에는 월별, 지역별 가계대출 잔액(단위는 십억)이 들어 있고, 대출유형(gubun)은 ‘주택담보대출’과 ‘기타대출’ 두 종류만 존재한다. 그럼 2013년 지역별 가계대출 총 잔액을 구해 보자.

이번엔 2013년 11월 총 잔액만 구해 보자.

그룹 쿼리를 사용하면 SELECT 리스트에 있는 컬럼명이나 표현식 중 집계 함수를 제외하고는 모두 GROUP BY절에 명시해야 하는데 앞의 쿼리는 period 컬럼을 명시하지 않아 오류가 난 것이다. 2013년 데이터는 2013년 10월과 11월만 존재하며 WHERE 절에서 기간을 201311로 주었으므로 굳이 period를 그룹에 포함시킬 필요는 없지만, 구문 문법상 GROUP BY 절에 포함시켜야 한다.

2. HAVING 절

HAVING 절은 GROUP BY절 다음에 위치해 GROUP BY한 결과를 대상으로 다시 필터를 거는 역할을 수행한다. HAVING 필터 조건 형태로 사용한다. 쿼리에서 대출잔액이 100조 이상인 건만 추출한다면 다음과 같이 쿼리를 작성하면 된다.

◈ 예제

WHERE 절은 쿼리 전체에 대한 필터 역할을 하고 HAVING 절은 WHERE 조건을 처리한 결과에 대해 GROUP BY를 수행 후 산출된 결과에 대해 다시 조건을 걸어 데이터를 걸러낸다.

※ 집계함수

대상 데이터를 특정 그룹으로 묶은 다음 이 그룹에 대해 총합, 평균, 최댓값, 최솟값 등을 구하는 함수를 말한다.

1. COUNT (expr)

COUNT는 쿼리 결과 건수, 즉 전체 로우 수를 반환하는 집계 함수다. 테이블 전체 로우는 물론 WHERE 조건으로 걸러진 로우 수를 반환한다.

◈ 예제

사원 테이블의 수가 107건임을 알 수 있다. 대부분은 COUNT(*) 형태로 사용한다.

2. SUM(expr)

SUM은 expr의 전체 합계를 반환하는 함수로 매개변수 expr에는 숫자형만 올 수 있다. 사원 테이블에서 급여가 숫자형이므로 전 사원의 급여 총액을 구해 보자.

◈ 예제

expr 앞에 DISTINCT를 붙이면 뒤따라 나오는 컬럼에 있는 유일한 값만 조회된다. 이때는 중복된 급여는 1번만 셈해진 전체 합계를 반환한다.

◈ 예제

3. AVG(expr)

AVG는 매개변수 형태나 쓰임새는 COUNT, SUM과 동일하며 평균값을 반환한다.

◈ 예제

4. MIN(expr), MAX(expr)

MIN과 MAX는 각각 최솟값과 최댓값을 반환한다.

◈ 예제

5. VARIANCE(expr), STDDEV(expr)

VARIANCE는 분산을 STDDEV는 표준편차를 구한다. 분산이란 주어진 범위의 개별 값과 평균값과의 차이인 편차를 구해 이를 제곱해서 평균한 값을 말하고 표준편차는 분산 값의 제곱근이다. 분산은 제곱한 평균이므로, 실제로 통계에서는 평균을 중심으로 값들이 어느 정도 분포하는지를 나타내는 수치인 표준편차를 지표로 사용한다.

◈ 예제