#7.SQL초보에서 Schema...

SQL초보에서 Schema Objects까지

3.2.7 ANY(SOME), ALL, EXISTS

ANY(SOME) : 주로 서브 쿼리와 함께 사용되며 서브쿼리에서 여러 개의 값이 나오게 되는 경우

이 값들에 대해 어떤 한 값 보다만 어떻다라고 비교할 때 사용된다. sal > any (500, 600, 700)와 같

은 구문을 보면 sal > 500 or sal > 600 or sal > 700 형태로 해석되어 3개의 값 중 하나의 값인

500보다 크면 TRUE가 된다(sal > 500). 만약 700보다 크다면 모든 값보다 크게 된다.

ALL : ALL은 집합의 모든 값과 비교를 하게 되는데, 모든 값과 비교하여 TRUE가 되어야 한다. 예

를 들어 급여가 500, 600, 700 모두 보다 큰 값을 원한다면 ALL을 사용하면 되는데 sal > all(500,

600, 700) 형태가 되는데 sal > 500 and sal > 600 and sal > 700의 의미로 결국 sal > 700와 같다.

ALL, ANY는 =, !=, >, <, <=, >= 연산자 앞에 나타나며 이어서 여러값을 가진 리스트나 서브쿼리

가 오게된다.

-- 먼저 30번 부서 사원들의 급여를 확인해 보자.(가장 작은 값이 950, 큰 값은 2850이다)

SQL> SELECT sal FROM emp WHERE deptno=30;

SAL

----------

1600

1250

1250

2850

1500

950

6 개의 행이 선택되었습니다.

-- any의 경우 어떠한 하나의 값만 만족시키면 되므로 sal > 950 과 같은 결과이다.

SQL> SELECT ename, sal FROM emp

WHERE sal > any (SELECT sal FROM emp WHERE deptno = 30)

ORDER BY SAL;

ENAME SAL

---------- ----------

ADAMS 1100

……

KING 5000


WHERE sal > 950

ORDER BY SAL;

ENAME SAL

---------- ----------

ADAMS 1100

……

KING 5000

-- SOME은 ANY와 동일하다


WHERE sal > some (SELECT sal FROM emp WHERE deptno = 30)

ORDER BY SAL;

ENAME SAL

---------- ----------

ADAMS 1100

……

KING 5000

-- 전부를 만족시키기 위해서는 30번부서의 최고 급여인 2850보가 커야 한다. sal > 2850의 의미


WHERE sal > all (SELECT sal FROM emp WHERE deptno = 30)

ORDER BY SAL;

ENAME SAL

---------- ----------

JONES 2975

SCOTT 3000

FORD 3000

KING 5000


WHERE sal > 2850

ORDER BY SAL;

ENAME SAL

---------- ----------

JONES 2975

FORD 3000

SCOTT 3000

KING 5000

-- 위 ALL을 이용한 쿼리를 동일한 결과를 출력하는 ANY로 변환하면 다음과 같다.

-- WHERE NOT (sal <= 2850) 형태가 되어 결국 sal > 2850이 된다.

SELECT ename, sal

FROM emp e1

WHERE NOT (sal <= ANY (SELECT sal

FROM emp

WHERE deptno = 30))

ORDER BY SAL;

-- ALL 구문을 ANY를 사용하지 않고 NOT EXISTS로 변환하면 다음과 같다. 사원을 출력하는데

급여가 30번 부서원들의 급여보다 작거나 같은 것이 존재하지 않는 것을 추출, 즉 30번 부사원들

의 모든 급여보다 큰 급여를 가진 사원을 추출

SELECT ename, sal

FROM emp e1

WHERE NOT EXISTS (SELECT e2.sal

FROM emp e2

WHERE e2.deptno = 30

AND e1.sal <= e2.sal);

EXISTS : 서브 쿼리가 적어도 하나의 행을 돌려주면 TRUE가 된다.

NOT EXISTS : 서브 쿼리가 적어도 하나의 행을 돌려주지 않으면 TRUE가 된다.

SQL> SELECT dname as "부서명",

deptno as "부서코드"

FROM dept;

부서명 부서코드

-------------- ----------

ACCOUNTING 10

RESEARCH 20

SALES 30

OPERATIONS 40

-- 부서테이블(DEPT)에서 사원을 한명이라도 가지고 있는 부서명, 부서코드를 출력하시오.



FROM dept

