'SQL' 카테고리의 글 목록 (2 Page)

SQL

[SQL 16] 서브 쿼리 - 단일 행 서브 쿼리 2017.12.10
[SQL 15] 조인 - ON 절, 외부 조인, 교차 조인(Cross Join) 2017.12.10
[SQL 14] 조인 - 자연 조인(Natural Join), Using 절을 이용한 조인 2017.12.08
[SQL 13] 조인 - 자체 조인(Self Join), 외부 조인(Outer Join) 2017.12.08
[SQL 12] 조인 문을 쉽게 만드는 Tip 2017.12.08
[SQL 11] 조인 - 등가조인, 비등가조인 2017.12.08
[SQL 10] 그룹 함수, GROUP BY 절, HAVING 절 2017.12.04 1
[SQL 09] 단일 행 함수 - 일반 함수 & 조건 표현식(CASE, DECODE) 2017.12.03
[SQL 08] 단일 행 함수 - 변환 함수 2017.12.03
[SQL 07] 단일 행 함수 - 숫자, 날짜 함수 2017.12.03

[SQL 16] 서브 쿼리 - 단일 행 서브 쿼리

2017. 12. 10. 18:26

샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.

No. 01

서브 쿼리(Sub Query)란?

서브 쿼리란 SQL문장 내에 쓰인 SELECT 문장을 의미하며, 이때 SQL문장은 메인 쿼리(Main Query)라 불린다.

서브 쿼리는 검색되는 결과의 유형에 따라 다음과 같이 나뉜다.

종 류	설 명
단일 행 서브 쿼리	서브 쿼리가 하나의 컬럼에서 하나의 행을 검색한다.
다중 행 서브 쿼리	서브 쿼리가 하나의 컬럼에서 여러 개의 행을 검색한다.
다중 열 서브 쿼리	서브 쿼리가 여러 개의 컬럼을 검색한다.

WHERE절, HAVING절과 같이 조건 절에 주로 쓰인다.
서브 쿼리는 반드시 괄호로 묶는다.
서브 쿼리는 메인 쿼리 실행 전에 실행된다.
서브 쿼리의 검색된 결과 값은 메인 쿼리에 사용된다.

No. 02

단일 행 서브 쿼리

서브 쿼리 문장에서 단 하나의 행과 열을 검색하는 SQL문장이다.

기본 구조

SELECT [DISTINCT] 컬럼, 컬럼 …

FROM 테이블

WHERE 컬럼 <단일 행 연산자> (SELECT 컬럼

FROM 테이블);

반드시 서브 쿼리의 결과 값은 한 개만 검색돼야 한다.
단일 행 연산자(=, <, >, <=, >=, !=) 오른쪽에 기술한다.
WHERE절에 기술된 컬럼의 타입은 서브 쿼리의 결과와 같아야 한다.

◈ 예제

select last_name, salary

from employees

where salary > (select salary

from employees

where last_name = 'Abel');

위의 SQL문은 Abel보다 급여가 높은 사원의 정보를 검색한 것이다.

서브 쿼리를 따로 검색해보면

select salary

from employees

where last_name = 'Abel';

Abel의 급여는 11000이다.

즉, 결과적으로 보면 급여가 11000보다 큰 사원의 정보를 검색한 것이다.

서브쿼리를 사용함으로써 Abel의 급여가 11000 이라는 정보를 모르더라도 조건문을 만들 수 있다.

▶ 단일 행 서브 쿼리 에러

select employee_id, last_name, salary

from employees

where salary > (select salary

from employees

where last_name = 'Smith');

ORA-01427: single-row subquery returns more than one row

01427. 00000 - "single-row subquery returns more than one row"

단일 행 연산자를 사용할 때는 위와 같은 경우를 조심해야 한다. 위의 SQL 문장이 에러가 발생하는 이유는 서브쿼리를 따로 실행시켜 보면 알겠지만 'Smith'라는 성을 가진 사원이 2명이라서 서브 쿼리의 결과 값이 두 개가 검색되기 때문이다.

select employee_id, last_name

from employees

where salary = (select min(salary)

from employees

group by department_id);

ORA-01427: single-row subquery returns more than one row

01427. 00000 - "single-row subquery returns more than one row"

이번에도 마찬가지로 서브 쿼리의 결과가 여러 행이 나왔기 때문에 에러가 발생했다.

서브 쿼리를 group by 절로 묶어줌으로써 각 부서별로 최소 급여가 검색되어 12개의 행이 검색됐기 때문이다.

단일 행 연산자를 사용할 때는 반드시 결과 값은 한 개만 검색돼야 한다.

▶ 여러 번 사용된 단일 행 서브 쿼리

select employee_id, last_name, department_id, salary

from employees

where department_id != (select department_id from employees where last_name = 'Hunold')

and salary < (select salary from employees where last_name = 'Hunold');

서브 쿼리는 문장 내에 필요하다면 여러 번 사용할 수 있다.

▶ HAVING절에 사용한 서브 쿼리

select department_id, min(salary)

from employees

where department_id is not null

group by department_id

having min(salary) > (select min(salary)

from employees

where department_id = 50);

select department_id, max(avg(salary)) 최대평균

from employees

group by department_id;

ORA-00937: not a single-group group function

00937. 00000 - "not a single-group group function"

select department_id

from employees

group by department_id

having avg(salary) = (select max(avg(salary))

from employees

group by department_id);

[SQL 10]에서 그룹 함수가 중첩된 경우 group by 절에 기술한 컬럼이라도 출력 할 수 없었음을 배웠다. 이런 경우 위 예제와 같이 서브 쿼리를 이용하면 해당 컬럼을 검색 할 수 있다.

이 구문은 전형적으로 쓰이는 구문이므로 구조를 기억해 두면 많은 부분에서 활용할 수 있다.

▶ 다른 테이블에서의 서브 쿼리

select employee_id, first_name

from employees

where department_id = (select department_id

from departments

where department_name = 'IT');

서브 쿼리에 사용하는 테이블은 메인 쿼리의 테이블과 달라도 상관없다.

위의 경우는 서브 쿼리 문장이나 조인 문장으로 작성할 수 있는 SELECT 문이다. 이런 경우는 작성자가 어떤 SQL을 작성해도 상관없다. 하지만, 메인 쿼리와 서브 쿼리에서 같은 테이블을 사용한다면 가능한 서브 쿼리로 쓸 것을 권한다.

저작자표시 비영리 변경금지

'SQL' 카테고리의 다른 글

[SQL 18] 집합 연산자(UNION, UNION ALL, INTERSECT, MINUS) (0)	2017.12.10
[SQL 17] 서브 쿼리 - 다중 행, 다중 열 서브 쿼리 (0)	2017.12.10
[SQL 15] 조인 - ON 절, 외부 조인, 교차 조인(Cross Join) (0)	2017.12.10
[SQL 14] 조인 - 자연 조인(Natural Join), Using 절을 이용한 조인 (0)	2017.12.08
[SQL 13] 조인 - 자체 조인(Self Join), 외부 조인(Outer Join) (0)	2017.12.08

[SQL 15] 조인 - ON 절, 외부 조인, 교차 조인(Cross Join)

2017. 12. 10. 13:41

샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.

이제부터 SQL:1999 표준에서 제시되어 있는 조인을 표현하는 방법을 배워보도록 하겠다

No. 01

ON 절을 이용한 조인

ON 절을 이용한 조인 표현법은 임의의 조인 조건을 지정하는 방법으로 일반적인 모든 형태의 조인을 표현할 수 있다.

기본 구조

SELECT 테이블1.컬럼, 테이블2.컬럼, …

FROM 테이블1

JOIN 테이블2 ON 조인 조건

[JOIN 테이블3 ON 조인 조건] …

WHERE 검색 조건;

자연 조인, USING 절을 이용한 조인 등을 모두 표현할 수 있다.
조인되는 컬럼의 이름이 서로 다르거나 비등가 조인인 경우 많이 사용한다.
비등가 조인과 같은 다양한 조인에서 사용한다.

ON 절을 이용한 조인 표현 기법은 외부 조인이나 교차 조인을 제외한 모든 조인 표현이 가능

◈ 예제

select employee_id 사원번호, first_name 이름,

departments.department_id 부서번호, department_name 부서명

from employees

join departments on employees.department_id = departments.department_id;

ON 절을 이용한 조인은 조인조건에 사용한 컬럼이라도 SELECT 절에 테이블명을 기술해야 한다.

▶ 여러 가지 표현법 비교

select department_id 부서번호, department_name 부서이름, d.location_id 지역번호, city 도시

from departments d, locations l

where d.location_id = l.location_id;

select department_id 부서번호, department_name 부서이름, location_id 지역번호, city 도시

from departments

natural join locations;

select department_id 부서번호, department_name 부서이름, location_id 지역번호, city 도시

from departments

join locations using (location_id);

select department_id 부서번호, department_name 부서이름, d.location_id 지역번호, city 도시

from departments d

join locations l on d.location_id = l.location_id;

