[SQLD] (2)SQL 기본 및 활용- 제2장 SQL 활용

[SQLD] (2)SQL 기본 및 활용- 제2장 SQL 활용

제2장 SQL 활용

제1절 표준 조인

https://dataonair.or.kr/db-tech-reference/d-guide/sql/?pageid=4&mod;=document&uid;=346

1. STANDARD SQL 개요 1970년: Dr. E.F.Codd 관계형 DBMS(Relational DB) 논문 발표 1974년: IBM SQL 개발 1979년: Oracle 상용 DBMS 발표 1980년: Sybase SQL Server 발표 (이후 Sybase ASE로 개명) 1983년: IBM DB2 발표 1986년: ANSI/ISO SQ

순수 관계 연산자와 SQL 문장 비교

SELECT 연산은 WHERE절로 구현

PROJECT 연산은 SELECT 절로 구현

(NATURAL) JOIN 연산은 다양한 JOIN 기능으로 구현

DIVIDE 연산은 현재 사용되지 않음

ANSI/ISO SQL에서 표시하는 FROM 절의 JOIN 형태

INNER JOIN, NATURAL JOIN, USING 조건절, ON 조건절, CROSS JOIN, OUTER JOIN(LEFT, RIGHT, FULL)

올바르게 작성된 SQL

SELECT C.컨텐츠ID, C.컨텐츠명 FROM 고객 A INNER JOIN 추천콘텐츠 B ON (A.고객ID=B.고객ID) INNER JOIN 컨텐츠 C ON (B.컨텐츠ID=C.컨텐츠ID) LEFT OUTER JOIN 비선호컨텐츠 D ON (B.고객ID=D.고객ID AND B.컨텐츠ID=D.컨텐츠ID) WHERE A.고객ID=#custID# AND B.추천대상일자=TO_CHAR(SYSDATE, 'YYYY.MM.DD') AND D.컨텐츠 IS NULL; SELECT C.컨텐츠ID, C.컨텐츠명 FROM 고객 A INNER JOIN 추천콘텐츠 B ON (A.고객ID=#custID# AND A.고객ID=B.고객ID) INNER JOIN 컨텐츠 C ON (B.컨텐츠ID=C.컨텐츠ID) LEFT OUTER JOIN 비선호컨텐츠 D ON (B.컨텐츠ID=C.컨텐츠ID) WHERE B.추천대상일자=TO_CHAR(SYSDATE, 'YYYY.MM.DD') AND NOT EXISTS(SELECT X.컨텐츠ID FROM 비선호컨텐츠 X WHERE X.고객ID=B.고객ID AND X.컨텐츠ID=B.컨텐츠ID);​

JOIN의 종류

INNER JOIN : OUTER(외부) 조인과 대비하여 내부 조인이라고 하며 조인 조건에서 동일한 값이 있는 행만 반환한다.

CROSS JOIN : 테이블 간 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다. 결과는 양쪽 집합의 M*N건의 데이터 조합

LEFT OUTER JOIN : 조인 수행시 먼저 표기된 좌측 테이블에 해당하는 데이터를 먼저 읽은 후, 나중 표기된 우측 테이블에서 조인 대상 데이터를 읽어온다. 즉, TABLE A와 B가 있을때(TABLE A 기준) A와 B를 비교해서 B의 조인 칼럼에서 같은 값이 있을 때 그 해당 데이터를 가져오고, B의 조인 칼럼에서 같은 값이 없는 경우에는 B테이블에서 가져오는 칼럼들은 NULL값으로 채운다.

FULL OUTER JOIN : 조인 수행시 좌측, 우측 테이블의 모든 데이터를 읽어 조인하여 결과를 생성한다. 즉, TABLE A와 B가 있을때(TABLE A와 B 둘다 기준 ) RIGHT OUTER JOIN과 LEFT OUTER JOIN 결과를 합집합으로 처리한 결과와 동일하다.

OUTER JOIN 문장 예시

LEFT OUTER JOIN

SELECT X.KEY1, Y.KEY2 FROM TAB1 X LEFT OUTER JOIN TAB2 Y ON (X.KEY1=Y.KEY2)

RIGHT OUTER JOIN

SELECT X.KEY1, Y.KEY2 FROM TAB1 X RIGHT OUTER JOIN TAB2 Y ON (X.KEY1=Y.KEY2)

FULL OUTER JOIN

SELECT X.KEY1, Y.KEY2 FROM TAB1 X FULL OUTER JOIN TAB2 Y ON (X.KEY1=Y.KEY2)

아래 생산설비를 위한 데이터 모델 설명

제품, 생산제품, 생산라인 엔터티를 INNER JOIN 하기 위해서 생산제품 엔터티는 WHERE절에 최소 2번이 나타나야 함

제품과 생산라인 엔터티를 Join시 적절한 Join 조건이 없으므로 카티시안 곱이 발생한다.