WHERE EXISTS( SELECT * FROM emp

WHERE dept.deptno = emp.deptno);


-------------- ----------

ACCOUNTING 10

RESEARCH 20

SALES 30

-- 위 쿼리 구문을 IN연산자를 이용하면 다음과 같다. (IN은 OR로 해석된다.)

-- 대부분 IN을 이용하는 것보다 EXISTS를 사용하는 것이 쿼리 성능면에서 장점을 가진다.



FROM dept

WHERE deptno in ( SELECT deptno FROM emp

WHERE deptno is not null);


-------------- ----------

ACCOUNTING 10

RESEARCH 20

SALES 30

-- myemp1 테이블에서 퇴사자가 존재하면 1’을 출력하라.

SQL> SELECT 1 FROM dual

WHERE EXISTS (SELECT empno FROM myemp1

WHERE outdate is not null);

1

----------

1

-- MYEMP테이블에서 job중 ‘ORACLEJAVA’ 가 없으면 1을 출력하시오.

SQL> SELECT 1 FROM dual

WHERE NOT EXISTS ( SELECT job FROM myemp1

WHERE job = 'ORACLEJAVA');

1

----------

1

-- 사원테이블(MYEMP1), 수강테이블(MYSUGANG1) 테이블에서 한과목 이상 수강한 사원의 수

는?

SQL> SELECT COUNT(EMPNO) FROM MYEMP1 E

WHERE EXISTS (

SELECT 1 FROM MYSUGANG1 S

WHERE E.EMPNO = S.EMPNO

)

-- 강좌테이블(MYLECTURE1)에서 수강생이 한명도 없는 과목이름을 출력하세요.

SQL> SELECT LECTURE_NM FROM MYLECTURE1 L

WHERE NOT EXISTS (

SELECT 1 FROM MYSUGANG1 S

WHERE L.LECTURE_ID = S.LECTURE_ID

)

LECTURE_NM

------------------

안드로이드과정

3.2.8 집합 연산자

하나의 SELECT문의 결과가 다른 Select문의 결과와 합집합, 교집합, 차집합 등 새로운 결과를 만

들어 낼 때 사용하는 연산자 이다. 이러한 집한 연산자를 사용하기 위해서는 연산을 하는 SELECT

문의 결과와 칼럼 구조(개수, 타입)가 같아야 한다. 즉 같은 데이터 형이며 순서 또한 같아야 한다.

결과의 칼럼 명은 첫번째 SELECT문의 칼럼 명이 사용된다.

UNION : 연결된 Select문의 결과 합을 구해준다. 중복된 것은 하나만 보여줌

UNION ALL : 연결된 Select문의 결과 합을 구해준다. 중복된 ROW가 있더라도 모두 보여줌, 정렬

이 일어나지 않는다.

INTERSECT : 연결된 Select문의 결과 교집합을 구해준다.

MINUS : 연결된 Select문의 결과 차를 구해준다.

-- 실습을 위해 고객용 Table 두개를 만들자.

SQL> create table customer1 (

name varchar2(10) not null,

addr varchar2(50));

테이블이 생성되었습니다.

SQL> create table customer2 (

name varchar2(10) not null,

addr varchar2(50));

테이블이 생성되었습니다.

SQL> insert into customer1 values ('나길동','서울');

1 개의 행이 만들어졌습니다.

SQL> insert into customer1 values ('가길동','부산');


SQL> insert into customer2 values ('라길동','서울');


SQL> insert into customer2 values ('가길동','부산');


SQL> commit;

커밋이 완료되었습니다.

SQL> select name, addr from customer1

union

select name, addr from customer2;

NAME ADDR

---------- -----------------------------------

가길동 부산

나길동 서울

라길동 서울


union all


NAME ADDR

---------- -----------------------------------

나길동 서울

가길동 부산

라길동 서울

가길동 부산


intersect


NAME ADDR

---------- -----------------------------------

가길동 부산


minus


NAME ADDR

---------- -----------------------------------

나길동 서울

--아래 예문을 보면 결과 칼럼의 컬 럼명은 첫번째 Select문의 칼럼명이 사용됨을 알수있다.

SQL> select name as "이름1" , addr as "주소1" from customer1

minus

select name as "이름2" , addr as "주소2" from customer2;

이름1 주소1

---------- --------------------------------------------------

나길동 서울

#7.SQL초보에서 Schema...

Education

Transcript of #7.SQL초보에서 Schema...