위의 조인 문장들의 결과는 동일하다.

동일한 조인문에 대해서 경우에 따라 여러 가지 조인 방법이 있을 수 있다는 걸 알고있자.

▶ 검색 조건 쓰는 두 가지 방법

select department_id 부서번호, department_name 부서이름, d.location_id 지역번호, city 도시

from departments d

join locations l on d.location_id = l.location_id

where city = 'London';

select department_id 부서번호, department_name 부서이름, d.location_id 지역번호, city 도시

from departments d

join locations l on d.location_id = l.location_id

and city = 'London';

ON 절을 이용한 조인은 where 절 대신 on에 and로 이어서 검색 조건을 기술할 수 있다.

on 절에 검색 조건을 기술하다는 것만 알고 있고, where 절에 기술하기를 권한다.

▶ ON 절을 이용한 비등가 조인 표현 ([SQL 11]의 비등가조인 예제 참고)

select employee_id, first_name, salary, grade

from employees, salarygrade

where salary between losalary and hisalary;

select employee_id, first_name, salary, grade

from employees

join salarygrade on losalary and hisalary;

예전에 언급했듯이 아직 salarygrade 테이블이 없기 때문에 실습할 수 없다.

테이블을 만드는 법을 배우고나면 다시 이부분만 실습해 보도록 하겠다. 지금은 ON 절을 이용한 조인은 비등가 조인도 할 수 있다는 것만 알아두자.

▶ ON 절을 이용한 자체 조인(Self Join)

select e1.employee_id 사원번호, e1.first_name 이름,

e1.manager_id 상사번호, e2.first_name 상사이름

from employees e1

join employees e2 on e1.manager_id = e2.employee_id;

ON 절을 이용한 자체 조인이다.

▶ ON 절과 USING 절의 혼용

select department_id 부서번호, department_name 부서이름, city 도시, country_name 국가이름

from departments d

join locations l on d.location_id = l.location_id

join countries c on c.country_id = l.country_id

where city in ('Seattle', 'London');

select department_id 부서번호, department_name 부서이름, city 도시, country_name 국가이름

from departments d

join locations l using (location_id)

join countries c on c.country_id = l.country_id

where city in ('Seattle', 'London');

ON 절은 USING 절과 혼용이 가능하지만, 규칙을 정해놓고 사용해야 헷갈리지 않는다.

등가 조인은 USING 절로만 사용하고, 비등가 조인은 ON 절로만 사용한다.

물론, 항상 ON 절만 이용할 수도 있다.

No. 02

외부 조인(Outer Join) - LEFT, RIGHT, FULL

예전 표현법 중 (+)기호를 사용하던 외부 조인과 같은 결과를 출력할 수 있다. 게다가 이전 외부 조인에는 없던 양 쪽 모두 (+) 기호를 추가한 것과 동일한 표현이 가능하다.

기본 구조

SELECT 테이블1.컬럼, 테이블2.컬럼, …

FROM 테이블1

[LEFT | RIGHT | FULL] [OUTER] JOIN 테이블2 [ON 조인 조건 | USING (조인 조건)]

WHERE 검색 조건;

조인 조건에 일치하지 않는 데이터까지 모두 출력해 준다.
출력된 데이터의 신뢰성을 제공해주기 위한 방법이다.
조인 조건은 ON 절이나 USING 절 모두 사용할 수 있다.
자연 조인과 혼용해서 사용할 수 없다.
부족한 쪽 데이터는 자동으로 NULL 값으로 설정된다.

앞쪽에 먼저 기술한 테이블을 왼쪽 테이블, 뒤쪽 테이블을 오른쪽 테이블로 간주한다. <테이블1> : 왼쪽 테이블, <테이블2> : 오른쪽 테이블
LEFT	왼쪽 테이블의 데이터는 모두 검색에 포함된다.
RIGHT	오른쪽 테이블의 데이터는 모두 검색에 포함된다.
FULL	양쪽 테이블 모두의 데이터가 검색에 포함된다.

각 옵션에 따라 조인 조건과 일치하지 않는 행도 검색에 포함한다.

외부 조인을 사용하는 이유는 이전에 언급했기 때문에 다시 말하지 않겠다. ([SQL 13] 참고)

◈ 예제

▶ OUTER 생략

select e.last_name, department_id, d.department_name

from employees e

left join departments d using (department_id);

OUTER 는 생략 가능하다.

▶ LEFT OUTER JOIN

select e.last_name, department_id, d.department_name

from employees e

left outer join departments d using (department_id)

order by department_id desc;

select e.last_name, e.department_id, d.department_name

from employees e

left outer join departments d on e.department_id = d.department_id

order by e.department_id desc;

왼쪽 테이블인 EMPLOYEES 테이블에서 Grant 사원은 부서가 없기 때문에 department_id는 null 값이다. 그러나 DEPARTMENTS 테이블은 department_id가 null인 부서의 이름을 갖고 있지 않다. 외부 조인을 사용하지 않았다면 무시 됐을 것이다. LEFT 외부 조인을 사용함으로써 왼쪽 테이블인 EMPLOYEES의 데이터가 모두 검색되었고, 매칭되지 않은 행은 NULL 값으로 표시된다.

▶ RIGHT OUTER JOIN

select e.last_name, d.department_id, d.department_name

from employees e

right join departments d on e.department_id = d.department_id;

이번에는 반대로 오른쪽 테이블인 DEPARTMENTS 테이블에는 27개의 부서가 있지만 16개의 부서에는 아직 사원이 없다. RIGHT 외부 조인으로 오른쪽 테이블의 부서가 모두 검색되었고, 매칭되지 않은 행은 NULL 값으로 표시된다.

▶ FULL OUTER JOIN

select e.last_name, department_id, d.department_name

from employees e

full outer join departments d using (department_id)

order by department_id desc;

LEFT 외부 조인과 RIGHT 외부 조인을 합쳐 놓은 결과와 같다.

No. 03

교차 조인(Cross Join)

카네이션 곱(Cartesian Product) 이라고도 부른다.

조인되는 테이블의 각각의 행들이 모두 매핑된 데이터가 검색되는 조인 방법이다.

기본 구조

SELECT 테이블1.컬럼, 테이블2.컬럼, …

FROM 테이블1

CROSS JOIN 테이블2;

조건이 생략된 조인문과 결과가 동일하다.
두 테이블의 교차 곱을 수행한다.
공통 컬럼과 관계가 없다.
테이블1과 테이블2를 교차 조인하면 각 테이블의 행의 수를 곱한 것과 같은 개수의 결과 행이 생긴다.

교체 조인을 사용한다면 결과 값에 대한 해석에 주의해야 한다.

교차 조인을 하면 다음과 같은 결과를 얻을 수 있다.

조인의 이름 그대로 Cross를 하는 것이다.

행의 개수가 2개인 테이블과 3개인 테이블의 교차 조인하면 2 x 3 = 6 개의 행이 생긴다.

◈ 예제

select department_id, department_name, city

from departments

cross join locations;

총 621개의 행이 검색되는 것을 확인할 수 있다.

DEPARTMENTS 27행 x LOCATIONS 23행 = 621행

교차 조인의 결과는 일반적으로 잘못된 정보이다.

전산업무에서 부득이 수행하는 경우가 있다고 하지만, 웬만하면 사용할 일은 없을 것이다.

그냥 이런 형태의 조인이 있다고만 알아두자.

이로써 모든 조인 표현법에 대해 배워봤다.

저작자표시 비영리 변경금지

'SQL' 카테고리의 다른 글

[SQL 17] 서브 쿼리 - 다중 행, 다중 열 서브 쿼리 (0)	2017.12.10
[SQL 16] 서브 쿼리 - 단일 행 서브 쿼리 (0)	2017.12.10
[SQL 14] 조인 - 자연 조인(Natural Join), Using 절을 이용한 조인 (0)	2017.12.08
[SQL 13] 조인 - 자체 조인(Self Join), 외부 조인(Outer Join) (0)	2017.12.08
[SQL 12] 조인 문을 쉽게 만드는 Tip (0)	2017.12.08

[SQL 14] 조인 - 자연 조인(Natural Join), Using 절을 이용한 조인

2017. 12. 8. 23:49

샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.

이제부터 SQL:1999 표준에서 제시되어 있는 조인을 표현하는 방법을 배워보도록 하겠다

No. 01
ㅈ
자연 조인(Natural Join)

자연 조인은 등가 조인하는 방법 중 하나이다.

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

기본 구조

SELECT 컬럼, 컬럼, …

FROM 테이블1

NATURAL JOIN 테이블2

[NATURAL JOIN 테이블3] …

WHERE 검색 조건;

반드시 두 테이블 간의 동일한 이름, 타입을 가진 컬럼이 필요하다.
조인에 이용되는 컬럼은 명시하지 않아도 자동으로 조인에 사용된다.
동일한 이름을 갖는 컬럼이 있지만 데이터 타입이 다르면 에러가 발생한다.
조인하는 테이블 간의 동일 컬럼이 SELECT 절에 기술되도 테이블 이름을 생략해야 한다.

