오라클 9i 버전부터 ANSI JOIN을 지원하기 시작했다. 그렇지만 아직까지 오라클 데이터베이스를 사용하는 많은 시스템에서 ANSI JOIN을 사용하지 않고 오라클 방식의 조인을 사용하여 쿼리문을 작성하고 있다. ANSI JOIN에 익숙하지 않으면 쿼리문을 작성할 때 헷갈리는 부분이 많이 발생한다. 2개의 테이블을 조인하는 예제는 많지만, 3개 이상의 테이블을 조인하는 예제가 많이 없어서 간단히 정리한 내용이다. SELECT a.empno , a.ename , a.deptno , b.dname , b.locno , c.lname FROM emp a INNER JOIN dept b ON a.deptno = b.deptno INNER JOIN loc c ON b.locno = c.locno WHERE a...
오라클 버전이 업그레이드 되면서 옵티마이저의 성능도 많이 향상 되어 쿼리를 실행하면 최적의 인덱스를 선택하여 쿼리를 실행한다. 그러나 옵티마이저가 엉뚱한 인덱스를 선택하여 쿼리가 느려지는 경우가 있다. 실무에서 쿼리문를 작성하다보면 "인덱스가 안탄다", "인덱스를 태워야한다" 와 같은 얘기를 많이 듣는다. 아래는 어떻게 인덱스를 태워야하는지 간단히 정리한 내용이다. SELECT절 첫 줄에 힌트 주석(/*+ */)을 작성하여 적절한 인덱스를 부여하면 된다. 주석에 꼭 "+"를 붙여야 힌트절이 실행되며 "+"가 없으면 일반 주석으로 간주하고 아무런 이벤트가 없다. SELECT /*+ INDEX(a EMP_IDX02) */ a.empno , a.ename , a.hiredate FROM emp a 인덱스 힌..
오라클 쿼리에서 숫자 값을 정렬(ORDER BY) 하였는데 뒤죽박죽 순서가 섞여서 조회되는 경우가 있다. 이런 경우는 대부분 컬럼 타입이 문자형(VARCHAR)인 경우가 많다. 숫자형 문자 컬럼인 경우 컬럼의 값을 TO_NUMBER 함수를 사용하여 숫자 타입으로 변경하거나, LPAD 함수를 사용하여 숫자 앞에 컬럼의 길이만큼 "0"을 붙여서 조회하면 된다. TO_NUMBER( ) 함수를 사용하는 방법 (컬럼의 값이 모두 숫자인 경우 사용) SELECT * FROM dept ORDER BY TO_NUMBER(dept_id) LPAD( ) 함수를 사용하는 방법 (컬럼의 값이 숫자 + 문자인 경우 사용) SELECT * FROM dept ORDER BY LPAD(dept_id, 3, '0') - 컬럼의 길이만..
DUAL 테이블 사용 방법 오라클에서 쿼리문을 작성시 테이블이 없이 데이터를 만들고 싶을때가 있다. MS-SQL이나 MySQL에서는 FROM절 없이 SELECT 절만 사용해서 쿼리를 실행할 수 있다. 오라클은 FROM절이 없을경우 쿼리문이 실행되지 않는다. 그래서 FROM절에 사용가능한 가상테이블 DUAL 이라는 DUMMY 테이블을 제공하고 있다. SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd') FROM dual 위의 쿼리를 실행하면 테이블이 없어도 현재의 날짜를 가져올 수 있다. SELECT 7788 empno , 'SCOTT' ename , 'ANALYST' job FROM dual 여러개의 컬럼을 나열하여 결과를 얻을 수 있다. 활용 예제 1. 시스템의 현재 날짜를 얻어온다 2...
오라클 쿼리문 작성이 익숙하지 않을 때 가장 헷갈렸던 부분이 아우터 조인(Outer Join)이다. 오라클 아우터 조인 시 대상 컬럼에 "(+)" 기호를 붙여서 조인을 하면된다. 아우터 조인을 사용하는 이유는 기준 테이블의 데이터가 모두 조회(누락 없이) 되고, 대상 테이블에 데이터가 있을 경우 해당 컬럼의 값을 가져오기 위해서이다. 핵심은 조인을 하더라도 기준 테이블의 데이터가 누락되지 않도록 하는 것이다. 위의 데이터를 보면 "KING", "JAMES"의 DEPTNO가 DEPT 테이블에 존재하지 않는다. 이 상태에서 조인으로 쿼리문을 작성하면 "KING", "JAMES"의 데이터는 조회되지 않는다. 아우터 조인으로 쿼리문을 작성하면 조인이 되면 DEPT 테이블의 데이터를 가져오고, 조인이 되지 않으면..
오라클의 ANY, SOME, ALL 연산자는 실무에서 자주 사용은 안하지만, 사용법이 궁금해서 찾아보면 사용법이 생각보다 어렵고 어떤 상황의 쿼리문에서 사용해야 할지 머릿속에 그려지지 않는다. SOME은 ANY와 이름만 다를뿐 동일한 기능의 연산자이며 아래의 예제는 ANY로만 작성하였다. ANY(SOME), ALL은 주로 서브쿼리와 함께 사용하는 다중 행 연산자이며, ANY는 조건을 만족하는 값이 하나라도 있으면 결과를 리턴하고, ALL은 모든 값이 조건을 만족해야 결과를 리턴한다. ● ANY 조건 예시 (만족하는 값 하나만 있으면 됨) 조건 결과 설명 1000 > ANY (500, 1000, 2000) TRUE ANY 값에 1000 보다 작은 500이 있으므로 TRUE 1000 = ANY (500, 1..
오라클에서 쿼리문을 작성하다 보면 가장 많이 사용하는 연산자 중 하나는 BETWEEN일 것이다. BETWEEN은 날짜, 숫자의 범위 검색에 아주 유용하게 사용할 수 있으며, 문자의 범위 검색도 가능하다. 아래의 BETWEET 연산자①와 비교 연산자(>=, = 시작일자 AND 컬럼명
오라클에서 쿼리문을 작성할 때 NULL을 제대로 이해하지 않으면 원하는 결과가 출력되지 않을 때가 많다. 다른 DB나 언어와 다른 부분이 있으니 아래의 예제를 보면서 오라클에서 NULL 사용법을 조금이라도 이해하면 좋을 것이다. 오라클에서 빈 문자열( '' )은 NULL로 인식하기 때문에, 컬럼의 값이 빈 문자열이면 NULL과 동일한 조건으로 쿼리를 작성해야 한다. [ 잘못된 사용 ] ① job = NULL (NULL은 비교 연산자를 사용할 수 없다) ② job != NULL (NULL은 비교 연산자를 사용할 수 없다) ③ job = '' (빈 문자열은 비교 연산자를 사용할 수 없다) ④ sal + NULL (수치값에 NULL을 사칙연산하면 결과는 NULL 이다) [ 올바른 사용 ] ① job IS NU..
오라클에서 소계, 합계, 총계의 쿼리(SQL)를 작성할 때는 ROLLUP을 많이 사용한다. ROLLUP의 경우 나열된 컬럼의 단계별로 소계, 합계를 자동으로 집계를 한다. 그에 반해 GROUPING SETS는 여러 그룹핑 쿼리를 UNION ALL 한 것과 같은 결과를 만들 수 있어 조금 더 유연하게 소계, 합계를 집계할 수 있다. SELECT job , deptno , COUNT(*) cnt FROM emp GROUP BY GROUPING SETS((job, mgr), (job, deptno), ()) GROUPING SETS( 컬럼, 컬럼, 컬럼, ... ) GROUPING SETS( (컬럼그룹), (컬럼그룹), (컬럼그룹), ... ) GROUPING SETS 사용방법 - job 컬럼별 소계와 dep..
오라클에서 EXISTS( ) 함수를 처음 접하면 조금 어려움 느낌이 들 수도 있다. EXISTS를 사용하지 않아도 비슷한 결과를 만들 수 있는 기능이 많기 때문에, EXISTS에 대해 자세히 익히지 않고 넘어가는 경우가 많다. EXISTS(서브 쿼리)는 서브 쿼리의 결과가 "한 건이라도 존재하면" TRUE 없으면 FALSE를 리턴한다. EXISTS는 서브 쿼리에 일치하는 결과가 한 건이라도 있으면 쿼리를 더 이상 수행하지 않는다. 아래의 샘플 테이블을 참고하여 예제 쿼리를 보면 쉽게 이해할 수 있을 것이다. SELECT a.empno , a.ename , a.deptno FROM emp a WHERE a.job = 'MANAGER' AND EXISTS (SELECT 1 FROM dept_history a..