제품과 생산라인 엔터티에는 생산제품과 대응되지 않는 레코드가 있을 수 있다.

특정 생산라인에서 생산되는 제품의 제품명을 알기위해서는 제품과 생산제품까지 2개의 엔터티만을 Inner Join 하면 된다.

구매 이력이 있는 고객 중 구매 횟수가 3회 이상인 고객의 이름과 등급을 출력하시오.

ㄱ: INNER JOIN 구매정보 B ON A.고객번호=B.고객번호

ㄴ: HAVING COUNT(B.구매번호) >= 3

시간대별사용량 테이블 기반으로 고객별 사용금액을 추출하는 SQL

SELECT A.고객ID, A.고객명, SUM(B.사용량*C.단가) AS 사용금액 FROM 고객 A INNER JOIN 시간대별사용량 B ON (A.고객ID=B.고객ID) INNER JOIN 시간대구간 C ON B.사용대시간 BETWEEN C.시작시간대 AND C.종료시간대 GROUP BY A.고객ID, A.고객명 ORDER BY A.고객ID, A.고객명;​

TEAM 테이블과 STADIUM 테이블의 관계를 이용해서 소속팀이 가지고 잇는 전용구장의 정보를 팀의 정보와 함께 출력하는 SQL

SELECT T.REGION_NAME, T.TEAM_NAME, STADIUM_ID, S.STADIUM NAME FROM TEAM T INNER JOIN STADIUM S USING(STADIUM_ID); SELECT TEAM.REGION_NAME, TEAM.TEAM_NAME, TEAM.STADIUM_ID, STADIUM.STADIUM NAME FROM TEAM INNER JOIN STADIUM ON(TEAM.STADIUM_ID=STADIUM.STADIUM_ID); SELECT T.REGION_NAME, T.TEAM_NAME, T.STADIUM_ID, S.STADIUM NAME FROM TEAM T, STADIUM S WHERE T.STADIUM_ID=S.STADIUM_ID; SELECT TEAM.REGION_NAME, TEAM.TEAM_NAME, TEAM.STADIUM_ID, STADIUM.STADIUM NAME FROM TEAM, STADIUM WHERE TEAM.STADIUM_ID=STADIUM.STADIUM_ID;

Cartesian Product를 만들기 위한 SQL 문장

SELECT ENAME, DNAME FROM EMP, DEPT ORDER BY ENAME; = SELECT ENAME, DNAME FROM EMP CROSS JOIN DEPT ORDER BY ENAME; CROSS JOIN은 일반 집합 연산자의 PRODUCT의 개념으로 테이블 간 조인 조건이 없는 경우 생길 수 있는 모든 데이터의 집합

SQL 문장 수행결과

고객번호 고객명 단말기ID 단말기명 OSID OS명 11000 홍길동 1000 A1000 100 Android 12000 강감찬 NULL NULL NULL NULL 13000 이순신 NULL NULL NULL NULL 14000 안중근 NULL NULL NULL NULL 15000 고길동 NULL NULL NULL NULL 16000 이대로 NULL NULL NULL NULL

(1), (2), (3)의 SQL에서 실행결과가 같은 것은?

1,2,3

EMP, DEPT 테이블을 LEFT, FULL, RIGHT 외부조인하면 생성되는 결과 건수는?

3건, 5건, 4건

신규 부서의 경우 일시적으로 사원이 없는 겨우도 있다 가정하고 DEPT와 EMP를 조인하되 사원이 없는 부서의 정보도 같이 출력하고자 할때의 SQL

SELECT E.ENAME, D.DEPTNO, D.DNAME FROM DEPT D LEFT JOIN(또는 LEFT OUTER JOIN) EMP E ON D.DEPTNO = E.DEPTNO;

SQL 수행 결과

C1 C2 C1 C2 A 1 B 2 B 2 C 3 C 3 D 4 E 5 outer 조인에서 ON절은 조인할 대상을 결정한다. 그러나 기준 테이블은 항상 모두 표시된다.

결과 전에 대한 필터링은 WHERE절에서 수행된다.

아래의 SQL을 ANSI 표준 구문으로 변경

SELECT A.게시판, A.게시판명, COUNT(B.게시글ID) AS CNT FROM 게시판 A LEFT OUTER JOIN 게시글 B ON (A.게시판ID=B.게시판ID AND B.삭제여부='N') WHERE A.사용여부='Y' GROUP BY A.게시판ID, A.게시판명 ORDER BY A.게시판ID; 오라클에서는 OUTER JOIN 구문을 (+) 기호를 사용하여 처리 할 수 있으며 이를 ANSI 문장으로 변경하기 위해서는 Inner 쪽 테이블(게시글)에 조건절을 ON절 위치시켜야 정상적인 OUTER JOIN을 수행할 수 있다.

from http://lindsayan.tistory.com/49 by ccl(A) rewrite - 2021-11-10 13:26:31