◈ 예제

select department_id 부서번호, department_name 부서이름, location_id 지역번호, city 도시

from departments

natural join locations

where city = 'Seattle';

원하는 결과를 얻기 위해 일반 조건을 사용할 경우는 원래처럼 where 절에 기술하면 된다.

▶ 이전 표현과 자연 조인을 비교

1) 오라클(SQL:1999 이전 문법)

select department_id 부서번호, department_name 부서이름, d.location_id 지역번호, city 도시

from departments d, locations l

where d.location_id = l.location_id;

2) SQL:1999 표준

select department_id 부서번호, department_name 부서이름, location_id 지역번호, city 도시

from departments

natural join locations;

두 테이블은 location_id(지역번호) 컬럼을 이용하여 조인되었고, 같은 결과를 출력한다.

보시다시피 자연 조인에서는 조인 조건을 기술하지 않아도 자동으로 공통 컬럼을 찾아서 조인한다. 또 SELECT 절에 테이블 명을 기술하지 않고 검색하는 것이 가능하다.

▶ 두 테이블에 동일한 이름과 타입의 컬럼이 두 개 있을 때

select employee_id 사원번호, first_name 이름,

d.department_id 부서번호, department_name 부서명

from employees e, departments d

where e.department_id = d.department_id;

select employee_id 사원번호, first_name 이름,

department_id 부서번호, department_name 부서명

from employees

natural join departments;

위 두개의 SQL 문을 보면 같은 결과가 나올 것 같지만 결과가 다르다는 것을 볼 수 있다.

자연 조인은 조인 조건을 기술하지 않고 간단하게 조인을 표현하는 방법이지만 제약이 따른다.

우선 첫 번째 결과가 원하던 결과였다.

두 번째 SQL 문의 결과가 다르게 나온 이유는 EMPLOYEES 테이블과 DEPARTMENTS 테이블은 department_id, manager_id 두 개의 이름, 타입이 동일한 컬럼을 가지고 있다. department_id 컬럼은 두 테이블에서 의미하는 것이 같지만 manager_id 컬럼은 의미하는 것이 다르다.

employees.manager_id는 각 사원의 상사를 의미하는 번호이고 departments.manager_id는 그 부서의 부장을 의미하는 번호이다. 자연 조인은 컬럼의 이름, 타입이 같은 컬럼들을 모두 조인에 사용하기 때문에 원하지 않는 결과가 나올 수 있다.

자연 조인은 테이블간에 동일한 형식을 갖는 공통 컬럼이 반드시 하나만 존재해야 한다.

No. 02

USING 절을 사용한 조인

자연 조인에서 사용하는 테이블간에 동일한 이름과 형식의 컬럼이 둘 이상인 경우 자연 조인을 사용할 수 없다.

이럴 경우 USING 절을 이용한 조인문을 이용하면 조인문을 구사할 수 있다.

기본 구조

SELECT 컬럼, 컬럼, …

FROM 테이블1

JOIN 테이블2 USING(조인 컬럼)

[JOIN 테이블3 USING(조인 컬럼)] …

WHERE 검색 조건;

USING 절은 조인에 사용될 컬럼을 지정한다.
NATURAL 절과 USING 절은 함께 사용할 수 없다.
조인에 이용되지 않은 동일 이름을 가진 컬럼은 컬럼명 앞에 테이블명을 기술한다.
조인 컬럼은 괄호로 묶어서 기술해야 한다.

◈ 예제

select department_id 부서번호, department_name 부서이름, location_id 지역번호, city 도시

from departments

join locations using (location_id);

select department_id 부서번호, department_name 부서이름, location_id 지역번호, city 도시

from departments

join locations using (location_id)

where city = 'Seattle';

USING 절에는 조인 컬럼을 기술해야 하며, 괄호로 묶어서 표현해야 한다.

▶ 두 테이블에 동일한 이름과 타입의 컬럼이 두 개 있을 때

1) 오라클(SQL:1999 이전 문법)

select employee_id 사원번호, first_name 이름, e.manager_id 상사번호,

d.department_id 부서번호, department_name 부서명

from employees e, departments d

where e.department_id = d.department_id;

2) SQL:1999 표준

select employee_id 사원번호, first_name 이름, e.manager_id 상사번호,

department_id 부서번호, department_name 부서명

from employees e

join departments d using (department_id);

위의 예제는 자연 조인문으로 정확한 결과를 보지 못했던 예전 표현을 USING절을 이용하여 조인문을 구사했다. 조인에 사용될 컬럼을 명시적으로 지정하기 때문에 동일한 결과를 얻는 것이 가능하다.

주의할 점은 조인에 사용된 컬럼에는 테이블명을 기술하지 않고, 각 테이블에 조인이 이용되지 않았지만 동일 이름을 가진 컬럼은 테이블명을 기술해야 한다.

▶ NATURAL 조인과 USING 절을 이용한 조인

select department_name, city, country_name

from countries

join locations using (country_id)

join departments using (location_id)

where city in ('Seattle', 'London')

and country_name like 'United%';

select department_name, city, country_name

from countries

natural join locations

natural join departments

where city in ('Seattle', 'London')

and country_name like 'United%';

위의 경우처럼 조인되는 테이블간 공통된 컬럼이 하나 밖에 없을 경우 서로 바꿔 표현할 수 있다.

여러번 얘기 했지만 조인되는 테이블간 공통된 컬럼이 2개 이상이라면 둘의 결과는 완전히 다르다.

TIP

* 여러 테이블 간 조인할 경우 자연 조인과 USING 절을 이용한 조인 모두 사용 가능할 때 가독성이 좋은 USING 절을 이용한 방법을 권한다.

* 자연 조인이나 USING 절을 이용한 조인은 등가 조인만을 표현할 수 있다.

저작자표시 비영리 변경금지

'SQL' 카테고리의 다른 글

[SQL 16] 서브 쿼리 - 단일 행 서브 쿼리 (0)	2017.12.10
[SQL 15] 조인 - ON 절, 외부 조인, 교차 조인(Cross Join) (0)	2017.12.10
[SQL 13] 조인 - 자체 조인(Self Join), 외부 조인(Outer Join) (0)	2017.12.08
[SQL 12] 조인 문을 쉽게 만드는 Tip (0)	2017.12.08
[SQL 11] 조인 - 등가조인, 비등가조인 (0)	2017.12.08

[SQL 13] 조인 - 자체 조인(Self Join), 외부 조인(Outer Join)

2017. 12. 8. 22:58

샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.

No. 01

자체 조인(Self Join)

자기 참조 조인이라고도 한다.

동일 테이블의 다른 컬럼을 참조하는 컬럼을 이용해서 동일 테이블간 조인하는 방법이다.

기본 구조

SELECT 별명1.컬럼, 별명2.컬럼, …

FROM 테이블 별명1, 테이블 별명2, …

WHERE 조인 조건

[AND 일반 조건];

동일 테이블을 자기 참조에 의해 조인한다.
테이블에 반드시 별명을 붙여야 한다.

자체 조인은 하나의 테이블에 여러 별명을 붙여서 마치 서로 다른 테이블인 것처럼 조인한다.

◈ 예제

▶ 각 사원을 관리하는 상사의 이름을 검색한다.

select e1.employee_id 사원번호, e1.first_name 사원이름, e1.manager_id 상사번호,

e2.employee_id "상사의 사원번호", e2.first_name 상사이름

from employees e1, employees e2

where e1.manager_id = e2.employee_id;

EMPLOYEES 테이블의 manager_id(상사번호) 컬럼은 자신의 상사의 employee_id(사원번호)이다.

그 상사도 또한 회사의 사원이기 때문에 e1 테이블의 manager_id 컬럼을 e2 테이블의 employee_id 컬럼으로 참조한다.

자주 쓰이지는 않지만, 위와 같은 경우에 자체 조인을 사용한다.

자기 자신을 조인한다고 해서 어려운것이 아니고 서로 다른 별명을 별개의 테이블로 생각하면 보통의 등가 조인이나 비등가 조인과 동일하다.

No. 02

외부 조인(Outer Join)

포괄 조인이라고도 한다.

기본 구조

SELECT 테이블1.컬럼, 테이블2.컬럼, …

FROM 테이블1, 테이블2

WHERE 조인 조건(+)

[AND 일반 조건];

조인 조건에 일치하지 않는 데이터까지 모두 출력해준다.
출력된 데이터의 신뢰성을 제공해주기 위한 방법이다.
'+'기호는 데이터가 적은 쪽에 기술한다.
부족한 쪽 데이터는 자동으로 NULL 값으로 설정된다.

◈ 예제

▶ 각 부서별로 사원을 검색한다.

1) 일반 조인인 경우

select d.department_id 부서번호, department_name 부서명, first_name 사원이름

from departments d, employees e

where d.department_id = e.department_id

order by 1 desc;

2) 외부 조인의 경우

select d.department_id 부서번호, department_name 부서명, first_name 사원이름

