MSA Full-Stack 개발자 양성 과정 -23일차 노트 필기_데이터베이스(Oracle) Join, Set 집합, 서브쿼리(Sub-Query)_220823

Join

: 두 개 이상의 테이블을 하나의 테이블로 만들어 한번의 검색으로 여러 컬럼의 정보를 확인할 때 사용함.

: 반드시 연관 관계가 있는 테이블끼리만 join 가능 (FK)

: select 전용

: 단점: join을 많이 할수록 성능 떨어짐

 

Join의 종류

    1) Inner Join
        - Equi Join: 동등 조인 = Natural Join
        - Non Equi Join: 조인 대상 테이블의 어떤 컬럼의 값도 일치하지 않을때 사용 (=제외한)
          ex) Between And, Is null, >, < 와 같은 조건문을 사용할 때 쓴다. (자주 사용X)

 

    2) Outer Join: 기본은 Equi Join을 하면서 별도의 테이블의 정보를 모두 검색하고 싶을 때
        - Left Outer Join
        - Right Outer Join
        - Full Join

    3) Self Join: 자기 자신의 테이블을 조인하는 것, (즉, 재귀적 관계일 때 사용, 하나의 테이블을 2개처럼 사용)

Join 코딩 방법(문법)
    1) SQL Join 방식: Full Outer Join 제공하지 않음
    2) ANSI Join 방식: 미국국립표준연구소, 즉, 미국 표준을 기본으로 사용하는 문법(권장)

 

 

---

Join예제)

CREATE TABLE TEST1(
    ID VARCHAR2(10) PRIMARY KEY,
    NAME VARCHAR2(30),
    ADDR VARCHAR2(50)
);

SELECT * FROM TEST1;

INSERT INTO TEST1 VALUES('JANG','희정','서울');
INSERT INTO TEST1 VALUES('HI','원영','여수');
INSERT INTO TEST1 VALUES('HYO','효리','제주도');
INSERT INTO TEST1 VALUES('SOO','성수','대구');
INSERT INTO TEST1 VALUES('CRYSTAL','수정','부산');

--TEST 1의 ID를 찾조하는 FK 설정
CREATE TABLE TEST2(
    CODE CHAR(3) PRIMARY KEY,
    ID VARCHAR2(10) REFERENCES TEST1(ID), -- NULL 허용, 중복 가능 // 한 사람이 JOB을 여러개 가질 수 있음
    JOB VARCHAR2(30),
    SAL NUMBER(3)
);

SELECT * FROM TEST2;

INSERT INTO TEST2 VALUES('A01', 'JANG', '개발자', 300);
INSERT INTO TEST2 VALUES('A02', 'CRYSTAL', '디자이너', 150);
INSERT INTO TEST2 VALUES('A03', 'SOO', '세일즈', 200);
INSERT INTO TEST2 VALUES('A04', NULL, '건축가', 400);
INSERT INTO TEST2 VALUES('A05', NULL, '기획자', 350);
INSERT INTO TEST2 VALUES('A06', 'JANG', '강사', 300);

 

1. INNER JOIN

--ID, NAME, ADDR, CODE, JOB, SAL 정보를 한번의 SELECT로 검색하고 싶다 = JOIN
-- 동등 조인 = INNER JOIN
--1) SQL JOIN 방식
SELECT *
FROM TEST1, TEST2
WHERE TEST1.ID=TEST2.ID;-- 조건을 주지 않으면 CROSS JOIN 일어남

-- 원하는 컬럼만 선택적으로..
SELECT TEST1.ID, NAME, ADDR, CODE, JOB, SAL -- 양쪽의 동일한 이름의 컬럼이 있을 경우, 반드시 테이블 이름, 컬럼명 명시해야 함
FROM TEST1, TEST2
WHERE TEST1.ID=TEST2.ID;

