부서로 묶으면 부서 하나가 한 행이 된다. 그 한 행에 사원 이름 여러 개 중 무엇을 보일지 정할 수 없다.
SELECT DEPTNO, ENAME, SUM(SAL) FROM EMP GROUP BY DEPTNO;
SELECT DEPTNO, ENAME, SUM(SAL) FROM EMP GROUP BY DEPTNO *ERROR at line 1:ORA-00979: "ENAME": must appear in the GROUP BY clause or be used in an aggregate function
교재의 문구는 not a GROUP BY expression 이다. 23ai 는 문제가 된 열 이름까지 알려 준다. 번호는 같다.
반대 방향은 괜찮다. GROUP BY 에 있는 열을 SELECT 에서 빼는 것은 오류가 아니다.
부서마다 CLERK 사원을 붙이되, CLERK 가 없는 부서도 보이고 싶다. (+) 표기에서는 EMP 쪽 조건마다 (+) 를 붙여야 외부 조인이 유지된다.
SELECT D.DNAME, E.ENAME FROM DEPT D, EMP E WHERE D.DEPTNO = E.DEPTNO(+) AND E.JOB(+) = 'CLERK' ORDER BY D.DNAME, E.ENAME;
DNAME ENAME-------------- ----------ACCOUNTING MILLEROPERATIONSRESEARCH ADAMSRESEARCH SMITHSALES JAMES
E.JOB 에서 (+) 하나를 빠뜨리면 OPERATIONS 가 사라진다.
SELECT D.DNAME, E.ENAME FROM DEPT D, EMP E WHERE D.DEPTNO = E.DEPTNO(+) AND E.JOB = 'CLERK' ORDER BY D.DNAME, E.ENAME;
DNAME ENAME-------------- ----------ACCOUNTING MILLERRESEARCH ADAMSRESEARCH SMITHSALES JAMES
OPERATIONS 행은 외부 조인으로 E.JOB 이 NULL 로 채워졌다. 이어서 NULL = 'CLERK' 를 검사하니 참이 아니라 떨어진다. 결과는 내부 조인과 같다.
ANSI 표기에서는 같은 차이가 ON 과 WHERE 사이에서 생긴다. LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO AND E.JOB = 'CLERK' 은 위의 5행, ON D.DEPTNO = E.DEPTNO WHERE E.JOB = 'CLERK' 은 4행이다. 실습 컨테이너에서 각각 위 두 결과와 같은 행이 나왔다.
안쪽 테이블(EMP) 조건의 위치
적용 시점
짝 없는 부서
(+) 를 붙인 WHERE 조건, 또는 ON
짝을 지을 때
NULL 로 남는다
(+) 없는 WHERE 조건
외부 조인이 끝난 뒤
떨어진다 (내부 조인과 같아진다)
⚠️ 함정: 외부 조인 문제에서 WHERE 에 NULL 로 채워질 쪽 테이블의 열 조건이 (+) 없이 있으면, 외부 조인을 썼어도 결과 행 수는 내부 조인과 같다. 보기의 행 수를 셀 때 NULL 행을 먼저 지운다.
NATURAL JOIN 과 USING 은 공통 열을 하나로 합친다. 합쳐진 열은 어느 테이블의 것도 아니라 E. 나 D. 를 붙이면 오류다(W4 Day 5).
SELECT D.DEPTNO, DNAME FROM EMP E JOIN DEPT D USING (DEPTNO) WHERE E.ENAME = 'KING';
SELECT D.DEPTNO, DNAME FROM EMP E JOIN DEPT D USING (DEPTNO) WHERE E.ENAME = 'KING' *ERROR at line 1:ORA-25154: column part of USING clause cannot have qualifier
SELECT E.DEPTNO, DNAME FROM EMP E NATURAL JOIN DEPT D WHERE ENAME = 'KING';
SELECT E.DEPTNO, DNAME FROM EMP E NATURAL JOIN DEPT D WHERE ENAME = 'KING' *ERROR at line 1:ORA-25155: column used in NATURAL join cannot have qualifier
별칭을 빼면 둘 다 실행된다. 공통 열이 아닌 E.ENAME 에는 별칭을 붙여도 된다.
SELECT DEPTNO, DNAME FROM EMP E JOIN DEPT D USING (DEPTNO) WHERE E.ENAME = 'KING';
=·> 같은 단일행 연산자 뒤의 서브쿼리는 한 행 이하를 돌려줘야 한다. ANALYST 두 명은 급여가 둘 다 3000 이지만 행이 둘이다.
SELECT ENAME, SAL FROM EMP WHERE SAL = (SELECT SAL FROM EMP WHERE JOB = 'ANALYST');
SELECT ENAME, SAL FROM EMP WHERE SAL = (SELECT SAL FROM EMP WHERE JOB = 'ANALYST') *ERROR at line 1:ORA-01427: single-row subquery returns more than one row
값이 같아도 행 수로 판단한다. 실행해 봐야 행 수가 정해지므로 파싱 오류가 아니라 실행 중 오류다.
서브쿼리가 0행이면 오류가 아니다. 비교 대상이 NULL 이 되어 결과가 0행이다. WHERE SAL = (SELECT SAL FROM EMP WHERE DEPTNO = 40) 의 COUNT(*) 는 0 이었다.
여러 행을 받으려면 IN 을 쓴다. SAL IN (SELECT SAL FROM EMP WHERE JOB = 'ANALYST') 는 FORD·SCOTT 두 행이다.
SELECT ENAME FROM EMP WHERE ENAME LIKE '_A%' ORDER BY ENAME;
ENAME----------JAMESMARTINWARD
_ 자체를 찾으려면 ESCAPE 로 지정한 문자를 앞에 붙여 와일드카드 뜻을 끈다. 세 값 'A_B', 'AXB', 'AB' 에 두 패턴을 걸어 본다.
SELECT V FROM (SELECT 'A_B' AS V FROM DUAL UNION ALL SELECT 'AXB' FROM DUAL UNION ALL SELECT 'AB' FROM DUAL) WHERE V LIKE 'A_B' ORDER BY V;
V---AXBA_B
SELECT V FROM (SELECT 'A_B' AS V FROM DUAL UNION ALL SELECT 'AXB' FROM DUAL UNION ALL SELECT 'AB' FROM DUAL) WHERE V LIKE 'A\_B' ESCAPE '\' ORDER BY V;
V---A_B
패턴
'A_B'
'AXB'
'AB'
이유
'A_B'
O
O
X
_ 는 아무 한 글자. 'AB' 는 가운데 글자가 없다
'A\_B' ESCAPE '\'
O
X
X
\_ 는 밑줄 문자 그 자체
'A%B'
O
O
O
% 는 0글자도 된다
ESCAPE 문자는 \ 로 정해져 있지 않다. ESCAPE '#' 이면 'A#_B' 로 쓴다. ESCAPE 절이 없으면 \ 도 그냥 한 글자다.
내일은 같은 SQL 이 Oracle 과 SQL Server 에서 어떻게 달라지는지(문자열 연결, Top N, 날짜 함수 등)를 짝지어 정리한다.