from departments d, employees e

where d.department_id = e.department_id(+)

order by 1 desc;

일반 조인과 외부 조인의 출력 결과를 비교해보자.

DEPARTMENTS 테이블에는 100번부터 270번 부서까지 총 27개의 부서가 있지만, 일반 조인의 경우 모든 부서가 검색되지 않는다. 그 이유는 사원이 한명도 속하지 않은 부서도 있기 때문이다. 부서별로 사원을 검색하는데 사원이 없는 부서까지 함께 검색이 필요한 경우가 있을 것이다.

예를 들어 사원이 없는 120~270번 부서가 내년에 새로 만들어질 부서라고 가정해보자. 다음 년도 예산을 결정하기 위해 일반 조인으로 검색된 결과를 보고서에 제출한다면 120~270번 부서는 없는 부서로 인식되고 내년에 개설되어 업무를 시작할 부서임에도 불구하고 예산이 확보되지 않아 업무상 오류가 발생할 수 있다. 이런 오류를 방지하기 위해 사용하는 것이 외부 조인이다.

외부 조인은 데이터가 부족한 쪽 조인조건에 '(+)' 기호를 추가한다. 데이터가 적은 쪽 테이블과 매칭되지 않는 컬럼들은 자동으로 NULL값으로 설정된다.

select d.department_id 부서번호, department_name 부서명, first_name 사원이름

from departments d, employees e

where d.department_id(+) = e.department_id

order by 1 desc;

이번에는 DEPARTMENTS 쪽에 데이터가 부족하다고 (+)기호를 추가했다.

아까와는 다르게 부서가 없는 사원의 이름까지 모두 검색된다.

결론적으로 말하면

데이터가 부족한 쪽에 (+) 기호를 추가하면, 부족하지 않은 쪽 데이터는 모두 검색되고 매칭되지 않는 행은 NULL값으로 설정된다.

단, (+)기호는 조인 조건 양쪽에 동시에 기술할 수 없다.

저작자표시 비영리 변경금지

'SQL' 카테고리의 다른 글

[SQL 15] 조인 - ON 절, 외부 조인, 교차 조인(Cross Join) (0)	2017.12.10
[SQL 14] 조인 - 자연 조인(Natural Join), Using 절을 이용한 조인 (0)	2017.12.08
[SQL 12] 조인 문을 쉽게 만드는 Tip (0)	2017.12.08
[SQL 11] 조인 - 등가조인, 비등가조인 (0)	2017.12.08
[SQL 10] 그룹 함수, GROUP BY 절, HAVING 절 (1)	2017.12.04

[SQL 12] 조인 문을 쉽게 만드는 Tip

2017. 12. 8. 22:24

샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.

No. 01

조인 문을 위한 준비

조인 문을 만들기 위해서는 반드시 테이블간 관계를 알아야 한다. 어떤 테이블이 어떤 테이블과 공통컬럼이 있는가 등의 내용을 명확하게 알지 못한다면 정확한 정보를 제공하는 조인 문을 절대 만들지 못할 것이다.

우리가 사용하고 있느 HR 스키마의 구조를 보면 다음과 같다.

HR schema 구조

이렇게 스키마의 구조를 도표로 표현한 것을 ERD(Entity-Relationship Diagram)이라고 한다.

이런 자료들을 문서로 가지고 있거나 직접 테이블 구조를 보는 명령어를 통해 구조를 파악하는 것은 매우 중요하다.

실제 현장에서는 ERD를 웬만하면 볼 수 없을 것이고, 스스로 테이블 구조를 파악해야 할 것이다.

테이블 구조의 생성, 수정, 삭제, 파악 등의 방법은 나중에 배워보도록 하고,

지금은 HR 스키마의 ERD를 보고 대충 구조를 기억해 놓자. 이미 실습을 통해 몇 개의 테이블은 대충 파악됐을 것이다. 이런 관계들을 일부러 외우지는 말자. 헷갈린다면 그냥 ERD를 보고하면 그만이다.

[SQL 00] 에도 같은 그림이 있다.

No. 02

조인 문을 만드는 과정

이제부터 조인문을 만드는 과정을 단계별로 설명할 것이다. SQL은 비절차적 언어이기 때문에 단계를 만들어서 한다는 것은 옳은 방법은 아니라고 생각한다. 단지 조인문의 초보자들에게 여러 방법 중 이런 방법이 있다는 것을 설명해주는 것이라 생각하면 될 것 같다.

지금까지는 두 가지 조인밖에 배우지 않았고, 앞으로 많은 조인 방법을 배울 것이다. 종류는 다르지만 조인문을 만드는 과정은 크게 다르지 않다. 이 과정들이 조인문을 만들 때 도움이 되기를 바란다.

SQL문을 연습하다보면 자연스럽게 자신만의 편한 방법을 찾게 될 것이다.

절대로 설명되는 과정들을 외워서 메뉴얼처럼 생각하지말자!

1. 검색 대상과 조건을 찾는다.

- 검색 대상은 SELECT 절에, 조건은 WHERE 절에 기술한다.

2. 검색될 정보를 가지고 있는 테이블을 파악한다.

- FROM 절에 기술한다.

3. 테이블간의 관계를 확인한다. (연결 고리를 찾는다)

- 연결 고리가 없다면 중계자 역할을 할 수 있는 테이블을 추가한다.

4. 조인 조건을 기술한다.

5. 전체 문장을 다듬는다.

위에 제시한 단계별로 지문으로부터 SQL문을 만들어 보자.

▶ 사원번호가 200번 이하인 사원들의 이름과 일하고 있는 부서이름, 도시이름을 검색한다.

1. 검색 대상과 조건을 찾는다.

§ 검색 대상

이름 : first_name

부서이름 : department_name

도시이름 : city

§ 조건

사원번호가 200번 이하 : employee_id <= 200

SELECT first_name, department_name, city

FROM

WHERE employee_id <= 200;

2. 검색될 정보를 가지고 있는 테이블을 파악한다.

SELECT 절과 WHERE 절에 사용된 컬럼이 포함된 테이블을 찾는다.

- first_name : employees 테이블

- department_name : departments 테이블

- city : locations 테이블

SELECT first_name , department_name ,city

FROM employees, departments, locations

WHERE employee_id <= 200;

3. 테이블간의 관계를 확인한다.(연결 고리를 찾는다)

공통컬럼 공통컬럼

employees (department_id) departments (location_id) locations

4. 조인 조건을 기술한다.

테이블 간의 연결고리를 이용하여 조인 조건을 기술한다.

SELECT first_name, department_name, city

FROM employees, departments, locations

WHERE employee.department_id = departments.department_id

AND departments.location_id = locations.location_id

AND employee_id <= 200;

5. 전체 문장을 다듬는다.

새로운 컬럼을 SELECT 절에 추가하거나, 별명을 지정하는 등의 부가적인 요소들을 추가한다.

부가적인 요소를 추가할 때 조인되는 테이블에 있는 컬럼인지 결과를 변형시켜 잘못된 정보를 출력하게 하는 요소인지 점검한다.

SELECT first_name, department_name, city

FROM employees e, departments d, locations l

WHERE e.department_id = d.department_id

AND d.location_id = l.location_id

AND employee_id <= 200;

조인문을 만드는 과정을 배워봤다. 테이블의 이름, 구조 등을 완전히 알고 있다면 SELECT 절부터 WHERE 절까지 막힘없이 기술할 수 있겠지만, 그런 것들을 기억하기는 쉽지 않다. 실제 현장에 가도 마찬가지일 것이다. 물론 자신이 테이블을 만드는 상황이라면 쉽게 파악할 수 있지만, 대부분은 누군가가 만들어 놓은 테이블을 이용한다.

SQL 문을 만드는 과정은 새로운 환경에서는 쉽지 않다. 문장의 앞뒤를 오가면서 수정하고 삽입하는 과정을 거쳐야 할 것이다. 제 블로그를 통해서 공부하시는 분들 중에 SELECT 문을 만들면서 한 번에 쭉 문장을 기술하지 못한다고 스스로를 자책하는 분이 있을 수 있지만, 절대 그런 고민은 할 필요가 없다. 능숙한 전문가라도 항상 자신이 만든 SQL문을 확인하고 수정하는 작업을 거친다.

저작자표시 비영리 변경금지

'SQL' 카테고리의 다른 글

[SQL 14] 조인 - 자연 조인(Natural Join), Using 절을 이용한 조인 (0)	2017.12.08
[SQL 13] 조인 - 자체 조인(Self Join), 외부 조인(Outer Join) (0)	2017.12.08
[SQL 11] 조인 - 등가조인, 비등가조인 (0)	2017.12.08
[SQL 10] 그룹 함수, GROUP BY 절, HAVING 절 (1)	2017.12.04
[SQL 09] 단일 행 함수 - 일반 함수 & 조건 표현식(CASE, DECODE) (0)	2017.12.03

[SQL 11] 조인 - 등가조인, 비등가조인