-- 테이블 이름에 별칭 주기
SELECT T1.ID, NAME, ADDR, CODE, JOB, SAL 
FROM TEST1 T1, TEST2 T2 -- 테이블 이름에 별칭을 선언하면 반드시 별칭으로 접근해야 한다.
WHERE T1.ID=T2.ID;

-- 2) ANSI JOIN 방식으로 EQUI JOIN하기 -- (권장)
SELECT * 
FROM TEST1 /*INNER*/ JOIN TEST2 -- INNER 단어 생략 가능
ON TEST1.ID = TEST2.ID; --조건절에 ON 사용

-- 원하는 컬럼만 선택적으로..
SELECT TEST1.ID, NAME, ADDR, CODE, JOB, SAL
FROM TEST1 INNER JOIN TEST2
ON TEST1.ID=TEST2.ID;

-- 테이블 이름에 별칭 주기
SELECT T1.ID, NAME, ADDR, CODE, JOB, SAL 
FROM TEST1 T1 JOIN TEST2 T2 -- 테이블 이름에 별칭을 선언하면 반드시 별칭으로 접근해야 한다.
ON T1.ID=T2.ID;

-- USING(컬럼명) 사용하기: ON절 대신 사용한다. 조인의 대상이 되는 PK와 FK가 동일한 컬럼명을 사용할 때 쓴다.
SELECT ID, NAME, ADDR, CODE, JOB, SAL -- 동일한 컬럼은 하나만 출력된다. 즉, 식별자가 필요 없음; 
FROM TEST1 JOIN TEST2 
USING (ID);

--NATURAL JOIN : 양쪽 테이블의 동일한 타입과 동일한 이름을 가진 컬럼을 찾아서 스스로 조인을 자동으로 해줌
SELECT * 
FROM TEST1 NATURAL JOIN TEST2;

 

2. OUTER JOIN

-- OUTER JOIN
--1) SQL JOIN 방법
-- LEFT
SELECT * 
FROM TEST1, TEST2 -- 왼쪽 테이블의 모든 레코드 조회
WHERE TEST1.ID = TEST2.ID(+);

--RIGHT
SELECT * 
FROM TEST1, TEST2 -- 오른쪽 테이블의 모든 레코드 조회
WHERE TEST1.ID(+) = TEST2.ID;

--FULL: SQL JOIN 방식에서는 지원하지 않음

--2) ANSI JOINT 방법
--LEFT
SELECT *
FROM TEST1 LEFT OUTER JOIN TEST2 -- OUTER 생략 가능
USING (ID);

-- RIGHT
SELECT *
FROM TEST1 RIGHT JOIN TEST2
USING (ID);

-- FULL
SELECT *
FROM TEST1 FULL JOIN TEST2
USING (ID);

 

3. 조인 대상이 조건 이외에 조건 더 추가하기

-- 조인 대상이 조건 이외에 조건 더 추가하기
-- SQL JOIN
SELECT *
FROM TEST1, TEST2
WHERE TEST1.ID = TEST2.ID AND TEST1.ID='JANG';

-- ANSI JOIN
--1)ON 절에 조건 추가
SELECT * 
FROM TEST1 JOIN TEST2 -- INNER 단어 생략 가능
ON TEST1.ID = TEST2.ID AND TEST1.ID = 'JANG';

-- 2) WHERE절 추가
SELECT * 
FROM TEST1 JOIN TEST2 -- INNER 단어 생략 가능
ON TEST1.ID = TEST2.ID 
WHERE TEST1.ID = 'JANG';

--3) USING인 경우
SELECT *
FROM TEST1 JOIN TEST2
USING (ID)
WHERE ID = 'JANG'; -- WHERE 절 추가해야 함

 

---

 

 

-- 3번째 테이블 생성
CREATE TABLE TEST3(
    CODE CHAR(3) PRIMARY KEY REFERENCES TEST2(CODE),
    /*
    식별 관계
    : 1대 1의 경우
    : PK+FK (다른 테이블의 PK를 참조(FK)하는 PK)
    */
    MANAGER_NAME VARCHAR2(30),
    PHONE VARCHAR2(20)
);

