문과생이 공대에서 살아남기

1. JOIN의 개요

두 개 이상의 테이블 들을 연결 또는 결합하여 데이터를 출력하는 것

PK, FK와 연관되어 있거나 혹은 없어도 논리적 값 연결로도 가능

여러 테이블과 나열이 되어도 처리할 때는 단 두개의 집합 간에만 조인이 일어남

원하는 조인 테이블 개수 - 1 은 이후 집합간의 조인 갯수

2. EQUI JOIN

등가조인, 서로 정확하게 일치하는 값을 가진 경우, 관계형 데이터베이스의 큰 장점

대부분 PK, FK 관계로 이루어짐 (모든것은 아님)

SELECT 테이블1.칼럼명, 테이블2.칼럼명, ...

FROM 테이블1, 테이블2

WHERE 테이블1.칼럼명1 = 테이블2.칼럼명2; → WHERE 절에 JOIN 조건을 넣는다

SELECT 테이블1.칼럼명, 테이블2.칼럼명, ...

FROM 테이블1 INNER JOIN 테이블2

ON 테이블1.칼럼명1 = 테이블2.칼럼명2; → ON 절에 JOIN 조건을 넣는다.

SELECT 구문에서 유일한 이름을 가진 컬럼이라면 테이블명을 붙이지않아도 되지만 두 테이블에 동일한 칼럼명이 있다면 지정해주어야 함

또한 개발자나 사용자의 가독성이나 유지보수성을 높이는 효과, 오류를 방지하고 일관성을 위해 테이블명을 지정해주는 습관 필요

N개의 테이블로부터 필요한 데이터를 조회하기 위한 JOIN조건은 N-1개 필요함

테이블 명이 길어 SELECT 구문이 길어짐으로 FROM절의 테이블에도 별명을 사용할 수 있음

별명 사용시에는 SELECT, WHERE절에서는 별명을 사용해야 함

3. NON EQUI JOIN

비등가조인, 서로 값이 일치하지 않는 경우 >, < , >=, <= , BETWEEN 연산자 사용(모델에 따라 불가능할 수 있음)

SELECT 테이블1.칼럼명, 테이블2.칼럼명, ...

FROM 테이블1, 테이블2

WHERE 테이블1.칼럼명1 BETWEEN 테이블2.칼럼명1 AND 테이블2.칼럼명2;

4. 3개 이상 TABEL JOIN

예제)SELECT P.PLAYER_NAME 선수명, P.POSITION 포지션, T.REGION_NAME 연고지, T.TEAM_NAME 팀명, S.STADIUM_NAME 구장명

FROM PLAYER P, TEAM T, STADIUM S

WHERE P.TEAM_ID = T.TEAM_ID AND T.STADIUM_ID = S.STADIUM_ID

ORDER BY 선수명;

또는 INNER JOIN을 명시하여 사용할 수도 있다.

SELECT P.PLAYER_NAME 선수명, P.POSITION 포지션, T.REGION_NAME 연고지, T.TEAM_NAME 팀명, S.STADIUM_NAME 구장명

FROM PLAYER P INNER JOIN TEAM T

ON P.TEAM_ID = T.TEAM_ID

INNER JOIN STADIUM S

ON T.STADIUM_ID = S.STADIUM_ID

ORDER BY 선수명;

1. ORDER BY 정렬

칼럼명, SELECT의 별명, 또는 순서로 정리 (혼용사용도 가능)

SELECT 칼럼명 [ALIAS명]

FROM 테이블명

[WHERE 조건식]

[GROUP BY 칼럼(Column)이나 표현식]

[HAVING 그룹조건식]

[ORDER BY 칼럼(Column)이나 표현식 [ASC 또는 DESC]] ;

ASC(Ascending) : 조회한 데이터를 오름차순으로 정렬한다.(기본 값이므로 생략 가능)

DESC(Descending) : 조회한 데이터를 내림차순으로 정렬한다.

ORACLE       : 내림차순에서는 NULL값이 앞에 출력, 오름차순에서는 NULL값이 뒤에 출력

SQL SERVER : 내림차순에는 NULL값이 뒤에 출력, 오름차순에는 NULL값이 앞에 출력

기본적인 정렬 순서는 오름차순 (숫자형일 경우는 가장 작은 값부터, 날짜형은 가장 빠른 날짜부터)

2. SELECT 문장의 실행 순서

5. SELECT 칼럼명 [ALIAS명]

1. FROM 테이블명

2. WHERE 조건식

3. GROUP BY 칼럼(Column)이나 표현식

4. HAVING 그룹조건식

6. ORDER BY 칼럼(Column)이나 표현식;

1. 발췌 대상 테이블을 참조한다. (FROM)