2017. 12. 8. 21:45

샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.

No. 01

조인(JOIN)이란?

하나의 스키마에서 여러 테이블이 존재하고 정보를 저장하고 있다. 지금까지는 하나의 테이블에서만 정보를 검색했지만, 동시에 두 개 이상의 테이블에서 정보를 검색해야 할 때가 있다. 여러 테이블의 데이터를 검색하는 것을 조인(Join)이라 하고 조인은 테이블 간의 관계를 기반으로 수행된다.

예를 들어 2개의 테이블을 조인한다면 2개의 테이블을 합친 가상의 하나의 테이블이 만들어진다고 생각하면 된다.

두 테이블은 각각 사원의 정보와 부서의 정보를 담고 있는 테이블이다.

조인을 함으로써 각 사원이 일하고 있는 부서의 이름을 확인할 수 있다.

조인을 하지 않고 각 사원이 일하고 있는 부서의 이름을 알아내려면 EMPLOYEES 테이블에서 각 사원이 일하고 있는 부서번호를 검색하고 다시 DEPARTMENTS 테이블을 검색해서 그 분서번호에 대응하는 부서명을 알아 낼 수 있다. 검색 결과를 따로 봐야 하기 때문에 불편함이 있고 효율적이지 못하다.

반면,조인은 하나의 SQL문으로 검색하고자 하는 결과를 정리하여 볼 수 있다.

조인을 쉽게 사용하고자 한다면 각 테이블간의 관계를 명확히 파악해야 한다. 각 테이블간의 공통 컬럼이나 동일 내용을 담고 있는 컬럼이 무엇인지 알지 못한다면 조인 문장을 구사할 수 없다.

그러나 조인은 반드시 같은 값을 이용하는 것만은 아니다.

No. 02

조인(JOIN)의 종류

JOIN은 SQL:1999 문법 이전과 이후로 구분할 수 있다.

오라클 8i 버전까지의 조인 구문은 ANSI 표준과 달랐지만, 9i 버전부터는 SQL:1999 표준을 준수하는 조인구문을 제공한다.

오라클(SQL:1999 이전 문법)	SQL:1999 표준
등가 조인	자연 / 내부 조인 JOIN USING
포괄 조인	왼쪽 포괄 조인, 오른쪽 포괄 조인
-	전체 포괄 조인
자체조인	JOIN ON
비등가 조인	JOIN ON
카티시안 곱	교차조인

SQL:1999 이전 문법을 설명하는 이유는 실제 현장에서는 아직도 예전 표현법을 사용하는 곳도 있기 때문이다. 예전 표현법을 보더라도 당황하지 않고 해석할 수 있기를 바라면서 모든 표현법들을 공부해 보도록 한다.

그러나 가능한 새로운 표현법을 사용하길 권한다. 표준으로 정해 놓았기 때문에 오라클 SQL이 아닌 다른 SQL 프로그램에서도 사용되는 표현법이기 때문이다.

No. 03

등가 조인, 비등가 조인

기본 구조

SELECT 테이블1.컬럼, 테이블2.컬럼, …

FROM 테이블1, 테이블2, …

[AND 조인 조건]

[AND 일반 조건];

조인 조건이란 두 테이블 간의 관계를 수식으로 표현한 것이다.
조인 문장에서 기술된 모든 테이블은 반드시 조인 조건을 기술해야 한다.
조인에 사용되는 테이블 개수가 n이라면 조인 조건의 개수는 (n-1)개 이상이어야 한다.
FROM 절을 기술할 때 각각의 테이블에 별명을 붙일 수 있다.
동일한 이름의 컬럼이 여러 테이블에 존재하는 경우 테이블 이름을 써줘야 한다.

조인 조건에 '='를 이용하는 조인을 등가 조인(Equi-join)이라 하고

부등호가 포함된 조인 조건을 이용하는 조인을 비 등가 조인(Non equi-join)이라고 한다.

▶ 등가 조인 예제

select employee_id 사원번호, first_name 이름, employees.department_id 부서번호,

departments.department_id 부서번호, department_name 부서이름

from employees, departments

where employees.department_id = departments.department_id;

위와 같이 조인 조건에 등호(=)를 이용하는 조인을 등가 조인이라 한다.

조인에 사용하는 테이블의 개수가 2개 이므로 조인 조건은 (2-1)개의 조인 조건을 이용했다.

SELECT절에 사용되는 컬럼은 원래 테이블에서 사용하던 컬럼이름을 써도 되지만, 조인에 참여하는 다른 테이블에 같은 이름의 컬럼이 존재하는 경우 어느 테이블의 컬럼인지 반드시 써줘야 한다.

select location_id, employee_id 사원번호, first_name 이름,

e.department_id 부서번호, department_name 부서이름

from employees e, departments d

where e.department_id = d.department_id

and location_id = 1700;

'location_id = 1700'과 같은 일반 조건도 같이 사용할 수 있다.

select location_id, employee_id 사원번호, first_name 이름,

employees.department_id 부서번호, department_name 부서이름

from employees e, departments d

where e.department_id = d.department_id

and location_id = 1700;

ORA-00904: "EMPLOYEES"."DEPARTMENT_ID": invalid identifier

00904. 00000 - "%s: invalid identifier"

테이블에 별명을 사용했음에도 불구하고 별명을 쓰지 않으면 위와 같은 에러가 발생한다.

그 이유는 SQL문을 처리할 때 select 절보다 from 절이 먼저 처리되기 때문이다.

FROM 절의 테이블에 별명을 사용할 경우 SQL문 전체에서 반드시 테이블명 대신 별명을 사용해야 한다.

select e.last_name, d.department_name, l.city

from employees e, departments d, locations l

where e.department_id = d.department_id

and d.location_id = l.location_id;

위와 같이 3개 이상의 테이블을 조인 가능하다.

조인을 할 때 주의점은 각 테이블간의 공통된 컬럼(연결고리)가 있어야 한다는 것이다.

▶ 비등가 조인 예제

HR스키마의 테이블로 표현하기 어려운 부분이 있어 테이블 예제를 들어 설명한다.

테이블을 새로 만들어 실습해볼 수 있지만 아직 배우지 않았기 때문에 나중에 테이블 생성을 배우고 직접 실행시켜보기 바란다.

다음과 같은 두 개의 테이블이 있다고 한다면

EMPLOYEES 테이블

EMPLOYEE_ID	FIRST_NAME	SALARY	DEPARTMENT_ID
100	Steven	24000	90
108	Nancy	12000	100
110	John	8200	100
115	Alexander	3100	30
145	John	14000	80

SALARYGRADE 테이블

GRADE	LOSALARY	HISALARY
A	20000	99999
B	12000	19999
C	8000	11999
D	4000	7999
E	0	3999

select employee_id, first_name, salary, grade

from employees, salarygrade

where salary between losalary and hisalary;

EMPLOYEE_ID	FIRST_NAME	SALARY	GRADE
100	Steven	24000	A
108	Nancy	12000	B
110	John	8200	C
115	Alexander	3100	E
145	John	14000	B

위 예제의 조인은 조인조건은 급여가 salarygrade 테이블의 losalary 컬럼의 값 보다는 크고 hisalary 컬럼의 값보다는 작은 범위를 검색하는 것으로 동일한 한 값을 찾는 등가 조인과는 다른 방식이다.

위와 같이 조인 조건에 등호(=)를 이용하지 않는 조인을 비등가 조인이라 한다.

저작자표시 비영리 변경금지

'SQL' 카테고리의 다른 글

[SQL 13] 조인 - 자체 조인(Self Join), 외부 조인(Outer Join) (0)	2017.12.08
[SQL 12] 조인 문을 쉽게 만드는 Tip (0)	2017.12.08
[SQL 10] 그룹 함수, GROUP BY 절, HAVING 절 (1)	2017.12.04
[SQL 09] 단일 행 함수 - 일반 함수 & 조건 표현식(CASE, DECODE) (0)	2017.12.03
[SQL 08] 단일 행 함수 - 변환 함수 (0)	2017.12.03

[SQL 10] 그룹 함수, GROUP BY 절, HAVING 절

2017. 12. 4. 22:05

샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.

No. 01

그룹 함수

그룹 함수는 검색된 여러 행을 이용하여 통계정보를 계산하는 함수이다.

그룹 함수

함수명	기능	사용
COUNT	행의 수를 계산한다.	COUNT(컬럼 \| *)
MAX	값들 중에 최대 값을 반환한다.	MAX(컬럼)
MIN	값들 중에 최소 값을 반환한다.	MIN(컬럼)
AVG	평균 값을 계산한다.	AVG(컬럼)
SUM	총 합계를 계산한다.	SUM(컬럼)
VARIANCE	분산을 계산한다.	VARIANCE(컬럼)
STDDEV	표준편차를 계산한다.	STDDEV(컬럼)

반드시 하나의 값만을 반환한다.
NULL 값은 무시된다.
NULL 값이 무시되지 않으려면 NVL, NVL2와 같은 함수를 이용한다.
GROUP BY 설정 없이 일반 컬럼과 기술될 수 없다.

