-
007. SEQUENCE 와 ROW_NUMBER, JOIN(SQL 1992/1999 CODE), UNION / UNION ALLSsY/Class 2023. 3. 30. 17:52728x90
2023.3.30 (목)
SCOTT 계정 실습
- 어제 시퀀스/게시판 부터 이어서
- SEQUENCE 와 ROW_NUMBER()
- 데이터 입력 (게시물 작성)
더보기SELECT USER FROM DUAL; --==>> SCOTT ------------------------------------------------------------------------------------------- CREATE SEQUENCE SEQ_BOARD START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCACHE; --> //기본 구문 외에 붙는것은 모두 옵션이라고 한다. // 테이블과는 별개다 --==>> Sequence SEQ_BOARD이(가) 생성되었습니다. ------------------------------------------------------------------------------------------- --○ 테이블 생성(TBL_BOARD) // 사용자가 작성하는지 CREATE TABLE TBL_BOARD -- TBL_BOARD 이름의 테이블 생성 → 게시판으로 활용 ( NO NUMBER -- 게시물 번호 X , TITLE VARCHAR2(50) -- 게시물 제목 ○ , CONTENTS VARCHAR2(2000) -- 게시물 내용 ○ , NAME VARCHAR2(30) -- 게시물 작성자 △ , PW VARCHAR2(40) -- 게시물 패스워드 △ , CREATED DATE DEFAULT SYSDATE -- 게시물 작성일 X ); --==>> Table TBL_BOARD이(가) 생성되었습니다. ------------------------------------------------------------------------------------------- --○ 데이터 입력 → 게시판에 게시물 작성 INSERT INTO TBL_BOARD VALUES (게시물번호, 게시물제목, 게시물내용, 작성자, 패스워드, 작성일); ------------------------------------------------------------------------------------------- INSERT INTO TBL_BOARD VALUES (SEQ_BOARD.NEXTVAL, '좋은아침입니다' , '화이팅합시다', 'ㄱㄱㅁ', 'java006$', DEFAULT); --==>> 1 행 이(가) 삽입되었습니다. INSERT INTO TBL_BOARD VALUES (SEQ_BOARD.NEXTVAL, '건강관리', '다들 건강 챙깁시다', 'ㄱㅎㅈ', 'java006$', DEFAULT); --==>> 1 행 이(가) 삽입되었습니다. INSERT INTO TBL_BOARD VALUES (SEQ_BOARD.NEXTVAL, '오늘은', '점심 뭐 먹지...', 'ㅁㅅㅎ', 'java006$', DEFAULT); --==>> 1 행 이(가) 삽입되었습니다. INSERT INTO TBL_BOARD VALUES (SEQ_BOARD.NEXTVAL, '술은', '평일에 마시지 말아야지', 'ㄱㅈㅎ', 'java006$', DEFAULT); --==>> 1 행 이(가) 삽입되었습니다. INSERT INTO TBL_BOARD VALUES (SEQ_BOARD.NEXTVAL, '오늘은', '많이 웃어야지', 'ㅈㅎㅈㅎ', 'java006$', DEFAULT); --==>> 1 행 이(가) 삽입되었습니다. INSERT INTO TBL_BOARD VALUES (SEQ_BOARD.NEXTVAL, '여러분', '오타조심하세요', 'ㅇㅎㅈ', 'java006$', DEFAULT); --==>> 1 행 이(가) 삽입되었습니다. INSERT INTO TBL_BOARD VALUES (SEQ_BOARD.NEXTVAL, '미세먼지', '오늘은 좀 화창하네', 'ㅇㅇㅅ', 'java006$', DEFAULT); --==>> 1 행 이(가) 삽입되었습니다. INSERT INTO TBL_BOARD VALUES (SEQ_BOARD.NEXTVAL, '지하철', '난 정말 지하철이 싫어요', 'ㅊㅎㄱ', 'java006$', DEFAULT); --==>> 1 행 이(가) 삽입되었습니다. INSERT INTO TBL_BOARD VALUES (SEQ_BOARD.NEXTVAL, '질문있습니다', '생각나면 다시 질문 할게요', 'ㄱㅂㅎ', 'java006$', DEFAULT); --==>> 1 행 이(가) 삽입되었습니다. INSERT INTO TBL_BOARD VALUES (SEQ_BOARD.NEXTVAL, '여기서는', '칠판이 잘 안보여요', 'ㅎㅇㅎ', 'java006$', DEFAULT); --==>> 1 행 이(가) 삽입되었습니다. ------------------------------------------------------------------------------------------- --○ 확인 SELECT * FROM TBL_BOARD; --==>> /* 1 좋은아침입니다 화이팅합시다 ㄱㄱㅁ java006$ 23/03/30 2 건강관리 다들 건강 챙깁시다 ㄱㅎㅈ java006$ 23/03/30 3 오늘은 점심 뭐 먹지... ㅁㅅㅎ java006$ 23/03/30 4 술은 평일에 마시지 말아야지 ㄱㅈㅎ java006$ 23/03/30 5 오늘은 많이 웃어야지 ㅈㅎㅈㅎ java006$ 23/03/30 6 여러분 오타조심하세요 ㅇㅎㅈ java006$ 23/03/30 7 미세먼지 오늘은 좀 화창하네 ㅇㅇㅅ java006$ 23/03/30 8 지하철 난 정말 지하철이 싫어요 ㅊㅎㄱ java006$ 23/03/30 9 질문있습니다 생각나면 다시 질문 할게요 ㄱㅂㅎ java006$ 23/03/30 10 여기서는 칠판이 잘 안보여요 ㅎㅇㅎ java006$ 23/03/30 */ ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'; --==>> Session이(가) 변경되었습니다. --○ 커밋 COMMIT; --==>> 커밋 완료.더보기※ SQL-DEVELOPER 에서 날짜 세션이 기본으로 나오는 형태를 변경
- 데이터 (게시물 삭제 & 작성)
더보기--○ 게시물 삭제 DELETE FROM TBL_BOARD WHERE NO=4; --==>> 1 행 이(가) 삭제되었습니다. --○ 게시물 작성 INSERT INTO TBL_BOARD VALUES (SEQ_BOARD.NEXTVAL, '저는요', '글자가 작은게 좋아요', 'ㅂㄱㅇ', 'java006$', DEFAULT); --==>> 1 행 이(가) 삽입되었습니다. --○ 게시물 삭제 DELETE FROM TBL_BOARD WHERE NO=8; --==>> 1 행 이(가) 삭제되었습니다. --○ 게시물 삭제 DELETE FROM TBL_BOARD WHERE NO=2; --==>> 1 행 이(가) 삭제되었습니다. --○ 게시물 작성 INSERT INTO TBL_BOARD VALUES (SEQ_BOARD.NEXTVAL, '이제는', '속이 아주 편해졌어요', 'ㅇㄱㅂ', 'java006$', DEFAULT); --==>> 1 행 이(가) 삽입되었습니다. --○ 커밋 COMMIT; --==>> 커밋 완료. --○ 확인 SELECT * FROM TBL_BOARD; --==>> /* 1 좋은아침입니다 화이팅합시다 ㄱㄱㅁ java006$ 2023-03-30 3 오늘은 점심 뭐 먹지... ㅁㅅㅎ java006$ 2023-03-30 5 오늘은 많이 웃어야지 ㅈㅎㅈㅎ java006$ 2023-03-30 6 여러분 오타조심하세요 ㅇㅎㅈ java006$ 2023-03-30 7 미세먼지 오늘은 좀 화창하네 ㅇㅇㅅ java006$ 2023-03-30 9 질문있습니다 생각나면 다시 질문 할게요 ㄱㅂㅎ java006$ 2023-03-30 10 여기서는 칠판이 잘 안보여요 ㅎㅇㅎ java006$ 2023-03-30 11 저는요 글자가 작은게 좋아요 ㅂㄱㅇ java006$ 2023-03-30 12 이제는 속이 아주 편해졌어요 ㅇㄱㅂ java006$ 2023-03-30 */
- 내부적으로 띄엄 띄엄 보이던 SEQUENCE() 는 그대로 두고
외부에서 보기에 편하게 ROW_NUMBER() 을 통해 다시 보여주는 방식
- 단, 요즘은 게시물 번호를 기억하여 추후에 다시 활용하는 사용자가 많아졌기 때문에 잘 사용하지 않게 됨
SELECT ROW_NUMBER() OVER(ORDER BY CREATED) "글번호" , TITLE "제목", NAME "작성자", CREATED "작성일" FROM TBL_BOARD ORDER BY 4 DESC; --==>> /* 글번호 제목 작성자 작성일 ---------- -------------------------------------------------- ------------------------------ ---------- 9 이제는 ㅇㄱㅂ 2023-03-30 8 저는요 ㅂㄱㅇ 2023-03-30 7 여기서는 ㅎㅇㅎ 2023-03-30 6 질문있습니다 ㄱㅂㅎ 2023-03-30 5 미세먼지 ㅇㅇㅅ 2023-03-30 4 여러분 ㅇㅎㅈ 2023-03-30 3 오늘은 ㅈㅎㅈㅎ 2023-03-30 2 오늘은 ㅁㅅㅎ 2023-03-30 1 좋은아침입니다 ㄱㄱㅁ 2023-03-30 */더보기- 해당 구문으로 조회를 한다면 이후에 작성, 삭제 하더라도 글 번호에 이가 빠지지 않게 보이게 된다.
--○ 게시물 작성 INSERT INTO TBL_BOARD VALUES (SEQ_BOARD.NEXTVAL, '오늘은', '숨기지 않았어요', 'ㅊㅇㅅ', 'java006$', DEFAULT); --==>> 1 행 이(가) 삽입되었습니다. SELECT ROW_NUMBER() OVER(ORDER BY CREATED) "글번호" , TITLE "제목", NAME "작성자", CREATED "작성일" FROM TBL_BOARD ORDER BY 4 DESC; --○ 게시물 삭제 DELETE FROM TBL_BOARD WHERE NO=7; --==>> 1 행 이(가) 삭제되었습니다. SELECT ROW_NUMBER() OVER(ORDER BY CREATED) "글번호" , TITLE "제목", NAME "작성자", CREATED "작성일" FROM TBL_BOARD ORDER BY 4 DESC; --==>> /* 9 오늘은 ㅊㅇㅅ 2023-03-30 8 이제는 ㅇㄱㅂ 2023-03-30 7 저는요 ㅂㄱㅇ 2023-03-30 6 여기서는 ㅎㅇㅎ 2023-03-30 5 질문있습니다 ㄱㅂㅎ 2023-03-30 4 여러분 ㅇㅎㅈ 2023-03-30 3 오늘은 ㅈㅎㅈㅎ 2023-03-30 2 오늘은 ㅁㅅㅎ 2023-03-30 1 좋은아침입니다 ㄱㄱㅁ 2023-03-30 */ --○ 커밋 COMMIT; --==>> 커밋 완료.
- JOIN(조인)
---■■■ JOIN(조인) ■■■-- SELECT * FROM EMP; SELECT * FROM DEPT; SELECT * FROM SALGRADE; -- 3개의 테이블에서 뽑아낸 7369 SMITH 사원의 정보 -- 7369 SMITH 800 1980-12-17 20 RESEARCH DALLAS GRADE1 --> 이런 데이터들을 연결을 시켜주려고 하는 문법이 JOIN -- 1992버전과 1999 버전이 있고 버전에 따라 장/단점이 다름
- 1. SQL 1992 CODE
- Equi Join
- Non-Equi Join
- Equi Join 수행 시 『+』 를 활용한 결합 방법
※ WHERE 조건절로 결합 조건을 명시
--1. SQL 1992 CODE SELECT * FROM EMP , DEPT; --// 2개의 테이블을 연결해서 한 번에 조회하겠다 --> 수학에서 말하는 데카르트 곱(Catersian Product) // (말하자면, 크로스조인) -- 두 테이블을 합친(결합한) 모든 경우의 수 // 그래서 이 방식은 잘 사용하지 않는다
- Equi Join
--Equi Join : 서로 정확히 일치하는 데이터들끼리 관계를 구성하여 연결시키는 결합 SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; SELECT * FROM EMP E, DEPT D --// 테이블에 ALIAS를 붙여 사용할 수 있다 WHERE E.DEPTNO = D.DEPTNO;
- Non-Equi Join
-- Non-Equi Join : 범위 안에 포함되는 적합한 데이터들끼리 연결시키는 결합 --// 범위안에 들어가는 데이터들은 정확히 일치하는 데이터에서 연결시킬 수 없기 때문에 SELECT * FROM SALGRADE; SELECT * FROM EMP; SELECT * FROM EMP E, SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL; --==>> /* 7369 SMITH CLERK 7902 1980-12-17 800 20 1 700 1200 7900 JAMES CLERK 7698 1981-12-03 950 30 1 700 1200 7876 ADAMS CLERK 7788 1987-07-13 1100 20 1 700 1200 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 2 1201 1400 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 2 1201 1400 7934 MILLER CLERK 7782 1982-01-23 1300 10 2 1201 1400 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 3 1401 2000 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 3 1401 2000 7782 CLARK MANAGER 7839 1981-06-09 2450 10 4 2001 3000 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 4 2001 3000 7566 JONES MANAGER 7839 1981-04-02 2975 20 4 2001 3000 7788 SCOTT ANALYST 7566 1987-07-13 3000 20 4 2001 3000 7902 FORD ANALYST 7566 1981-12-03 3000 20 4 2001 3000 7839 KING PRESIDENT 1981-11-17 5000 10 5 3001 9999 */
- Equi Join 수행 시 『+』 를 활용한 결합 방법
-- Equi Join 수행 시 『+』 를 활용한 결합 방법 SELECT * FROM TBL_EMP E, TBL_DEPT D WHERE E.DEPTNO = D.DEPTNO; --==>> /* 7369 SMITH CLERK 7902 1980-12-17 800 20 20 RESEARCH DALLAS 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 30 SALES CHICAGO 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 30 SALES CHICAGO 7566 JONES MANAGER 7839 1981-04-02 2975 20 20 RESEARCH DALLAS 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 30 SALES CHICAGO 7782 CLARK MANAGER 7839 1981-06-09 2450 10 10 ACCOUNTING NEW YORK 7788 SCOTT ANALYST 7566 1987-07-13 3000 20 20 RESEARCH DALLAS 7839 KING PRESIDENT 1981-11-17 5000 10 10 ACCOUNTING NEW YORK 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 30 SALES CHICAGO 7876 ADAMS CLERK 7788 1987-07-13 1100 20 20 RESEARCH DALLAS 7900 JAMES CLERK 7698 1981-12-03 950 30 30 SALES CHICAGO 7902 FORD ANALYST 7566 1981-12-03 3000 20 20 RESEARCH DALLAS 7934 MILLER CLERK 7782 1982-01-23 1300 10 10 ACCOUNTING NEW YORK */ --> 총 14건의 데이터가 결합되어 조회된 상황 -- 즉, 부서번호를 갖지 못한 사원들은(5인) 모두 누락 됨 SELECT * FROM TBL_EMP; SELECT * FROM TBL_EMP E, TBL_DEPT D WHERE E.DEPTNO = D.DEPTNO(+); --// + 가 붙어있지 않은쪽이 주인공! (+가 붙은건 첨가물~) // 주인공을 기준으로 두고 +가 되는 쪽을 가져다 붙임 --==>> /* 7934 MILLER CLERK 7782 1982-01-23 1300 10 10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 1981-11-17 5000 10 10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 1981-06-09 2450 10 10 ACCOUNTING NEW YORK 7902 FORD ANALYST 7566 1981-12-03 3000 20 20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 1987-07-13 1100 20 20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 1987-07-13 3000 20 20 RESEARCH DALLAS 7566 JONES MANAGER 7839 1981-04-02 2975 20 20 RESEARCH DALLAS 7369 SMITH CLERK 7902 1980-12-17 800 20 20 RESEARCH DALLAS 7900 JAMES CLERK 7698 1981-12-03 950 30 30 SALES CHICAGO 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 30 SALES CHICAGO 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 30 SALES CHICAGO 8005 ㅇㅎㅈ SALESMAN 7698 2023-03-28 1000 8004 ㅇㅇㅅ SALESMAN 7698 2023-03-28 2500 8003 ㅊㅎㄱ SALESMAN 7698 2023-03-28 2000 8002 ㅇㅅㅇ CLERK 7566 2023-03-28 1000 0 8001 ㅊㅇㅅ CLERK 7566 2023-03-28 1500 10 */ --> 총 19건의 데이터가 결합되어 조회된 상황 -- 즉, 부서번호를 갖지 못한 사원들(5인) 도 모두 조회된 상황! SELECT * FROM TBL_EMP E, TBL_DEPT D WHERE E.DEPTNO(+) = D.DEPTNO; --==>> /* 7369 SMITH CLERK 7902 1980-12-17 800 20 20 RESEARCH DALLAS 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 30 SALES CHICAGO 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 30 SALES CHICAGO 7566 JONES MANAGER 7839 1981-04-02 2975 20 20 RESEARCH DALLAS 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 30 SALES CHICAGO 7782 CLARK MANAGER 7839 1981-06-09 2450 10 10 ACCOUNTING NEW YORK 7788 SCOTT ANALYST 7566 1987-07-13 3000 20 20 RESEARCH DALLAS 7839 KING PRESIDENT 1981-11-17 5000 10 10 ACCOUNTING NEW YORK 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 30 SALES CHICAGO 7876 ADAMS CLERK 7788 1987-07-13 1100 20 20 RESEARCH DALLAS 7900 JAMES CLERK 7698 1981-12-03 950 30 30 SALES CHICAGO 7902 FORD ANALYST 7566 1981-12-03 3000 20 20 RESEARCH DALLAS 7934 MILLER CLERK 7782 1982-01-23 1300 10 10 ACCOUNTING NEW YORK 40 OPERATIONS BOSTON --// 모든 부서의 정보가 올라온 상태에서 연결하게 됨 */ --> 총 15건의 데이터가 결합되어 조회된 상황 -- 즉, 부서에 소속된 사원이 아무도 없는 부서(1개, 40번) 도 모두 조회된 상황. --※ (+) 가 없는 쪽 테이블의 데이터를 모두 메모리에 우선 적재한 후 -- (+) 가 있는 쪽 테이블의 데이터를 하나하나 확인하여 결합시키는 형태로 -- JOIN 이 이루어진다. SELECT * FROM TBL_EMP E, TBL_DEPT D WHERE E.DEPTNO(+) = D.DEPTNO(+); --> 위와 같은 이유로 이러한 형식의 JOIN 구문은 존재하지 않는다.
- 2. SQL 1999 CODE
→ 『JOIN』키워드 등장 → JOIN 유형 명시
→ 『WHERE 조건절』 대신에 『ON』 키워드로 결합조건 명시
- CROSS JOIN
- INNER JOIN
- OUTER JOIN
--2. SQL 1999 CODE → 『JOIN』키워드 등장 → JOIN 유형 명시 -- 결합조건은 『WHERE』 대신에 『ON』
- CROSS JOIN
--CROSS JOIN SELECT * FROM EMP CROSS JOIN DEPT; --==>> /* 7369 SMITH CLERK 7902 1980-12-17 800 20 10 ACCOUNTING NEW YORK 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 10 ACCOUNTING NEW YORK 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 10 ACCOUNTING NEW YORK 7566 JONES MANAGER 7839 1981-04-02 2975 20 10 ACCOUNTING NEW YORK 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 10 ACCOUNTING NEW YORK 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 1981-06-09 2450 10 10 ACCOUNTING NEW YORK 7788 SCOTT ANALYST 7566 1987-07-13 3000 20 10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 1981-11-17 5000 10 10 ACCOUNTING NEW YORK 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 10 ACCOUNTING NEW YORK 7876 ADAMS CLERK 7788 1987-07-13 1100 20 10 ACCOUNTING NEW YORK 7900 JAMES CLERK 7698 1981-12-03 950 30 10 ACCOUNTING NEW YORK 7902 FORD ANALYST 7566 1981-12-03 3000 20 10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 1982-01-23 1300 10 10 ACCOUNTING NEW YORK 7369 SMITH CLERK 7902 1980-12-17 800 20 20 RESEARCH DALLAS 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 20 RESEARCH DALLAS 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 20 RESEARCH DALLAS 7566 JONES MANAGER 7839 1981-04-02 2975 20 20 RESEARCH DALLAS 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 20 RESEARCH DALLAS 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 20 RESEARCH DALLAS 7782 CLARK MANAGER 7839 1981-06-09 2450 10 20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 1987-07-13 3000 20 20 RESEARCH DALLAS 7839 KING PRESIDENT 1981-11-17 5000 10 20 RESEARCH DALLAS 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 1987-07-13 1100 20 20 RESEARCH DALLAS 7900 JAMES CLERK 7698 1981-12-03 950 30 20 RESEARCH DALLAS 7902 FORD ANALYST 7566 1981-12-03 3000 20 20 RESEARCH DALLAS 7934 MILLER CLERK 7782 1982-01-23 1300 10 20 RESEARCH DALLAS 7369 SMITH CLERK 7902 1980-12-17 800 20 30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 30 SALES CHICAGO 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 30 SALES CHICAGO 7566 JONES MANAGER 7839 1981-04-02 2975 20 30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 30 SALES CHICAGO 7782 CLARK MANAGER 7839 1981-06-09 2450 10 30 SALES CHICAGO 7788 SCOTT ANALYST 7566 1987-07-13 3000 20 30 SALES CHICAGO 7839 KING PRESIDENT 1981-11-17 5000 10 30 SALES CHICAGO 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 30 SALES CHICAGO 7876 ADAMS CLERK 7788 1987-07-13 1100 20 30 SALES CHICAGO 7900 JAMES CLERK 7698 1981-12-03 950 30 30 SALES CHICAGO 7902 FORD ANALYST 7566 1981-12-03 3000 20 30 SALES CHICAGO 7934 MILLER CLERK 7782 1982-01-23 1300 10 30 SALES CHICAGO 7369 SMITH CLERK 7902 1980-12-17 800 20 40 OPERATIONS BOSTON 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 40 OPERATIONS BOSTON 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 40 OPERATIONS BOSTON 7566 JONES MANAGER 7839 1981-04-02 2975 20 40 OPERATIONS BOSTON 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 40 OPERATIONS BOSTON 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 40 OPERATIONS BOSTON 7782 CLARK MANAGER 7839 1981-06-09 2450 10 40 OPERATIONS BOSTON 7788 SCOTT ANALYST 7566 1987-07-13 3000 20 40 OPERATIONS BOSTON 7839 KING PRESIDENT 1981-11-17 5000 10 40 OPERATIONS BOSTON 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 40 OPERATIONS BOSTON 7876 ADAMS CLERK 7788 1987-07-13 1100 20 40 OPERATIONS BOSTON 7900 JAMES CLERK 7698 1981-12-03 950 30 40 OPERATIONS BOSTON 7902 FORD ANALYST 7566 1981-12-03 3000 20 40 OPERATIONS BOSTON 7934 MILLER CLERK 7782 1982-01-23 1300 10 40 OPERATIONS BOSTON */
- INNER JOIN
--INNER JOIN // Equi Join 이 변한 것 + Non-Equi Join 도 함께 SELECT * FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO; --==>> /* 7782 CLARK MANAGER 7839 1981-06-09 2450 10 10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 1981-11-17 5000 10 10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 1982-01-23 1300 10 10 ACCOUNTING NEW YORK 7566 JONES MANAGER 7839 1981-04-02 2975 20 20 RESEARCH DALLAS 7902 FORD ANALYST 7566 1981-12-03 3000 20 20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 1987-07-13 1100 20 20 RESEARCH DALLAS 7369 SMITH CLERK 7902 1980-12-17 800 20 20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 1987-07-13 3000 20 20 RESEARCH DALLAS 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 30 SALES CHICAGO 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 30 SALES CHICAGO 7900 JAMES CLERK 7698 1981-12-03 950 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 30 SALES CHICAGO */ --※ INNER JOIN 시 INNER 는 생략 가능 SELECT * FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO; --==>> /* 7782 CLARK MANAGER 7839 1981-06-09 2450 10 10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 1981-11-17 5000 10 10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 1982-01-23 1300 10 10 ACCOUNTING NEW YORK 7566 JONES MANAGER 7839 1981-04-02 2975 20 20 RESEARCH DALLAS 7902 FORD ANALYST 7566 1981-12-03 3000 20 20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 1987-07-13 1100 20 20 RESEARCH DALLAS 7369 SMITH CLERK 7902 1980-12-17 800 20 20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 1987-07-13 3000 20 20 RESEARCH DALLAS 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 30 SALES CHICAGO 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 30 SALES CHICAGO 7900 JAMES CLERK 7698 1981-12-03 950 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 30 SALES CHICAGO */ SELECT * FROM EMP E INNER JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL; SELECT * FROM EMP E JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL; --// Equi Join Non-Equi Join 이 함께 INNER JOIN 이 된 것을 확인 가능
- OUTER JOIN
--OUTER JOIN // (+)이 붙는 결합이 바뀐모양 → 모양이 사라지고 LEFT/RIGHT 로 구분 , 방향이 가리키는 쪽이 주인공! SELECT * FROM TBL_EMP E LEFT OUTER JOIN TBL_DEPT D ON E.DEPTNO = D.DEPTNO; --==>> /* 7934 MILLER CLERK 7782 1982-01-23 1300 10 10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 1981-11-17 5000 10 10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 1981-06-09 2450 10 10 ACCOUNTING NEW YORK 7902 FORD ANALYST 7566 1981-12-03 3000 20 20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 1987-07-13 1100 20 20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 1987-07-13 3000 20 20 RESEARCH DALLAS 7566 JONES MANAGER 7839 1981-04-02 2975 20 20 RESEARCH DALLAS 7369 SMITH CLERK 7902 1980-12-17 800 20 20 RESEARCH DALLAS 7900 JAMES CLERK 7698 1981-12-03 950 30 30 SALES CHICAGO 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 30 SALES CHICAGO 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 30 SALES CHICAGO 8005 ㅇㅎㅈ SALESMAN 7698 2023-03-28 1000 8004 ㅇㅇㅅ SALESMAN 7698 2023-03-28 2500 8003 ㅊㅎㄱ SALESMAN 7698 2023-03-28 2000 8002 ㅇㅅㅇ CLERK 7566 2023-03-28 1000 0 8001 ㅊㅇㅅ CLERK 7566 2023-03-28 1500 10 */ --※ 방향이 지정된 쪽 테이블(→LEFT, EMP) 의 데이터를 모두 먼저 메모리에 적재한 후 -- 방향이 지정되지 않은 쪽 테이블(→ DEPT) 의 데이터를 각각 확인하여 결합시키는 형태로 -- JOIN 이 이루어진다. SELECT * FROM TBL_EMP E RIGHT OUTER JOIN TBL_DEPT D ON E.DEPTNO = D.DEPTNO; --==>> /* 7369 SMITH CLERK 7902 1980-12-17 800 20 20 RESEARCH DALLAS 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 30 SALES CHICAGO 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 30 SALES CHICAGO 7566 JONES MANAGER 7839 1981-04-02 2975 20 20 RESEARCH DALLAS 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 30 SALES CHICAGO 7782 CLARK MANAGER 7839 1981-06-09 2450 10 10 ACCOUNTING NEW YORK 7788 SCOTT ANALYST 7566 1987-07-13 3000 20 20 RESEARCH DALLAS 7839 KING PRESIDENT 1981-11-17 5000 10 10 ACCOUNTING NEW YORK 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 30 SALES CHICAGO 7876 ADAMS CLERK 7788 1987-07-13 1100 20 20 RESEARCH DALLAS 7900 JAMES CLERK 7698 1981-12-03 950 30 30 SALES CHICAGO 7902 FORD ANALYST 7566 1981-12-03 3000 20 20 RESEARCH DALLAS 7934 MILLER CLERK 7782 1982-01-23 1300 10 10 ACCOUNTING NEW YORK 40 OPERATIONS BOSTON */ SELECT * FROM TBL_EMP E FULL OUTER JOIN TBL_DEPT D --// FULL : 모두 주인공! ON E.DEPTNO = D.DEPTNO; --==>> /* 7369 SMITH CLERK 7902 1980-12-17 800 20 20 RESEARCH DALLAS 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 30 SALES CHICAGO 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 30 SALES CHICAGO 7566 JONES MANAGER 7839 1981-04-02 2975 20 20 RESEARCH DALLAS 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 30 SALES CHICAGO 7782 CLARK MANAGER 7839 1981-06-09 2450 10 10 ACCOUNTING NEW YORK 7788 SCOTT ANALYST 7566 1987-07-13 3000 20 20 RESEARCH DALLAS 7839 KING PRESIDENT 1981-11-17 5000 10 10 ACCOUNTING NEW YORK 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 30 SALES CHICAGO 7876 ADAMS CLERK 7788 1987-07-13 1100 20 20 RESEARCH DALLAS 7900 JAMES CLERK 7698 1981-12-03 950 30 30 SALES CHICAGO 7902 FORD ANALYST 7566 1981-12-03 3000 20 20 RESEARCH DALLAS 7934 MILLER CLERK 7782 1982-01-23 1300 10 10 ACCOUNTING NEW YORK 8001 ㅊㅇㅅ CLERK 7566 2023-03-28 1500 10 8002 ㅇㅅㅇ CLERK 7566 2023-03-28 1000 0 8003 ㅊㅎㄱ SALESMAN 7698 2023-03-28 2000 8004 ㅇㅇㅅ SALESMAN 7698 2023-03-28 2500 8005 ㅇㅎㅈ SALESMAN 7698 2023-03-28 1000 40 OPERATIONS BOSTON */ --※ OUTER JOIN 에서 OUTER 는 생략 가능 -- LEFT / RIGHT / FULL 이 있는 것으로 INNER JOIN 과 구별 가능 SELECT * FROM TBL_EMP E LEFT JOIN TBL_DEPT D -- (LEFT) OUTER JOIN ON E.DEPTNO = D.DEPTNO; SELECT * FROM TBL_EMP E RIGHT JOIN TBL_DEPT D -- (RIGHT) OUTER JOIN ON E.DEPTNO = D.DEPTNO; SELECT * FROM TBL_EMP E FULL JOIN TBL_DEPT D -- (FULL) OUTER JOIN ON E.DEPTNO = D.DEPTNO; SELECT * FROM TBL_EMP E JOIN TBL_DEPT D -- INNER JOIN ON E.DEPTNO = D.DEPTNO;
+ ON 의 사용법에 대한 주의점
SELECT * FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO; --// WHERE 대신에 ON 사용함 -- 위 결과에서... 직종인 CLERK 인 사원들만 조회 SELECT * FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO AND E.JOB = 'CLERK'; -- 이렇게 쿼리문을 구성해도 조회하는데 문제가 없다. SELECT * FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO WHERE E.JOB = 'CLERK'; -- 하지만, 이와 같이 구성하여 조회할 수 있도록 권장한다. SELECT * FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.JOB = 'CLERK'; --// 물론 92년도에는 ON 이라는 키워드가 존재하지 않았으므로, AND 연산자를 통해 이을 수 밖에 없음
--○ EMP 테이블과 DEPT 테이블을 대상으로 -- 직종이 MANNAGER 와 CLERK 인 사원들만 -- 부서번호, 부서명, 사원명, 직종명, 급여 항목을 조회한다.※ 부모테이블 - 자식테이블
- 부모테이블의 컬럼을 참조해야한다.더보기SELECT DEPTNO, DNAME, ENAME, JOB, SAL FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO; --==>> 에러 발생 /* ORA-00918: column ambiguously defined 00918. 00000 - "column ambiguously defined" *Cause: *Action: 607행, 8열에서 오류 발생 */ --> 두 테이블 간 중복되는 컬럼이 존재할 경우 -- 해당 컬럼에 대한 소속 테이블을 정해줘야(명시해줘야) 한다. -------------------------------------------------------------------------------------- SELECT D.DEPTNO "부서번호", DNAME "부서명", ENAME "사원명", JOB "직종명", SAL "급여" FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO; --==>> /* 10 ACCOUNTING CLARK MANAGER 2450 10 ACCOUNTING KING PRESIDENT 5000 10 ACCOUNTING MILLER CLERK 1300 20 RESEARCH JONES MANAGER 2975 20 RESEARCH FORD ANALYST 3000 20 RESEARCH ADAMS CLERK 1100 20 RESEARCH SMITH CLERK 800 20 RESEARCH SCOTT ANALYST 3000 30 SALES WARD SALESMAN 1250 30 SALES TURNER SALESMAN 1500 30 SALES ALLEN SALESMAN 1600 30 SALES JAMES CLERK 950 30 SALES BLAKE MANAGER 2850 30 SALES MARTIN SALESMAN 1250 */ --// 명시해주면 오류가 안난다. SELECT DNAME "부서명", ENAME "사원명", JOB "직종명", SAL "급여" FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO; --> 두 테이블 간 중복되는 컬럼이 존재하지 않는 조회 구문은 -- 에러 발생하지 않는다. --==>> /* ACCOUNTING CLARK MANAGER 2450 ACCOUNTING KING PRESIDENT 5000 ACCOUNTING MILLER CLERK 1300 RESEARCH JONES MANAGER 2975 RESEARCH FORD ANALYST 3000 RESEARCH ADAMS CLERK 1100 RESEARCH SMITH CLERK 800 RESEARCH SCOTT ANALYST 3000 SALES WARD SALESMAN 1250 SALES TURNER SALESMAN 1500 SALES ALLEN SALESMAN 1600 SALES JAMES CLERK 950 SALES BLAKE MANAGER 2850 SALES MARTIN SALESMAN 1250 */ -------------------------------------------------------------------------------------- SELECT E.DEPTNO "부서번호", DNAME "부서명", ENAME "사원명", JOB "직종명", SAL "급여" FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO; SELECT D.DEPTNO "부서번호", DNAME "부서명", ENAME "사원명", JOB "직종명", SAL "급여" FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO; --> 두 테이블 간 중복되는 컬럼에 대해 소속 테이블을 명시하는 경우 -- 부서(DEPT), 사원(EMP) 중 어떤 테이블을 지정해도 -- 쿼리문 수행에 대한 결과 반환에 문제가 없다. --※ 하.지.만, -- 두 테이블 간 중복되는 컬럼에 대해 소속 테이블을 명시하는 경우 -- 부모 테이블 컬럼을 참조할 수 있도록 해야한다. --// 연결고리를 맺는 컬럼의 데이터 값이 고유하게(값이 단 한 개) 있는 쪽이 부모 테이블 --// 테이블의 크기 나 컬럼의 개수 등은 관계 없음 SELECT * FROM EMP; -- 자식 테이블 SELECT * FROM DEPT; -- 부모 테이블 --※ 부모 자식 테이블의 관계를 명확히 정리할 수 있도록 한다. -- DEPTNO -- EMP ------------- DEPT --(many) : (1) --// 아래 두 경우에는 부서번호 40이 null 값이 되는지 아닌지의 차이가 생기며 --// 즉, 부모테이블 컬럼을 참조해야하는 이유가 설명이 된다. SELECT D.DEPTNO "부서번호", DNAME "부서명", ENAME "사원명", JOB "직종명", SAL "급여" -- 부모 참조 FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO = D.DEPTNO; SELECT E.DEPTNO "부서번호", DNAME "부서명", ENAME "사원명", JOB "직종명", SAL "급여" -- 자식 참조 FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO = D.DEPTNO; -------------------------------------------------------------------------------------- -- 문제 해결 최종 구성 쿼리문 SELECT E.DEPTNO "부서번호", DNAME "부서명", ENAME "사원명", JOB "직종명", SAL "급여" FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO; --> 두 테이블 간 중복된 컬럼이 아니더라도... (문법적인 제한을 가하지 않더라도...) -- 소속 테이블을 명시할 수 있도록 권장한다. --// 중복된 컬럼들을 둘 다 확인해서 에러가 발생함, --// 즉, 소속을 명시하지 않으면 모든 테이블에서 다 찾아 보는 것을 알 수 있다. --// 따라서, 해당 컬럼이 어디에 소속되어있는지 명시함으로써, 해당되는 테이블에서만 컬럼을 가져와 참조할 수 있게 해준다.
-- EMP 테이블의 데이터를 다음과 같이 조회할 수 있도록 쿼리문을 구성한다. /* ---------------------------------------------------------------------------- 사원번호 사원명 직종명 관리자번호 관리자명 관리자직종명 ---------------------------------------------------------------------------- 7369 SMITH CLERK 7902 FORD ANNALYST 7499 ALLEN SALESMAN 7698 BLAKE MANAGER : */더보기-- 방법 1 SELECT D.EMPNO "사원번호" , D.ENAME "사원명" , D.JOB "직종명" , D.MGR "관리자번호" , E.ENAME "관리자명" , E.JOB "관리자직종명" FROM EMP E, EMP D WHERE E.EMPNO = D.MGR; --> 근데 KING 이 안나와요... -- 방법 2 SELECT D.EMPNO "사원번호" , D.ENAME "사원명" , D.JOB "직종명" , D.MGR "관리자번호" , E.ENAME "관리자명" , E.JOB "관리자직종명" FROM EMP E RIGHT JOIN EMP D ON E.EMPNO = D.MGR; --> KING 도 나와요... 근데 앞에 D. E. 기준을 잘 모르겠네... 왜 됐지...?...--○ SELF JOIN (자기 조인) -- EMP 테이블의 데이터를 다음과 같이 조회할 수 있도록 쿼리문을 구성한다. /* E E E E E E EMPNO ENAME JOB MGR → ① → E1 EMPNO ENAME JOB → ② → E2 ---------------------------------------------------------------------------- 사원번호 사원명 직종명 관리자번호 관리자명 관리자직종명 */ SELECT EMPNO, ENAME, JOB, MGR FROM EMP; SELECT E1.EMPNO "사원번호", E1.ENAME "사원명", E1.JOB "직종명" --, E1.MGR "관리자번호" -- // 부모 테이블에 있는걸 쓰는 것이 바람직하다.(EMPNO 는 단 하나지만 관리자번호는 여러개) , E2.EMPNO "관리자번호", E2.ENAME "관리자명", E2.JOB "관리자직종명" FROM EMP E1 JOIN EMP E2 ON E1.MGR = E2.EMPNO ; SELECT E1.EMPNO "사원번호", E1.ENAME "사원명", E1.JOB "직종명" , E2.EMPNO "관리자번호", E2.ENAME "관리자명", E2.JOB "관리자직종명" FROM EMP E1 LEFT JOIN EMP E2 ON E1.MGR = E2.EMPNO ;
HR 계정 실습
- JOIN (이어서)
- 세 개 이상의 테이블 조인(JOIN)
SELECT USER FROM DUAL; --==>> HR -- //JOIN 문법은 결합하는 테이블이 몇 개 인지 제한을 두지 않는다
--○ 세 개 이상의 테이블 조인(JOIN) -- 형식1(SQL 1992 CODE) --// 크게 문법이 달라지지 않는다 SELECT 테이블명1.컬럼명, 테이블명2.컬럼명, 테이블명3.컬럼명 FROM 테이블명1, 테이블명2, 테이블명3 WHERE 테이블명1.컬럼명1 = 테이블명2.컬럼명1 AND 테이블명2.컬럼명2 = 테이블명3.컬럼명2; -- 형식2(SQL 1999 CODE) SELECT 테이블명1.컬럼명, 테이블명2.컬럼명, 테이블명3.컬럼명 FROM 테이블명1 JOIN 테이블명2 ON 테이블명1.컬럼명1 = 테이블명2.컬럼명1 JOIN 테이블명3 ON 테이블명2.컬럼명2 = 테이블명3.컬럼명2;
--○ HR 계정 소유의 테이블 또는 뷰 목록 조회
SELECT * FROM TAB;
--○ HR.JOBS, HR.EMPLOYEES, HR.DEPARTMENTS 테이블을 대상으로 -- 직원들의 FIRST_NAME, LAST_NAME, JOB_TITLE, DEPARTMENT_NAME 항목을 조회한다. -- ---------- --------- --------- -------------- -- E E J D SELECT E.FIRST_NAME, E.LAST_NAME, J.JOB_TITLE, D.DEPARTMENT_NAME FROM EMPLOYEES E, DEPARTMENTS D, JOBS J WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID(+) AND E.JOB_ID = J.JOB_ID; SELECT E.FIRST_NAME, E.LAST_NAME, J.JOB_TITLE, D.DEPARTMENT_NAME FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID JOIN JOBS J ON E.JOB_ID = J.JOB_ID;
-- E D J L C R --○ EMPLOYEES, DEPARTMENTS, JOBS, LOCATIONS, COUNTRIES, REGIONS 테이블을 대상으로 -- 직원들의 데이터를 다음과 같이 조회한다. -- FIRST_NAME, LAST_NAME, JOB_TITLE, DEPARTMENT_NAME, CITY, COUNTRY_NAME, REGION_NAME -- ---------- --------- --------- --------------- ---- ------------ ----------- -- E E J D L C R --① 방법 1992 SELECT E.FIRST_NAME, E.LAST_NAME, J.JOB_TITLE, D.DEPARTMENT_NAME , L.CITY, C.COUNTRY_NAME, R.REGION_NAME FROM EMPLOYEES E, DEPARTMENTS D, JOBS J, LOCATIONS L, COUNTRIES C, REGIONS R WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID(+) AND E.JOB_ID = J.JOB_ID AND D.LOCATION_ID = L.LOCATION_ID(+) AND L.COUNTRY_ID = C.COUNTRY_ID(+) AND C.REGION_ID = R.REGION_ID(+); --② 방법2 1999 SELECT E.FIRST_NAME, E.LAST_NAME, J.JOB_TITLE, D.DEPARTMENT_NAME , L.CITY , C.COUNTRY_NAME, R.REGION_NAME FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID JOIN JOBS J ON E.JOB_ID = J.JOB_ID LEFT JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID LEFT JOIN COUNTRIES C ON L.COUNTRY_ID = C.COUNTRY_ID LEFT JOIN REGIONS R ON C.REGION_ID = R.REGION_ID;
SCOTT 계정 실습
- UNION / UNION ALL
- 실습 테이블 생성 및 데이터 입력, 추가입력
더보기--○ 실습 테이블 생성(TBL_JUMUN) CREATE TABLE TBL_JUMUN -- 주문 테이블 생성 ( JUNO NUMBER -- 주문 번호 , JECODE VARCHAR2(30) -- 주문된 제품코드 , JUSU NUMBER -- 주문 수량 , JUDAY DATE DEFAULT SYSDATE -- 주문 일자 ); --==>> Table TBL_JUMUN이(가) 생성되었습니다. --> 고객의 주문이 발생했을 경우 주문 내용에 대한 데이터가 입력될 수 있는 테이블 ------------------------------------------------------------------------------------ --○ 데이터 입력 → 고객의 주문 발생/접수 INSERT INTO TBL_JUMUN VALUES (1,'초코파이', 20, TO_DATE('2001-10-01 09:05:10', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO TBL_JUMUN VALUES (2,'새우깡', 10, TO_DATE('2001-10-01 10:05:10', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO TBL_JUMUN VALUES (3,'쿠크다스', 30, TO_DATE('2001-10-01 11:05:10', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO TBL_JUMUN VALUES (4,'도리토스', 30, TO_DATE('2001-10-01 12:05:10', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO TBL_JUMUN VALUES (5,'오감자', 12, TO_DATE('2001-10-02 10:05:10', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO TBL_JUMUN VALUES (6,'홈런볼', 40, TO_DATE('2001-10-03 10:05:10', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO TBL_JUMUN VALUES (7,'빼빼로', 30, TO_DATE('2001-10-04 10:05:10', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO TBL_JUMUN VALUES (8,'칸쵸', 20, TO_DATE('2001-10-05 11:05:10', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO TBL_JUMUN VALUES (9,'포카칩', 20, TO_DATE('2001-10-06 15:05:10', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO TBL_JUMUN VALUES (10,'포테토칩', 20, TO_DATE('2001-10-06 17:05:10', 'YYYY-MM-DD HH24:MI:SS')); --==>> 1 행 이(가) 삽입되었습니다. * 10 ------------------------------------------------------------------------------------ ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; --==>> Session이(가) 변경되었습니다. ------------------------------------------------------------------------------------ --○ 확인 SELECT * FROM TBL_JUMUN; --==>> /* 1 초코파이 20 2001-10-01 09:05:10 2 새우깡 10 2001-10-01 10:05:10 3 쿠크다스 30 2001-10-01 11:05:10 4 도리토스 30 2001-10-01 12:05:10 5 오감자 12 2001-10-02 10:05:10 6 홈런볼 40 2001-10-03 10:05:10 7 빼빼로 30 2001-10-04 10:05:10 8 칸쵸 20 2001-10-05 11:05:10 9 포카칩 20 2001-10-06 15:05:10 10 포테토칩 20 2001-10-06 17:05:10 */ --○ 커밋 COMMIT; --==>> 커밋 완료더보기--○ 데이터 추가 입력 → 2001년 부터 시작된 주문이 현재(2023년) 까지 계속 발생! INSERT INTO TBL_JUMUN VALUES(938765, '홈런볼', 10, SYSDATE); --==>> 1 행 이(가) 삽입되었습니다. INSERT INTO TBL_JUMUN VALUES(938766, '꼬북칩', 10, SYSDATE); --==>> 1 행 이(가) 삽입되었습니다. INSERT INTO TBL_JUMUN VALUES(938767, '칸쵸', 20, SYSDATE); --==>> 1 행 이(가) 삽입되었습니다. INSERT INTO TBL_JUMUN VALUES(938768, '칸쵸', 10, SYSDATE); --==>> 1 행 이(가) 삽입되었습니다. INSERT INTO TBL_JUMUN VALUES(938769, '홈런볼', 20, SYSDATE); --==>> 1 행 이(가) 삽입되었습니다. INSERT INTO TBL_JUMUN VALUES(938770, '홈런볼', 30, SYSDATE); --==>> 1 행 이(가) 삽입되었습니다. INSERT INTO TBL_JUMUN VALUES(938771, '홈런볼', 40, SYSDATE); --==>> 1 행 이(가) 삽입되었습니다. INSERT INTO TBL_JUMUN VALUES(938772, '새우깡', 10, SYSDATE); --==>> 1 행 이(가) 삽입되었습니다. INSERT INTO TBL_JUMUN VALUES(938773, '쿠크다스', 20, SYSDATE); --==>> 1 행 이(가) 삽입되었습니다. INSERT INTO TBL_JUMUN VALUES(938774, '포카칩', 10, SYSDATE); --==>> 1 행 이(가) 삽입되었습니다. INSERT INTO TBL_JUMUN VALUES(938775, '오감자', 14, SYSDATE); --==>> 1 행 이(가) 삽입되었습니다. INSERT INTO TBL_JUMUN VALUES(938776, '포카칩', 20, SYSDATE); --==>> 1 행 이(가) 삽입되었습니다. ------------------------------------------------------------------------------------ --○ 확인 SELECT * FROM TBL_JUMUN; --==>> /* 1 초코파이 20 2001-10-01 09:05:10 2 새우깡 10 2001-10-01 10:05:10 3 쿠크다스 30 2001-10-01 11:05:10 4 도리토스 30 2001-10-01 12:05:10 5 오감자 12 2001-10-02 10:05:10 6 홈런볼 40 2001-10-03 10:05:10 7 빼빼로 30 2001-10-04 10:05:10 8 칸쵸 20 2001-10-05 11:05:10 9 포카칩 20 2001-10-06 15:05:10 10 포테토칩 20 2001-10-06 17:05:10 : : : 938765 홈런볼 10 2023-03-30 16:32:19 938766 꼬북칩 10 2023-03-30 16:32:38 938767 칸쵸 20 2023-03-30 16:32:59 938768 칸쵸 10 2023-03-30 16:33:20 938769 홈런볼 20 2023-03-30 16:33:43 938770 홈런볼 30 2023-03-30 16:34:01 938771 홈런볼 40 2023-03-30 16:34:17 938772 새우깡 10 2023-03-30 16:34:43 938773 쿠크다스 20 2023-03-30 16:35:23 938774 포카칩 10 2023-03-30 16:35:35 938775 오감자 14 2023-03-30 16:36:10 938776 포카칩 20 2023-03-30 16:36:25 */ ------------------------------------------------------------------------------------ --○ 커밋 COMMIT; --==>> 커밋 완료
--※ ㄱㅂ 가 2001년부터 과자 쇼핑몰 운영중... -- TBL_JUMUN 테이블이 너무 무거워진 상황... -- 어플리케이션과의 연동으로 인해 발생하는 주문 내역을 -- 다른 테이블에 저장할 수 있도록 만드는 것은 힘든 상황... -- 테이블이 무거워졌다고 해서 -- 기존의 모든 데이터를 덮어놓고 지우는 것도 불가능한 상황... -- → 결과적으로... -- 현재까지 누적된 주문 데이터 중 -- 금일 발생한 주문 내역을 제외하고 -- 나머지 데이터를 다른 테이블(TBL_JUMUNBACKUP)로 -- 데이터 이관을 수행할 계획
SELECT * FROM TBL_JUMUN WHERE 금일 주문이 아닌 것; ------------------------------------------------------------------------------------ SELECT * FROM TBL_JUMUN WHERE TO_CHAR(JUDAY, 'YYYY-MM-DD') != TO_CHAR(SYSDATE, 'YYYY-MM-DD'); --==>> /* 1 초코파이 20 2001-10-01 09:05:10 2 새우깡 10 2001-10-01 10:05:10 3 쿠크다스 30 2001-10-01 11:05:10 4 도리토스 30 2001-10-01 12:05:10 5 오감자 12 2001-10-02 10:05:10 6 홈런볼 40 2001-10-03 10:05:10 7 빼빼로 30 2001-10-04 10:05:10 8 칸쵸 20 2001-10-05 11:05:10 9 포카칩 20 2001-10-06 15:05:10 10 포테토칩 20 2001-10-06 17:05:10 */ ------------------------------------------------------------------------------------ --○ 위의 조회 결과로 TBL_JUMUNBACKUP 테이블 생성 CREATE TABLE TBL_JUMUNBACKUP AS SELECT * FROM TBL_JUMUN WHERE TO_CHAR(JUDAY, 'YYYY-MM-DD') != TO_CHAR(SYSDATE, 'YYYY-MM-DD'); --==>> Table TBL_JUMUNBACKUP이(가) 생성되었습니다. ------------------------------------------------------------------------------------ --○ 확인 SELECT * FROM TBL_JUMUNBACKUP; --==>> /* 1 초코파이 20 2001-10-01 09:05:10 2 새우깡 10 2001-10-01 10:05:10 3 쿠크다스 30 2001-10-01 11:05:10 4 도리토스 30 2001-10-01 12:05:10 5 오감자 12 2001-10-02 10:05:10 6 홈런볼 40 2001-10-03 10:05:10 7 빼빼로 30 2001-10-04 10:05:10 8 칸쵸 20 2001-10-05 11:05:10 9 포카칩 20 2001-10-06 15:05:10 10 포테토칩 20 2001-10-06 17:05:10 */ --> TBL_JUMUN 테이블의 데이터들 중 -- 금일 주문내역 이외의 데이터는 모두 TBL_JUMUNBACKUP 테이블에 백업을 마친 상태.
-- TBL_JUMUN 테이블의 데이터들 중 -- 금일 주문내역 이외의 데이터는 모두 삭제 DELETE FROM TBL_JUMUN WHERE TO_CHAR(JUDAY, 'YYYY-MM-DD') != TO_CHAR(SYSDATE, 'YYYY-MM-DD'); --==>> 10개 행 이(가) 삭제되었습니다. → 938764 건의 데이터 삭제(가정) ------------------------------------------------------------------------------------ -- 아직 제품 발송이 완료되지 않은 금일 주문 데이터를 제외하고 -- 이전의 모든 주문 데이터들이 삭제된 상황이므로 -- 테이블은 행(레코드)의 갯수가 줄어들어 매우 가벼워진 상황 ------------------------------------------------------------------------------------ --○ 확인 SELECT * FROM TBL_JUMUN; --==>> /* 938765 홈런볼 10 2023-03-30 16:32:19 938766 꼬북칩 10 2023-03-30 16:32:38 938767 칸쵸 20 2023-03-30 16:32:59 938768 칸쵸 10 2023-03-30 16:33:20 938769 홈런볼 20 2023-03-30 16:33:43 938770 홈런볼 30 2023-03-30 16:34:01 938771 홈런볼 40 2023-03-30 16:34:17 938772 새우깡 10 2023-03-30 16:34:43 938773 쿠크다스 20 2023-03-30 16:35:23 938774 포카칩 10 2023-03-30 16:35:35 938775 오감자 14 2023-03-30 16:36:10 938776 포카칩 20 2023-03-30 16:36:25 */ ------------------------------------------------------------------------------------ --○ 커밋 COMMIT; --==>> 커밋 완료
- UNION / UNION ALL
- 특징 알고 사용하기
- UNION 이 UNION ALL 보다 부하가 심하다 → 정렬기능, 중복제거 기능 까지 있기 때문
-- 지금까지 주문받은 내역에 대한 정보를 -- 제품별 총 주문량으로 나타내어야 할 상황이 발생하게 되었다. -- 그렇다면... TBL_JUMUNBACKUP 테이블의 레코드(행)와 -- TBL_JUMUN 테이블의 레코드(행)를 합쳐러 -- 하나의 테이블을 조회하는 것과 같은 -- 결과를 확인할 수 있도록 해야한다. -- 컬럼과 컬럼의 관계를 고려하여 테이블을 결합하고자 하는 경우 JOIN 을 사용하지만 -- 레코드(행)와 레코드(행)를 결합하고자 하는 경우 UNION / UNION ALL 을 사용할 수 있다. SELECT * FROM TBL_JUMUNBACKUP; SELECT * FROM TBL_JUMUN; SELECT * FROM TBL_JUMUNBACKUP UNION --// 사이에 들어간 ; 만 지우고 UNION 만 적으면 끝 SELECT * FROM TBL_JUMUN; --==>> /* 1 초코파이 20 2001-10-01 09:05:10 2 새우깡 10 2001-10-01 10:05:10 3 쿠크다스 30 2001-10-01 11:05:10 4 도리토스 30 2001-10-01 12:05:10 5 오감자 12 2001-10-02 10:05:10 6 홈런볼 40 2001-10-03 10:05:10 7 빼빼로 30 2001-10-04 10:05:10 8 칸쵸 20 2001-10-05 11:05:10 9 포카칩 20 2001-10-06 15:05:10 10 포테토칩 20 2001-10-06 17:05:10 938765 홈런볼 10 2023-03-30 16:32:19 938766 꼬북칩 10 2023-03-30 16:32:38 938767 칸쵸 20 2023-03-30 16:32:59 938768 칸쵸 10 2023-03-30 16:33:20 938769 홈런볼 20 2023-03-30 16:33:43 938770 홈런볼 30 2023-03-30 16:34:01 938771 홈런볼 40 2023-03-30 16:34:17 938772 새우깡 10 2023-03-30 16:34:43 938773 쿠크다스 20 2023-03-30 16:35:23 938774 포카칩 10 2023-03-30 16:35:35 938775 오감자 14 2023-03-30 16:36:10 938776 포카칩 20 2023-03-30 16:36:25 */ SELECT * FROM TBL_JUMUNBACKUP UNION ALL --// 사이에 들어간 ; 만 지우고 UNION ALL 만 적으면 끝 SELECT * FROM TBL_JUMUN; --==>> /* 1 초코파이 20 2001-10-01 09:05:10 2 새우깡 10 2001-10-01 10:05:10 3 쿠크다스 30 2001-10-01 11:05:10 4 도리토스 30 2001-10-01 12:05:10 5 오감자 12 2001-10-02 10:05:10 6 홈런볼 40 2001-10-03 10:05:10 7 빼빼로 30 2001-10-04 10:05:10 8 칸쵸 20 2001-10-05 11:05:10 9 포카칩 20 2001-10-06 15:05:10 10 포테토칩 20 2001-10-06 17:05:10 938765 홈런볼 10 2023-03-30 16:32:19 938766 꼬북칩 10 2023-03-30 16:32:38 938767 칸쵸 20 2023-03-30 16:32:59 938768 칸쵸 10 2023-03-30 16:33:20 938769 홈런볼 20 2023-03-30 16:33:43 938770 홈런볼 30 2023-03-30 16:34:01 938771 홈런볼 40 2023-03-30 16:34:17 938772 새우깡 10 2023-03-30 16:34:43 938773 쿠크다스 20 2023-03-30 16:35:23 938774 포카칩 10 2023-03-30 16:35:35 938775 오감자 14 2023-03-30 16:36:10 938776 포카칩 20 2023-03-30 16:36:25 */ -- 위 아래 위치 뒤바꾸기 SELECT * FROM TBL_JUMUN UNION SELECT * FROM TBL_JUMUNBACKUP; --==>> /* 1 초코파이 20 2001-10-01 09:05:10 2 새우깡 10 2001-10-01 10:05:10 3 쿠크다스 30 2001-10-01 11:05:10 4 도리토스 30 2001-10-01 12:05:10 5 오감자 12 2001-10-02 10:05:10 6 홈런볼 40 2001-10-03 10:05:10 7 빼빼로 30 2001-10-04 10:05:10 8 칸쵸 20 2001-10-05 11:05:10 9 포카칩 20 2001-10-06 15:05:10 10 포테토칩 20 2001-10-06 17:05:10 938765 홈런볼 10 2023-03-30 16:32:19 938766 꼬북칩 10 2023-03-30 16:32:38 938767 칸쵸 20 2023-03-30 16:32:59 938768 칸쵸 10 2023-03-30 16:33:20 938769 홈런볼 20 2023-03-30 16:33:43 938770 홈런볼 30 2023-03-30 16:34:01 938771 홈런볼 40 2023-03-30 16:34:17 938772 새우깡 10 2023-03-30 16:34:43 938773 쿠크다스 20 2023-03-30 16:35:23 938774 포카칩 10 2023-03-30 16:35:35 938775 오감자 14 2023-03-30 16:36:10 938776 포카칩 20 2023-03-30 16:36:25 */ SELECT * FROM TBL_JUMUN UNION ALL SELECT * FROM TBL_JUMUNBACKUP; --==>> /* 938765 홈런볼 10 2023-03-30 16:32:19 938766 꼬북칩 10 2023-03-30 16:32:38 938767 칸쵸 20 2023-03-30 16:32:59 938768 칸쵸 10 2023-03-30 16:33:20 938769 홈런볼 20 2023-03-30 16:33:43 938770 홈런볼 30 2023-03-30 16:34:01 938771 홈런볼 40 2023-03-30 16:34:17 938772 새우깡 10 2023-03-30 16:34:43 938773 쿠크다스 20 2023-03-30 16:35:23 938774 포카칩 10 2023-03-30 16:35:35 938775 오감자 14 2023-03-30 16:36:10 938776 포카칩 20 2023-03-30 16:36:25 1 초코파이 20 2001-10-01 09:05:10 2 새우깡 10 2001-10-01 10:05:10 3 쿠크다스 30 2001-10-01 11:05:10 4 도리토스 30 2001-10-01 12:05:10 5 오감자 12 2001-10-02 10:05:10 6 홈런볼 40 2001-10-03 10:05:10 7 빼빼로 30 2001-10-04 10:05:10 8 칸쵸 20 2001-10-05 11:05:10 9 포카칩 20 2001-10-06 15:05:10 10 포테토칩 20 2001-10-06 17:05:10 */ --※ UNION 은 항상 결과물의 첫 번째 컬럼을 기준으로 -- 오름차순 정렬을 수행한다. -- 반면, UNION ALL 은 (쿼리문에서) 결합된 순서대로 조회한 결과를 반환한다. (정렬없음) -- 이로 인해 UNION 의 부하가 더 크다 -- 또한, UNION 은 결과물에서 중복된 행이 존재할 경우 -- 중복을 제거하고 1개 행만 조회된 결과를 반환하게 된다.
--○ 지금까지 주문받은 모든 데이터를 활용하여 -- 제품별 총 주문량을 조회하는 쿼리문을 구성한다. /* ---------------------------- 제품코드 주문량 ---------------------------- .... XX ... XXX : ---------------------------- */더보기SELECT * FROM TBL_JUMUNBACKUP; SELECT T.제품코드 "제품코드", SUM(T.주문수) "총주문량" FROM ( SELECT JUNO "주문번호", JECODE "제품코드", JUSU "주문수", JUDAY "주문일자" FROM TBL_JUMUNBACKUP UNION ALL SELECT JUNO "주문번호", JECODE "제품코드", JUSU "주문수", JUDAY "주문일자" FROM TBL_JUMUN ) T GROUP BY T.제품코드; --==>> /* 제품코드 총주문량 ------------------------------ ---------- 초코파이 20 도리토스 30 꼬북칩 10 새우깡 20 쿠크다스 50 포테토칩 20 오감자 26 포카칩 50 홈런볼 140 빼빼로 30 칸쵸 50 */728x90'SsY > Class' 카테고리의 다른 글
009. 정규화(Normalization), Primary Key, 관계 (0) 2023.04.03 008. UNION / UNION ALL, INTERSECT / MINUS, NATURAL JOIN, USING (0) 2023.03.31 006. 그룹 함수, 분석 함수(ROLLUP / CUBE / GROUPING SETS), HAVING절, 서브상관쿼리, ROW_NUMBER() / SEQUENCE() (0) 2023.03.29 005. 서브쿼리, 인라인뷰, 등수 함수, 그룹 함수 와 주의점 (0) 2023.03.29 004. 문자열 / 숫자 데이터 / 날짜 데이터 관련 함수, 변환 함수, CASE문(조건문, 분기문) (0) 2023.03.28 - 어제 시퀀스/게시판 부터 이어서