INSERT INTO TEST3 VALUES('A01', '유진', '010-1234-1234');
INSERT INTO TEST3 VALUES('A03', '희민', '010-2345-2345');
INSERT INTO TEST3 VALUES('A06', '은희', '010-3456-3456');

 

4. 여러개의 테이블의 조인

--3개의 테이블을 조인해보자

-- ID에 해당하는 사람의 NAME, JOB, SAL, MANAGER_NAME, PHONE 정보 검색
-- SQL 조인 방식
SELECT TEST1.ID, NAME, JOB, SAL, MANAGER_NAME, PHONE
FROM TEST1, TEST2, TEST3
WHERE TEST1.ID = TEST2.ID AND TEST2.CODE = TEST3.CODE AND /*추가 조건*/TEST1.ID = 'JANG';

-- ANSI 조인 방식
SELECT *
FROM TEST1 JOIN TEST2 
ON TEST1.ID = TEST2.ID JOIN TEST3
ON TEST2.CODE = TEST3.CODE AND TEST1.ID = 'JANG';

-- USING 사용
SELECT *
FROM TEST1 JOIN TEST2 
USING (ID) JOIN TEST3
USING (CODE)
WHERE ID = 'JANG';

 

---

SET 집합

    1) 합집합
    UNION - 중복행을 제외하고 합친다.
    UNION ALL - 중복행을 포함해서 합친다.
    
    2) 교집합
    INTERSECT: A와 B테이블의 공통된 레코드를 검색
    
    3) 차집합
    MINUS: A MINUS B 했을 때 A에서 B를 뺀 나머지 레코드 검색

 

**주의사항

- select문장의 컬럼의 개수와 데이터타입이 일치해야 한다. (char(5) 와 varchar(5) 은 다르게 본다)

- order by절은 맨 마지막에 한번 사용가능 함.

 

 

SET 집합 예제)

-- 테이블 복사
SELECT * FROM COPY_EMP;
DROP TABLE COPY_EMP;

CREATE TABLE COPY_EMP 
AS SELECT EMPNO, ENAME, JOB, SAL FROM EMP 
WHERE DEPTNO = 20;

-- 복사본 테이블에 레코드 2개 추가
INSERT INTO COPY_EMP VALUES (8000, 'HEEJUNG', 'TEACHER', 2500);
INSERT INTO COPY_EMP VALUES (9000, 'HYORI', 'DEVELOPER', 3500);

-- EMP와 COPY_EMP의 SET집합을 TEST해본다.
--1) UNION -- 열의 개수와 타입이 같아야 한다
SELECT EMPNO, ENAME, JOB, SAL FROM EMP
UNION  -- 중복 제거
SELECT * FROM COPY_EMP;  

SELECT EMPNO, ENAME, JOB, SAL FROM EMP
UNION ALL -- 중복 제거X
SELECT * FROM COPY_EMP;

--2) INTERSECT -- 교집합

SELECT EMPNO, ENAME, JOB, SAL FROM EMP
INTERSECT
SELECT * FROM COPY_EMP;

--3) MINUS
SELECT EMPNO, ENAME, JOB, SAL FROM EMP
MINUS
SELECT * FROM COPY_EMP;

SELECT * FROM COPY_EMP
MINUS
SELECT EMPNO, ENAME, JOB, SAL FROM EMP;

 

---

Sub-Query

① 메인 쿼리 안에서 또 다른 쿼리문이 있는 것.

② 반드시 서브쿼리를 괄호로 묶는다.

③ 메인쿼리 보다 서브쿼리가 먼저 실행되어 결과를 메인 쿼리의 조건으로 사용

④ 서브쿼리의 결과가 한 개 이상 반환될때는 in , any, all 연산자를 사용함.

⑤ 서브쿼리의 결과가 한 개일 때는 비교연산자 사용함.

ex) select * from emp where ename = ( 서브쿼리문장 ) ;