◈ 예제

select count(commission_pct), count(nvl(commission_pct,0))

from employees;

보너스를 받지 않는 사람은 commission_pct가 null로 되어있다.

그룹함수는 null 값을 무시하기 때문에 첫 번째의 경우는 보너스를 받는 사람의 수만 계산된 것이고,

두 번째의 경우는 보너스를 받지 않는 사람은 0으로 만들어 줌으로써 전체 사원의 수가 계산된 것이다.

select avg(salary) 평균급여1, round(avg(salary)) 평균급여2

from employees;

평균급여1 같은 경우 출력 값의 형식은 세션의 설정이나 평균급여2와 같이 단일 함수를 이용해서 지정할 수 있다.

select sum(salary) 총액, round(avg(salary)) 평균, count(*) 전체인원

from employees;

select min(salary), max(salary), min(hire_date), max(hire_date)

from employees;

MIN, MAX 함수는 숫자 뿐만 아니라 날짜, 문자에도 사용할 수 있다. 문자의 경우 글자수로 판단한다.

이름을 가지고 MIN, MAX 함수를 사용해 보자.

No. 02

GROUP BY 절

특정 속성을 기준으로 그룹화 하여 검색할 때 그룹화 할 속성을 지정한다.

기본 구조

SELECT [DISTINCT] 컬럼, 그룹 함수(컬럼)

FROM 테이블명

[WHERE 조건]

[GROUP BY Group대상]

[ORDER BY 정렬대상 [ASC/DESC]]

SELECT 절에 그룹 함수와 컬럼명이 같이 기술된 경우 해당 컬럼은 반드시 GROUP BY절에 그룹화 돼야 한다.
결과 값이 정렬되길 원한다면 반드시 ORDER BY 절을 추가한다.3

GROUP BY 절은 반드시 그룹함수와 함께 쓰이며 그룹 함수의 결과 값은 GROUP BY 절에 기술된 컬럼의 항목들의 행의 개수에 의해 결정된다.

◈ 예제

select department_id, round(avg(salary))

from employees

group by department_id;

각 부서별로 사원들 급여의 평균 값이 계산됐다.

일반 언어로 표현한다면 GROUP BY절에 기술된 '컬럼(부서)'별로 평균(그룹 함수)를 계산하라.

GROUP BY절의 의미는 그룹 함수를 GROUP BY절에 지정된 컬럼의 값이 같은 행에 대해서 통계 정보를 계산하라는 의미이다.

select round(avg(salary))

from employees

group by department_id;

GROUP BY절에 기술된 컬럼이 SELECT절에 기술되지 않아도 된다.

위의 경우 부서별로 사원들 급여의 평균 값이 계산됐지만 어느 부서의 평균 값인지 모르기 때문에 쓸모없는 정보나 마찬가지이다.

그러므로 SELECT절에도 GROUP BY절에 기술된 컬럼을 기술하길 권한다.

▶ 2개 이상의 그룹화

select department_id 부서번호, job_id 직업, sum(salary)

from employees

group by department_id, job_id

order by department_id;

위의 예를 보면 20번 부서가 2번 나와 있는데 이는 같은 부서일지라도 직업이 다르기 때문에 다른 그룹으로 묶인 것이다.

select department_id 부서번호, job_id 직업, manager_id 상사번호, sum(salary)

from employees

group by department_id, job_id, manager_id

order by department_id;

이번에는 부서번호와 직업이 같은 경우라도 하나의 그룹으로 묶이지 않았다. 이번에는 GROUP BY절에 상사번호까지 기술했기 때문이다.

부서번호, 직업, 상사번호까지 같아야 하나의 그룹으로 묶여 그룹함수의 계산이 이루어진다.

추가로 GROUP BY절을 사용할 경우 ORDER BY절을 사용하기를 권한다.(파악하기 좋게 하기 위해서)

즉, 여러 컬럼을 그룹화 할 경우 GROUP BY절에 나열된 컬럼들의 값이 모두 같아야 같은 그룹으로 묶인다.

▶ 그룹 함수를 중첩해서 사용하는 경우

select department_id 부서번호, max(avg(salary)) 최대평균, min(avg(salary)) 최소평균

from employees

group by department_id;

ORA-00937: not a single-group group function

00937. 00000 - "not a single-group group function"

부서번호를 그룹화 했음에도 불구하고 위의 예제의 경우 에러가 발생한다.

max(avg()), min(avg())는 평균값의 최대 값과 최소 값이므로 어떤 컬럼으로 그룹을 하던 결과는 최대 값 하나 최소 값 하나이다. 그러나 부서번호의 경우 부서별로 그룹을 묶었기 때문에 값이 여러 개이다.

이해가 안된다면 차근차근 생각해보자.

일단 위의 예제는 부서별로 그룹을 묶었다. 10, 20, 30, … 각 부서마다 그룹으로 묶인 것이다.
그 후 그룹별로 사원들 급여의 평균 값을 구한다.
각 그룹별로 평균 값 중에서 최대 값과 최소 값을 구했다. 각각 결과는 하나이다.
select절에 부서번호를 기술했기 때문에 화면에 출력할 때는 그룹으로 묶인 부서들도 보여주어야 한다.

현재는 어느 부서의 최대평균, 최소평균인지는 모른다. 단지 평균값들 중에 최대, 최소가 무엇인지만 검색된 상황이다. 각 부서별로 하나의 행을 차지하고 있을텐데 특정 부서의 옆에 최대평균, 최소평균을 같이 출력한다면 마치 그 부서의 최대평균과 최소평균인 것처럼 보이기 때문에 잘못된 정보가 되버린다. 그래서 이런 상황이 나오지 않도록 예제처럼 SQL문을 작성할 경우 에러로 인식되게 한 것이다.

select max(avg(salary)) 최대평균, min(avg(salary)) 최소평균

from employees

group by department_id;

정리하면 그룹 함수가 중첩된 경우 GROUP BY절에 기술한 컬럼도 출력 할 수 없다.

No. 03

HAVING 절

HAVING 절은 해석상 WHERE 절과 동일하다. 단 조건 내용에 그룹 함수를 포함하는 것만을 포함한다.

일반 조건은 WHERE 절에 기술하지만 그룹 함수를 포함한 조건은 HAVING 절에 기술한다.

기본 구조

SELECT [DISTINCT] 컬럼, 그룹 함수(컬럼)

FROM 테이블명

[WHERE 조건]

[GROUP BY Group대상]

[HAVING 그룹 함수 포함 조건]

[ORDER BY 정렬대상 [ASC/DESC]]

HAVING 절은 GROUP BY 절 뒤에 기술한다.
HAVING 절의 조건은 그룹 함수를 포함해야 한다.

◈ 예제

select department_id 부서번호, round(avg(salary)) 평균급여

from employees

group by department_id

having avg(salary) < 7000;

SQL문장에서 일반 조건은 WHERE절에 기술하지만 조건에 그룹 함수가 포함된 경우에는 HAVING절에 기술해야 한다.

WHERE절에 그룹 함수가 포함된 조건을 기술하면 에러가 발생한다.

select department_id 부서번호, sum(salary) 급여총액

from employees

group by department_id

having job_id != 'IT_PROG';

ORA-00979: not a GROUP BY expression

00979. 00000 - "not a GROUP BY expression"

반대로 HAVING 절에 일반 조건을 기술하면 에러가 발생한다.

그러나 GROUP BY에 기술된 컬럼에 대한 일반 조건은 HAVING절에 기술 가능하다. 하지만 그렇게 되면 SQL문을 해석하기 힘들기 때문에 기술하지 않는 것이 좋다.

일반 조건은 WHERE 절에 그룹 함수를 포함한 조건은 HAVING 절에 기술한다.

select job_id, sum(salary) 급여총액

from employees

where job_id not like '%REP%'

group by job_id

having sum(salary) > 13000

order by sum(salary);

GROUP BY 와 HAVING 절까지 기술한 SELECT 구문을 해석해보자.

1. from 절에 기술한 테이블에서

2. where 절에 기술한 조건에 해당하는 내용들만 정리하고

3. 정리된 내용들을 가지고 group by 절에 나열된 컬럼에 대해서 그룹을 만든다.

4. 만들어진 그룹별로 having절에 기술된 그룹함수로 계산하여 having절 조건에 맞는 그룹만 추리고

5. 추려진 그룹들을 order by 절에 잇는 조건으로 정렬시킨다.

6. 마지막으로 select절에 있는 그룹화된 컬럼 또는 그룹함수를 화면에 출력한다.

저작자표시 비영리 변경금지

'SQL' 카테고리의 다른 글

[SQL 12] 조인 문을 쉽게 만드는 Tip (0)	2017.12.08
[SQL 11] 조인 - 등가조인, 비등가조인 (0)	2017.12.08
[SQL 09] 단일 행 함수 - 일반 함수 & 조건 표현식(CASE, DECODE) (0)	2017.12.03
[SQL 08] 단일 행 함수 - 변환 함수 (0)	2017.12.03
[SQL 07] 단일 행 함수 - 숫자, 날짜 함수 (0)	2017.12.03

