과목 Ⅱ. 데이터모델링의 이해
2장 SQL 활용
1절 조인(Join)
1. 조인
1-1) EQUI 조인
: 두 테이블 간 일치하는 것을 조인
#EQUI 조인 SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
조인문에 조건 및 정렬을 추가할 수 있음
#EQUI 조인하면서 조건문 SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO #부서번호를 기준으로 조인하고 AND EMP.ENAME LIKE '%시%' #직원이름 중 '시'가 포함되는 사람을 조회하는데 ORDER BY SAL #조회할 때는 연봉을 기준으로 오름차순 정렬해서 조회해주세요 ;
※ 해시조인은 EQUI조인(동등조인)일 때에만 사용가능하다.
※ HASH 조인에 관한 설명
- 먼저 선행 테이블을 결정하고 선행 테이블에서 주어진 조건에 해당하는 행 선택
- 해당 행이 선택되면 조인 키를 기준으로 해시함수를 사용해서 해시 테이블을 메인 메모리에 생성하고 후행 테이블에서 주어진 조건에 만족하는 행을 찾음
- 후행 테이블의 조인키를 사용해서 해시 함수를 적용하여 해당 버킷을 검색
※ 조인 조건과 조회 조건은 서로 분리되어야 함 (조회 조건은 WHERE 절로 분리)
1-2) INNER JOIN
: EQUI JOIN과 같은데 INNER JOIN은 ON문을 사용
#INNER JOIN SELECT * FROM EMP INNER JOIN DEPT #EMP테이블과 DEPT테이블을 조인해라 ON EMP.DEPTNO = DEPT.DEPTNO; #DEPTNO칼럼을 기준으로
마찬가지로 조인문에 조건과 정렬을 추가해줄 수 있음
#INNER JOIN에 조건+정렬 SELECT * FROM EMP INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO #부서번호로 조인하면서 AND EMP.ENAME LIKE '%김%' #사원명에 '김'이 들어가는 사람을 ORDER BY SAL DESC #연봉을 기준으로 오름차순으로 정렬해주세요 ;
1-3) INTERSECT 연산 (MySQL에서는 지원 안 함)
: 두 테이블에서 교집합(공통된 값 조회)
#INTERSECT 연산 (Oracle 가능) SELECT DEPTNO FROM EMP INTERSECT SELECT DEPTNO FROM DEPT;
MySQL에서는 이 연산을 지원하지 않으므로 조인 연산을 활용해야 한다.
#MySQL에서 INTERSECT 연산하기 (MySQL) SELECT distinct a.deptno FROM EMP a INNER JOIN DEPT b ON a.deptno = b.deptno;
2. NON-EQUI (비등가)조인
: 두 테이블을 조인할 때, "="을 사용하지 않고 >, <, >=, <= 등을 사용한다.
즉, 일치하지 않는 것을 조인하는 것이 비등가조인이다.
3. OUTER JOIN
: 두 테이블의 교집합을 조회하고(EQUI조인), 한쪽 테이블에만 있는 데이터도 포함하여 조회하는 것
- LEFT OUTER JOIN : 왼쪽 테이블만 OUTER JOIN
- RIGHT OUTER JOIN : 오른쪽 테이블만 OUTER JOIN
- FULL OUTER JOIN : 양쪽 테이블 모두 조인 (MYSQL은 불가능)
#LEFT OUTER JOIN SELECT * FROM emp LEFT OUTER JOIN dept ON EMP.DEPTNO = DEPT.DEPTNO; #RIGHT OUTER JOIN SELECT * FROM emp RIGHT OUTER JOIN dept ON EMP.DEPTNO = DEPT.DEPTNO; #FULL OUTER JOIN (MySQL에서는 불가능) SELECT * FROM emp FULL OUTER JOIN dept ON EMP.DEPTNO = DEPT.DEPTNO; #MySQL에서 FULL OUTER JOIN을 하려면 UNION 활용 SELECT * FROM emp LEFT OUTER JOIN dept ON EMP.DEPTNO = DEPT.DEPTNO UNION SELECT * FROM emp RIGHT OUTER JOIN dept ON EMP.DEPTNO = DEPT.DEPTNO;
(1) LEFT OUTER JOIN
(2) RIGHT OUTER JOIN
(3) FULL OUTER JOIN
※ Oracle에서는 OUTER JOIN을 "(+)" 기호를 사용해 수행할 수 있다.
※ (+) 기호는 표현식의 한 편에만 올 수 있으며, 조인시킬 값이 없을 수도 있는 쪽에 추가한다.
#Oracle에서 (+)기호를 사용해 OUTER JOIN을 수행하는 방법 SELECT * FROM EMP, DEPT WHER EMP.DEPTNO (+)= DEPT.DEPTNO; #(+)가 붙은 쪽의 데이터가 없을 수도 있다. 즉, 여기서는 RIGHT OUTER JOIN (DEPT)이 되는 셈
4. CROSS JOIN
: 두 테이블을 조인 조건 없이 하나로 조인 (기준없이 두 테이블의 각 행끼리의 조합을 만듦)
- 조인 조건이 없어서 카테시안 곱이 발생한다.
ex) emp테이블은 10행이고 dept테이블은 4행일 때, CROSS JOIN을 하면 카테시안곱이 발생해 10*4 = 40 행이 조회됨
※ 근데 CROSS JOIN을 수행할 때에도 만약 조인 조건을 집어넣어주면 등가 조인이 된다. (INNER JOIN과 같은 결과)
#CROSS JOIN SELECT * FROM EMP CROSS JOIN DEPT; #모든 행을 보기는 귀찮아서 COUNT로 집계만 해주었다. SELECT COUNT(*) FROM EMP CROSS JOIN DEPT;
5. NATURAL JOIN
: 테이블 간 동일한 이름을 가진 칼럼을 모두 조인 -> WHERE문을 활용한 조인 조건 설정 불가능
- NATURAL JOIN은 별칭 사용 불가능
6. SELF JOIN
: 동일한 테이블 사이의 조인 -> FROM절에 동일 테이블이 두번이상 나타난다.
- SELF JOIN은 테이블이 동일하기 때문에 꼭 별칭을 사용해서 식별해주어야 한다.
5. UNION을 사용한 합집합 구현
5-1. UNION
- UNION 연산은 두 테이블을 하나로 만드는 연산
- UNION 연산은 두 테이블을 합치면서 중복된 데이터를 제거함 -> ∴ SORT(정렬) 과정 발생
※ 두 테이블의 칼럼 수, 칼럼의 데이터 형식 모두 일치해야 함
#UNION 연산 SELECT * FROM EMP UNION SELECT * FROM EMP;
귀찮으므로 굳이 같은 형식의 다른 테이블을 만들어 실제로 합집합이 되는지 확인하지는 않겠다.
5-2. UNION ALL
- UNION ALL 연산은 중복을 제거하지 않는다. -> ∴ 정렬 과정 역시 발생하지 않는다.
#UNION ALL 연산 SELECT * FROM EMP UNION ALL SELECT * FROM EMP;
- UNION과 UNION ALL 연산의 차이를 꼭 기억해두자! : 중복제거
6. MINUS 연산 (Oracle 기준)
- 차집합을 조회하는 연산이다.
- MS-SQL에서는 MINUS 대신 EXCEPT를 사용한다.
- MySQL에서는 역시 MINUS 연산이 없어 is null 조건을 사용해 직접 만들어서 써야 한다.
#MINUS 연산 (Oracle 기준) select deptno from emp minus select deptno from dept;
위의 명령을 실행하면 EMP테이블에만 있는 DEPTNO가 출력된다.
내가 만든 테이블에서는 DEPTNO가 50이 출력되어야 한다.
이를 MySQL에서 실행하려면,
#MINUS 연산 MySQL에서 만들기 SELECT EMP.DEPTNO FROM EMP LEFT OUTER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO WHERE DEPT.DEPTNO IS NULL;
이렇게 MINUS 연산을 힘들게 만들 수 있다.
2절 계층형 조회(CONNECT BY) - ORACLE
- 계층형(트리형) 조회는 Oracle에서 지원하는 기능이다.
- 기본적으로 트리 형태 구조를 위(ROOT NODE)에서 아래(하위 노드) 방향(순방향)으로 조회하는 것이고, 아래-> 위(역방향) 조회도 가능하다.
- CONNECT BY 는 트리 형태의 구조로 질의를 수행하는 것으로 START WITH구는 시작 조건을 의미하고 CONNECT BY PRIOR는 조인 조건이다.
- MAX(LEVEL) 함수로 트리 구조의 계층(LEVEL)수를 구할 수 있다.
정리하자면 사용 가능한 조건은
- START WITH : 시작 조건
<CONNECT BY 키워드>
| 키워드 | 설명 |
| LEVEL | 검색 항목의 깊이를 의미 (최상위 레벨 = 1) |
| CONNECT_BY_ROOT | 최상위 계층(트리)의 값 표시 (최상위 값이 맞으면 1, 아니면 0 반환) |
| CONNECT_BY_ISLEAF | 최하위 계층 표시 (최하위 값이 맞으면 1, 아니면 0 반환) |
| SYS_CONNECT_BY_PATH | 계층 구조의 전체 전개 경로 표시 |
| NOCYCLE | 순환구조가 발생하는 발생지점까지만 전개 |
| CONNECT_BY_ISCYCLE | 순환구조 발생지점 표시 - 전개 과정에서 자식을 갖는데 해당 데이터가 조상으로도 존재하면 1, 아니면 0 반환 |
| ORDER SIBLINGS BY | 형제 노드 사이에서 정렬을 지정 |
3절 서브쿼리(SUBQUERY)
1. Main query와 Subquery
- 메인쿼리 : 서브쿼리 밖의 SELECT문
- 서브쿼리 : SELECT문 내에 괄호를 사용해 SELECT문을 다시 사용하는 SQL문
- 인라인 뷰 : FROM구에 사용하는 서브쿼리
- 스칼라 서브쿼리 : 반드시 한 행과 한 칼럼만 반환하는 서브쿼리
- 연관 서브쿼리 : 서브쿼리 내에서 메인쿼리 내의 칼럼을 사용하는 것
- 서브쿼리 : WHERE구에 사용하는 서브쿼리
※ 서브쿼리도 비교연산자 사용 가능
2. 단일 행 서브쿼리와 다중 행 서브쿼리
- 단일 행 서브쿼리 : 서브쿼리 실행 결과가 반드시 한 행만 조회되는 것 -- ex) =, <, >, <=, >=, <>
- 다중 행 서브쿼리 : 서브쿼리 실행 결과가 여러 행으로 조회되는 것 -- ex) IN, ANY, ALL, EXISTS
3. 다중 행 서브쿼리
| 다중 행 연산 | 설명 |
| IN(서브쿼리) | 메인쿼리와 비교조건이 서브쿼리의 결과 중 하나만 동일하면 참 |
| ALL(서브쿼리) | 메인쿼리와 서브쿼리 결과가 모두 동일하면 참 |
| ANY(서브쿼리) | 메인쿼리와 비교조건이 서브쿼리의 결과 중 하나 이상 동일하면 참 |
| EXISTS(서브쿼리) | 메인쿼리와 서브쿼리의 결과가 하나라도 존재하면 참 |
#다중 행 서브쿼리 #IN문 SELECT ENAME, DNAME, SAL FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.EMPNO #메인쿼리 : 부서번호로 조인했을때 사원번호와, 부서명, 급여 조회 IN(SELECT EMPNO FROM EMP WHERE SAL>2000); #서브쿼리 : 급여가 2000이 넘는 사원의 사번 #IN문에 해당되는 결과가 하나만 있어도 조회가 되는 것이므로 서브쿼리를 만족하는 모든 사번이 조회될 것이다.
#ALL문 SELECT * FROM EMP WHERE DEPTNO < ALL (select deptno from emp where ename = '황시목'); #황시목이라는 사원의 부서번호보다 작은 부서번호를 가진 사원의 데이터가 모두 조회된다.
#EXISTS문 SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND EXISTS(SELECT DEPTNO FROM EMP WHERE SAL>4000); #급여가 4000이 넘는 사원이 존재하면 참이 되어서 메인쿼리에서 조인된 모든 사원을 조회함
4. 스칼라 서브쿼리
: 반드시 한 행과 한 칼럼만 반환되는 서브쿼리
#스칼라 서브쿼리 SELECT ENAME, (SELECT AVG(SAL) FROM EMP) #얘가 바로 한 행, 한 칼럼으로 나타나는 스칼라 서브쿼리 AS '급여' FROM EMP WHERE EMPNO = 100;
5. (상호)연관 서브쿼리(Correlated Subquery)
: 서브쿼리 내에서 메인쿼리 내의 칼럼을 사용하는 것
#연관 서브쿼리 select * from emp a where a.deptno = (select deptno from dept b where b.deptno = a.deptno); #서브쿼리 내에 메인쿼리의 칼럼인 a.deptno를 끌어와서 사용 == 연관 서브쿼리
이번엔 조인으로 등가조인, inner join, outer join, cross join과 union, union all, intersect, minus연산, 계층형 조회, 메인쿼리와 서브쿼리를 구분하면서 in, any, all, exists문 그리고 각종 서브쿼리를 알아보았다.
다음으로는 정말 자주 출제되는 그룹함수, 순위함수를 비롯한 다양한 함수와 파티션을 공부해보자.
