오라클, SQL, ANY(SOME), ALL, EXISTS

오라클, SQL, ANY(SOME), ALL, EXISTS

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=605

 

오라클, SQL, ANY(SOME), ALL, EXISTS

오라클, SQL, ANY(SOME), ALL, EXISTS3.2.7 ANY(SOME), ALL, EXISTS ANY(SOME) : 주로 서브 쿼리와 함께 사용되며 서브쿼리에서 여러 개의 값이 나오게 되는 경우 이 값들에 대해 어떤 한 값 보다만 어떻다라고 비교할 때 사용된다. sal > any (500, 600, 700)와 같은 구문을 보면 sal &…

ojc.asia

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 SQL> SELECT ename, sal FROM emp WHERE sal > 950 ORDER BY SAL; ENAME SAL ---------- ---------- ADAMS 1100 …… KING 5000 -- SOME은 ANY와 동일하다 SQL> SELECT ename, sal FROM emp WHERE sal > some (SELECT sal FROM emp WHERE deptno = 30) ORDER BY SAL; ENAME SAL ---------- ---------- ADAMS 1100 …… KING 5000 -- 전부를 만족시키기 위해서는 30번부서의 최고 급여인 2850보가 커야 한다. sal > 2850의 의미 SQL> SELECT ename, sal FROM emp WHERE sal > all (SELECT sal FROM emp WHERE deptno = 30) ORDER BY SAL; ENAME SAL ---------- ---------- JONES 2975 SCOTT 3000 FORD 3000 KING 5000 SQL> SELECT ename, sal FROM emp 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)에서 사원을 한명이라도 가지고 있는 부서명, 부서코드를 출력하시오. SQL> SELECT dname as "부서명", deptno as "부서코드" FROM dept WHERE EXISTS( SELECT * FROM emp WHERE dept.deptno = emp.deptno); 부서명 부서코드 -------------- ---------- ACCOUNTING 10 RESEARCH 20 SALES 30 -- 위 쿼리 구문을 IN연산자를 이용하면 다음과 같다. (IN은 OR로 해석된다.) -- 대부분 IN을 이용하는 것보다 EXISTS를 사용하는 것이 쿼리 성능면에서 장점을 가진다. SQL> SELECT dname as "부서명", deptno as "부서코드" 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 ------------------ 안드로이드과정