[SQL 09] 단일 행 함수 - 일반 함수 & 조건 표현식(CASE, DECODE)

2017. 12. 3. 19:45

샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.

No. 01

단일 행 함수 - 일반 함수

▣ 일반 함수

함수명	기능 & 사용
NVL	NVL(expr1, expr2)
	expr1의 값이 NULL일 경우 expr2 값으로 대체한다.

NVL2	NVL2(expr1, expr2, expr3)
	expr1의 값이 NULL이 아닌 경우 : expr2 값을 반환 expr1의 값이 NULL인 경우 : expr3 값을 반환

NULLIF	NULLIF(expr1, expr2)
	expr1과 expr2의 값이 같은 경우 : NULL 값을 반환 expr1과 expr2의 값이 다른 경우 : expr1 값을 반환

COALESCE	COALESCE(expr1, expr2, …, exprn)
	표현식 중 NULL이 아닌 첫 번째 표현식을 반환한다.

◈ 예제

select commission_pct, nvl2(commission_pct, commission_pct+0.1, 0.25) "NVL2 함수"

from employees;

select nullif(1, 1) 같은경우, nullif(1, 2) 다른경우, nullif('aa', 'ab') 다른경우2

from dual;

select coalesce(1, 2, 3, 4, 5), coalesce(null, null, 3, 4, 5)

from dual;

앞에서부터 차례로 null 값인지 검사하고 아닐 경우 그 값을 반환한다.

No. 02

조건 표현식(CASE, DECODE)

CASE : ANSI 표준 SQL (9i 부터 가능)

DECODE : 오라클 구문

CASE 표현 기본 구조

CASE 컬럼|표현식 WHEN 비교값1 THEN 리턴값1

[WHEN 비교값2 THEN 리턴값2

WHEN 비교값 n THEN 리턴값n

ELSE 기본값]

END [별명]

컬럼(또는 표현식)과 WHEN절의 비교 값이 같으면 리턴 값을 반환한다.
WHEN 절에 같은 값이 없으면 기본값을 반환한다.
'ELSE 기본값'을 쓰지 않으면 같은 값이 없을 경우 NULL 값을 반환한다.
표현식이 길기 때문에 사용을 권한다.

◈ 예제

select last_name, job_id, salary,

case job_id when 'IT_PROG' then 1.10*salary

when 'ST_CLERK' then 1.15*salary

when 'SA_REP' then 1.20*salary

else salary

end "REVISED_SALARY"

from employees;

DECODE 함수 기본 구조

DECODE (컬럼|표현식, 찾을값1, 결과1 [,찾을값, 결과2, …][, 기본값]) [별명]

컬럼(또는 표현식)과 찾을값 들과 비교하여 매칭되는 곳의 결과 값을 반환한다.
일치하는 값이 없을 경우 기본값을 반환한다.
기본값을 쓰지 않으면 찾을 값이 없을 경우 NULL 값을 반환한다.
표현식이 길기 때문에 별명 사용을 권한다.
CASE 표현과 같은 기능을 한다.

◈ 예제

select last_name, job_id, salary,

decode (job_id, 'IT_PROG', 1.10*salary,

'ST_CLERK', 1.15*salary,

'SA_REP', 1.20*salary,

salary) "REVISED_SALARY"

from employees;

CASE와 DECODE 둘 다 같은 기능을 수행하지만,

가독성이 CASE 표현식이 더 좋기 때문에 사용할 일이 생긴다면 CASE 표현식을 권장한다.

저작자표시 비영리 변경금지

'SQL' 카테고리의 다른 글

[SQL 11] 조인 - 등가조인, 비등가조인 (0)	2017.12.08
[SQL 10] 그룹 함수, GROUP BY 절, HAVING 절 (1)	2017.12.04
[SQL 08] 단일 행 함수 - 변환 함수 (0)	2017.12.03
[SQL 07] 단일 행 함수 - 숫자, 날짜 함수 (0)	2017.12.03
[SQL 06] 단일 행 함수 - 문자 함수 (0)	2017.12.03

[SQL 08] 단일 행 함수 - 변환 함수

2017. 12. 3. 19:02

샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.

No. 01

데이터 변환 유형

오라클은 상식적인 수준의 형 변환을 자동으로 구현하고 있다. (암시적 데이터 변환)

varchar2, char → number (문자가 유효한 숫자로만 나타내는 경우)

varchar2, char →date

number → varchar2

date → varchar2

예를 들면

select '1' + '2'

from dual;

연산자를 사용할 경우 문자 '1'과 '2'를 숫자 1,2로 자동으로 변환되어 계산된다.

데이터 변환 유형은 2가지이다.

오라클 자체적으로 자동으로 데이터를 변환하는 것을 암시적 데이터 변환이라 하고,

변환 함수를 사용하여 데이터의 형 변환을 하는 것을 명시적 데이터 변환이라 한다.

SQL문의 좀 더 빠른 응답을 위해서는 명시적인 함수의 사용을 권장한다.

No. 02

단일 행 함수에서 사용하는 출력 형식

▣ 날짜 출력 형식

	표현 문자	결 과
년	YYYY	네 자리로 표현된 년도 (2000, 2007, 2017)
	YY	두 자리로 표현된 년도 (99, 07, 17) ◆ 앞에 생략된 두 자리는 현재 년도를 사용한다. ◆ 99년은 2099년을 의미한다.
	RR	두 자리로 표현된 년도 (99, 07) ◆ 앞에 두 자리른 현재 년도와 가까운 년도를 사용한다. ◆ 99년은 1999년을 의미한다.

월	MM	두 자리 숫자로 표현된 월 (06, 07)
	MONTH	영문이나 한글로 표현된 월 (JUNE, JULY, 6월, 7월)
	MON	약자로 표현된 영문 및 한글 월 (JUN, JUL, 6월, 7월) ◆ 한글인 경우 MONTH와 동일하다.

일	DD	두 자리 숫자로 표현된 일자 (07, 10)
	DAY	영문이나 한글 요일 (SUNDAY, MONDAY, 일요일, 월요일)
	DY	약자로 표현된 요일 (SUN, MON, 일, 월)

▣ 시간 출력 형식

	표현 문자	결 과
시	HH24	1시에서 24시까지 표현
시	HH	1시에서 12시까지 표현 ◆ AM/PM은 표현되지 않는다.

분	MI	두 자리 분 표시

초	SS	두 자리 초 표시
초	SSSSS	하루를 초로 환산한 다음 표현(0-86399)

오전/오후	AM/PM	오전 오후 표기 (둘 중 하나만 쓰면 된다.)

▣ 숫자 출력 형식

표현 문자	결 과
9	숫자의 출력 폭지정. (자리수가 부족하면 생략)
0	선행 0 표기 (자리수를 반드시 맞춘다.)
$	화폐 표기
L	지역 화폐 표기 (각 국가 코드에 따라 다르다.)
,	쉼표 위치 지정
.	마침표 위치 지정
MI	음수의 - 기호를 오른쪽에 표기
EEEE	실수 표현법을 이용

No. 03

변환 함수

숫자 → 문자, 날짜 → 문자 : TO_CHAR 함수

문자 → 숫자 : TO_NUMBER 함수

문자 → 날짜 : TO_DATE 함수

▣ 변환 함수

함수명	기 능	사 용
TO_CHAR	날짜나 숫자를 문자로 변환한다.	TO_CHAR(날짜, 출력형식) TO_CHAR(숫자, 출력형식)
TO_DATE	문자를 날짜형으로 변환한다.	TO_DATE(문자, 출력형식)
TO_NUMBER	문자를 숫자로 변환한다.	TO_NUMBER(문자, 출력형식)

◈ 예제

▶ 현재 날짜를 다양한 형식으로 출력

select to_char(sysdate, 'yyyy/mm/dd') 날짜,

to_char(sysdate, 'yyyy/mm/dd:hh24:mi:ss') "날짜+시간",

to_char(sysdate, 'yyyy/mm/dd/hh:mi:ss am') "오전/오후 표시"

from dual;

select to_char(sysdate, 'dd month yyyy') 오늘날짜1,

to_char(sysdate, 'day mon yy') 오늘날짜2,

to_char(sysdate, 'dy mon yy') 오늘날짜3

from dual;

select to_char(sysdate, '"오늘 날짜는 "yyyy"년 "mm"월 "dd"일 입니다."')

from dual;

