오라클 스칼라 서브쿼리란?(oracle subquery)
Scalar Subquery는 질의 수식으로부터 유도된 Scalar 값 지정하기 위해 사용
Oracle8i 에서는 다음과 같이 제한된 경우에만 지원
Insert 문장의 VALUES List, 데이터 형 생성자에 대한 인자
Oracle9i 이상에서 Scalar 서브 쿼리는 유효한 수식이 쓰일 수 있는 모든 곳에서 사용 가능
Scalar Subquery는 오직 하나의 값만 반환
반환되는 값의 데이터 형은 서브 쿼리에서 선택된 데이터 형과 일치
소량의 데이터의 경우에는 효과적이나 대량의 데이터의 경우 성능 저하 가능
Scalar Subquery의 사용예
SELECT empno, ename,
(
SELECT dname
FROM dept d
WHERE e.deptno = d.deptno
) dname
FROM emp e
(
SELECT dname
FROM dept d
WHERE e.deptno = d.deptno
) dname
FROM emp e
SELECT empno, ename
FROM emp e
ORDER BY (
SELECT dname
FROM dept d
WHERE e.deptno = d.deptno
)
FROM emp e
ORDER BY (
SELECT dname
FROM dept d
WHERE e.deptno = d.deptno
)
SELECT ename, SUBSTR (
(SELECT dname
FROM dept d
WHERE d.deptno = e.deptno),1, 10) dname
FROM emp e;
(SELECT dname
FROM dept d
WHERE d.deptno = e.deptno),1, 10) dname
FROM emp e;
1. Select List에서의 Scalar Subquery
SELECT employee_id, last_name,
(
SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id
) department_name
FROM employees e
ORDER BY department
(
SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id
) department_name
FROM employees e
ORDER BY department
2. Where 절에서의 Scalar Subquery
SELECT employee_id, last_name
FROM employees e
WHERE ( ( SELECT location_id
FROM departments d
WHERE e.department_id = d.department_id)
=
( SELECT location_id
FROM locations l
WHERE STATE_province = 'California')
)
FROM employees e
WHERE ( ( SELECT location_id
FROM departments d
WHERE e.department_id = d.department_id)
=
( SELECT location_id
FROM locations l
WHERE STATE_province = 'California')
)
3. Order By 절에서의 Scalar Subquery
SELECT employee_id, last_name
FROM employees e
ORDER BY (
SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id
)
FROM employees e
ORDER BY (
SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id
)
4.CASE 수식에서의 Scalar Subquery
SELECT employee_id, last_name,
(CASE
WHEN department_id IN
(SELECT department_id FROM departments
WHERE location_id = 1800)
THEN 'Canada' ELSE 'other'
END) location
FROM employees
(CASE
WHEN department_id IN
(SELECT department_id FROM departments
WHERE location_id = 1800)
THEN 'Canada' ELSE 'other'
END) location
FROM employees
5. 함수에서의 Scalar Subquery
SELECT last_name, SUBSTR (
(SELECT department_name
FROM departments d
WHERE d.department_id = e.department_id),
1, 10) department
FROM employees e;
(SELECT department_name
FROM departments d
WHERE d.department_id = e.department_id),
1, 10) department
FROM employees e;
[개강확정강좌]오라클자바커뮤니티에서 운영하는 개발자 전문교육 ,개인80%환급(www.onjprogramming.co.kr)
[주말]
[11/2]C#,ASP.NET마스터
[11/2]Spring3.X, MyBatis, Hibernate실무과정
[11/2]JAVA&WEB프레임워크실무과정
[평일야간]
[10/29]C#,ASP.NET마스터
[10/31]JAVA&WEB프레임워크실무과정
[11/1]Spring3.X, MyBatis, Hibernate실무과정
[주간]
[11/4]Spring3.X, MyBatis, Hibernate실무과정
[주말]
[11/2]C#,ASP.NET마스터
[11/2]Spring3.X, MyBatis, Hibernate실무과정
[11/2]JAVA&WEB프레임워크실무과정
[평일야간]
[10/29]C#,ASP.NET마스터
[10/31]JAVA&WEB프레임워크실무과정
[11/1]Spring3.X, MyBatis, Hibernate실무과정
[주간]
[11/4]Spring3.X, MyBatis, Hibernate실무과정
[기타 다른 강좌는 아래 해당 카테고리를 클릭해주세요]
댓글 없음:
댓글 쓰기