2. 발췌 대상 데이터가 아닌 것은 제거한다. (WHERE)

3. 행들을 소그룹화 한다. (GROUP BY)

4. 그룹핑된 값의 조건에 맞는 것만을 출력한다. (HAVING)

5. 데이터 값을 출력/계산한다. (SELECT)

6. 데이터를 정렬한다. (ORDER BY)

ORDER BY 절에서는 SELECT 목록에 없는 문자형 항목 포함 가능

단, SELECT DISTINCT를 지정하더거나 GROUP BY 절, UNION 연산자 있다면 SELECT 목록에 표시 되어야함

예제) SELECT EMPNO, ENAME FROM EMP ORDER BY MGR;

인라인 뷰의 SELECT 절에서 정의한 칼럼은 메인쿼리에서도 사용 가능 그러나 서브쿼리 범위를 벗어나면 더 이상 사용 불가

GROUP BY 이후 SELECT나 ORDER BY 절에서는 개별 데이터 사용 불가

SELECT 절에서 정의하지 않은 MAX, SUM, COUNT 집계 함수도 ORDER BY에서는 사용 가능

예제) SELECT JOB

FROM EMP

GROUP BY JOB

HAVING COUNT(*) > 0

ORDER BY MAX(EMPNO), MAX(MGR), SUM(SAL), COUNT(DEPTNO), MAX(HIREDATE);

3. TOP N 쿼리

가. ROWNUM

ORACLE 순위가 높은 N개의 로우를 추출하기 위해 WHERE절에서 ROWNUM 함수 사용

단, 원하는 데이터를 원할 순 없음 데이터 일부가 먼저 추출된 후 정렬작업이 일어남, ORDER BY절 효과가 없음

인라인 뷰로 먼저 데이터 정렬 후 사용 가능, 인덱스로 추출가능

나. TOP ( )

SQL SERVER는 TOP 조건을 사용하여 사용 가능

TOP (Expression) [PERCENT] [WITH TIES]

WITH TIES : 마지막 행으로 표시되는 추가 행의 데이터가 같을 경우 N+ 동일 정렬 순서를 추가 반환 하도록 지정된 옵션

1. 집계함수

여러행들의 그룹이 모여 그룹당 단 하나의 결과를 돌려줌

GROUP BY는 행들을 소그룹화함

Select, having, order by 절에서 사용가능

2. GROUP BY 절

SELECT [DISTINCT] 칼럼명 [ALIAS명]

FROM 테이블명

[WHERE 조건식]

[GROUP BY 칼럼(Column)이나 표현식]

[HAVING 그룹조건식] ;

GROUP BY절에서 소그룹화 후 SELECT절에서 집계함수 사용

NULL값 제외하고 행 수행

GROUP BY절에서는 별명 사용 불가

집계함수는 WHERE절에는 사용 불가

WHERE절은 전체데이터를 GROUP하기 전에 미리 제거

HAVING은 집계함수를 이용한 조건 표시

3. HAVING 절

WHERE절과 비슷하지만 그룹을 나타낸다는 결과 집합의 행에 조건이 적용된다는 점에서 차이가 있음

GROUP BY와 위치를 바꾸어도 문제가 생기진 않으나, 순서를 지키는 것을 권고

WHRER절에서 조건을 적용하여 데이터만 추출후 GROUP BY도 가능, 이 방법이 데이터 소모를 덜함

SELECT 절에 사용되지 않은 칼럼이나 집계함수가 아니더라고 

GROUP BY절의 기준항목이나 소그룹의 집계함수를 HAVING절에서 사용가능

HAVING절의 조건변경은 결과 데이터 변경은 없고 출력되는 레코드 갯수만 변경

예제) SELECT POSITION 포지션, ROUND(AVG(HEIGHT),2) 평균키

 FROM PLAYER

 GROUP BY POSITION

 HAVING MAX(HEIGHT) >= 190;

4. CASE 표현을 활용한 월별 데이터 집계

모델링의 제 1정규화로 인해 반복되는 칼럼의 경우 구분 칼럼을 두고 여러 개의 레코드로 만들어진 집합을

정해진 칼럼수만큼 확장해서 집계 보고서를 만드는 유용한 기법

STEP 1. 개별데이터 확인

STEP 2. 월별 데이터 구분 : SIMPLE CASE EXPRESSION을 통해 12월별 칼럼 구분

STEP 3. 부서별 데이터 집계 : 평균급여를 GROUP BY로 AVG하여 집계

DECODE 함수 가능

5. 집계함수와 NULL

NVL/ISNULL을 사용하여 빈칸을 0값으로 변경하는 것이 다중행 함수 안에서는 사용할 필요없음

결과값을 낼 때, NULL값을 제외하고 구함