#title SQL 기본 [[TableOfContents]] ==== SQL과의 첫만남 ==== 오라클에서 SCOTT/TIGER로 접속하여 자신의 소유 테이블 조회 {{{#!geshi plsql SQL> -- 아이디가 SCOTT이고 패스워드가 TIGER인 사용자로 접속 SQL> CONNECT SCOTT/TIGER 연결되었습니다. SQL> -- 현재 어떤 사용자로 접속하여 있는가? SQL> SHOW USER USER은 "SCOTT"입니다 SQL> -- SCOTT 소유의 모든 테이블을 조회 SQL> SELECT * FROM TAB; --> SCOTT 소유의 모든 테이블을 조회 TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- BONUS TABLE DEPT TABLE EMP TABLE MICROSOFTDTPROPERTIES TABLE SALGRADE TABLE SQL> -- EMP(사원)테이블의 모든 것을 조회하는 것.. SQL> SELECT * FROM EMP; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 80/12/17 800 20 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 7521 WARD SALESMAN 7698 81/02/22 1250 500 30 7566 JONES MANAGER 7839 81/04/02 2975 20 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 7698 BLAKE MANAGER 7839 81/05/01 2850 30 7782 CLARK MANAGER 7839 81/06/09 2450 10 7788 SCOTT ANALYST 7566 87/04/19 3000 20 7839 KING PRESIDENT 81/11/17 5000 10 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 7876 ADAMS CLERK 7788 87/05/23 1100 20 7900 JAMES CLERK 7698 81/12/03 950 30 7902 FORD ANALYST 7566 81/12/03 3000 20 7934 MILLER CLERK 7782 82/01/23 1300 10 14 개의 행이 선택되었습니다. SQL> SELECT * FROM DEPT; -----> DEPT(부서)테이블의 모든 데이타를 조회.. DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON }}} 우리는 위에서 SELECT * FROM EMP; 라고 했습니다.이것은 'SQL이란 무엇인가'에서도 살펴보았듯이 사용자가 필요한 정보가 무엇인가를 명시해준 것 뿐입니다(나중에 나올 수행경로까지 결정..) 나머지는 오라클에서 알아서 하는 것입니다. SQL도 언어이기 때문에 문법을 가지고 있습니다. 문법을 살펴봅시다. SELECT문의 기본구조는 다음과 같습니다. {{{#!geshi plsql SELECT COLUMN_NAME1, COLUMN_NAME2.... FROM TABLE_NAME }}} ==== SELECT 절에서의 연산 ==== FROM절 다음에는 테이블명을 명시해 줍니다. 즉, 내가 필요로 하는 정보가 어떤 테이블에 있는지를 명시해 주는 것입니다.SELECT절 다음에는 컬럼명을 명시해줍니다.물론 SELECT절에 서 여러가지 함수를 사용해서 데이타를 조작(사칙연산등)한다거나 할 수도 있습니다.한번 해보겠습니다. '''SELECT 절에서의 숫자 연산''' {{{#!geshi plsql SQL> SELECT 1+1 FROM DUAL; 1+1 ---------- 2 SQL> SELECT 'A' || 'B' FROM DUAL; 'A -- AB SQL> SELECT 'A' + 'B' FROM DUAL; --> 주의 깊게 살펴 봅니다. SELECT 'A' + 'B' FROM DUAL * 1행에 오류: ORA-01722: 수치가 부적합합니다 SQL> SELECT '1' + 1 FROM DUAL; '1'+1 ---------- 2 SQL> SELECT '1' + '1' FROM DUAL; '1'+'1' ---------- 2 SQL> SELECT '1' || '1' FROM DUAL; '1 -- 11 SQL> -- 나머지는 일반 프로그래밍과 같다. 단지 나누기 연산의 결과가 나머지로 표현되는 SQL> -- 것만보자. SQL> SELECT MOD(3,2) FROM DUAL; MOD(3,2) ---------- 1 }}} '''참고'''' MSSQL SERVER에서는 문자와 숫자의 더하기 연산을 모두 "+" 기호를 사용합니다. 또한, FROM 절을 빼도 간단한 연산 같은 것을 하는 것은 할 수 있습니다. 또한 '1' + '1'을 문자 더하기 연산을 수행하여 '11' 의 결과가 나왔습니다. {{{#!geshi plsql SELECT '1' + '1' ---- 11 (1개 행 적용됨) SELECT 3%2 AS '나머지' 나머지 ----------- 1 (1개 행 적용됨) }}} ==== SELECT 기초1 ==== 처음 보시는 분들은 DUAL이란 테이블이 도대체 어디에 있는가 모를 것입니다.DUAL이란 테이블은 한개의 행과 한개의 컬럼을 갖는 오라클에서 만들어논 특수한 테이블입니다. 오라클은 숫자연산을 할 때 '+'로 하고 문자 연산을 할때는 '||'로 합니다.우리는 SELECT절이 단순히 컬럼을 나열하는 것이 아니라는 것을 알 수 있습니다. 나중에 나오겠지만 여러 가지 함수도 올수 있고, 또한 SELECT 절을 중첩하여 포함할 수 있습니다. 지금은 개념을 익히는 단계이므로 복잡한 것은 나중에 해보도록 하겠습니다. 독자분들이 주의 깊게 살펴볼 것은 '1' 과 1의 차이입니다. '1'는 숫자형 문자를 나타내고,1은 숫자를 나타내는 것입니다. MSSQL SERVER는 '1' + '1' = 2 를 지원하지 않습니다. 즉, 아무리 숫자형 문자라도 "숫자형 문자 + 숫자형 문자" 는 문자로 인식한다는 것입니다. 그렇다면 1 + '1'은 어떤 것일까요? 분명한 것은 숫자 + 문자의 형태라는 것입니다. 그런데 결과는 2 입니다. 결과로 볼 때 분명히 '1' 이 1로 바뀌었다는 것을 알 수 있습니다. 즉, 숫자연산을 할 때는 숫자형 문자를 숫자로 자동형 변환이 되는 것입니다. 간단히 생각해봅시다.아까 FROM 절에 테이블명을 명시해 준다고 했습니다. 이것을 집합으로 생각해야 합니다. 테이블이라는 2차원 공간을 하나의 집합이라고 생각하고 위에서 본 EMP테이블의 EMPNO, ENAME만을 조회한다고 생각해 봅시다.즉, 사원테이블에서 사번과, 사원이름을 조회하라라는 연산을 생각하자 이거지요. 먼저 어떻게 해야 할까요? 먼저 테이블에 접근해야 합니다. {{{#!geshi plsql EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 80/12/17 800 20 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 7521 WARD SALESMAN 7698 81/02/22 1250 500 30 7566 JONES MANAGER 7839 81/04/02 2975 20 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 7698 BLAKE MANAGER 7839 81/05/01 2850 30 7782 CLARK MANAGER 7839 81/06/09 2450 10 7788 SCOTT ANALYST 7566 87/04/19 3000 20 7839 KING PRESIDENT 81/11/17 5000 10 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 7876 ADAMS CLERK 7788 87/05/23 1100 20 7900 JAMES CLERK 7698 81/12/03 950 30 7902 FORD ANALYST 7566 81/12/03 3000 20 7934 MILLER CLERK 7782 82/01/23 1300 10 }}} 두번째로 가져올 EMPNO, ENAME만을 추려서 가져와야 합니다. {{{#!geshi plsql EMPNO ENAME ---------- ---------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER }}} 집합적인 사고방식을 길러야 합니다. EMP테이블 전체도 하나의 집합이고 바로 위에 있는 사번, 사원이름도 하나의 집합니다. 결과적으로 접근 순서는 FROM -> SELECT 입니다.먼저 테이블에 접근해서 SELECT 절에 나열된 컬럼을 가져오는 것입니다... {{{#!geshi plsql SQL> SELECT EMPNO, ENAME FROM EMP; EMPNO ENAME ---------- ---------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER }}} 사실 행의 수가 14개 밖에 안되기 때문에 이런식의 조회도 정보가 될 수 있습니다.만약 이 테이블을 가지고 있는 어떤 기업의 사원수가 만명이 넘는다면 이런식의 출력가지고는 정보가될 수 없음이지요.즉, 만명중에 사원이름이 SMITH인 사람을 찾는 다는 것은 참으로 어려운 일입니다. 물론 이름이 같은 사람이 있을 수도 있습니다. 그래서 PRIMARY KEY가 존재하고, 조회를 할때 조건을 걸어주는 WHERE절이 존재하는 것입니다. {{{#!geshi plsql SELECT COLUMN_NAME1, COLUMN_NAME2.... FROM TABLE_NAME WHERE CONDITION..... }}} 만약 사원중 월급(SAL)을 1500 이상 받는 사람을 조회하고 싶다면 어떻게 해야 할까요? 먼저 EMP테이블(전체집합)에 접근해야 합니다. {{{#!geshi plsql EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 80/12/17 800 20 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 7521 WARD SALESMAN 7698 81/02/22 1250 500 30 7566 JONES MANAGER 7839 81/04/02 2975 20 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 7698 BLAKE MANAGER 7839 81/05/01 2850 30 7782 CLARK MANAGER 7839 81/06/09 2450 10 7788 SCOTT ANALYST 7566 87/04/19 3000 20 7839 KING PRESIDENT 81/11/17 5000 10 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 7876 ADAMS CLERK 7788 87/05/23 1100 20 7900 JAMES CLERK 7698 81/12/03 950 30 7902 FORD ANALYST 7566 81/12/03 3000 20 7934 MILLER CLERK 7782 82/01/23 1300 10 }}} 다음은 월급이 1500 이상이 사람이라는 조건에 맞는 집합을 찾아야 합니다. (필자는 분명히 말하지만 조건에 맞는 행을 찾아 내려가는 것이라고 생각해서는 안 됩니다.) {{{#!geshi plsql EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 80/12/17 800 20 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 <-- 1 7521 WARD SALESMAN 7698 81/02/22 1250 500 30 7566 JONES MANAGER 7839 81/04/02 2975 20 <-- 2 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 7698 BLAKE MANAGER 7839 81/05/01 2850 30 <-- 3 7782 CLARK MANAGER 7839 81/06/09 2450 10 <-- 4 7788 SCOTT ANALYST 7566 87/04/19 3000 20 <-- 5 7839 KING PRESIDENT 81/11/17 5000 10 <-- 6 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 <-- 7 7876 ADAMS CLERK 7788 87/05/23 1100 20 7900 JAMES CLERK 7698 81/12/03 950 30 7902 FORD ANALYST 7566 81/12/03 3000 20 <-- 8 7934 MILLER CLERK 7782 82/01/23 1300 10 }}} 결과는 다음과 같습니다. {{{#!geshi plsql SQL> SELECT * FROM EMP 2 WHERE SAL >= 1500; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 7566 JONES MANAGER 7839 81/04/02 2975 20 7698 BLAKE MANAGER 7839 81/05/01 2850 30 7782 CLARK MANAGER 7839 81/06/09 2450 10 7788 SCOTT ANALYST 7566 87/04/19 3000 20 7839 KING PRESIDENT 81/11/17 5000 10 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 7902 FORD ANALYST 7566 81/12/03 3000 20 8 개의 행이 선택되었습니다. }}} 이것은 행단위로 처리되는 것이 아닙니다. 하나의 집합 단위로 처리되는 것입니다. 한 행씩 내려가면서 SAL >= 1500의 조건을 만족하는 행을 걸러내는 것이 아니라 한번에 걸러내는 것입니다. 조금의 의아해 하시는 분이 있을 것입니다. 어떻게 한번에(집합단위)로 처리를 한단 말인가?? 라는 생각을 가지신 분은 사고구조의 전환이 필요합니다. 앞에서 조, 쌀, 콩을 걸러 내기 위해서 채를 사용한다는 이야기를 했었습니다. 만약 콩만 필요하다면 행단위의 처리는 콩을 하나씩 골라내는 일이 되는 것이고, 집합 단위로 처리하는 것은 채를 이용한 것이 되겠습니다. 이를 보면 일반적으로 행단위 처리와 집합 단위 처리가 어떤 성능차를 가지고 있는지 아실겁니다. 만약 이해가 안가신다면 그 동안 절차적인 언어에 익숙해져서 절차적으로 생각을 할수 밖에 없다는 것입니다. 나중에 전체적으로 본다면 상당히 쉬울지도 모르겠습니다. 집합적인 사고 구조란 말뜻을 모르시더라도 실망을 하지 마세요. 지금은 첫 단계니까요. 일단 SQL을 다루려면 사고의 전환이 필요하며 절차적 요소는 나중에 생각하고, 기본적으로는 집합적인 사고방식을 가지려고 노력해야 합니다. 이제부터 SELECT ~ FROM ~ WHERE ~ 의 이렇게 3개의 절을 가지고 한참동안 실습을 해보도록 하겠습니다. ||EMP 테이블은 사원에 관한 것이고 DEPT 테이블은 부서에 관련된 것입니다. 사원 테이블을 보면, 사원번호, 이름, 직무, 상사직원, 입사일, 월급, 커미션, 부서번호 순으로 되어 있습니다. 부서는 부서번호, 부서이름, 위치입니다.||| ==== SELECT 기초2 ==== {{{#!geshi plsql SQL> -- 모든 사원의 사원번호와 이름을 조회 SQL> SELECT EMPNO, ENAME 2 FROM EMP; EMPNO ENAME ---------- ---------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER 14 개의 행이 선택되었습니다. SQL> -- 직무가 CLERK 인 사원번호 조회 SQL> SELECT EMPNO 2 FROM EMP 3 WHERE JOB = 'CLERK'; EMPNO ---------- 7369 7876 7900 7934 SQL> -- 컬럼을 합친것 (4장의 복합속성에서 참고할 것) SQL> SELECT EMPNO || ' ' || ENAME || ' ' || JOB 2 FROM EMP; EMPNO||''||ENAME||''||JOB ------------------------------------------------------------- 7369 SMITH CLERK 7499 ALLEN SALESMAN 7521 WARD SALESMAN 7566 JONES MANAGER 7654 MARTIN SALESMAN 7698 BLAKE MANAGER 7782 CLARK MANAGER 7788 SCOTT ANALYST 7839 KING PRESIDENT 7844 TURNER SALESMAN 7876 ADAMS CLERK 7900 JAMES CLERK 7902 FORD ANALYST 7934 MILLER CLERK 1111 MILLER CLE%RK 15 개의 행이 선택되었습니다. SQL> -- 오라클 SQL에서는 기본적으로 대소문자 구분을 하지는 않지만 SQL> -- 저장된 것을 기준으로 하기 때문에 WHERE 절에 조건을 기술할 때는 대소문자 구분이 SQL> -- 필요하다. SQL> SELECT EMPNO 2 FROM EMP 3 WHERE JOB = 'CLERK'; 선택된 레코드가 없습니다. SQL> -- 월급이 3000 이상인 사원의 이름을 조회 SQL> SELECT ENAME 2 FROM EMP 3 WHERE SAL >= 3000; ENAME ---------- SCOTT KING FORD SQL> -- 사원번호가 7698인 사원의 부하직원의 이름과 직무를 조회 SQL> SELECT ENAME, JOB 2 FROM EMP 3 WHERE MGR = 7698; --> 숫자형임을 알 수 있다. ENAME JOB ---------- --------- ALLEN SALESMAN WARD SALESMAN MARTIN SALESMAN TURNER SALESMAN JAMES CLERK SQL> -- 사원번호는 오른쪽으로 치우쳐 나오고 이름은 왼쪽에 치우쳐 나오는 것을 볼 수 있다. SQL> -- 이것은 사원번호가 숫자형이고, 이름은 문자형이라는 것을 의미한다. SQL> -- 숫자형 문자는 왼쪽으로 치우쳐서 결과가 나온다. SQL> -- 사원번호가 7698인 사원의 부하직원중 월급이 1500 이하인 직원의 사항 모두 조회 SQL> SELECT * 2 FROM EMP 3 WHERE MGR = 7698 AND SAL <= 1500; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7521 WARD SALESMAN 7698 81/02/22 1250 500 30 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 7900 JAMES CLERK 7698 81/12/03 950 30 SQL> -- 부서번호가 20이거나 월급이 3000이상인 사원의 모든 정보를 조회 SQL> SELECT * 2 FROM EMP 3 WHERE DEPTNO = 20 OR SAL >= 3000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 80/12/17 800 20 7566 JONES MANAGER 7839 81/04/02 2975 20 7788 SCOTT ANALYST 7566 87/04/19 3000 20 7839 KING PRESIDENT 81/11/17 5000 10 7876 ADAMS CLERK 7788 87/05/23 1100 20 7902 FORD ANALYST 7566 81/12/03 3000 20 6 개의 행이 선택되었습니다. SQL> -- 직무가 ANALYST이고, SQL> -- 부서번호가 20이거나 월급이 3000이상인 사원의 모든 정보를 조회 SQL> -- 다음은 틀렸다. SQL> SELECT * 2 FROM EMP 3 WHERE JOB = 'ANALYST' 4 AND DEPTNO = 20 OR SAL >= 3000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 87/04/19 3000 20 7839 KING PRESIDENT 81/11/17 5000 10 7902 FORD ANALYST 7566 81/12/03 3000 20 SQL> -- 이것도 틀렸다. SQL> SELECT * 2 FROM EMP 3 WHERE DEPTNO = 20 OR SAL >= 3000 4 AND JOB = 'ANALYST'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 80/12/17 800 20 7566 JONES MANAGER 7839 81/04/02 2975 20 7788 SCOTT ANALYST 7566 87/04/19 3000 20 7876 ADAMS CLERK 7788 87/05/23 1100 20 7902 FORD ANALYST 7566 81/12/03 3000 20 SQL> -- 맞는 것 !!!! SQL> SELECT * 2 FROM EMP 3 WHERE JOB = 'ANALYST' 4 AND (DEPTNO = 20 OR SAL >= 3000); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 87/04/19 3000 20 7902 FORD ANALYST 7566 81/12/03 3000 20 SQL> -- AND와 OR중 어떤 것이 우선순위가 높을까? 예에서 보듯이 AND가 우선순위가 높다. SQL> SQL> -- 중복행 제거 연산자 DISTINCT SQL> SELECT JOB FROM EMP; JOB --------- CLERK SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK 14 개의 행이 선택되었습니다. SQL> SELECT DISTINCT JOB, ENAME FROM EMP; JOB ENAME --------- ---------- ANALYST FORD ANALYST SCOTT CLERK ADAMS CLERK JAMES CLERK MILLER CLERK SMITH MANAGER BLAKE MANAGER CLARK MANAGER JONES PRESIDENT KING SALESMAN ALLEN SALESMAN MARTIN SALESMAN TURNER SALESMAN WARD 14 개의 행이 선택되었습니다. SQL> -- ENAME이 모두 틀리므로 JOB으로 정렬은 되지만 JOB에 대한 중복제거는 하지 못했다. SQL> }}} 이제 감이 좀 잡히시나요? 앞서서 우리는 EMP 테이블이라는 전체집합에서 WHERE 절에 조건을 기술해 줌으로 해서 우리가 원하는 집합을 가져올 수 있었습니다. 이렇게 WHERE 절은 실제로 사용자가 원하는 집합을 만들어 내기 위한 직접적인 관련을 맺고 있습니다. 이제 약간 특수(?)한 연산자에 대해서 살펴보겠습니다. 여러분이 도서관에서 책을 찾으려고 '데이타베이스'라고 도서 검색을 하실겁니다. 그러나 책이 모두 '데이타베이스'라고 나오는 것은 아니죠. 보통은 '데이타베이스 시스템'이나 '데이타베이스 관리' 또는 '데이타베이스론' 이라고 나옵니다. 이럴 때는 어떤식으로 검색이 이루어 질까요? 또한 서점을 가보면 앞에서 시작하는 글자, 중간글자, 끝글자 하고 나누어 검색하는 모습도 보일겁니다. 즉, 패턴매칭 검색을 하는 것입니다. 이럴 때는 'LIKE' 연산자를 사용합니다. 원래 영어 뜻은 좋아하다, 바라다, 닮은꼴, ~처럼 등의 뜻이 있습니다. SQL에서는 LIKE는 '닮은꼴'의 뜻이 되겠지요. 한번 해 볼까요? ==== LIKE 연산자를 이용한 패턴매칭 검색 ==== {{{#!geshi plsql SQL> SELECT * FROM EMP 2 WHERE JOB LIKE 'PRE%'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7839 KING PRESIDENT 81/11/17 5000 10 SQL> SELECT * FROM EMP 2 WHERE JOB LIKE '%ENT'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7839 KING PRESIDENT 81/11/17 5000 10 SQL> -- 저장된 데이타를 기준으로 하기 때문에 대소문자 구분 SQL> SELECT * FROM EMP 2 WHERE JOB LIKE 'PRE%'; 선택된 레코드가 없습니다. SQL> SELECT * FROM EMP 2 WHERE JOB LIKE '%SI%'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7839 KING PRESIDENT 81/11/17 5000 10 SQL> SELECT * FROM EMP 2 WHERE JOB LIKE '%E%'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 80/12/17 800 20 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 7521 WARD SALESMAN 7698 81/02/22 1250 500 30 7566 JONES MANAGER 7839 81/04/02 2975 20 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 7698 BLAKE MANAGER 7839 81/05/01 2850 30 7782 CLARK MANAGER 7839 81/06/09 2450 10 7839 KING PRESIDENT 81/11/17 5000 10 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 7876 ADAMS CLERK 7788 87/05/23 1100 20 7900 JAMES CLERK 7698 81/12/03 950 30 7934 MILLER CLERK 7782 82/01/23 1300 10 12 개의 행이 선택되었습니다. SQL> SELECT * FROM EMP 2 WHERE JOB LIKE '_RESIDENT'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7839 KING PRESIDENT 81/11/17 5000 10 SQL> SELECT * FROM EMP 2 WHERE JOB LIKE '_A%'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 7521 WARD SALESMAN 7698 81/02/22 1250 500 30 7566 JONES MANAGER 7839 81/04/02 2975 20 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 7698 BLAKE MANAGER 7839 81/05/01 2850 30 7782 CLARK MANAGER 7839 81/06/09 2450 10 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 7 개의 행이 선택되었습니다. SQL> -- 날짜도 패턴매칭 검색이 된다. SQL> SELECT * FROM EMP 2 WHERE HIREDATE LIKE '81%'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 7521 WARD SALESMAN 7698 81/02/22 1250 500 30 7566 JONES MANAGER 7839 81/04/02 2975 20 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 7698 BLAKE MANAGER 7839 81/05/01 2850 30 7782 CLARK MANAGER 7839 81/06/09 2450 10 7839 KING PRESIDENT 81/11/17 5000 10 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 7900 JAMES CLERK 7698 81/12/03 950 30 7902 FORD ANALYST 7566 81/12/03 3000 20 10 개의 행이 선택되었습니다. SQL> -- NOT 연산자 이용 SQL> SELECT * FROM EMP 2 WHERE HIREDATE NOT LIKE '81%'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 80/12/17 800 20 7788 SCOTT ANALYST 7566 87/04/19 3000 20 7876 ADAMS CLERK 7788 87/05/23 1100 20 7934 MILLER CLERK 7782 82/01/23 1300 10 1111 MILLER CLE%RK 7782 82/01/23 1300 10 }}} '%'는 우리가 파일이나 폴더를 검색할 때 쓰이는 '*'기호와 같은 의미를 가집니다. 즉, 제일 처음 예에서 보는 바와 같이 앞에 JOB에서 'PRE'가 들어간 모든 사원정보를 조회하는 것이 됩니다. 또한 '_'는 한 글자만을 의미합니다. 즉, '_RESIDENT' 의미는 첫글자가 어떤 것이든 패턴이 매칭되는 것은 모두 가져오라는 뜻이 되는 것입니다. 마지막의 예에서 보는 바와 같이 날짜 또한 패턴매칭 검색이 됩니다. 여기서 '%' 나 '_'를 와일드 카드 문자라고 합니다. {{{#!geshi plsql SQL> -- 만약 '%' 기호가 들어간 문자열에 대한 패턴매칭 검색을 하려면? SQL> INSERT INTO EMP VALUES 2 (1111,'MILLER','CLE%RK',7782,TO_DATE('23-1-1982','DD-MM-YYYY'),1300,NULL,10); 1 개의 행이 만들어졌습니다. SQL> SELECT ENAME, JOB FROM EMP 2 WHERE EMPNO = 1111; ENAME JOB ---------- --------- MILLER CLE%RK SQL> -- 찾을 문자에 '\'를 붙이고 '\' 다음에 오는 문자는 '%'가 연산자가 아님을 표시 SQL> -- 꼭 '\' 문자가 아니어도 된다. SQL> SELECT ENAME, JOB 2 FROM EMP 3 WHERE JOB LIKE '%\%%' ESCAPE '\'; ENAME JOB ---------- --------- MILLER CLE%RK SQL> SELECT ENAME, JOB 2 FROM EMP 3 WHERE JOB LIKE '%/%%' ESCAPE '/'; ENAME JOB ---------- --------- MILLER CLE%RK SQL> }}} ==== BETWEEN 연산자를 이용한 검색 ==== {{{#!geshi plsql SQL> -- BETWEEN을 이용한 81년 05월 01일 ~ 81년 12월 31일 사이에 입사한 사원을 조회 SQL> SELECT EMPNO, ENAME, HIREDATE 2 FROM EMP 3 WHERE HIREDATE BETWEEN '81/05/01' AND '81/12/31'; EMPNO ENAME HIREDATE ---------- ---------- -------- 7654 MARTIN 81/09/28 7698 BLAKE 81/05/01 7782 CLARK 81/06/09 7839 KING 81/11/17 7844 TURNER 81/09/08 7900 JAMES 81/12/03 7902 FORD 81/12/03 7 개의 행이 선택되었습니다. SQL> -- BETWEEN 연산자는 내부적으로 AND 연산자로 바뀌어 수행... SQL> -- BETWEEN 연산자가 AND 연산자로 바뀌는 시간은 아주아주 짧음..무시해도 될 정도... SQL> -- 그러나 조금이라도 빠른 성능을 가지게 하고 싶다면 BETWEEN 대신에 AND를 사용... SQL> -- AND 연산자를 이용한 81년 05월 01일 ~ 81년 12월 31일 사이에 입사한 사원을 조회 SQL> SELECT EMPNO, ENAME, HIREDATE 2 FROM EMP 3 WHERE HIREDATE >= '81/05/01' AND HIREDATE <= '81/12/31'; EMPNO ENAME HIREDATE ---------- ---------- -------- 7654 MARTIN 81/09/28 7698 BLAKE 81/05/01 7782 CLARK 81/06/09 7839 KING 81/11/17 7844 TURNER 81/09/08 7900 JAMES 81/12/03 7902 FORD 81/12/03 7 개의 행이 선택되었습니다. SQL> -- NOT BETWEEN 의 이용 SQL> SELECT EMPNO, ENAME, HIREDATE 2 FROM EMP 3 WHERE HIREDATE NOT BETWEEN '81/05/01' AND '81/12/31'; EMPNO ENAME HIREDATE ---------- ---------- -------- 7369 SMITH 80/12/17 7499 ALLEN 81/02/20 7521 WARD 81/02/22 7566 JONES 81/04/02 7788 SCOTT 87/04/19 7876 ADAMS 87/05/23 7934 MILLER 82/01/23 1111 MILLER 82/01/23 8 개의 행이 선택되었습니다. }}} ==== 한글의 범위 연산 ==== 아래의 스크립트를 복사해서 SQL*PLUS에서 실행합니다. {{{#!geshi plsql DROP TABLE TEST; CREATE TABLE TEST( NAME VARCHAR(6)); INSERT INTO TEST VALUES ('ㄱ'); INSERT INTO TEST VALUES ('가'); INSERT INTO TEST VALUES ('갸'); INSERT INTO TEST VALUES ('기'); INSERT INTO TEST VALUES ('너'); INSERT INTO TEST VALUES ('ㄴ'); INSERT INTO TEST VALUES ('다냐?'); INSERT INTO TEST VALUES ('머냐?'); INSERT INTO TEST VALUES ('더다'); INSERT INTO TEST VALUES ('랄라라'); SQL> -- 오라클에서... SQL> SELECT * FROM TEST 2 WHERE NAME BETWEEN 'ㄱ' AND 'ㄹ'; NAME ------ ㄱ ㄴ SQL> SELECT * FROM TEST 2 WHERE NAME BETWEEN '가' AND '라'; NAME ------ 가 갸 기 너 다냐? 더다 6 개의 행이 선택되었습니다. -- MS-SQL SERVER 에서... SELECT * FROM TEST WHERE NAME BETWEEN 'ㄱ' AND 'ㄹ' NAME ------ ㄱ 가 갸 기 너 ㄴ 다냐? 더다 (8개 행 적용됨) SELECT * FROM TEST WHERE NAME BETWEEN '가' AND '라' NAME ------ 가 갸 기 너 ㄴ 다냐? 더다 (7개 행 적용됨) }}} ==== IN 연산자를 이용한 검색 ==== {{{#!geshi plsql SQL> -- IN 연산자는 몇개의 값중 명시한 컬럼과 일치하는 값이 있는 경우 검색 조건이 참 SQL> -- 사원번호가 7369, 7782, 7789 인 사원의 EMPNO, ENAME, JOB, SAL 인 사원 검색 SQL> SELECT EMPNO, ENAME, JOB, SAL 2 FROM EMP 3 WHERE EMPNO IN (7369, 7782, 7789); EMPNO ENAME JOB SAL ---------- ---------- --------- ---------- 7782 CLARK MANAGER 2450 7369 SMITH CLERK 800 SQL> -- IN 연산자 안에 명시한 값중 사원번호가 7789 인 사원은 없으므로 검색이 안됀다. SQL> -- OR 연산자를 이용한 검색 (바로 위의 것과 같다.) SQL> -- DBMS는 IN을 OR 연산자로 바꾸어 연산하지만 그 시간차는 순간이다. SQL> SELECT EMPNO, ENAME, JOB, SAL 2 FROM EMP 3 WHERE EMPNO = 7369 4 OR EMPNO = 7782 5 OR EMPNO = 7789; EMPNO ENAME JOB SAL ---------- ---------- --------- ---------- 7782 CLARK MANAGER 2450 7369 SMITH CLERK 800 SQL> -- 부서번호가 20, 30 인 사원의 EMPNO, ENAME, JOB, DEPTNO를 조회 SQL> SELECT EMPNO, ENAME, JOB, DEPTNO 2 FROM EMP 3 WHERE DEPTNO NOT IN (20, 30); EMPNO ENAME JOB DEPTNO ---------- ---------- --------- ---------- 7782 CLARK MANAGER 10 7839 KING PRESIDENT 10 7934 MILLER CLERK 10 1111 MILLER CLE%RK 10 }}} ==== IS NULL 의 사용 ==== 관계형 데이타베이스는 널(NULL) 값을 분명히 명시하고 있습니다. 널값은 '값은 있기는 있으나 알지 못하는 미지수'라는 의미를 가집니다. 우리가 수학에서 무한대 * 1000 의 연산을 하면 무한대가 나오 듯이 NULL * 1000을 하면 NULL값이 나옵니다. 보통의 DBMS에서는 값을 삽 입하지 않으면 그 값을 NULL 값으로 받습니다. 입력을 하지 않았다는 것은 0(ZERO)의 의미도 아니고, ''(SPACE)의 의미도 아닌 것입니다. 만약 학생(학번, 이름, 전화번호)의 논리적스키마를 가진 릴레이션을 DBMS에 적용하여 테이블로 만들었는데 9555023(학번)과 이재학(이름)만 삽입했다면 이것의 의미는 공백인가요? 아니면 0 일까요? 이것은 '값은 있으나 알지 못하는 값' 이 되는 것입니다. 값을 삽입하지 않았다고 하여 지금 입력한 학생이 전화번호가 없는 것일까요? 그것은 아닐 것입니다. 우리가 웹사이트에 들어가서 회원가입을 할 때 생각을 해보면 필수 사항과 선택사항이 있는 것을 보았을 것입니다. 선택사항의 경우 없어서 입력하지 않는 것은 아닐 것입니다. 만약 회원 가입하는 폼에 선택사항으로 주소가 있다면 주소에 대한 사항을 넣지 않았다고 해서 주소값이 0이거나 또는 공백일까요? 아닙니다. 널 값이 갖는 의미 즉, '값이 있으나 알지 못하는 값'이 되는 것입니다. 널값의 의미를 잘 세겨 두도록 하시길 바랍니다. {{{#!geshi plsql SQL> SELECT EMPNO, ENAME, JOB 2 FROM EMP 3 WHERE COMM IS NULL; EMPNO ENAME JOB ---------- ---------- --------- 7369 SMITH CLERK 7566 JONES MANAGER 7698 BLAKE MANAGER 7782 CLARK MANAGER 7788 SCOTT ANALYST 7839 KING PRESIDENT 7876 ADAMS CLERK 7900 JAMES CLERK 7902 FORD ANALYST 7934 MILLER CLERK 1111 MILLER CLE%RK 11 개의 행이 선택되었습니다. SQL> -- COMM = NULL 이라고 해서는 NULL값에 대한 검색이 이루어 지지 않는다. SQL> -- '='은 정확하게 정해진 값에 대한 비교 연산자 이다. SQL> SELECT EMPNO, ENAME, JOB 2 FROM EMP 3 WHERE COMM = NULL; 선택된 레코드가 없습니다. SQL> -- NULL은 공백이 아니다. SQL> SELECT EMPNO, ENAME, JOB 2 FROM EMP 3 WHERE COMM = ''; 선택된 레코드가 없습니다. SQL> -- NULL은 0 이 아니므로 COMM = 0 인 사원만 검색되었다. SQL> SELECT EMPNO, ENAME, JOB 2 FROM EMP 3 WHERE COMM = 0; EMPNO ENAME JOB ---------- ---------- --------- 7844 TURNER SALESMAN SQL> COMM = NULL 은 안됀다. 즉, COMM IS NULL이 되어야 한다. SQL> SELECT EMPNO, ENAME, COMM 2 FROM EMP 3 WHERE COMM IN (NULL, 1400); EMPNO ENAME COMM ---------- ---------- ---------- 7654 MARTIN 1400 SQL> -- 위의 것을 다시 풀어서 쓰면.. SQL> SELECT EMPNO, ENAME, COMM 2 FROM EMP 3 WHERE COMM = NULL 4 OR COMM = 1400; EMPNO ENAME COMM ---------- ---------- ---------- 7654 MARTIN 1400 SQL> SELECT EMPNO, ENAME, COMM 2 FROM EMP 3 WHERE COMM NOT IN (NULL, 1400); 선택된 레코드가 없습니다. SQL> -- 풀어서 쓰면 다음과 같다. SQL> SELECT EMPNO, ENAME, COMM 2 FROM EMP 3 WHERE COMM <> NULL 4 AND COMM <> 1400; --> OR가 아니라 AND가 된다. 선택된 레코드가 없습니다. SQL> -- 이렇게 원치 않는 결과를 얻은 사람이 실제로 원하는 결과는 다음과 같을 것이다. SQL> SELECT EMPNO, ENAME, COMM 2 FROM EMP 3 WHERE COMM IS NOT NULL 4 AND COMM <> 1400; EMPNO ENAME COMM ---------- ---------- ---------- 7499 ALLEN 300 7521 WARD 500 7844 TURNER 0 }}} 위에서 IN 연산자는 OR로 바뀐다고 했는데 OR에 NOT연산자가 붙었다. 즉, {{{#!geshi plsql --------------- A B 결과 --------------- 0 0 0 0 1 1 1 0 1 1 1 1 }}} 그러나 위에서는 AND 연산자를 사용한 것이 보인다. 이를 위의 표와 아래의 표와 비교해 보겠습니다. {{{ --------------------- A B 결과 NOT --------------------- 0 0 0 1 0 1 0 1 1 0 0 1 1 1 1 0 }}} 그러나 각각의 A와 B에 NOT을 취하면 1은 0이 되고, 0은 1이 됩니다.. {{{ --------------- - - A B 결과 --------------- 1 1 1 --> A = 0 이고 NOT A = 1, B = 0 이고 NOT B = 1 1 0 1 0 1 1 0 0 0 }}} 즉, OR가 아니라 AND의 결과에 NOT을 취한 값이 되었습니다. === 결과의 정렬 === 정보시스템 구축은 시스템의 사용자에게 유용한 정보를 제공하기 위함입니다. 만약 학생 성적에 따른 등수를 매겨논 데이타가 저장된 정보시스템에서 등수를 나타내기 위해서 학생 성적에 대한 검색을 하였을 때 단지 입력된 순서대로 검색결과 나온다면 이것은 사용자에게 별로 유용한 정보가 되지 못할 수가 있습니다. 이러한 것을 감안하여 모든 DBMS에는 정렬기능이 모두 구현되어 있고, 표준 SQL에도 명시되어 있는 사항입니다. 정렬에 대한 키워드는 ORDER BY입니다. ORDER BY는 가장 나중에 실행됩니다. 또한 정렬작업은 시스템이 하는 작업의 하나이므로(시스템이 할 일이 증가한다.) 필요한 경우에만 사용하는 것이 좋습니다. {{{#!geshi plsql SQL> -- 월급을 가장 적게 받는 사원 순으로 사원의 사원번호와 사원이름과 월급을 조회 SQL> SELECT EMPNO, ENAME, SAL 2 FROM EMP 3 ORDER BY SAL; EMPNO ENAME SAL ---------- ---------- ---------- 7369 SMITH 800 7900 JAMES 950 7876 ADAMS 1100 7521 WARD 1250 7654 MARTIN 1250 7934 MILLER 1300 1111 MILLER 1300 7844 TURNER 1500 7499 ALLEN 1600 7782 CLARK 2450 7698 BLAKE 2850 7566 JONES 2975 7788 SCOTT 3000 7902 FORD 3000 7839 KING 5000 15 개의 행이 선택되었습니다. SQL> -- 정렬을 위한 키워드는 ORDER BY 이다. SQL> -- ASC 는 내림차순 정렬이고, 기본값으로 명시해 주지 않아도 된다. SQL> SELECT EMPNO, ENAME, SAL 2 FROM EMP 3 ORDER BY SAL ASC; EMPNO ENAME SAL ---------- ---------- ---------- 7369 SMITH 800 7900 JAMES 950 7876 ADAMS 1100 7521 WARD 1250 7654 MARTIN 1250 7934 MILLER 1300 1111 MILLER 1300 7844 TURNER 1500 7499 ALLEN 1600 7782 CLARK 2450 7698 BLAKE 2850 7566 JONES 2975 7788 SCOTT 3000 7902 FORD 3000 7839 KING 5000 15 개의 행이 선택되었습니다. SQL> -- 월급을 가장 많이 받는 사원 순으로 사원의 사원번호와 사원이름과 월급을 조회 SQL> -- DESC는 오름차순 정렬 SQL> SELECT EMPNO, ENAME, SAL 2 FROM EMP 3 ORDER BY SAL DESC; EMPNO ENAME SAL ---------- ---------- ---------- 7839 KING 5000 7788 SCOTT 3000 <-- 값이 같다. 7902 FORD 3000 <-- 값이 같다. 7566 JONES 2975 7698 BLAKE 2850 7782 CLARK 2450 7499 ALLEN 1600 7844 TURNER 1500 7934 MILLER 1300 <-- 값이 같다. 1111 MILLER 1300 <-- 값이 같다. 7521 WARD 1250 <-- 값이 같다. 7654 MARTIN 1250 <-- 값이 같다. 7876 ADAMS 1100 7900 JAMES 950 7369 SMITH 800 15 개의 행이 선택되었습니다. SQL> -- 앞의 결과처럼 SAL이 같을 경우는 사원이름으로 결과를 정렬 SQL> SELECT EMPNO, ENAME, SAL 2 FROM EMP 3 ORDER BY SAL, ENAME; EMPNO ENAME SAL ---------- ---------- ---------- 7369 SMITH 800 7900 JAMES 950 7876 ADAMS 1100 7654 MARTIN 1250 7521 WARD 1250 7934 MILLER 1300 1111 MILLER 1300 7844 TURNER 1500 7499 ALLEN 1600 7782 CLARK 2450 7698 BLAKE 2850 7566 JONES 2975 7902 FORD 3000 7788 SCOTT 3000 7839 KING 5000 15 개의 행이 선택되었습니다. SQL> SELECT EMPNO, ENAME, SAL 2 FROM EMP 3 ORDER BY SAL DESC, ENAME ASC; EMPNO ENAME SAL ---------- ---------- ---------- 7839 KING 5000 7902 FORD 3000 7788 SCOTT 3000 7566 JONES 2975 7698 BLAKE 2850 7782 CLARK 2450 7499 ALLEN 1600 7844 TURNER 1500 7934 MILLER 1300 1111 MILLER 1300 7654 MARTIN 1250 7521 WARD 1250 7876 ADAMS 1100 7900 JAMES 950 7369 SMITH 800 15 개의 행이 선택되었습니다. SQL> -- 만약 SELECT 절에 나열된 순서대로 정렬을 한다고 하면 SQL> -- 다음과 같이 숫자로 명시해도 된다. SQL> SELECT EMPNO, ENAME, SAL 2 FROM EMP 3 ORDER BY 1,2; EMPNO ENAME SAL ---------- ---------- ---------- 1111 MILLER 1300 7369 SMITH 800 7499 ALLEN 1600 7521 WARD 1250 7566 JONES 2975 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 7876 ADAMS 1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 15 개의 행이 선택되었습니다. }}} ==== 집계함수의 사용과 GROUP BY ==== 이제 약간은 초보자들이 어렵게 느끼시는 집계함수와 GROUP BY에 대해서 알아보겠습니다. 집계함수는 어떤 범위에 대한 여러가지 연산을 수행하는 함수입니다. 다음을 그대로 따라해 보시기 바랍니다. {{{#!geshi plsql SQL> CREATE TABLE A_FN( 2 집계함수 VARCHAR(20) PRIMARY KEY, 3 설명 VARCHAR(30) ); 테이블이 생성되었습니다. SQL> INSERT INTO A_FN VALUES ('AVG(항목명)','평균단가'); 1 개의 행이 만들어졌습니다. SQL> INSERT INTO A_FN VALUES ('COUNT(항목명 또는 *)', '검색된 레코드의 총 수'); 1 개의 행이 만들어졌습니다. SQL> INSERT INTO A_FN VALUES ('MAX(항목명)', '최고값'); 1 개의 행이 만들어졌습니다. SQL> INSERT INTO A_FN VALUES ('MIN(항목명)', '최소값'); 1 개의 행이 만들어졌습니다. SQL> INSERT INTO A_FN VALUES ('SUM(항목명)', '합계'); 1 개의 행이 만들어졌습니다. SQL> COMMIT; 커밋이 완료되었습니다. SQL> SELECT * FROM A_FN 2 ORDER BY 집계함수; 집계함수 설명 -------------------- ------------------------------ AVG(항목명) 평균단가 COUNT(항목명 또는 *) 검색된 레코드의 총 수 MAX(항목명) 최고값 MIN(항목명) 최소값 SUM(항목명) 합계 SQL> }}} 위에서 설명해 놓은 것처럼 각 함수들은 테이블을 집단화여 그 특정 집단의 행들에 대한 집계를 합니다. 이 집계함수에 대한 각각의 설명을 자세히 하는 것보다는 실습을 통해서 느끼는 것이 좋습니다. 실습으로 바로 들어가겠습니다. {{{#!geshi plsql SQL> -- 모든 사원의 월급에 대한 평균 SQL> SELECT AVG(SAL) FROM EMP; AVG(SAL) ---------- 2021.66667 SQL> -- EMP 테이블의 총 행의 개수 SQL> SELECT COUNT(*) FROM EMP; COUNT(*) ---------- 15 SQL> -- 사원중 가장 많은 월급 SQL> SELECT MAX(SAL) FROM EMP; MAX(SAL) ---------- 5000 SQL> -- 사원중 가장 적은 월급 SQL> SELECT MIN(SAL) FROM EMP; MIN(SAL) ---------- 800 SQL> -- 전체 사원에게 지급되는 월급의 총합 SQL> SELECT SUM(SAL) FROM EMP; SUM(SAL) ---------- 30325 }}} 집계함수의 각각의 기본은 위와 같습니다. 만약 특정한 조건을 걸고, 집단화하여 결과값을 얻으려면 GROUP BY 를 써줘야 합니다. 다음을 봅시다. {{{#!geshi plsql SQL> -- 각 부서별 사원이 받는 월급의 총합은? SQL> SELECT DEPTNO, SUM(SAL) 2 FROM EMP 3 GROUP BY DEPTNO; DEPTNO SUM(SAL) ---------- ---------- 10 10050 20 10875 30 9400 SQL> -- 부서번호가 10 인 부서에 속한 사원들의 월급의 총합은? SQL> SELECT DEPTNO, SUM(SAL) 2 FROM EMP 3 WHERE DEPTNO = 10 4 GROUP BY DEPTNO; DEPTNO SUM(SAL) ---------- ---------- 10 10050 SQL> -- 각 부서에 속한 사원은 몇명인가? SQL> SELECT DEPTNO, COUNT(*) FROM EMP 2 GROUP BY DEPTNO; DEPTNO COUNT(*) ---------- ---------- 10 4 20 5 30 6 SQL> -- 80년에 입사한 사원은 몇명인가? SQL> SELECT HIREDATE, COUNT(*) FROM EMP 2 WHERE HIREDATE LIKE '80%' 3 GROUP BY HIREDATE; HIREDATE COUNT(*) -------- ---------- 80/12/17 1 SQL> -- 집계함수는 기본으로 널값을 제외하고 계산한다. SQL> SELECT SUM(COMM) FROM EMP; SUM(COMM) ---------- 2200 SQL> -- 이런 경우는 널값을 제외하지 않으므로 엉뚱한 결과가 나타난다. SQL> SELECT SUM(SAL + COMM) FROM EMP; SUM(SAL+COMM) ------------- 7800 SQL> -- NVL()함수는 널값을 두번째 파라미터로 대치한다. 첫번째 파라미터는 보통 계산하려 SQL> -- 는 컬럼의 이름이 온다. SQL> SELECT SUM(SAL + NVL(COMM, 0)) FROM EMP; SUM(SAL+NVL(COMM,0)) -------------------- 32525 SQL> SELECT SUM(SAL) + SUM(COMM) FROM EMP; SUM(SAL)+SUM(COMM) ------------------ 32525 SQL> -- "주의!!!" GROUP BY를 사용할때 SELECT 절의 집계함수와 GROUP BY에 지정된 항목 이 SQL> -- 외에는 어떤 컬럼도 포함되서는 안됀다. 생각해보면 각각의 사원은 모두 고유한 SQL> -- 사원번호를 가지고 있는데 SELECT 절에서는 사원번호, 부서번호를 조회하고자 하였 SQL> -- 으니 각 사원은 부서번호로 GROUP BY 한 상태에서 어떻게 조회를 할 수 있겠는가? SQL> -- 이해가 안간다면 아래 설명을 차근 차근 읽어 보시길.... SQL> SELECT EMPNO, DEPTNO, COUNT(*) 2 FROM EMP 3 WHERE DEPTNO = 10 4 GROUP BY DEPTNO; SELECT EMPNO, DEPTNO, COUNT(*) * 1행에 오류: ORA-00979: GROUP BY 의 식이 없습니다 }}} ==== 쿼리문의 기본적인 처리과정 ==== 필자의 예상으로는 아직도 감이 안잡히시는 분이 많으리라 예상합니다. 필자의 경우도 GROUP BY가 들어간 쿼리문은 상당히 이해가 안갔습니다. 이제 GROUP BY가 들어가 SELECT문이 어떻게 처리되어 결과적으로 사용자에게 보여지는가를 살펴보겠습니다. 다음의 예를 가지고 설명하겠습니다. {{{#!geshi plsql SQL> SELECT DEPTNO, SUM(SAL), COUNT(*) 2 FROM EMP 3 WHERE HIREDATE LIKE '81%' 4 GROUP BY DEPTNO 5 ORDER BY SUM(SAL); (1) 우리가 처음에 살펴본듯이 제일 먼저 접근하는 것은 FROM 절에서 나열한 테이블입니다. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO -------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 80/12/17 800 20 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 7521 WARD SALESMAN 7698 81/02/22 1250 500 30 7566 JONES MANAGER 7839 81/04/02 2975 20 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 7698 BLAKE MANAGER 7839 81/05/01 2850 30 7782 CLARK MANAGER 7839 81/06/09 2450 10 7788 SCOTT ANALYST 7566 87/04/19 3000 20 7839 KING PRESIDENT 81/11/17 5000 10 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 7876 ADAMS CLERK 7788 87/05/23 1100 20 7900 JAMES CLERK 7698 81/12/03 950 30 7902 FORD ANALYST 7566 81/12/03 3000 20 7934 MILLER CLERK 7782 82/01/23 1300 10 1111 MILLER CLE%RK 7782 82/01/23 1300 10 (2) 그 다음은 조건절에서 기술한 HIREDATE LIKE '81%' 인 것만 걸러냅니다. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 7521 WARD SALESMAN 7698 81/02/22 1250 500 30 7566 JONES MANAGER 7839 81/04/02 2975 20 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 7698 BLAKE MANAGER 7839 81/05/01 2850 30 7782 CLARK MANAGER 7839 81/06/09 2450 10 7839 KING PRESIDENT 81/11/17 5000 10 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 7900 JAMES CLERK 7698 81/12/03 950 30 7902 FORD ANALYST 7566 81/12/03 3000 20 (3) 다음은 DEPTNO 별로 그룹을 지어 줍니다. SAL DEPTNO ---------- ---------- 1600 30 <-- 그룹3 1250 30 <-- 그룹3 2975 20 <-- 그룹2 1250 30 <-- 그룹3 2850 30 <-- 그룹3 2450 10 <-- 그룹1 5000 10 <-- 그룹1 1500 30 <-- 그룹3 950 30 <-- 그룹3 3000 20 <-- 그룹2 (4) SELECT 절에서 기술한 집계함수를 이용해서 그룹1, 그룹2, 그룹3에 대해서 각 그룹에 대 한합계를 내고, 각 그룹에 속한 사원의 수를 센 후, SELECT 절에 기술한 순서대로 나열 합니다. 만일 더 많은 그룹으로 나뉠 수 있다면 그룹의 수는 증가하겠지요. 기술한 그룹1, 그룹2..는 제가 임의적으로 설명을 하기 위해서 붙인 것입니다. DEPTNO SUM(SAL) COUNT(*) ---------- ---------- ---------- 10 7450 2 --> 그룹 1 : 부서번호 10인 사원들 20 5975 2 --> 그룹 2 : 부서번호 20인 사원들 30 9400 6 --> 그룹 3 : 부서번호 30인 사원들 (5) 마지막으로 ORDER BY에 기술한 대로 SUM(SAL)로 내림차순 정렬을 한 후 사용자에게 결과 집합을 보여줍니다. DEPTNO SUM(SAL) COUNT(*) ---------- ---------- ---------- 20 5975 2 10 7450 2 30 9400 6 }}} 정리하자면 위에서 한 것은 FROM --> WHERE --> GROUP BY --> ORDER BY --> SELECT 순으로 처리됩니다. 이것을 보면 저것은 절차적으로 처리되는 것 아니냐? 하는 질문을 던질 수 있습니다. 또한 DBMS도 프로그래밍 언어로 만든 것이므로 절차적으로 처리될 수 밖에 없다. 라고 따지는 분도 계실 겁니다. 그러나 이것은 초등학교에서 선생님이 양손을 들어 올리면서 '여러분 똑같죠?' 했을 때 '아니요~! 오른손을 많이 쓰니까 오른손이 조금더 크다고 하던데요~!!' 라고 따지는 것과 같습니다. 중요한 것은 사용자와 DBMS가 통신을 하기 위한 유일한 수단이 SQL이며 사용자가 생각한 결과집합에 대한 결과를 집합적으로 처리한다는 것입니다. 부디 여러분이 앞서서 얘기한 오해를 하지 않고, 구조적이고 집합적인 사고를 할 수 있기를 바랄 뿐입니다. ==== HAVING 절의 사용 ==== 그룹에 대해서 좀 더 살펴보도록 하겠습니다. 만약 여러분이 "사원이 5명 이상 속해 있는 부서의 부서번호, 그 부서의 월급 총합과 부서원의 수" 를 알고 싶다면 어떻게 해야 할까요? 그룹으로 묶여 있는 상태에서 조건을 기술해 줘야 겠지요. 일반적인 조건은 WHERE절에서 쓰지만 그룹에 대한 조건은 HAVING 입니다. 위의 예제를 그대로 사용하고 HAVING 절만 덧붙여 보겠습니다. {{{#!geshi plsql SQL> SELECT DEPTNO, SUM(SAL), COUNT(*) 2 FROM EMP 3 WHERE HIREDATE LIKE '81%' 4 GROUP BY DEPTNO 5 HAVING COUNT(*) >= 5 6 ORDER BY SUM(SAL); DEPTNO SUM(SAL) COUNT(*) ---------- ---------- ---------- 30 9400 6 SQL> -- HAVING절은 SELECT 절을 처리하기 바로 전에 수행됩니다. }}} ==== 조인의 사용 ==== 이제까지 우리는 하나의 테이블만 가지고 조회를 했었습니다. 그러나 관계형 모델(오라클은 정확히 객체-관계형 DBMS이다.)은 하나 또는 그 이상의 엔티티 집합과 관계를 맺고 있습니다. 이것은 두개이 테이블에서 유용한 정보가 많이 출력이 될 수 있다는 의미입니다. 실제로도 하나의 테이블에서만 나오는 정보는 많치 않습니다. 물론 작은 데이타베이스에서는 하나의 테이블에서 정보를 많이 출력해 냅니다. 엔티티 집합을 물리적으로 구현하면 테이블이 됩니다. 물론 정규화 과정을 거치면 하나의 엔티티 집합이 여러개로 쪼개집니다. 이렇게 쪼개지거나 원래부터 관계를 맺고 있는 두개 이상 의 테이블에서 조회를 할때는 JOIN을 사용합니다. JOIN의 사용법을 이제부터 실습을 통해 익혀보도록 하겠습니다. {{{#!geshi plsql SQL> -- 사원번호와 사원이름과 그 사원이 속한 부서명의 조회 SQL> -- WHERE절에 EMP.DEPTNO = DEPT.DEPTNO의 조건을 주었다. EMP.DEPTNO는 테이블.컬럼명 SQL> SELECT EMP.EMPNO, EMP.ENAME,EMP.DEPTNO,DEPT.DNAME 2 FROM EMP, DEPT 3 WHERE EMP.DEPTNO = DEPT.DEPTNO; EMPNO ENAME DEPTNO DNAME ---------- ---------- ---------- -------------- 7369 SMITH 20 RESEARCH 7499 ALLEN 30 SALES 7521 WARD 30 SALES 7566 JONES 20 RESEARCH 7654 MARTIN 30 SALES 7698 BLAKE 30 SALES 7782 CLARK 10 ACCOUNTING 7788 SCOTT 20 RESEARCH 7839 KING 10 ACCOUNTING 7844 TURNER 30 SALES 7876 ADAMS 20 RESEARCH 7900 JAMES 30 SALES 7902 FORD 20 RESEARCH 7934 MILLER 10 ACCOUNTING 1111 MILLER 10 ACCOUNTING 15 개의 행이 선택되었습니다. SQL> SELECT EMPNO, ENAME, DEPTNO, DNAME 2 FROM EMP, DEPT 3 WHERE EMP.DEPTNO = DEPT.DEPTNO; SELECT EMPNO, ENAME, DEPTNO, DNAME * 1행에 오류: ORA-00918: 열의 정의가 애매합니다 SQL> -- DEPTNO는 EMP테이블과 DEPT테이블에 모두 존재하므로 어떤 테이블에서 가져올지 SQL> -- 오라클은 판단하지 못한다. DEPTNO 대신에 DEPT.DEPTNO로 해보자 SQL> SELECT EMPNO, ENAME, DEPT.DEPTNO, DNAME 2 FROM EMP, DEPT 3 WHERE EMP.DEPTNO = DEPT.DEPTNO; EMPNO ENAME DEPTNO DNAME ---------- ---------- ---------- -------------- 7369 SMITH 20 RESEARCH 7499 ALLEN 30 SALES 7521 WARD 30 SALES 7566 JONES 20 RESEARCH 7654 MARTIN 30 SALES 7698 BLAKE 30 SALES 7782 CLARK 10 ACCOUNTING 7788 SCOTT 20 RESEARCH 7839 KING 10 ACCOUNTING 7844 TURNER 30 SALES 7876 ADAMS 20 RESEARCH 7900 JAMES 30 SALES 7902 FORD 20 RESEARCH 7934 MILLER 10 ACCOUNTING 1111 MILLER 10 ACCOUNTING 15 개의 행이 선택되었습니다. SQL> -- 꼭 조건절에 EMP.DEPTNO = DEPT.DEPTNO 를 명시해 주어야 하는가? SQL> -- 아래를 수행하면 4장에서 잠시 맛을 본 카테시안 곱이 되버린다. SQL> -- 여러분은 머리속에 있는 것을 모두 SQL로 표현해야만 합니다. 머리속에 맴돌게 하지 SQL> -- 마시고 표현을 하세요. ^^; SQL> SELECT EMPNO, ENAME, DEPT.DEPTNO, DNAME 2 FROM EMP, DEPT; EMPNO ENAME DEPTNO DNAME ---------- ---------- ---------- ----------- 7369 SMITH 10 ACCOUNTING 7499 ALLEN 10 ACCOUNTING 7521 WARD 10 ACCOUNTING . . . . . . . . . . . . 7900 JAMES 40 OPERATIONS 7902 FORD 40 OPERATIONS 7934 MILLER 40 OPERATIONS 1111 MILLER 40 OPERATIONS 60 개의 행이 선택되었습니다. SQL> -- 조인을 실습하기 위한 환경 만들기. SQL> UPDATE EMP 2 SET DEPTNO = NULL 3 WHERE EMPNO=1111; 1 행이 갱신되었습니다. SQL> SELECT DEPTNO FROM EMP 2 WHERE EMPNO = 1111; DEPTNO ---------- SQL> -- 아래의 예제를 유심히 살펴보시길... SQL> -- 외부조인의 표준과 오라클의 방법이 있는데 표준을 사용할 것을 필자는 권장함... SQL> -- MS-SQL SERVER 는 (+) 기호로 외부조인이 안됌 SQL> -- 왼쪽 외부 조인 (표준방법) SQL> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO 2 FROM EMP LEFT OUTER JOIN DEPT 3 ON EMP.DEPTNO = DEPT.DEPTNO; EMPNO ENAME DEPTNO ---------- ---------- ---------- 7369 SMITH 20 7499 ALLEN 30 7521 WARD 30 7566 JONES 20 7654 MARTIN 30 7698 BLAKE 30 7782 CLARK 10 7788 SCOTT 20 7839 KING 10 7844 TURNER 30 7876 ADAMS 20 7900 JAMES 30 7902 FORD 20 7934 MILLER 10 1111 MILLER --> 이 행이 어떻게해서 나왔을까를 생각해야 함 15 개의 행이 선택되었습니다. SQL> -- 오라클에서는 아래와 같이 왼쪽 외부조인을 수행할 수 있다. SQL> -- (+) 기호가 붙은 의미는 왼쪽에 있는 테이블(여기서는 EMP)에 덧붙인다는 의미로 보 SQL> -- 면 된다. SQL> -- SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO 2 -- FROM EMP, DEPT 3 -- WHERE EMP.DEPTNO = DEPT.DEPTNO(+); SQL> SQL> -- 오른쪽 외부 조인 (표준방법) SQL> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO 2 FROM EMP RIGHT OUTER JOIN DEPT 3 ON EMP.DEPTNO = DEPT.DEPTNO; EMPNO ENAME DEPTNO ---------- ---------- ---------- 7369 SMITH 20 7499 ALLEN 30 7521 WARD 30 7566 JONES 20 7654 MARTIN 30 7698 BLAKE 30 7782 CLARK 10 7788 SCOTT 20 7839 KING 10 7844 TURNER 30 7876 ADAMS 20 7900 JAMES 30 7902 FORD 20 7934 MILLER 10 40 --> 이 행이 어떻게해서 나왔을까를 생각해야 함 15 개의 행이 선택되었습니다. SQL> -- 오라클에서는 오른쪽 외부 조인인 아래와 같이 해도 된다. SQL> -- SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO 2 -- FROM EMP, DEPT 3 -- WHERE EMP.DEPTNO(+) = DEPT.DEPTNO; SQL> -- 양쪽 외부 조인 (표준방법) SQL> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO 2 FROM EMP FULL OUTER JOIN DEPT 3 ON EMP.DEPTNO = DEPT.DEPTNO; EMPNO ENAME DEPTNO ---------- ---------- ---------- 7369 SMITH 20 7499 ALLEN 30 7521 WARD 30 7566 JONES 20 7654 MARTIN 30 7698 BLAKE 30 7782 CLARK 10 7788 SCOTT 20 7839 KING 10 7844 TURNER 30 7876 ADAMS 20 7900 JAMES 30 7902 FORD 20 7934 MILLER 10 1111 MILLER --> 이 행이 어떻게해서 나왔을까를 생각해야 함 40 --> 이 행이 어떻게해서 나왔을까를 생각해야 함 16 개의 행이 선택되었습니다. SQL> -- 아쉽게도 이렇게 되지는 않는다. 양쪽 외부조인을 할때는 표준을 따라야 한다. SQL> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO 2 FROM EMP, DEPT 3 WHERE EMP.DEPTNO(+) = DEPT.DEPTNO(+); WHERE EMP.DEPTNO(+) = DEPT.DEPTNO(+) * 3행에 오류: ORA-01468: outer-join된 테이블은 1개만 지정할 수 있습니다 SQL> -- 다음을 비교해 보시기 바랍니다. SQL> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO 2 FROM EMP, DEPT 3 WHERE EMP.DEPTNO(+) = DEPT.DEPTNO 4 AND EMPNO(+) = 7521; EMPNO ENAME DEPTNO ---------- ---------- ---------- 10 20 7521 WARD 30 40 SQL> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO 2 FROM EMP RIGHT OUTER JOIN DEPT 3 ON EMP.DEPTNO = DEPT.DEPTNO 4 WHERE EMP.EMPNO = 7521; --> WHERE 절을 유심히... EMPNO ENAME DEPTNO ---------- ---------- ---------- 7521 WARD 30 SQL> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO 2 FROM EMP RIGHT OUTER JOIN DEPT 3 ON EMP.DEPTNO = DEPT.DEPTNO 4 AND EMP.EMPNO = 7521; --> ON 절에 AND 를 이용해서 연산을 한다. EMPNO ENAME DEPTNO ---------- ---------- ---------- 7521 WARD 30 40 20 10 SQL> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO 2 FROM EMP LEFT OUTER JOIN DEPT --> 왼쪽 외부 조인 사용 3 ON EMP.DEPTNO = DEPT.DEPTNO 4 AND EMP.EMPNO IN (7521, 7369, 7788); EMPNO ENAME DEPTNO ---------- ---------- ---------- 7369 SMITH 20 7499 ALLEN 7521 WARD 30 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 20 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER 1111 MILLER 15 개의 행이 선택되었습니다. }}} '''MS-SQL Server에서의 외부조인''' ||MS-SQL Server에서는 표준 조인방법과 MS-SQL Server자체적인 조인의 방법을 제공하고 있습니다. MS-SQL Server 자체적인 조인 방법은 '=*', '*=', '*=*'표시로 조인 연산을 수행하도록하고 있습니다. 그러나 MS측에서도 이와 같은 방법은 추천하지 않으며, 표준에 맞게 조인연 산을 수행하도록 권하고 있습니다. MS사에서 표준의 조인 방법을 권하는 이유는 표준을 사용하지 않았을 때 원치않는 정보를 출력할 수도 있기 때문입니다.|| ==== 서브쿼리 ==== 이제 서브쿼리에 대해서 알아 보겠습니다. 보통의 서브쿼리는 WHERE 절에 비교를 하기 위해 또다른 쿼리를 이용합니다. 일반적으로 서브쿼리가 먼저 처리되고, 메인쿼리가 수행됩니다. 여러분들은 서브쿼리를 작성할 때 먼저 하위쿼리를 작성한 후에 메인쿼리를 작성해야 할 것 입니다. 우선 생각할 것은 서브쿼리의 수행속도를 생각하고 서브쿼리를 작성해야하며, WHERE절에서 비교되는 것이 하나인다 복수개인가를 생각해서 적절한 연산자를 사용해야 합니다. 역시 '백문이불여일타' 입니다. {{{#!geshi plsql SQL> --일반적인 서브쿼리의 형태 SQL> SELECT * FROM EMP 2 WHERE DEPTNO = (SELECT DEPTNO 3 FROM DEPT 4 WHERE DEPTNO = 30); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 7521 WARD SALESMAN 7698 81/02/22 1250 500 30 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 7698 BLAKE MANAGER 7839 81/05/01 2850 30 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 7900 JAMES CLERK 7698 81/12/03 950 30 6 개의 행이 선택되었습니다. SQL> -- 위의 서브쿼리와 같은 결과를 내는 조인연산 SQL> -- 위의 서브쿼리는 내부적으로 이와같이 변환되어 수행된다. 그러므로 서브쿼리를 조 SQL> -- 인으로 바꾸는 과정도 하나의 시스템의 일이므로 웬만해서는 서브쿼리보다 조인을 SQL> -- 권장한다. 그러나 항상 같은 조건이라도 서브쿼리가 성능이 더 빠를 수도 있다. 이 SQL> -- 것은 단순히 서브쿼리와 조인을 두고 말하는 것이다. SQL> SELECT EMP.* 2 FROM EMP, DEPT 3 WHERE EMP.DEPTNO = DEPT.DEPTNO 4 AND DEPT.DEPTNO = 30; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 7521 WARD SALESMAN 7698 81/02/22 1250 500 30 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 7698 BLAKE MANAGER 7839 81/05/01 2850 30 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 7900 JAMES CLERK 7698 81/12/03 950 30 6 개의 행이 선택되었습니다. SQL> -- IN 연산자를 이용한 서브쿼리 SQL> SELECT EMPNO, ENAME, HIREDATE 2 FROM EMP 3 WHERE EMPNO IN (SELECT EMPNO 4 FROM EMP 5 WHERE HIREDATE LIKE '82%'); EMPNO ENAME HIREDATE ---------- ---------- -------- 7934 MILLER 82/01/23 1111 MILLER 82/01/23 SQL> -- ALL 연산자를 이용한 서브쿼리... ALL 연산자가 오면 서브쿼리에 의해 생성되는 모 SQL> -- 모든 값이 조건을 만족할 때만 검색 조건이 되어 TRUE가 된다. SQL> -- 어떤 DEPTNO 30인 사원의 월급보다도 더 많은 월급을 받는 사원의 ENAME, SAL, SQL> -- DEPTNO검색 SQL> SELECT ENAME, SAL, DEPTNO 2 FROM EMP 3 WHERE SAL > ALL (SELECT SAL 4 FROM EMP 5 WHERE DEPTNO = 30); ENAME SAL DEPTNO ---------- ---------- ---------- JONES 2975 20 SCOTT 3000 20 KING 5000 10 FORD 3000 20 SQL> -- 결국 아래와 같은 쿼리와 같은 의미가 된다. SQL> SELECT ENAME, SAL, DEPTNO 2 FROM EMP 3 WHERE SAL > 2850; ENAME SAL DEPTNO ---------- ---------- ---------- JONES 2975 20 SCOTT 3000 20 KING 5000 10 FORD 3000 20 SQL> -- ANY/SOME 연산자는 서브쿼리에 의해서 생성되는 값 중 어느 하나라도 조건을 만족하 SQL> -- 면 검색 조건이 TRUE가 된다. SQL> -- ANY/SOME의 효과는 동일하며, 아래의 ANY, IN, ALL 연산자 비교를 보시기 바랍니다. SQL> SELECT ENAME, SAL, DEPTNO 2 FROM EMP 3 WHERE SAL = ALL (SELECT SAL 4 FROM EMP 5 WHERE DEPTNO = 30); 선택된 레코드가 없습니다. SQL> SELECT ENAME, SAL, DEPTNO 2 FROM EMP 3 WHERE SAL IN (SELECT SAL 4 FROM EMP 5 WHERE DEPTNO = 30); ENAME SAL DEPTNO ---------- ---------- ---------- JAMES 950 30 WARD 1250 30 MARTIN 1250 30 TURNER 1500 30 ALLEN 1600 30 BLAKE 2850 30 6 개의 행이 선택되었습니다. SQL> SELECT ENAME, SAL, DEPTNO 2 FROM EMP 3 WHERE SAL = ANY (SELECT SAL 4 FROM EMP 5 WHERE DEPTNO = 30); ENAME SAL DEPTNO ---------- ---------- ---------- JAMES 950 30 WARD 1250 30 MARTIN 1250 30 TURNER 1500 30 ALLEN 1600 30 BLAKE 2850 30 6 개의 행이 선택되었습니다. SQL> -- EXISTS 연산자는 서브쿼리에 결과가 있는가 없는가 즉, TRUE인지 FLASE 인지 판단하 SQL> -- 는 연산자이다. 다른 연산자에 비해 TURE/FALSE만 알면되므로 가볍게 수행할 수 있 SQL> -- 는 매력이 있는 연산자 SQL> SELECT ENAME, JOB, DEPTNO 2 FROM EMP 3 WHERE EXISTS (SELECT * -- 주의할 것.. EXISTS 연산자 앞에 아무것도 없다. 4 FROM DEPT 5 WHERE DEPT.DEPTNO = EMP.DEPTNO 6 AND DEPTNO = 30); ENAME JOB DEPTNO ---------- --------- ---------- ALLEN SALESMAN 30 WARD SALESMAN 30 MARTIN SALESMAN 30 BLAKE MANAGER 30 TURNER SALESMAN 30 JAMES CLERK 30 6 개의 행이 선택되었습니다. SQL> -- IN 연산자를 이용해서 같은 결과를 낼 수 있다. 그러나 EXISTS 연산자가 더 가볍다. SQL> SELECT ENAME, JOB, DEPTNO 2 FROM EMP 3 WHERE DEPTNO IN (SELECT DEPTNO 4 FROM DEPT 5 WHERE DEPT.DEPTNO = EMP.DEPTNO 6 AND DEPTNO = 30); ENAME JOB DEPTNO ---------- --------- ---------- ALLEN SALESMAN 30 WARD SALESMAN 30 MARTIN SALESMAN 30 BLAKE MANAGER 30 TURNER SALESMAN 30 JAMES CLERK 30 6 개의 행이 선택되었습니다. SQL> -- 상관서브쿼리: 바깥쪽 SELECT 문의 컬럼중 하나가 서브쿼리의 조건에 이용 SQL> -- 먼저 서브쿼리에서 이용할 메인쿼리의 컬럼 데이타를 구하고 서브쿼리를 실행한 뒤, SQL> -- 그 결과값을 이용해서 메인쿼리를 실행 SQL> -- 이런 이유로 상관서브쿼리는 쿼리성능이 떨어진다. 웬만하면 이런 쿼리를 만들지 말 SQL> -- 도록 해야한다. SQL> SELECT EMPNO, ENAME, SAL, DEPTNO 2 FROM EMP MAIN_QUERY 3 WHERE SAL > (SELECT SUM(COMM) 4 FROM DEPT 5 WHERE DEPT.DEPTNO = MAIN_QUERY.DEPTNO 6 AND DEPT.DEPTNO = 30); EMPNO ENAME SAL DEPTNO ---------- ---------- ---------- ---------- 7499 ALLEN 1600 30 7521 WARD 1250 30 7844 TURNER 1500 30 }}} 앞에서 우리는 서브쿼리와 여러가지 연산자에 대해서 살펴보았습니다. 다음으로는 자기 자신을 조인하는 SELF JOIN에 대해서 살펴보도록 하겠습니다. SELF JOIN은 하나의 테이블에서 복잡한 연산을 할 때 많이 쓰입니다. 셀프조인은 자기 자신에 대한 조인이기 때문에 반드시 별명(alias)를 사용해야 합니다. {{{#!geshi plsql SQL> SELECT T1.EMPNO "MANAGER", T1.ENAME, T2.EMPNO, T2.ENAME, T2.MGR 2 FROM EMP T1 INNER JOIN EMP T2 3 ON T1.EMPNO = T2.MGR; MANAGER ENAME EMPNO ENAME MGR ---------- ---------- ---------- ---------- ---------- 7902 FORD 7369 SMITH 7902 7698 BLAKE 7499 ALLEN 7698 7698 BLAKE 7521 WARD 7698 7839 KING 7566 JONES 7839 7698 BLAKE 7654 MARTIN 7698 7839 KING 7698 BLAKE 7839 7839 KING 7782 CLARK 7839 7566 JONES 7788 SCOTT 7566 7698 BLAKE 7844 TURNER 7698 7788 SCOTT 7876 ADAMS 7788 7698 BLAKE 7900 JAMES 7698 7566 JONES 7902 FORD 7566 7782 CLARK 7934 MILLER 7782 7782 CLARK 1111 MILLER 7782 14 개의 행이 선택되었습니다. }}} === 집합 연산자 === 이제 SELECT 기본의 마지막인 집합연산자를 다루겠습니다. 집합연산자는 합집합, 교집합, 차집합입니다. 합집합은 UNION, 교집합은 INTERSECT, 차집합은 MINUS 연산자를 사용합니다. 실습을 하면 바로 알수 있는 것입니다. {{{#!geshi plsql SQL> SELECT EMPNO, ENAME, DEPTNO 2 FROM EMP 3 WHERE DEPTNO = 10 . 4 UNION -- UNION 만 했을 때는 정렬, 중복제거 작업까지 일어난다. 5 SELECT EMPNO, ENAME, DEPTNO 6 FROM EMP 7 WHERE DEPTNO = 20; EMPNO ENAME DEPTNO ---------- ---------- ---------- 1111 MILLER 10 7369 SMITH 20 7566 JONES 20 7782 CLARK 10 7788 SCOTT 20 7839 KING 10 7876 ADAMS 20 7902 FORD 20 7934 MILLER 10 9 개의 행이 선택되었습니다. SQL> -- WHERE절에 'OR DEPTNO = 20'을 삽입하여 UNION ALL과 UNION을 비교해 본다 SQL> SELECT EMPNO, ENAME, DEPTNO 2 FROM EMP 3 WHERE DEPTNO = 10 4 UNION ALL -- UNION ALL 했을 때는 정렬, 중복제거 작업이 일어나지 않는다. 5 SELECT EMPNO, ENAME, DEPTNO 6 FROM EMP 7 WHERE DEPTNO = 20; EMPNO ENAME DEPTNO ---------- ---------- ---------- 7782 CLARK 10 7839 KING 10 7934 MILLER 10 1111 MILLER 10 7369 SMITH 20 7566 JONES 20 7788 SCOTT 20 7876 ADAMS 20 7902 FORD 20 9 개의 행이 선택되었습니다. SQL> -- 아래는 UNION 에 대한 제약사항을 알아보기 위한 것 SQL> SELECT 'YASI', 'YASI', 'YASI' FROM DUAL 2 UNION 3 SELECT 'YASICOM', 'YASICOM' FROM DUAL; SELECT 'YASI', 'YASI', 'YASI' FROM DUAL * 1행에 오류: ORA-01789: 질의의 결과 열의 수가 틀립니다 --> 잘봐야 함 SQL> SELECT 'YASI', 'YASI', 'YASI' FROM DUAL 2 UNION 3 SELECT 'YASICOM', 'YASICOM', 0 FROM DUAL; SELECT 'YASI', 'YASI', 'YASI' FROM DUAL * 1행에 오류: ORA-01790: 대응하는 식과 같은 데이터 유형이어야 합니다 --> 잘봐야 함 SQL> SELECT 'YASI', 'YASI', 'YASI' FROM DUAL 2 UNION 3 SELECT 'YASICOM', 'YASICOM', '0' FROM DUAL; /* 'YASI' 'YASI' 'YAS ------- ------- ---- YASI YASI YASI YASICOM YASICOM 0 */ SQL> SELECT EMPNO, ENAME, DEPTNO 2 FROM EMP 3 INTERSECT 4 SELECT EMPNO, ENAME, DEPTNO 5 FROM EMP 6 WHERE DEPTNO = 10; EMPNO ENAME DEPTNO ---------- ---------- ---------- 1111 MILLER 10 7782 CLARK 10 7839 KING 10 7934 MILLER 10 SQL> SELECT EMPNO, ENAME, DEPTNO 2 FROM EMP 3 MINUS 4 SELECT EMPNO, ENAME, DEPTNO 5 FROM EMP 6 WHERE DEPTNO = 10; EMPNO ENAME DEPTNO ---------- ---------- ---------- 7369 SMITH 20 7499 ALLEN 30 7521 WARD 30 7566 JONES 20 7654 MARTIN 30 7698 BLAKE 30 7788 SCOTT 20 7844 TURNER 30 7876 ADAMS 20 7900 JAMES 30 7902 FORD 20 11 개의 행이 선택되었습니다. }}}