*새로운 Oracle 9i의 쿼리문*

*새로운 Oracle 9i의 쿼리문*

1. SQL: 1999 죠인.. 2

1. SQL: 1999 죠인

SQL: 1999 죠인 문법은 Oracle 죠인의 그것과 다음과 같은 점에서 차이가 있습니다.

- 죠인의 형태가 FROM 절에서 명시적으로 지정됩니다.

- 죠인 조건이 WHERE 절의 검색 조건과 구별되어 ON 절에서 명시됩니다.

1.1. SQL: 1999에 정의된 죠인의 형태들

1.1.1. Cross 죠인

- CROSS 죠인은 두 테이블의 곱집합을 생성합니다.

- 이는 두 테이블 간의 Cartesian 곱과 같습니다.

예)

SELECT last_name, department_name

FROM employees CROSS JOIN departments;

1.1.2. Natural 죠인

- NATURAL 죠인은 두 테이블에서 같은 이름을 가진 모든 컬럼에 기반합니다.

- 두 테이블의 대응되는 모든 컬럼에 대해 같은 값을 가지는 행들을 선택합니다.

- 만일 같은 이름을 가지는 컬럼들이 서로 다른 데이터 형을 가질 때에는 오류가 반환됩니다.

- 만일 SELECT * 문법을 사용한다면, 공통 컬럼들은 결과 집합에서 단 한번만 나타납니다.

- 테이블 이름이나 가명 등의 수식자들은 NATURAL 죠인에 사용된 컬럼들을 수식할 수 없습니다.

예)

SELECT department_id, location_id

FROM locations NATURAL JOIN departments;

1.1.3. USING 절을 이용한 죠인

- 만일 여러 개의 컬럼이 이름은 같지만 데이터 형이 모두 일치되지는 않을 때에는,

NATURAL JOIN은 USING 절을 이용하여 동등 죠인에 사용될 컬럼들을 명시하도록 수정될 수 있습니다.

- USING 절에서 참조되는 컬럼들은 SQL 문 어디에서도 수식자(테이블 이름이나 가명)에 의해 수식될 수 없습니다.

- NATURAL 과 USING의 두 키워드는 상호 배타적으로 사용됩니다.

예)

SELECT e.employee_id, e.last_name, d.location_id

FROM employees e JOIN departments d

USING (department_id);

1.1.4. ON 절을 사용하는 죠인

- Natural 죠인의 죠인 조건은 기본적으로 같은 이름을 가진 모든 컬럼들에 대한 동등 조건입니다.

- 임의의 죠인 조건을 지정하거나, 또는 죠인할 컬럼을 명시하기 위해서 ON 절이 사용됩니다.

- ON 절은 죠인 조건과 다른 조건들을 분리합니다.

- ON 절은 코드를 보다 이해하기 쉽게 합니다.

예)

SELECT e.employee_id, e.last_name,

e.department_id, d.department_id,

d.location_id

FROM employees e JOIN departments d

ON (e.department_id = d.department_id);

1.1.5. 복잡한 죠인

- ON 절을 사용함으로써 다음과 같은 것들을 이용한 복잡한 죠인을 만들 수 있습니다.

- 서브쿼리

- AND/OR 연산자

- [NOT] EXISTS

- [NOT] IN

1.1.6. 죠인 조건과 ON 절

- ON 절을 사용함으로써 다른 조건과 죠인 조건을 분리시킬 수 있습니다;

그렇게 하면 코드가 보다 이해하기 쉬워집니다.

- ON 절은 서브쿼리나 논리 연산자 등을 포함한 임의의 조건을 지정할 수 있습니다.

예)

SELECT e.manager_id, e.last_name,

e.department_id, d.location_id

FROM employees e JOIN departments d

ON ((e.department_id = d.department_id)

AND e.manager_id = 102

);

예)Exists의 사용

SELECT department_name, city

FROM locations l JOIN departments d

ON (l.location_id = d.location_id)

AND NOT EXISTS (SELECT 1 FROM employees e

WHERE e.department_id = d.department_id

)

);

예)복수 테이블의 죠인

SELECT employee_id, city, department_name

FROM locations l

JOIN departments d

ON (l.location_id = d.location_id)

JOIN employees e

ON (d.department_id = e.department_id) ;

1.1.7. INNER 대 OUTER 죠인

- SQL: 1999 표준에 의하면 두 테이블을 죠인하여 오로지 대응되는 행들만을 반환하는 것을

INNER 죠인이라 합니다.

- INNER 죠인의 결과와 함께 왼쪽(오른쪽) 테이블의 대응되지 않는 행들도 반환하는 것을

LEFT(RIGHT)OUTER 죠인이라 합니다.

- INNER 죠인의 결과와 함께 LEFT 및 RIGHT OUTER 죠인의 결과까지 모두 반환하는 것을 FULL OUTER 죠인이라 합니다.

예) LEFT OUTER 죠인

SELECT e.last_name, d.department_name

FROM employees e LEFT OUTER JOIN departments d

ON (e.department_id = d.department_id)

예) RIGHT OUTER 죠인

SELECT e.last_name, d.department_name

FROM employees e RIGHT OUTER JOIN departments d

ON (e.department_id = d.department_id)

예) FULL OUTER 죠인

SELECT e.last_name, d.department_name

FROM employees e FULL OUTER JOIN departments d

ON (e.department_id = d.department_id)

2. CASE 수식

2.1. SQL: 1999의 CASE 수식

SQL:1999에는 다음과 같은 형태의 CASE 문들이 있습니다.

- 단순 CASE

- 검색 CASE

- NULLIF

- COALESCE

2.1.1. 단순 CASE 수식

- 단순 CASE 수식은 DECODE 함수와 유사합니다.

- 주어진 수식 내에서 값을 찾고 대체할 수 있습니다.

- 각각의 값에 대해 반환값을 명시할 수 있습니다.

- 비교 연산자를 사용할 수는 없습니다.

예)

SELECT last_name,

(CASE department_id

WHEN 10 THEN 'Administration'

WHEN 20 THEN 'Marketing'

WHEN 30 THEN 'Purchasing'

WHEN 40 THEN 'Human Resources'

…

ELSE 'N/A'

END) as "Department Names"

FROM employees

ORDER by department_id;

2.1.2. 검색 CASE 수식

- 검색 CASE 수식은 IF… THEN … ELSE 구문과 유사합니다.

- 수식에 대하여 조건부로 값을 찾고 대체할 수 있습니다.

- 각각의 WHEN 조건은 달리 주어지며 복수의 조건이 논리 연산자에 의해 결합될 수 있습니다.

- 조건 수식에 비교 연산자를 사용할 수 있습니다.

- 검색 CASE 수식은 단순 CASE 수식에 비해 보다 유연합니다.

예) 검색 CASE문

INSERT INTO raise

SELECT last_name,

CASE

WHEN job_id LIKE 'AD%' THEN '10%'

WHEN job_id LIKE 'IT%' THEN '15%'

WHEN job_id LIKE 'PU%' THEN '18%'

ELSE '20%'

END

FROM employees;

예) 검색 CASE문

INSERT INTO raise

SELECT last_name,

CASE

WHEN job_id LIKE 'AD%' THEN '10%'

WHEN job_id LIKE 'IT%' THEN '15%'

WHEN job_id LIKE 'PU%' THEN '18%'

ELSE '20%'

END

FROM employees;