형식 안에 문자열을 추가할 경우 이중 인용부호(")을 사용해야 한다.

▶ 다양한 형식으로 숫자를 출력

select to_char(1234.56, '999,999.9999') 숫자

from dual;

소수점 이하에 지정한 자리는 0을 채우지만 앞쪽 빈자리는 무시한다.

select to_char(1234.56, '009,999.99') 숫자

from dual;

숫자 출력형식 0을 사용하면 자리수를 반드시 맞춘다.

select to_char(1234.56, '9999.9') 숫자

from dual;

소수점 이하 자리수를 부족하게 지정하면 반올림 된다.

select to_char(1234.56, '999.99') 숫자

from dual;

소수점 앞쪽 형식을 원래 값보다 작게 하면 제대로 표시되지 않는다.

select to_char(1234.56, '$99,999.99') 화폐표기, to_char(1234.56, 'L99,999.99') 지역화폐표기

from dual;

지역화폐표기는 시스템에 설정되어 있는 지역마다 다르다. (￥, ￦, ＄ …)

▶ TO_DATE 사용

select '2002/01/01' + 1

from dual;

ORA-01722: invalid number

01722. 00000 - "invalid number"

select to_date('2002/01/01') + 1

from dual;

얼핏 보면 첫 번째 SQL문도 실행이 될 것 같지만 에러가 난다. '2002/01/01'는 모양은 날짜같지만 실제로는 문자이기 때문이다.

이럴 때 TO_DATE 함수를 사용하여 날짜를 바꾸어 계산할 수 있다.

저작자표시 비영리 변경금지

'SQL' 카테고리의 다른 글

[SQL 10] 그룹 함수, GROUP BY 절, HAVING 절 (1)	2017.12.04
[SQL 09] 단일 행 함수 - 일반 함수 & 조건 표현식(CASE, DECODE) (0)	2017.12.03
[SQL 07] 단일 행 함수 - 숫자, 날짜 함수 (0)	2017.12.03
[SQL 06] 단일 행 함수 - 문자 함수 (0)	2017.12.03
[SQL 05] ORDER BY 절 (0)	2017.12.03

[SQL 07] 단일 행 함수 - 숫자, 날짜 함수

2017. 12. 3. 17:55

샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.

No. 01

숫자 함수

슷자 함수는 숫자를 인자로 사용하고 결과 값도 숫자를 반환하는 함수이다.

▣ 숫자 함수

함수명	기 능	사 용
ROUND	m을 n 자리까지 반올림한다.	ROUND(m, n)
		ROUND(45.926, 2) → 45.93

TRUNC	m을 n 자리 미만을 버린한다.	TRUNC(m, n)
		TRUNC(45.926, 2) → 45.9

MOD	m을 n으로 나눈 나머지를 계산한다.	MOD(m, n)
		MOD(1600, 300) → 100

CEIL	n보다 큰 가장 작은 정수를 찾는다.	CEIL(n)
		CEIL(12.34) → 13

FLOOR	n보다 작은 가장 큰 정수를 찾는다.	FLOOR(n)
		FLOOR(12.34) → 12

POWER	m의 n승을 계산한다.	POWER(m, n)
		POWER(2, 3) → 8

ABS	n의 절대 값을 계산한다.	ABS(n)
		ABS(-3) → 3

SQRT	n의 제곱근을 계산한다.	SQRT(n)
		SQRT(4) → 2

SIGN	n이 음수일 때 -1, 양수일 때 1, 0일 때 0을 반환한다.	SIGN(n)
		SIGN(-5) → -1

◈ 예제

▶ ROUND

select round(45.923, 2), round(45.923, 0), round(45.923, -1)

from dual;

round 함수의 자리수(n)은 소수점을 기준으로 한다.

예) 1: 소수점 첫 째 자리, 0: 1의 자리(생략 가능), -1: 10의 자리, -2: 100의 자리 …

▶ TRUNC

select trunc(45.923, 2), trunc(45.923), trunc(45.923, -1)

from dual;

자리수(n)이 소수점을 기준으로하는 것은 round 함수와 같다.

▶ MOD

select first_name, salary, mod(salary, 4000)

from employees

where job_id = 'SA_REP';

▶ CEIL, FLOOR

select ceil(45.923), floor(45.923), ceil(-45.923), floor(-45.923)

from dual;

No. 02

날짜 함수

날짜 데이터는 문자처럼 표기 하지만 숫자처럼 계산되는 데이터이다.

▣ 날짜에 산술 연산을 하는 경우

연 산	변환 되는 데이터 타입	결 과
날짜 + 숫자	날짜	일수 이후 날짜
날짜 - 숫자	날짜	일수 이전 날짜
날짜 + 숫자/24	날짜	시간을 더한 날짜
날짜 - 날짜	숫자	두 날짜 간에 차(일수)

SYSDATE : 시스템의 현재 날짜 및 시간 반환

◈ 예제

select hire_date, hire_date + 3, hire_date -3

from employees;

select syadate, hire_date, sysdate-hire_date "날짜 차이"

from employees;

현재 날짜와 사원들의 고용일과의 날짜 차이이다.

날짜 차이가 소수점으로 나오는 이유는 눈에 보이지 않지만 시분초에 해당하는 데이터가 존재하기 때문이다.

시분초를 출력하는 방법은 변환 함수를 배울 때 알아보도록 한다.

▣ 날짜 함수

함수명	기능 & 사용
ROUND	형식에 맞추어 반올림한 날짜를 반환한다.	YYYY : 년도 MM : 월 DD : 일 HH : 시간 MI : 분 SS : 초
	ROUND(날짜, 형식) ROUND(sysdate, 'DD') → 2017/12/04

TRUNC	형식에 맞추어 버림한 날짜를 반환한다.
	TRUNC(날짜, 형식) TRRUNC(sysdate, 'YYYY') → 2017/01/01

MONTHS_BETWEEN	두 날짜간의 기간을 월 수로 계산한다.
	MONTHS_BETWEEN(날짜1, 날짜2) MONTHS_BETWEEN('2017/10/10', '2017/07/10') → 3

ADD_MONTHS	날짜에 n 달을 더한 날짜를 계산한다.
	ADD_MONTHS(날짜, n) ADD_MONTHS('2017/07/10', 3) → 2017/10/10

NEXT_DAY	날짜 이후 지정된 요일에 해당하는 날짜를 계산한다.
	NEXT_DAY(날짜, 요일), 요일 표현 : 'sun', '일요일', 1 NEXT_DAY('2017/07/10', '토요일') → 2017/07/15

LAST_DAY	날짜를 포함한 달의 마지막 날짜를 계산한다.
	LAST_DAY(날짜) LAST_DAY('2017/07/10') → 2017/07/31

◈ 예제

▶ ROUND

select hire_date, round(hire_date, 'YY') 년, round(hire_date, 'MM') 월, round(hire_date, 'DD') 일

from employees

where employee_id = 101;

▶ TRUNC

select hire_Date, trunc(hire_date, 'YY') 년, trunc(hire_date, 'MM') 월, truc(hire_date, 'DD') 일

from employees

where employee_id = 101;

▶ 사원 번호 101번인 사원의 일한 일수

select trunc(sysdate, 'DD') - trunc(hire_date, 'DD') + 1 day

from employees

where employee_id = 101;

오늘 날짜에서 입사일을 빼면 입사일은 근무 일수에서 빠지므로 마지막에 1을 더해준다.

▶ 부서번호 50번인 사원들의 근무 개월수

select employee_id, first_name, trunc(months_between(sysdate, hire_date)) 근무개월수

from employees

where department_id = 50;

저작자표시 비영리 변경금지

'SQL' 카테고리의 다른 글

[SQL 09] 단일 행 함수 - 일반 함수 & 조건 표현식(CASE, DECODE) (0)	2017.12.03
[SQL 08] 단일 행 함수 - 변환 함수 (0)	2017.12.03
[SQL 06] 단일 행 함수 - 문자 함수 (0)	2017.12.03
[SQL 05] ORDER BY 절 (0)	2017.12.03
[SQL 04] WHERE 절 (0)	2017.12.03

PREV 1 2 3 NEXT

냉유's Log

SQL

[SQL 16] 서브 쿼리 - 단일 행 서브 쿼리

'SQL' 카테고리의 다른 글

[SQL 15] 조인 - ON 절, 외부 조인, 교차 조인(Cross Join)

'SQL' 카테고리의 다른 글

[SQL 14] 조인 - 자연 조인(Natural Join), Using 절을 이용한 조인

'SQL' 카테고리의 다른 글

[SQL 13] 조인 - 자체 조인(Self Join), 외부 조인(Outer Join)

'SQL' 카테고리의 다른 글

[SQL 12] 조인 문을 쉽게 만드는 Tip

'SQL' 카테고리의 다른 글

[SQL 11] 조인 - 등가조인, 비등가조인

'SQL' 카테고리의 다른 글

[SQL 10] 그룹 함수, GROUP BY 절, HAVING 절

'SQL' 카테고리의 다른 글

[SQL 09] 단일 행 함수 - 일반 함수 & 조건 표현식(CASE, DECODE)

'SQL' 카테고리의 다른 글

[SQL 08] 단일 행 함수 - 변환 함수

'SQL' 카테고리의 다른 글

[SQL 07] 단일 행 함수 - 숫자, 날짜 함수

'SQL' 카테고리의 다른 글

+ Recent posts

티스토리툴바