SELECT
SELECT#
구문#
select ::=#
with_clause ::=#
subquery ::=#
select_clause ::=#
hierarchical_query_clause ::=, group_by_clause ::=
select_list ::=#
tbl_reference ::=#
single_table ::=#
pivot_clause ::=#
pivot_for_clause ::=#
pivot_in_clause ::=#
unpivot_clause ::=#
unpivot_in_clause ::=#
joined_table ::=#
tbl_reference ::=, single_table ::=
join_type ::=#
apply_type ::=#
where_clause ::=#
hierarchical_query_clause ::=#
group_by_clause ::=#
rollup_cube_clause ::=#
grouping_sets_clause ::=#
grouping_expression_list ::=#
expression_list ::=#
order_by_clause ::=#
limit_clause ::=#
for_update_clause ::=#
전제 조건#
SYS 사용자, 테이블 소유자, SELECT ANY TABLE 시스템 권한을 가진 사용자 및 테이블에 대한 SELECT 객체 권한을 가진 사용자만이 이 구문으로 해당 테이블에서 데이터를 조회할 수 있다.
설명#
한 개 이상의 테이블 또는 뷰에서 데이터를 검색하는 구문이다.
with_clause#
Altibase는 with_clause 절을 인라인 뷰 또는 임시 테이블로 처리하여 최적화한다.
일반적인 WITH query_name절은 AS 다음에 따라오는 부질의(subquery)에 이름을 부여한다. 부질의에 부여된 이름은 주질의 내의 여러 위치에서 참조될 수 있다.
그러나 재귀적인 WITH 구문(이하 recursive with 또는 재귀 질의)에서는 반드시 query_name 뒤에 칼럼의 별명을 지정하고, 부질의 내에서 집합 연산자를 사용해야 해당 질의를 반복적으로 수행할 수 있다. subquery 내에는 집합 연산자를 기준으로 두 개의 질의가 존재하며, 앞의 질의는 주 질의(기준 질의)가 되고 뒤의 질의가 재귀 질의가 된다. 재귀 질의는 프로퍼티 RECURSION_LEVEL_MAXIMUM에 설정한 레벨 값(기본값 1000)까지 최대 출력된다. 자세한 내용은 Altibase 매뉴얼 중 General Reference의 Altibase 프로퍼티 장을 참조한다.
사용자는 이 절을 주질의 및 대부분 유형의 부질의에 명시할 수 있다.
제약 사항:#
- 하나의 SQL 문에 하나의 with_clause만 명시할 수 있다. query_name은 자신을 정의하는 부질의에서는 사용할 수 없다. 단 with_clause 안에서 여러 개의 쿼리 이름을 정의하는 경우, 뒤이어 정의되는 다른 부질의에는 앞서 정의한 다른 쿼리 이름을 사용할 수 있다.
- RECURSIVE WITH 구문에서 주 질의와 재귀 질의의 위치가 변경되면 안된다. 또한 query_name을 재귀 질의에서 한 번은 명시적으로 참조해야 한다. 그러나 재귀 질의에서는 집계 함수, DISTINCT 키워드, GROUP BY 절을 사용할 수 없다.
- RECURSIVE WITH 구문의 주 질의에 query_name을 참조하는 서브쿼리를 사용할 수 없다.
- RECURSIVE WITH 구문의 재귀 질의에 query_name을 참조하는 뷰 또는 Outer Join의 오른쪽 테이블에 사용할 수 없다.
TOP (expr)#
쿼리의 결과 집합 중 반환할 행의 개수를 지정하는 절이다. expr에는 정수만 올 수 있다.
select_list 절#
DISTINCT를 명시할 경우 결과 집합에서 중복된 레코드는 제거된다.
만약 GROUP BY 절이 존재한다면 상수, 집계 함수(aggregate functions), GROUP BY 절에 명시된 표현식과 이들을 조합한 표현식만 SELECT 리스트에 명시 가능하다.
SELECT 리스트가 칼럼이 포함되지 않은 수식 또는 상수로만 이루어져 있는 경우, FROM 절 이하를 생략할 수 있다.
SELECT 리스트에 *만 명시한 것은 FROM 절의 모든 테이블과 뷰의 모든 칼럼들을 의미한다. SELECT 리스트에 *가 칼럼 또는 수식과 함께 명시된 경우에도 마찬가지 의미이다.
FROM 절#
같은 별명(alias_name)을 FROM 절에 두 번 이상 사용할 수 없다. 같은 테이블 명을 FROM절에 여러 번 사용할 때, 다른 별명을 주어야 한다. 즉, 별명을 명시하지 않고 같은 테이블 명을 두 번 이상 사용할 수 없다.
FROM절에 올 수 있는 테이블 또는 뷰의 최대 개수는 32개이다.
OUTER JOIN#
조인 조건을 만족하지 않는 데이터를 처리하기 위한 JOIN의 확장 형태이다. (INNER) JOIN이 두 테이블에서 키 값이 일치하는 데이터만 가져오는 것에 비해 OUTER JOIN은 어느 한 쪽의 데이터를 모두 가져온다. 즉, 한 테이블의 행에 대응하는 행이 다른 테이블에 존재하지 않을 때, 빈 칼럼들에 대해서 결과 집합에는 NULL로 채워져서 반환된다.
인라인 뷰(Inline View)#
FROM 절에 오는 부질의(subquery)를 인라인 뷰라고 한다.
Lateral View#
FROM 절에 오는 인라인 뷰는 FROM 절에 나열된 다른 객체나 상위 쿼리에 있는 객체를 참조할 수 없다. 그러나 FROM 절의 인라인 뷰를 Lateral View로 정의하면 인라인 뷰의 외부 객체를 참조할 수 있게 된다.
인라인 뷰를 Lateral View로 정의하려면, LATERAL 또는 APPLY 키워드를 인라인 뷰 앞에 지정하면 된다. 단, Lateral View가 참조할 수 있는 객체는 해당 Lateral View의 왼쪽에 지정된 객체들뿐이다. Lateral View로 정의하더라도 Lateral View가 외부 객체를 참조하지 않으면, Altibase 서버가 해당 Lateral View를 일반적인 인라인 뷰로 취급한다.APPLY 키워드는 인라인 뷰를 Lateral View로 정의하면서 APPLY 키워드의 왼쪽 객체와 해당 Lateral View의 조인도 함께 수행한다. 일반적인 조인문이 ON 키워드 뒤에 조인 조건을 지정하는 것과 달리, APPLY 키워드를 사용하는 조인의 경우에는 조인 조건이 필요 없다.
APPLY 키워드를 사용해서 다음의 조인 유형을 지정할 수 있다. - CROSS APPLY는 왼쪽 객체와 Lateral View를 Inner Join 할 것을 지정한다. - OUTER APPLY는 왼쪽 객체와 Lateral View를 Left Outer Join 할 것을 지정한다.
주의 사항:#
아래와 같이 Lateral View를 사용하는 경우, 오류 메시지가 반환된다.
- Lateral View에서 Fixed Table을 참조하는 경우
- Lateral View에 PIVOT 절, UNPIVOT 절이 사용된 경우
- Lateral View에서 해당 Lateral View의 오른쪽에 지정된 객체를 참조하는 경우
- Lateral View가 참조하는 객체와 Lateral View를 Right Outer Join 또는 Full-Outer Join을 하는 경우
- LATERAL 키워드와 APPLY 키워드를 붙여서 같이 사용한 경우
- APPLY 키워드와 ON 절을 함께 사용한 경우
pivot_clause#
pivot_clause는 데이터 집계 연산 및 별개의 행으로 존재하는 데이터를 칼럼으로 재배열하여 보여주기 위해 사용할 수 있다. 이것은 GROUP BY 절에 두 개의 칼럼을 사용할 때보다 데이터를 더 읽기 쉽게 보여준다.
편의상, 많은 수의 칼럼 출력 또는 변형 연산의 결과로 생기는 특정 칼럼들의 이름을 명시하는 어려움을 방지하기 위해서 pivot_clause는 일반적으로 인라인 뷰와 함께 사용된다.
pivot_clause는 다음의 단계를 수행한다.
- 먼저 pivot_clause 는 마치 GROUP BY 절처럼 그룹 연산을 수행한다. 그 결과는 pivot_clause내의 참조되지 않은 모든 칼럼과 pivot_in_clause에 명시된 값에 대해 그룹화된다.
- 다음으로 pivot_clause는 결과로 나온 그룹화된 칼럼들과 집계된 값들을 회전된 표 형식으로 배열한다.
pivot_for_clause#
pivot_in_clause에 명시된 값이 칼럼 형태로 바꾸어질 때 이에 해당하는 칼럼 이름을 명시한다.
pivot_in_clause#
pivot_for_clause에 명시된 칼럼들에 존재하는 값을 이 절에 명시할 수 있다. 이 값들은 pivot 연산에서 칼럼 이름으로 사용될 것이다.
unpivot_clause#
unpivot_clause는 칼럼의 데이터들을 행으로 변환시켜 결과를 반환한다.
INCLUDE | EXCLUDE NULLS 옵션은 UNPIVOT 절로 생성되는 칼럼의 값에 NULL을 허용할 것인지를 선택할 수 있다. EXCLUDE NULLS는 NULL 값이 생략된 결과를 반환하며, 생략하면 EXCLUDE NULLS로 동작한다.
column_name에는 UNPIVOT 구문으로 생성되는 칼럼에 해당하는 값들이 반환되는 칼럼 이름을 지정한다.
unpivot_clause에서 사용한 칼럼의 개수만큼 pivot_for_clause 와 pivot_in_clause에 사용되는 칼럼 개수도 같아야 한다. 또한 alias의 개수도 같아야 한다.
unpivot ( ( column, column ) for ( column, column ) in ( ( column, column ) as (
column, column ) ) );
다음처럼 칼럼 개수가 다른 구문은 지원하지 않는다.
unpivot ( ( column, column ) for column in ( ( column, column ) as column ) );
Table Function#
사용자 정의 함수에서 Associative Array 타입이나 Record 타입을 사용할 때 반환되는 결과를 테이블 형태로 변환하여 출력한다.
where 조건절#
WHERE 절의 조건에 대한 설명은 8장 조건 연산자를 참고한다.
Hierarchical Query 절#
계층적 질의(Hierarchical query)란 데이터 조회시 계층적 구조로 출력되는 쿼리문이다. 테이블에 부모, 자식 관계를 가지는 데이터가 존재하는 경우 이 쿼리문을 이용해서 부모, 자식 관계를 계층적으로 출력할 수 있다. 즉, 주어진 검색 조건을 갖는 루트 행에 대하여, 그 행과 그의 종속 행에 대한 계층적 조건을 만족하는 행들을 검색한다.
만약 ORDER BY 절 또는 GROUP BY 절을 사용하여 계층적 데이터를 조회하면, CONNECT BY절로 수립된 계층적 순서가 바뀔 수 있으므로 주의가 필요하다. 단, ORDER SIBLINGS BY를 사용하면 계층 구조를 보존하면서 동일한 레벨의 자식들 사이에서 순서를 정할 수 있다.
START WITH 절#
계층적 질의의 루트 행으로 사용될 행을 식별하는 조건을 명시하는 절이다. 이 조건을 만족하는 모든 행들이 루트 행으로 사용된다. 이 절을 생략하면 Altibase는 테이블에 있는 모든 행들을 루트 행으로 사용한다.
ROWNUM 의사칼럼은 이 절에 사용될 수 없다.
CONNECT BY 절#
계층 구조에서 부모 행들과 자식 행들간의 관계를 식별하는 조건을 명시한다.
CONNECT BY 절은 부질의를 포함할 수 없고 조인과 함께 사용될 수도 없다.
CONNECT BY 절은 WHERE 절 이후 ORDER BY, GROUP BY, HAVING 절 이전에 사용하여야 한다.
NOCYLE 키워드는 계층적 질의의 결과 집합에 순환구조가 존재하더라도, 순환이 발생하기 전까지의 행을 반환할 것을 데이터베이스에 요구한다.
SYS_CONNECT_BY_PATH함수는 계층적 질의에서 현재 행까지의 PATH 정보를 쉽게 가져올 수 있다. 이 함수에 대한 자세한 내용은 "6장 SQL 함수"를 참고하라.
PRIOR 연산자#
이전에 검색된 행과 현재 행을 구분하기 위해서 PRIOR 연산자를 사용한다. 즉, 부모행을 언급하기 위해 반드시 PRIOR 연산자를 사용해야 한다.
PRIOR 연산자는 CONNECT BY 절을 포함하는 질의문의 SELECT 리스트, WHERE 절, 또는 CONNECT BY 절에서만 사용할 수 있다.
질의에 집합 연산자 (UNION, INTERSECT, 등)가 사용되면, PRIOR 연산자는 ORDER BY 절에 올 수 없다.
CONNECT_BY_ROOT 연산자#
CONNECT_BY_ROOT는 계층적 질의에서만 사용 가능한 단일 연산자이다. 칼럼에 이 연산자를 사용하면 루트 행의 칼럼 값을 반환한다.
START WITH 조건 또는 CONNECT BY 조건에 이 연산자를 사용할 수 없다.
CONNECT_BY_ISLEAF 의사칼럼#
CONNECT_BY_ISLEAF 의사칼럼은 현재 행이 CONNECT BY 조건에 의해 정의된 트리에서 리프 노드(자식 노드가 없는 노드)인 경우 1을 반환하고, 그렇지 않으면 0을 반환한다.
LEVEL 의사칼럼#
계층적 질의를 포함하는 SQL문은 select_list에 LEVEL 의사칼럼을 포함할 수 있다. LEVEL 의사칼럼은 루트 행과 종속 행간의 계층적 거리를 나타낸다. 즉, 루트 행의 LEVEL은 1이고, 자식 행의 LEVEL은 2, 손자 행의 LEVEL은 3, 등이 된다.
LEVEL의사칼럼은 WHERE 절, ORDER BY 절, GROUP BY 절, HAVING 절에서도 사용 가능하다. 또한, LEVEL 의사칼럼은 다음 예처럼 쿼리문에 CONNECT BY 절이 없더라도 select_list 에 올 수 있다:
select level from t1;
IGNORE#
행들간 계층 관계가 순환 형태를 이룰 경우 Altibase는 오류를 반환한다. (여기서 순환이란 한 행이 다른 행의 부모 행도 되고 자식 행도 되는 경우를 말한다.) 단, IGNORE LOOP이 명시되었을 경우, 질의 수행시의 순환 형성이 오류를 발생시키지 않고, 순환 형태의 행들이 질의 결과 집합에서 제거된다.
GROUP BY 절#
GROUP BY 절은 주어진 하나 이상의 표현식에 대해서 같은 값을 가지는 레코드들끼리 그룹짓고, 각 그룹별로 집계한 정보를 한 행으로 반환받기 위해 사용되는 절이다.
WHERE 조건을 사용하여 반환되는 그룹을 제한할 수 없는 대신에, HAVING 절을 사용하여 반환되는 그룹을 제한할 수 있다.
HAVING 및 GROUP BY 절은 WHERE 절과 hierarchical_clause 뒤에 위치시킨다. 만약 ORDER BY 절이 있다면 이는 쿼리문의 맨 마지막에 와야 한다.
rollup_cube_clause, grouping_sets_clause#
ROLLUP, CUBE, GROUPING SETS는 GROUP BY절이 확장된 것으로써, 여러 개의 그룹화 세트를 지정할 수 있다. 즉, ROLLUP, CUBE 또는 GROUPING SETS를 사용하면 GROUP BY 절을 포함하는 여러 쿼리를 UNION ALL로 결합한 것과 동일한 결과를 얻을 수 있다.
제약 사항:#
- GROUP BY 절에서 ROLLUP ,CUBE 또는 GROUPING SETS는 한 번만 명시할 수 있다.
- SELECT 대상에 부질의(subquery)를 인자로 가지는 집계 함수를 사용할 수 없다.
- GROUP BY 절에 GROUPING SETS를 사용하면 ORDER BY 절에 윈도우 함수를 사용할 수 없다.
- CUBE절에 최대 15개의 수식을 지정할 수 있다.
- GROUPING SETS와 중첩 집계 함수를 함께 사용할 수 없다.
ROLLUP#
ROLLUP은 GROUP BY 절과 함께 사용되며, GROUP BY 절에 의해서 그룹 지어진 집합 결과에 대하여 상세한 정보를 반환하는 기능을 수행한다.
SUM연산과 함께 사용되면, 총계뿐만 아니라 ROLLUP에서 지정하는 칼럼들의 그룹별 소계까지 구한다.
ROLLUP은 그룹화 칼럼들의 개수가 n개일 때 (n+1)개의 GROUP BY가 수행된다. 예를 들어, GROUP BY 절 내에 ROLLUP(a,b,c)를 사용하면 반환되는 그룹의 조합은 다음과 같다:
(a,b,c), (a,b), (a), ( )
부분 롤업(Partial ROLLUP)#
아래 예제와 같이 GROUP BY의 그룹화 칼럼 중 일부분에만 ROLLUP을 적용하는 것을 "부분 ROLLUP"이라고 한다:
GROUP BY a, ROLLUP(b, c), d
이 경우, 반환되는 그룹의 조합은 다음과 같다:
(a, d, b, c), (a, d, b), (a, d)
복합 칼럼(Composite Columns) 사용하기#
아래 예제와 같이 ROLLUP 절에 복합 칼럼의 리스트를 명시할 수 있다:
GROUP BY ROLLUP((a, b), (c, d))
이 경우, 반환되는 그룹의 조합은 다음과 같다:
(a, b, c, d), (a, b), ( )
CUBE#
CUBE는 명시된 그룹화 칼럼들의 가능한 모든 조합으로 그룹화를 수행한다. 그룹화 칼럼의 개수가 n개일 때, CUBE는 2n개의 조합에 대해 그룹화를 수행한다. 예를 들어, GROUP BY CUBE(a,b,c)를 명시하면, (23=8)개의 조합이 아래와 같이 반환된다:
(a,b,c), (a,b), (a,c), (b,c), (a), (b), (c), ( )
부분 큐브(Partial CUBE)#
부분 CUBE는 부분 ROLLUP과 유사하다. 즉, CUBE 연산자에 명시한 칼럼들의 가능한 모든 조합이 CUBE 바깥에 있는 칼럼과 함께 결합되어 반환되는 그룹이 결정된다. 부분 CUBE의 문법은 다음과 같다:
GROUP BY a, CUBE(b, c), d
이 경우, 아래의 조합에 대한 소계가 반환된다:
(a, d, b, c), (a, d, b), (a, d, c), (a, d)
복합 칼럼(Composite Columns) 사용하기#
아래 예제와 같이 CUBE 절에 복합 칼럼의 리스트를 명시할 수 있다:
GROUP BY CUBE((a, b), (c, d))
이 경우, 반환되는 그룹의 조합은 다음과 같다:
(a, b, c, d), (a, b), (c, d), ( )
GROUPING SETS#
ROLLUP이나 CUBE에 의해 생성되는 그룹화 세트 중에서 일부만 필요한 경우에 GROUPING SETS에 원하는 그룹화 세트만 지정할 수 있다.
SUM연산과 함께 사용되면, GROUPING SETS에서 지정하는 그룹별 총계를 구한다.
GROUPING SETS은 그룹화된 칼럼의 개수가 n개일 때 n개의 GROUP BY가 수행된다. 예를 들어, GROUP BY절 내에 GROUPING SETS(a, b, c)를 사용하면 반환되는 그룹의 조합은 다음과 같다.
(a), (b), (c)
부분 GROUPING SETS (Partial GROUPING SETS)#
아래 예제와 같이 GROUP BY의 그룹화 칼럼 중 일부분에만 GROUPING SETS을 적용하는 것을 "부분 GROUPING SETS"이라고 한다.
GROUP BY a, GROUPING SETS(b, c), d
이 경우, 반환되는 그룹의 조합은 다음과 같다:
(a, b, d), (a, c, d)
복합 칼럼(Composite Columns) 사용하기#
아래 예제와 같이 GROUPING SETS절에 복합 칼럼의 리스트를 명시할 수 있다.
GROUP BY GROUPING SETS((a, b), (c, d))
이와 같이 GROUPING SETS 목록에서 내부 괄호 안에 2개 이상의 칼럼들이 있는 경우 하나의 칼럼으로 취급된다. 반환되는 그룹의 조합은 다음과 같다.
(a, b), (c, d)
빈 그룹(Empty Group) 사용하기#
아래 예제와 같이 GROUPING SETS절에 빈 괄호("()")를 사용해서 '빈 그룹'을 명시할 수 있다.
GROUP BY GROUPING SETS((), a, b, c)
빈 그룹은 그룹화 없이 총계를 구하기 위해 사용할 수 있다.
HAVING 조건절#
HAVING 절에는 상수, 집계 함수(aggregate functions), GROUP BY 절에 명시된 표현식과 이들을 조합한 표현식만 올 수 있다.
이 절은 명시된 조건이 참인 그룹에 해당하는 레코드만 반환한다.
HAVING 절의 조건문에 대한 자세한 설명은 "8장 조건 연산자"를 참고하기 바란다.
UNION (ALL), INTERSECT, MINUS#
집합 연산자는 두 SELECT 문의 결과 집합을 하나로 결합한다. 각 질의가 반환하는 칼럼들의 개수와 데이터 타입이 동일해야 하지만, 칼럼 길이는 달라도 된다. 집합 연산자의 앞 부분에 위치하는 select_list 의 표현식 이름이 전체 결과 집합의 칼럼 이름이 될 것이다.
집합 연산자에 대한 자세한 설명은 5장 집한 연산자를 참고한다.
ORDER BY 절#
검색된 레코드들을 정렬하는 절이다. 결과 집합은 오름차순 또는 내림차순으로 정렬 가능하다. 기본으로 오름차순 정렬된다.
ORDER BY 절 없이 같은 질의를 반복해서 수행할 때, 결과 집합이 일관되게 정렬된다는 보장이 없다.
ORDER BY 절은 SELECT 문에서 한번만 올 수 있다. 이는 부질의 (subquery)에서는 사용할 수 없다.
ORDER BY 절에 표현식을 명시한 경우에는 표현식 연산의 결과 값에 대해 정렬된다. 표현식은 select_list 또는 FROM 절의 테이블 또는 뷰의 칼럼을 기반으로 작성될 것이다. ORDER BY 절에 상수를 명시한 경우에는 select_list 내에서의 그 위치에 해당하는 검색 대상 값에 대해 정렬 한다.
집합 연산자(UNION, INTERSECT 등)를 사용한 경우, 위치(position) 또는 검색 대상의 별명만 ORDER BY 절에 사용 가능하다.
GROUP BY가 존재할 경우 그룹 표현식만 사용 가능하다.
ORDER BY 절에 다수의 표현식도 올 수 있다. 결과 집합은 먼저 첫 번째 표현식의 값을 기준으로 정렬되고, 첫번째 표현식의 값이 같은 레코드들은 두번째 표현식의 값을 기준으로 정렬되며, 같은 방식으로 이 후 표현식에 대해서도 정렬된다.
오름차순의 인덱스가 정렬 기준 칼럼에 존재한다면, 오름차순으로 정렬할 때는 다른 모든 것들의 뒤에, 그리고 내림차순으로 정렬할 때는 다른 다른 것들의 앞에, NULL 이 정렬된다. 내림차순의 인덱스가 정렬 기준 칼럼에 존재한다면, 오름차순으로 정렬할 때는 다른 모든 것들의 앞에, 그리고 내림차순으로 정렬할 때는 다른 다른 것들의 뒤에, NULL 이 정렬된다. 인덱스가 정렬 기준 칼럼에 존재하지 않는다면, 정렬 순서에 상관없이 NULL은 다른 모든 것들의 뒤에 나타난다.
검색 대상에 DISTINCT를 사용하면, ORDER BY 절에는 SELECT 리스트내의 표현식 또는 이들 표현식의 조합만 올 수 있다.
GROUP BY 절이 있다면, 다음의 표현식이 ORDER BY 절에 올 수 있다.
- 상수
- 집계 함수 (aggregate functions)
- GROUP BY 절의 표현식
- 위의 조합으로 이루어진 표현식
NULLS FIRST 또는 NULLS LAST 키워드를 사용해서 NULL을 정렬 순서에서 맨 처음 또는 맨 마지막에 위치시킬 수 있다.
LIMIT 절#
LIMIT 절은 반환되는 행의 개수를 제한하기 위해 사용된다.
- row_offset: 반환할 첫번째 레코드를 지정한다. 생략하면 전체 결과 집합의 첫번째 레코드부터 반환될 것이다.
- row_count: 반환할 레코드의 개수를 지정한다.
부질의에서도 LIMIT절을 사용할 수 있다.
FOR UPDATE 절#
현재 트랜잭션이 끝날 때까지 다른 사용자들이 행(row)을 잠그거나 수정할 수 없도록 선택된 행을 잠근다.
WAIT 옵션은 테이블의 잠금을 획득하기 위해 얼마나 대기할지를 지정한다. 적용할 수 있는 시간 단위는 second(초), millisecond(msec, 1/1000초), microsecond(usec, 1/1000000초)이며 표기하지 않으면 초 단위가 적용된다. 반면, NOWAIT 옵션은 잠금을 획득할 테이블이 이미 다른 트랜잭션에 의해 잠금이 걸린 상태라면 기다리지 말 것을 지시한다.
FOR UPDATE 절은 최 상위 SELECT 문에서만 사용 가능하다. 즉, 부질의에는 사용할 수 없다. 그러므로 다음과 같은 질의는 사용할 수 없다:
select eno from employees where (select eno from departments for update);
FOR UPDATE 절은 DISTINCT, GROUP BY절, 집계 함수, 집합 연산자(UNION, INTERSECT 등)와 함께 사용 할 수 없다.
조인#
조인이란 두 개 이상의 테이블, 뷰, materialized 뷰로부터 로우를 결합하는 것을 일컫는다. 쿼리의 FROM 절에 여러 테이블이 있을 때 조인이 수행된다.
조인 조건(join condition)은 두 테이블을 결합하는 방법을 정의한다. 조인 조건은 FROM 절이나 WHERE 절 중의 하나에 지정할 수 있다. 조인 조건에 있는 칼럼이 select 리스트에 반드시 있을 필요는 없다. 조인 조건은 주로 각 테이블로부터 두 칼럼을 비교하는데, Altibase는 조인 조건이 TRUE로 판정되는 각 테이블의 로우를 결합해서 반환한다.
세 개 이상의 테이블을 조인하는 경우, Altibase는 두 테이블을 먼저 조인한 다음 그 결과를 다른 테이블과 조인한다. 쿼리 옵티마이저는 조인할 테이블의 순서를 결정할 때 조인 조건, 인덱스, 통계 정보 등에 기반한다.
주의: LOB 칼럼은 조인 조건에 사용될 수 없다.
Altibase는 아래의 조인을 지원한다.
- Cross Join
- Inner Join
- Outer Join
- Semi Join
- Anti Join
Cross Join#
조인 조건을 갖지 않는 두 테이블을 결합한다. 한 테이블의 각 로우는 다른 테이블의 각각의 로우와 결합한다. Cartesian Products라고도 한다.
아래는 Cross Join 쿼리의 예제이다.
SELECT * FROM T1, T2;
Inner Join#
Inner Join은 일반적인 조인을 말하며, 두 테이블에서 조인 조건을 만족하는 로우들만 결합해서 반환한다.
아래는 Inner Join 쿼리의 예제이다.
SELECT * FROM T1, T2 WHERE T1.i1 = T2.i1;
SELECT * FROM T1 INNER JOIN T2 ON T1.i1 = T2.i1;
SELECT * FROM T1, T2, T3 WHERE T1.i1 = T2.i1 AND T2.i1 < T3.i2;
Outer Join#
Outer Join은 두 개의 다른 테이블에서 조인 조건을 만족하는 로우를 판별해서 반환한다. Inner Join과 Outer Join의 차이점은 한 테이블에만 조인 조건을 만족하는 로우가 있고 다른 테이블에는 조인 조건을 만족하는 로우가 없는 경우, Inner Join은 해당 로우를 반환하지 않는 반면 Outer Join은 조인 조건을 만족하는 로우가 없는 테이블의 값을 NULL로 반환한다.
Outer Join은 다음 세 종류가 있다 : Left Outer Join, Right Outer Join, Full Outer Join
Left Outer Join#
LEFT OUTER JOIN 키워드의 왼편에 테이블 A가, 오른편에 테이블 B가 있을 때, 조인 조건을 만족하는 A의 모든 로우가 반환된다. B에 만족하는 로우가 없는 경우에는 결과집합의 해당 자리에 NULL이 반환된다.
아래는 Left Outer Join 쿼리의 예제이다.
SELECT * FROM A LEFT OUTER JOIN B ON A.c1 = B.c1;
SELECT * FROM A, B WHERE A.c1 = B.c1(+);
Right Outer Join#
RIGHT OUTER JOIN 키워드의 왼편에 테이블 A가, 오른편에 테이블 B가 있을 때, 조인 조건을 만족하는 B의 모든 로우가 반환된다. A에 만족하는 로우가 없는 경우에는 결과집합의 해당 자리에 NULL이 반환된다.
아래는 Right Outer Join 쿼리의 예제이다.
SELECT * FROM A RIGHT OUTER JOIN B ON A.c1 = B.c1;
SELECT * FROM A, B WHERE A.c1(+) = B.c1;
Full Outer Join#
FULL OUTER JOIN 키워드의 왼편에 테이블 A가, 오른편에 테이블 B가 있을 때, A와 B 중 한 테이블에만 조인 조건을 만족하는 로우가 있어도 해당 로우가 반환된다. 조인 조건을 만족하는 로우가 없는 테이블에 대해서는 결과집합의 해당 자리에 NULL이 반환된다.
아래는 Full Outer Join 쿼리의 예제이다.
SELECT * FROM A FULL OUTER JOIN B ON A.c1 = B.c1;
Semi Join#
테이블 A와 테이블 B를 Semi Join하는 경우, B에 존재하는 A의 모든 로우를 반환한다. A의 한 로우에 대해서 B의 여러 로우가 일치하더라도 한 로우만 반환된다.
아래는 Semi Join 쿼리의 예제이다.
SELECT * FROM T1 WHERE EXISTS ( SELECT i1 FROM T2 WHERE T1.i1 = T2.i1 );
SELECT * FROM T1 WHERE i1 IN ( SELECT i1 FROM T2 );
Anti Join#
테이블 A와 테이블 B를 Anti Join하는 경우, B에 존재하지 않는 A의 로우들만 반환한다.
SELECT * FROM T1 WHERE NOT EXISTS ( SELECT i1 FROM T2 WHERE T1.i1 = T2.i1 );
SELECT * FROM T1 WHERE i1 NOT IN ( SELECT i1 FROM T2 );
예제#
위에서 설명한 각 조인의 실행 결과를 보여주기 위해 employee와 dept 테이블을 생성한다.
CREATE TABLE employee(name VARCHAR(10), empid INTEGER, deptname VARCHAR(20));
CREATE TABLE dept(deptname VARCHAR(20), manager VARCHAR(10));
INSERT INTO employee VALUES('Harry', 3415, 'Finance');
INSERT INTO employee VALUES('Sally', 2241, 'Sales');
INSERT INTO employee VALUES('George', 3401, 'Finance');
INSERT INTO employee VALUES('Harriet', 2202, 'Production');
INSERT INTO dept VALUES('Sales','Bob');
INSERT INTO dept VALUES('Sales','Thomas');
INSERT INTO dept VALUES('Production','Katie');
INSERT INTO dept VALUES('Production','Mark');
아래는 Cross Join 쿼리와 그 결과이다.
iSQL> SELECT * FROM employee, dept;
NAME EMPID DEPTNAME DEPTNAME MANAGER
--------------------------------------------------------------------------------------
Harry 3415 Finance Sales Bob
Harry 3415 Finance Sales Thomas
Harry 3415 Finance Production Katie
Harry 3415 Finance Production Mark
Sally 2241 Sales Sales Bob
Sally 2241 Sales Sales Thomas
Sally 2241 Sales Production Katie
Sally 2241 Sales Production Mark
George 3401 Finance Sales Bob
George 3401 Finance Sales Thomas
George 3401 Finance Production Katie
George 3401 Finance Production Mark
Harriet 2202 Production Sales Bob
Harriet 2202 Production Sales Thomas
Harriet 2202 Production Production Katie
Harriet 2202 Production Production Mark
16 rows selected.
아래는 Inner Join 쿼리와 그 결과이다.
iSQL> SELECT * FROM employee A, dept B WHERE A.deptname = B.deptname;
NAME EMPID DEPTNAME DEPTNAME MANAGER
--------------------------------------------------------------------------------------
Sally 2241 Sales Sales Thomas
Sally 2241 Sales Sales Bob
Harriet 2202 Production Production Mark
Harriet 2202 Production Production Katie
4 rows selected.
아래는 Left Outer Join 쿼리와 그 결과이다.
iSQL> SELECT * FROM employee A LEFT OUTER JOIN dept B ON A.deptname = B.deptname;
NAME EMPID DEPTNAME DEPTNAME MANAGER
--------------------------------------------------------------------------------------
Harry 3415 Finance
Sally 2241 Sales Sales Thomas
Sally 2241 Sales Sales Bob
George 3401 Finance
Harriet 2202 Production Production Mark
Harriet 2202 Production Production Katie
6 rows selected.
아래는 Right Outer Join 쿼리와 그 결과이다.
iSQL> SELECT * FROM employee A RIGHT OUTER JOIN dept B ON A.deptname = B.deptname;
NAME EMPID DEPTNAME DEPTNAME MANAGER
--------------------------------------------------------------------------------------
Sally 2241 Sales Sales Bob
Sally 2241 Sales Sales Thomas
Harriet 2202 Production Production Katie
Harriet 2202 Production Production Mark
4 rows selected.
아래는 Semi Outer Join 쿼리와 그 결과이다.
iSQL> SELECT * FROM employee A WHERE EXISTS ( SELECT deptname FROM dept B WHERE A.deptname = B.deptname );
NAME EMPID DEPTNAME
--------------------------------------------------
Sally 2241 Sales
Harriet 2202 Production
2 rows selected.
아래는 Anti Outer Join 쿼리와 그 결과이다.
iSQL> SELECT * FROM employee A WHERE NOT EXISTS ( SELECT deptname FROM dept B WHERE A.deptname = B.deptname );
NAME EMPID DEPTNAME
--------------------------------------------------
Harry 3415 Finance
George 3401 Finance
2 rows selected.
HINTS 절#
힌트의 문법과 자세한 설명은 "힌트 구문"과 "힌트 목록"을 참고하기 바란다.
제약사항#
Altibase는 SQL질의 및 저장프로시저 수행에 있어 다음과 같은 제약을 가진다.
-
최대 65536개까지의 내부 튜플1만 질의 처리에 사용될 수 있다.
-
FROM절에에 최대 32개 까지의 테이블 또는 뷰를 사용할 수 있다.
-
WHERE, GROUP BY, ORDER BY 같은 연산식이 사용 가능한 절에 최대 32개까지의 테이블 또는 뷰를 사용할 수 있다.
위와 같은 제약을 위배하게 되면 다음과 같은 에러가 발생하게 된다.
- qpERR_ABORT_QTC_TUPLE_SHORTAGE
There are too many DML statements in the stored procedure, or the SQL query is too long. - qpERR_ABORT_QTC_TOO_MANY_TABLES
Too many tables are referenced in a phrase.
예제#
단순 조회#
<질의> 모든 사원의 이름, 고용일, 월급을 검색하라.
iSQL> SELECT e_firstname, e_lastname, join_date, salary
FROM employees;
E_FIRSTNAME E_LASTNAME JOIN_DATE SALARY
-----------------------------------------------------------------------
Chan-seung Moon
Susan Davenport 18-NOV-2009 1500
Ken Kobain 11-JAN-2010 2000
.
.
.
20 rows selected.
<질의> 급여가 가장 많은 사원의 이름, 고용일, 월급을 검색하라.
iSQL> SELECT TOP (1) e_firstname, e_lastname, join_date, salary
2 FROM employees ORDER BY salary;
E_FIRSTNAME E_LASTNAME JOIN_DATE SALARY
--------------------------------------------------------------------------
Gottlieb Fleischer 24-JAN-2004 500
1 row selected.
<질의> FROM절 이하가 생략된 쿼리의 예제이다.
iSQL> SELECT cos(0), 256;
COS(0) 256
--------------------------------------
1 256
1 row selected.
<질의> 현재 날짜와 departments 테이블의 모든 칼럼 값을 검색하라.
iSQL> SELECT sysdate, *
FROM departments;
SYSDATE DNO DNAME DEP_LOCATION MGR_NO
--------------------------------------------------------------------------------------------
26-JUN-2013 1001 RESEARCH DEVELOPMENT DEPT 1 New York 16
26-JUN-2013 1002 RESEARCH DEVELOPMENT DEPT 2 Sydney 13
26-JUN-2013 1003 SOLUTION DEVELOPMENT DEPT Osaka 14
26-JUN-2013 2001 QUALITY ASSURANCE DEPT Seoul 17
26-JUN-2013 3001 CUSTOMERS SUPPORT DEPT London 4
26-JUN-2013 3002 PRESALES DEPT Peking 5
26-JUN-2013 4001 MARKETING DEPT Brasilia 8
26-JUN-2013 4002 BUSINESS DEPT Palo Alto 7
8 rows selected.
WITH subquery 절 사용#
<질의> dept_costs와 avg_cost라는 쿼리 이름을 생성하여 주 질의에서 이들 이름을 사용한다.
iSQL> WITH
2 dept_costs AS (
3 SELECT DNAME, SUM(salary) dept_total
4 FROM employees e, departments d
5 WHERE e.dno = d.dno
6 GROUP BY DNAME),
7 avg_cost AS (
8 SELECT SUM(dept_total)/COUNT(*) avg
9 FROM dept_costs)
10 SELECT * FROM dept_costs
11 WHERE dept_total > (SELECT avg FROM avg_cost)
12 ORDER BY DNAME;
DEPT_COSTS.DNAME DEPT_COSTS.DEPT_TOTAL
---------------------------------------------------------
BUSINESS DEPT 4190
RESEARCH DEVELOPMENT DEPT 1 4300
SOLUTION DEVELOPMENT DEPT 9753
3 rows selected.
파티션을 사용한 조회#
CREATE TABLE T1 (I1 INTEGER)
PARTITION BY RANGE (I1)
(
PARTITION P1 VALUES LESS THAN (100),
PARTITION P2 VALUES LESS THAN (200),
PARTITION P3 VALUES DEFAULT
) TABLESPACE SYS_TBS_DISK_DATA;
INSERT INTO T1 VALUES (55);
INSERT INTO T1 VALUES (123);
SELECT * FROM T1 PARTITION (P1);
I1
----------
55
SELECT * FROM T1 PARTITION (P2);
I1
----------
123
SELECT * FROM T1 PARTITION (P3);
No rows selected.
검색 조건 사용#
<질의> 월급이 100만원 이하인 직원의 이름, 업무, 입사일, 월급을 월급 순서로 정렬하라.
iSQL> SELECT e_firstname, e_lastname, emp_job, salary
FROM employees
WHERE salary < 1500
ORDER BY 4 DESC;
E_FIRSTNAME E_LASTNAME EMP_JOB SALARY
------------------------------------------------------------------------
Takahiro Fubuki PM 1400
Curtis Diaz planner 1200
Jason Davenport webmaster 1000
Mitch Jones PM 980
Gottlieb Fleischer manager 500
5 rows selected.
Hierachical query 사용 검색#
<질의> id 열의 값이 0인 행을 루트로 하는 행들을 얻기 위한 계층적 질의문은 다음과 같다.
iSQL> CREATE TABLE hier_order(id INTEGER, parent INTEGER);
Create success.
iSQL> INSERT INTO hier_order VALUES(0, NULL);
1 row inserted.
iSQL> INSERT INTO hier_order VALUES(1, 0);
1 row inserted.
iSQL> INSERT INTO hier_order VALUES(2, 1);
1 row inserted.
iSQL> INSERT INTO hier_order VALUES(3, 1);
1 row inserted.
iSQL> INSERT INTO hier_order VALUES(4, 1);
1 row inserted.
iSQL> INSERT INTO hier_order VALUES(5, 0);
1 row inserted.
iSQL> INSERT INTO hier_order VALUES(6, 0);
1 row inserted.
iSQL> INSERT INTO hier_order VALUES(7, 6);
1 row inserted.
iSQL> INSERT INTO hier_order VALUES(8, 7);
1 row inserted.
iSQL> INSERT INTO hier_order VALUES(9, 7);
1 row inserted.
iSQL> INSERT INTO hier_order VALUES(10, 6);
1 row inserted.
iSQL> SELECT ID, parent, LEVEL
FROM hier_order START WITH id = 0 CONNECT BY PRIOR id = parent ORDER BY level;
ID PARENT LEVEL
------------------------------------------------
0 1
6 0 2
5 0 2
1 0 2
10 6 3
4 1 3
7 6 3
3 1 3
2 1 3
8 7 4
9 7 4
11 rows selected.
[그림 4‑1] 계층적 구조 데이터
<질의> START WITH 절을 생략하여 테이블 내의 모든 행을 루트 행으로 사용하고 PRIOR id = parent 조건을 만족하는 질의이다.
iSQL> SELECT id, parent, level
FROM hier_order CONNECT BY PRIOR id = parent ORDER BY id;
ID PARENT LEVEL
------------------------------------------------
0 1
1 0 1
1 0 2
2 1 1
2 1 3
2 1 2
3 1 2
3 1 1
3 1 3
4 1 1
4 1 2
4 1 3
5 0 1
5 0 2
6 0 2
6 0 1
7 6 1
7 6 2
7 6 3
8 7 3
8 7 1
8 7 2
8 7 4
9 7 2
9 7 3
9 7 4
9 7 1
10 6 1
10 6 2
10 6 3
30 rows selected.
<질의> 다음 계층적 질의문은 순환을 형성하는 행을 제외한 결과 집합을 얻기 위하여 IGNORE LOOP 절을 사용한 예이다.
iSQL> CREATE TABLE triple(
num INTEGER,
tri INTEGER,
PRIMARY KEY(num, tri));
Create success.
iSQL> CREATE OR REPLACE PROCEDURE proc_tri
AS
v1 INTEGER;
BEGIN
FOR v1 IN 1 .. 1000 LOOP
INSERT INTO triple VALUES(v1, v1 * 3);
END LOOP;
INSERT INTO triple VALUES(1, 1);
END;
/
Create success.
iSQL> EXEC proc_tri;
Execute success.
iSQL> SELECT num, tri, level
FROM triple
WHERE num < 3001
START WITH num = 1
CONNECT BY PRIOR tri = num
IGNORE LOOP;
NUM TRI LEVEL
------------------------------------------------
1 1 1
1 3 2
3 9 3
9 27 4
27 81 5
81 243 6
243 729 7
729 2187 8
1 3 1
3 9 2
9 27 3
27 81 4
81 243 5
243 729 6
729 2187 7
15 rows selected.
<질의> 계층적 질의에서 CONNECT_BY_ROOT 연산자를 사용해서 각 id의 루트 노드를 구하라.
CREATE TABLE hier_order(id INTEGER, name varchar(10), parent INTEGER);
INSERT INTO hier_order VALUES(0, 'Moon', NULL);
INSERT INTO hier_order VALUES(1, 'Davenport', 0);
INSERT INTO hier_order VALUES(2, 'Kobain', 1);
INSERT INTO hier_order VALUES(3, 'Foster', 1);
INSERT INTO hier_order VALUES(4, 'Ghorbani', 1);
INSERT INTO hier_order VALUES(5, 'Momoi', 0);
INSERT INTO hier_order VALUES(6, 'Fleischer', 0);
INSERT INTO hier_order VALUES(7, 'Wang', 6);
INSERT INTO hier_order VALUES(8, 'Diaz', 7);
INSERT INTO hier_order VALUES(9, 'Liu', 7);
INSERT INTO hier_order VALUES(10, 'Hammond', 6);
iSQL> SELECT id, CONNECT_BY_ROOT id "Root_Id"
FROM hier_order
WHERE LEVEL > 1
START WITH id = 0
CONNECT BY PRIOR id = parent
ORDER BY id, "Root_Id";
ID Root_Id
---------------------------
1 0
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 0
10 rows selected.
<질의> 계층적 질의에서 CONNECT_BY_ISLEAF 의사 칼럼을 사용해서 각 행이 리프 노드인지 여부를 구하라.
iSQL> SELECT id, CONNECT_BY_ISLEAF "IsLeaf",
LEVEL
FROM hier_order
START WITH id = 0
CONNECT BY PRIOR id = parent
ORDER BY id, "IsLeaf";
ID IsLeaf LEVEL
----------------------------------------------------------
0 0 1
1 0 2
2 1 3
3 1 3
4 1 3
5 1 2
6 0 2
7 0 3
8 1 4
9 1 4
10 1 3
11 rows selected.
<질의> ORDER SIBLINGS BY를 사용해서 계층 구조를 보존하면서, name을 기준으로 정렬하라.
iSQL> SELECT name, id, parent, LEVEL
FROM hier_order
START WITH id = 0
CONNECT BY PRIOR id = parent
ORDER SIBLINGS BY name;
NAME ID PARENT LEVEL
--------------------------------------------------------------
Moon 0 1
Davenport 1 0 2
Foster 3 1 3
Ghorbani 4 1 3
Kobain 2 1 3
Fleischer 6 0 2
Hammond 10 6 3
Wang 7 6 3
Diaz 8 7 4
Liu 9 7 4
Momoi 5 0 2
11 rows selected.
Recursive query 검색#
<질의> id 열의 값이 0인 행을 루트로 하는 행들을 얻기 위한 계층적 질의문은 다음과 같다. (순환 데이타)
iSQL> INSERT INTO hier_order VALUES(7, 9);
1 row inserted.
iSQL> WITH q1 (q1_i1,q1_i2, lvl) as
2 (
3 SELECT id,parent,1 FROM hier_order WHERE id = 0
4 UNION ALL
5 SELECT a.id,a.parent,lvl+1 from hier_order a, q1 b where a.parent = b.q1_i1
6 )
7 select * from q1 limit 18;
Q1_I1 Q1_I2 LVL
----------------------------------------
0 1
1 0 2
5 0 2
6 0 2
2 1 3
3 1 3
4 1 3
7 6 3
10 6 3
8 7 4
9 7 4
7 9 5
8 7 6
9 7 6
7 9 7
8 7 8
9 7 8
7 9 9
18 rows selected.
GROUP BY를 이용한 조회#
<질의> 부서별 급여 평균을 계산하라.
iSQL> SELECT dno, AVG(salary) AS avg_sal
FROM employees
GROUP BY dno;
DNO AVG_SAL
---------------------------
1001 2150
1002 1340
1003 2438.25
2001 1400
3001 1800
3002 2500
4001 1550
4002 1396.66667
1500
9 rows selected.
- SELECT 목록의 열 중 집계 함수가 사용되지 않은 모든 열은 GROUP BY 절에 있어야 한다.
- 칼럼에 별명을 주거나, 칼럼 이름과 다른 별명을 사용하고 싶으면 위의 AS avg_sal 처럼 칼럼의 이름 뒤에 사용하고 싶은 별명을 적어주면 된다. 칼럼 별명을 만들 때 AS 키워드는 생략 가능하다.
- 하이픈 두개 ("--")가 오면 그 줄의 이후 부분은 모두 주석(comment) 으로 처리된다.
<질의> 여러 열에 GROUP BY 절을 사용해서 각 부서내에서 각 직위별로 지급되는 급여 총액을 출력하라.
iSQL> SELECT dno, emp_job, COUNT(emp_job) num_emp, SUM(salary) sum_sal
FROM employees
GROUP BY dno, emp_job;
DNO EMP_JOB NUM_EMP SUM_SAL
-------------------------------------------------------------------
3002 CEO 1
designer 1 1500
1001 engineer 1 2000
3001 PL 1 1800
3002 PL 1 2500
1002 programmer 1 1700
4002 manager 1 500
4001 manager 1
4001 planner 2 3100
1003 programmer 1 4000
1003 webmaster 2 3750
4002 sales rep 3 3690
1002 PM 1 980
1003 PM 1 2003
1001 manager 1 2300
2001 PM 1 1400
16 rows selected.
<질의> 평균 급여가 $1500 USD를 넘는 부서의 평균 급여를 출력하라.
iSQL> SELECT dno, AVG(salary)
FROM employees
WHERE AVG(salary) > 1500
GROUP BY dno;
[ERR-31061 : An aggregate function is not allowed here.
0003 : WHERE AVG(SALARY) > 1500000
^ ^
]
<질의> HAVING 절을 사용하여 위의 오류를 수정할 수 있다.
iSQL> SELECT dno, AVG(salary)
FROM employees
GROUP BY dno
HAVING AVG(salary) > 1500;
DNO AVG(SALARY)
---------------------------
1001 2150
1003 2438.25
3001 1800
3002 2500
4001 1550
5 rows selected.
<질의> 3개 이상 주문된 상품번호와 그 상품들의 총 수를 출력하라.
iSQL> SELECT gno, COUNT(*)
FROM orders
GROUP BY gno
HAVING COUNT(*) > 2;
GNO COUNT
------------------------------------
A111100002 3
C111100001 4
D111100008 3
E111100012 3
4 rows selected.
<질의> 12월 한 달 동안 2개 이상 주문된 상품번호와 그 상품들의 평균 주문양을 평균 주문양 순서대로 출력하라.
iSQL> SELECT gno, AVG(qty) month_avg
FROM orders
WHERE order_date BETWEEN '01-Dec-2011' AND '31-Dec-2011'
GROUP BY gno
HAVING COUNT(*) > 1
ORDER BY AVG(qty);
GNO MONTH_AVG
---------------------------
A111100002 35
D111100003 300
D111100004 750
C111100001 1637.5
D111100010 1750
D111100002 1750
E111100012 4233.33333
D111100008 5500
8 rows selected.
<질의> GROUP BY절에 ROLLUP을 사용해서 다음 세 조합에 대해 급여의 소계를 구한다: (dno, sex), (dno), (총계).
iSQL> select dno, sex, sum(SALARY) from employees group by rollup( dno, sex);
DNO SEX SUM(SALARY)
---------------------------------
1001 F 2300
1001 M 2000
1001 4300
1002 M 2680
1002 2680
1003 F 4000
1003 M 5753
1003 9753
2001 M 1400
2001 1400
3001 M 1800
3001 1800
3002 M 2500
3002 2500
4001 M 3100
4001 3100
4002 F 1890
4002 M 2300
4002 4190
F 1500
1500
31223
22 rows selected.
<질의> GROUP BY절에 CUBE를 사용해서 그룹화 칼럼의 모든 조합에 대한 급여의 소계를 구한다: (dno, sex), (dno), (sex), (총계).
iSQL> select dno, sex, sum(SALARY) from employees group by cube( dno, sex);
DNO SEX SUM(SALARY)
---------------------------------
31223
1001 F 2300
1001 M 2000
1001 4300
1002 M 2680
1002 2680
1003 F 4000
1003 M 5753
1003 9753
2001 M 1400
2001 1400
3001 M 1800
3001 1800
3002 M 2500
3002 2500
4001 M 3100
4001 3100
4002 F 1890
4002 M 2300
4002 4190
F 1500
1500
F 9690
M 21533
24 rows selected.
<질의> GROUP BY 절에 GROUPING SETS를 사용해서 다음의 세 그룹화에 대해 급여 소계를 구하라: (dno,sex), (dno), ()
iSQL> SELECT dno, sex, SUM(salary)
FROM employees
GROUP BY GROUPING SETS( (dno, sex), dno, () );
DNO SEX SUM(SALARY)
---------------------------------
3002 M 2500
F 1500
1001 M 2000
3001 M 1800
1002 M 2680
4002 M 2300
4001 M 3100
1003 F 4000
1003 M 5753
4002 F 1890
1001 F 2300
2001 M 1400
3002 2500
1500
1001 4300
3001 1800
1002 2680
4002 4190
4001 3100
1003 9753
2001 1400
31223
22 rows selected.
ORDER BY를 이용한 조회#
<질의> 모든 사원의 이름, 부서 번호 및 급여를 부서 번호를 기준으로 정렬한 후 급여를 기준으로 해서 내림차순으로 출력하라.
iSQL> SELECT e_firstname, e_lastname, dno, salary
FROM employees
ORDER BY dno, salary DESC;
E_FIRSTNAME E_LASTNAME DNO SALARY
-------------------------------------------------------------------------
Wei-Wei Chen 1001 2300
Ken Kobain 1001 2000
Ryu Momoi 1002 1700
Mitch Jones 1002 980
Elizabeth Bae 1003 4000
.
.
.
20 rows selected.
<질의> 다음은 모든 사원의 이름 및 급여를 부서 번호를 기준으로 정렬한 후 급여를 기준으로 해서 내림차순으로 출력하는 질의이다. (SELECT 목록에 없는 열을 기준으로 정렬할 수도 있다.)
iSQL> SELECT e_firstname, e_lastname, salary
FROM employees
ORDER BY dno, salary DESC;
E_FIRSTNAME E_LASTNAME SALARY
------------------------------------------------------------
Wei-Wei Chen 2300
Ken Kobain 2000
Ryu Momoi 1700
Mitch Jones 980
Elizabeth Bae 4000
.
.
.
20 rows selected.
연산자 사용 조회#
<질의> 재고 상품의 이름, 각 제품의 재고 값을 출력하라.
iSQL> SELECT gname, (stock*price) inventory_value
FROM goods;
GNAME INVENTORY_VALUE
-------------------------------------
IM-300 78000000
IM-310 9800000
NT-H5000 27924000
.
.
.
30 rows selected.
별명(alias_name)을 사용한 조회#
<질의> 부서 위치에 별명(지역명)을 지정하여 검색하라.
iSQL> SELECT dname, 'District Name', dep_location location
FROM departments;
DNAME 'District Name' LOCATION
------------------------------------------------
Applied Technology Team District Name Mapo
Engine Development Team District Name Yeoido
Marketing Team District Name Gangnam
Planning & Management Team District Name Gangnam
Sales Team District Name Shinchon
5 rows selected.
LIMIT절을 사용한 조회#
<질의> employees테이블에서 사원 이름을 3번째 레코드 부터 5명만 출력하라.
iSQL> SELECT e_firstname first_name, e_lastname last_name
FROM employees
LIMIT 3, 5;
FIRST_NAME LAST_NAME
-----------------------------------------------
Ken Kobain
Aaron Foster
Farhad Ghorbani
Ryu Momoi
Gottlieb Fleischer
5 rows selected.
<질의> 관리자 테이블에서 첫 번째 레코드에 해당하는 사원의 이름과 급여를 출력하라.
iSQL> CREATE TABLE managers(
mgr_no INTEGER PRIMARY KEY,
m_lastname VARCHAR(20),
m_firstname VARCHAR(20),
address VARCHAR(60));
Create success.
iSQL> INSERT INTO managers VALUES(7, 'Fleischer', 'Gottlieb', '44-25 YouIDo-dong Youngdungpo-gu Seoul Korea');
1 row inserted.
iSQL> INSERT INTO managers VALUES(8, 'Wang', 'Xiong', '3101 N Wabash Ave Brooklyn NY');
1 row inserted.
iSQL> INSERT INTO managers VALUES(12, 'Hammond', 'Sandra', '130 Gongpyeongno Jung-gu Daegu Korea');
1 row inserted.
iSQL> SELECT e_firstname, e_lastname, salary FROM employees WHERE eno = (SELECT mgr_no FROM managers LIMIT 1);
E_FIRSTNAME E_LASTNAME SALARY
------------------------------------------------------------
Gottlieb Fleischer 500
1 row selected.
FOR UPDATE를 사용한 조회#
| Transaction A | Time Point | Transaction B |
|---|---|---|
| iSQL> AUTOCOMMIT OFF; Set autocommit off success. |
iSQL> AUTOCOMMIT OFF; Set autocommit off success. |
|
| (request X lock on employees) iSQL> LOCK TABLE employees IN EXCLUSIVE MODE; Lock success. (acquire X lock on employees) iSQL> SELECT e_lastname FROM employees WHERE eno = 15; E_LASTNAME ------------------------ Davenport 1 row selected. |
1 | |
| 2 | iSQL> SELECT e_lastname FROM employees WHERE eno = 15 FOR UPDATE; (request conflicts with the X lock already held by transaction A) wait wait wait |
|
| iSQL> UPDATE employees SET ENO = 30 WHERE eno = 15; 1 row updated. iSQL> SELECT e_lastname FROM employees WHERE eno = 30; E_LASTNAME ------------------------ Davenport 1 row selected. |
3 | |
| iSQL> COMMIT; Commit success. |
4 | |
| 5 | (resume) E_LASTNAME ------------------------ No rows selected. |
HINTS를 사용한 조회#
Table Access Method Hints : full scan, index scan, index ascending order scan, index descending order scan, no index scan#
다음은 사원들 중 모든 여사원의 번호, 이름, 직업을 검색하는 질의이다.
SELECT eno, e_firstname, e_lastname, emp_job FROM employees WHERE sex = 'F';
iSQL> SELECT /*+ FULL SCAN(employees) */ eno, e_firstname, e_lastname, emp_job
FROM employees
WHERE sex = 'F';
ENO E_FIRSTNAME E_LASTNAME EMP_JOB
------------------------------------------------
.
.
.
------------------------------------------------
PROJECT ( COLUMN_COUNT: 4, TUPLE_SIZE: 65 )
SCAN ( TABLE: EMPLOYEES, FULL SCAN, ACCESS: 20, SELF_ID: 2 )
------------------------------------------------
iSQL> CREATE INDEX gender_index ON employees(sex);
Create success.
iSQL> SELECT /*+ INDEX(employees, gender_INDEX) use gender_index because there are few female ployees */ eno, e_firstname, e_lastname, emp_job
FROM employees
WHERE sex = 'F';
ENO E_FIRSTNAME E_LASTNAME EMP_JOB
------------------------------------------------
.
.
.
------------------------------------------------
PROJECT ( COLUMN_COUNT: 4, TUPLE_SIZE: 65 )
SCAN ( TABLE: EMPLOYEES, INDEX: GENDER_INDEX, ACCESS: 4, SELF_ID: 2 )
------------------------------------------------
create view orders as
select ono, order_date, eno, cno, gno, qty from orders_01
union all
select ono, order_date, eno, cno, gno, qty from orders_02
union all
select ono, order_date, eno, cno, gno, qty from orders_03;
create index order1_gno on orders_01(gno);
create index order2_gno on orders_02(gno);
create index order3_gno on orders_03(gno);
iSQL> select /*+ index( orders,
orders1_gno, orders2_gno,orders3_gno ) */
ONO, GNO, QTY
from orders;
ONO GNO QTY
-------------------------------------------------
.
.
.
------------------------------------------------
PROJECT ( COLUMN_COUNT: 3, TUPLE_SIZE: 24 )
VIEW ( ORDERS, ACCESS: 14, SELF_ID: 6 )
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48 )
VIEW ( ACCESS: 14, SELF_ID: 5 )
BAG-UNION
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48 )
SCAN ( TABLE: ORDERS_01, INDEX: ORDERS1_GNO, ACCESS: , SELF_ID: 0 )
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48 )
SCAN ( TABLE: ORDERS_02, INDEX: ORDERS2_GNO, ACCESS: 4, SELF_ID: 1 )
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48 )
SCAN ( TABLE: ORDERS_03, INDEX: ORDERS3_GNO, ACCESS: 7, SELF_ID: 4 )
------------------------------------------------
Join Order Hints (ordered, optimized)#
<질의> 주문된 상품을 담당하고 있는 직원의 사원번호, 이름과 해당 고객의 이름을 출력하라. (employees 테이블과 customers 테이블을 조인하고, 그 결과를 orders 테이블과 조인하기 위해 ORDERED 힌트를 사용하라.)
iSQL> SELECT /*+ ORDERED */ DISTINCT o.eno, e.e_lastname, c.c_lastname
FROM employees e, customers c, orders o
WHERE e.eno = o.eno AND o.cno = c.cno;
ENO E_LASTNAME C_LASTNAME
------------------------------------------------
.
.
.
------------------------------------------------
PROJECT ( COLUMN_COUNT: 3, TUPLE_SIZE: 48 )
DISTINCT ( ITEM_SIZE: 40, ITEM_COUNT: 21, BUCKET_COUNT: 1024, ACCESS: 21, SELF_ID: 4, REF_ID: 3 )
JOIN
JOIN
SCAN ( TABLE: EMPLOYEES E, FULL SCAN, ACCESS: 20, SELF_ID: 1 )
SCAN ( TABLE: CUSTOMERS C, FULL SCAN, ACCESS: 400, SELF_ID: 2 )
SCAN ( TABLE: ORDERS O, FULL SCAN, ACCESS: 12000, SELF_ID: 3 )
------------------------------------------------
iSQL> SELECT DISTINCT o.eno, e.e_lastname, c.c_lastname
FROM employees e, customers c, orders o
WHERE e.eno = o.eno AND o.cno = c.cno;
ENO E_LASTNAME C_LASTNAME
------------------------------------------------
.
.
.
------------------------------------------------
PROJECT ( COLUMN_COUNT: 3, TUPLE_SIZE: 48 )
DISTINCT ( ITEM_SIZE: 40, ITEM_COUNT: 21, BUCKET_COUNT: 1024, ACCESS: 21, SELF_ID: 4, REF_ID: 1 )
JOIN
JOIN
SCAN ( TABLE: CUSTOMERS C, FULL SCAN, ACCESS: 20, SELF_ID: 2 )
SCAN ( TABLE: ORDERS O, INDEX: ODR_IDX2, ACCESS: 30, SELF_ID: 3 )
SCAN ( TABLE: EMPLOYEES E, INDEX: __SYS_IDX_ID_366, ACCESS: 30, SELF_ID: 1 )
------------------------------------------------
Optimizer Mode Hints (rule, cost)#
iSQL> SELECT /*+ RULE */ * FROM t1, t2 WHERE t1.i1 = t2.i1;
iSQL> SELECT /*+ COST */ * FROM t1, t2 WHERE t1.i1 = t2.i1;
Normal Form Hints (CNF, DNF)#
iSQL> SELECT /*+ CNF */ * FROM t1 WHERE i1 = 1 OR i1 = 2;
iSQL> SELECT /*+ DNF */ * FROM t1 WHERE i1 = 1 OR i1 = 2;
Join Method Hints (nested loop, hash, sort, sort merge)#
iSQL> SELECT /*+ USE_NL (t1,t2) */ * FROM t1, t2 WHERE t1.i1 = t2.i1;
iSQL> SELECT /*+ USE_HASH (t1,t2) */ * FROM t1, t2 WHERE t1.i1 = t2.i1;
iSQL> SELECT /*+ USE_SORT (t1,t2) */ * FROM t1, t2 WHERE t1.i1 = t2.i1;
iSQL> SELECT /*+ USE_MERGE (t1,t2) */ * FROM t1, t2 WHERE t1.i1 = t2.i1;
Hash Bucket Size Hints (hash bucket count, group bucket count, set bucket count)#
iSQL> SELECT /*+ HASH BUCKET COUNT (20) */ DISTINCT * FROM t1;
iSQL> SELECT * FROM t1 GROUP BY i1, i2;
iSQL> SELECT /*+ GROUP BUCKET COUNT (20) */ * FROM t1 GROUP BY i1, i2;
iSQL> SELECT * FROM t1 INTERSECT SELECT * FROM t2;
iSQL> SELECT /*+ SET BUCKET COUNT (20) */ * FROM t1 INTERSECT SELECT * FROM t2;
Push Predicate Hints#
<질의> 1사분기(1월에서 3월까지) 동안 발생한 주문 중에서 한번의 주문수량이 10000개이상인 고객의 명단과 상품번호을 구하라.(고객 테이블과 주문 테이블을 조인하기 위해 Push Predicate 힌트를 사용하라.)
create view orders as
select ono, order_date, eno, cno, gno, qty from orders_01
union all
select ono, order_date, eno, cno, gno, qty from orders_02
union all
select ono, order_date, eno, cno, gno, qty from orders_03;
iSQL> select /*+ PUSH_PRED(orders) */ c_lastname, gno
2 from customers, orders
3 where customers.cno = orders.cno
4 and orders.qty >= 10000;
C_LASTNAME GNO
-------------------------------------
.
.
.
------------------------------------------------
PROJECT ( COLUMN_COUNT: 2, TUPLE_SIZE: 34 )
JOIN
SCAN ( TABLE: CUSTOMERS, FULL SCAN, ACCESS: 20, SELF_ID: 2 )
FILTER
[ FILTER ]
AND
OR
ORDERS.QTY >= 10000
VIEW ( ORDERS, ACCESS: 1, SELF_ID: 8 )
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48 )
VIEW ( ACCESS: 1, SELF_ID: 7 )
BAG-UNION
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48 )
SCAN ( TABLE: ORDERS_01, INDEX: ODR1_IDX2, ACCESS: 3, SELF_ID: 3 )
[ VARIABLE KEY ]
OR
AND
[ FILTER ]
AND
OR
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48 )
SCAN ( TABLE: ORDERS_02, INDEX: ODR2_IDX2, ACCESS: 4, SELF_ID: 4 )
[ VARIABLE KEY ]
OR
AND
[ FILTER ]
AND
OR
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48 )
SCAN ( TABLE: ORDERS_03, INDEX: ODR3_IDX2, ACCESS: 7, SELF_ID: 6 )
[ VARIABLE KEY ]
OR
AND
[ FILTER ]
AND
OR
------------------------------------------------
OUTER JOIN을 이용한 조회#
<질의> 모든 부서에 대한 부서 번호와 사원 이름을 출력하라. (단, 사원이 전혀 없는 부서 번호 5001도 출력되게 하라.)
iSQL> INSERT INTO departments VALUES('5001', 'Quality Assurance', 'Jonglo', 22);
1 row inserted.
iSQL> SELECT d.dno, e.e_lastname
FROM departments d LEFT OUTER JOIN employees e ON d.dno = e.dno
ORDER BY d.dno;
DNO E_LASTNAME
-------------------------------------
...
6002
...
<질의> 모든 부서에 대한 부서 번호와 사원 이름을 출력하라. (단, 부서에 소속이 되어 있지 않은 CEO도 출력되게 하라.)
iSQL> SELECT d.dno, e.e_lastname
FROM departments d RIGHT OUTER JOIN employees e ON d.dno = e.dno
ORDER BY d.dno;
DNO E_LASTNAME
-------------------------------------
...
Davenport
...
<질의> 부서의 위치와 상품을 모아 놓은 장소가 같은 곳에 해당하는 부서의 부서번호, 부서 이름, 상품 번호를 출력하라.
iSQL> INSERT INTO departments VALUES('6002', 'headquarters', 'CE0002', 100);
1 row inserted.
iSQL> SELECT d.dno, d.dname, g.gno
FROM departments d FULL OUTER JOIN goods g
ON d.dep_location = g.goods_location;
DNO DNAME GNO
------------------------------------------------------------
.
6002 headquarters E111100005
.
In-line View를 이용한 조회#
<질의> 자신이 속한 부서의 평균 급여보다 급여를 많이 받는 모든 사원의 이름, 급여, 부서 번호 및 그 부서의 평균 급여를 출력하라.
iSQL> SELECT e.e_lastname, e.salary, e.dno, v1.salavg
FROM employees e,
(SELECT dno, AVG(salary) salavg FROM employees GROUP BY dno) v1
WHERE e.dno = v1.dno
AND e.salary > v1.salavg;
ENAME SALARY DNO SALAVG
------------------------------------------------
.
.
.
Lateral View를 이용한 조회#
<질의> 각 부서의 부서명과 급여 총계, 급여 평균을 검색하라.
iSQL> SELECT DEPT.dname, LV.*
FROM departments DEPT, LATERAL ( SELECT sum(salary), avg(salary)
FROM employees EMP WHERE DEPT.dno = EMP.dno ) LV;
DNAME SUM(SALARY) AVG(SALARY)
-----------------------------------------------------------------------
RESEARCH DEVELOPMENT DEPT 1 4300 2150
RESEARCH DEVELOPMENT DEPT 2 2680 1340
SOLUTION DEVELOPMENT DEPT 9753 2438.25
QUALITY ASSURANCE DEPT 1400 1400
CUSTOMERS SUPPORT DEPT 1800 1800
PRESALES DEPT 2500 2500
MARKETING DEPT 3100 1550
BUSINESS DEPT 4190 1396.66666666667
8 rows selected.
<질의> 각 부서에서 사원 번호가 가장 빠른 사원 1인의 이름과 부서명을 검색하라. 부서에 사원이 없다면 부서명이라도 출력해야 한다.
insert into departments values(8000, 'empty dept', 'seoul', 20);
iSQL> SELECT LV.e_firstname, LV.e_lastname, DEPT.dname
FROM departments DEPT OUTER APPLY ( SELECT TOP 1 e_firstname, e_lastname
FROM employees EMP WHERE DEPT.dno = EMP.dno ORDER BY eno ) LV;
E_FIRSTNAME E_LASTNAME DNAME
--------------------------------------------------------------------------------
Ken Kobain RESEARCH DEVELOPMENT DEPT 1
Ryu Momoi RESEARCH DEVELOPMENT DEPT 2
Elizabeth Bae SOLUTION DEVELOPMENT DEPT
Takahiro Fubuki QUALITY ASSURANCE DEPT
Aaron Foster CUSTOMERS SUPPORT DEPT
Chan-seung Moon PRESALES DEPT
Xiong Wang MARKETING DEPT
Gottlieb Fleischer BUSINESS DEPT
empty dept
9 rows selected.
PIVOT/UNPIVOT 절을 이용한 조회#
<질의> 각 부서별 남자와 여자 직원의 수를 구하라.
iSQL> SELECT * FROM
(SELECT d.dname, e.sex
FROM departments d, employees e
WHERE d.dno = e.dno)
PIVOT (COUNT(*) FOR sex in ('M', 'F'))
ORDER BY dname;
DNAME 'M' 'F'
----------------------------------------------------------------------
BUSINESS DEPT 3 1
CUSTOMERS SUPPORT DEPT 1 0
MARKETING DEPT 3 0
PRESALES DEPT 2 0
QUALITY ASSURANCE DEPT 1 0
RESEARCH DEVELOPMENT DEPT 1 1 1
RESEARCH DEVELOPMENT DEPT 2 2 0
SOLUTION DEVELOPMENT DEPT 3 1
8 rows selected.
<질의> 다음 예제는 비교를 위해서 GROUP BY와 ORDER BY 절을 이용한 질의를 보여준다. 같은 정보를 출력하지만, 읽기가 더 힘든 것을 알 수 있다.
iSQL> SELECT d.dname, e.sex, count(*) FROM departments d, employees e WHERE d.dno = e.dno GROUP BY d.dname, e.sex ORDER BY d.dname, e.sex DESC;
DNAME SEX COUNT
--------------------------------------------------------------
BUSINESS DEPT M 3
BUSINESS DEPT F 1
CUSTOMERS SUPPORT DEPT M 1
MARKETING DEPT M 3
PRESALES DEPT M 2
QUALITY ASSURANCE DEPT M 1
RESEARCH DEVELOPMENT DEPT 1 M 1
RESEARCH DEVELOPMENT DEPT 1 F 1
RESEARCH DEVELOPMENT DEPT 2 M 2
SOLUTION DEVELOPMENT DEPT M 3
SOLUTION DEVELOPMENT DEPT F 1
11 rows selected.
<질의> 사원의 전화번호와 성별이 각각 출력되도록 한다.
iSQL> SELECT eno, e_lastname, e_firstname, "info", "item"
FROM employees
UNPIVOT ("info" FOR "item" IN (emp_tel as 'telno',
sex as 'sex'))
;
ENO E_LASTNAME E_FIRSTNAME info item
------------------------------------------------------------------------
1 Moon Chan-seung 01195662365 telno
1 Moon Chan-seung M sex
2 Davenport Susan 0113654540 telno
2 Davenport Susan F sex
3 Kobain Ken 0162581369 telno
3 Kobain Ken M sex
4 Foster Aaron 0182563984 telno
4 Foster Aaron M sex
...
20 Blake William 01154112366 telno
20 Blake William M sex
40 rows selected.
Table Fuction 조회#
<질의> 사용자 정의 함수 'func1'를 생성하여, 10개의 행을 검색하라.
iSQL> CREATE TYPESET type1
AS
TYPE rec1 IS RECORD (c1 INTEGER, c2 INTEGER);
TYPE arr1 IS TABLE OF rec1 INDEX BY INTEGER;
END;
/
Create success.
iSQL> CREATE FUNCTION func1(i1 INTEGER)
RETURN type1.arr1
AS
v1 type1.arr1;
BEGIN
for i in 1 .. i1 loop
v1[i].c1 := i;
v1[i].c2 := i * i;
END LOOP;
RETURN v1;
END;
/
Create success.
iSQL> SELECT * FROM TABLE( func1(10) );
C1 C2
---------------------------
1 1
2 4
3 9
4 16
5 25
6 36
7 49
8 64
9 81
10 100
10 rows selected.
-
내부 튜플(internal tuple) : 알티베이스가 질의 처리를 위해 할당하는 메모리의 단위이다 ↩