[Oracle/SQL] JOIN, 서브쿼리, 뷰, 테이블

💬 기억하고 싶거나 공부한 내용을 기록중입니다 :)   개인적으로 기억하고 싶은 내용들을 적어두고 있습니다.

 

1️⃣ JOIN

 

1. EQUI JOIN

- (=) 이용하는 조인, 두 테이블에 공통 컬럼 값을 가지고 있는 경우 사용하는 조인이다.

SELECT COLUMNNAME(A), COLUMNNAME(B), COLUMNNAME(C), ,,,
FROM TABLENAME A, TABLENAME B
WHERE A.공통COLUMNNAME = B.공통COLUMNNAME;

2. INNER JOIN ON

- 내부 조인의 경우, 조인하는 테이블에 지정한 열의 데이터가 모두 있어야 한다

SELECT A.COLUMNNAME(A), B.COLUMNNAME(B), ,,,
FROM TABLENAME A INNER JOIN TABLENAME B
ON A.공통COLUMNNAME = B.공통COLUMNNAME;

3. NATURAL JOIN

SELECT A.COLUMNNAME(A), B.COLUMNNAME(B), COLUMNNAME(common)
FROM TABLENAME A NATURAL JOIN TABLENAME B;		-- WHERE절 사용 가능

4. JOIN USING

SELECT A.COLUMNNAME(A), B.COLUMNNAME(B), COLUMNNAME(common)
FROM TABLENAME A JOIN TABLENAME B
USING (COLUMNNAME(common)) ;

5. OUTER JOIN 

- 외부 조인, 조인하는 두 테이블의 경우, 하나의 테이블에만 데이터가 있어도 결과가 출력된다

1) (+)

-- (예시) A테이블의 결과가 다 나오도록 있도록 (+) 처리
SELECT A.COLUMNNAME(A), B.COLUMNNAME(B)
FROM TABLENAME A, TABLENAME B
WHERE A.COLUMNNAME(common)(+) = B.COLUMNNAME(common);

2) FULL/RIGHT/LEFT OUTER JOIN

SELECT A.COLUMNNAME(A), B.COLUMNNAME(B)
FROM TABLENAME A FULL/RIGHT/LEFT OUTER JOIN TABLENAME B
ON A.COLUMNNAME(common) = B.COLUMNNAME(common);

6. NON EQUI JOIN

- 두 테이블에서 사용되는 컬럼의 값들이 정확히 일치하지 않더라도, 출력할 수 있다는 특징이 있다.

SELECT A.COLUMNNAME(A), B.COLUMNNAME(B), B.COLUMNNAME(C)
FROM TABLENAME A, TABLENAME B
WHERE A.COLUMNNAME BETWEEN B.COLUMNNAME(B) AND B.COLUMNNAME(C);

7. SELF JOIN

SELECT A.NO, A.NAME, B.NO_JOB
FROM TABLE(A) 별칭(여기서는 'A'), TABLE(A) 별칭(여기서는 'B')
WHERE A.NO = B.NO_JOB;

 

2️⃣ 서브쿼리

 

- 정의 : SQL 문장 (예: UPDATE, SELECT) 안에서 보조로 사용되는 SELECT문

- 유형

1) 스칼라 서브쿼리:  메인쿼리문 중 SELECT문에서 사용되는 서브쿼리

2) 인라인 뷰 : FROM절에서 사용되는 서브쿼리

3) WHERE절

- 서브쿼리 예시

SELECT COLUMNAME(a), (SELECT COLUMNNAME(b) FROM TABLENAME WHERE COLUMNNAME(b) > 3500) -- 스칼라
FROM (SELECT SUBSTR(COLUMNAME(c),1,3) FROM TABLENAME WHERE COLUMNNAME(d) <=2500)      -- 인라인뷰
GROUP BY COLUMNAME(a);

3️⃣ 뷰

- 정의 : 가상 테이블, 다른 테이블이나 다른 뷰의 데이터를 볼 수 있게 하는 데이터베이스 객체를 의미한다. 

- 활용

-- 1. VIEW 생성 또는 수정
CREATE [OR REPLACE] VIEW VIEWNAME
AS
SELECT COLUMNNAME(a), COLUMNNAME(b), COLUMNNAME(c)
FROM TABLENAME;
where COLUMNAME(b) = (select COLUMNAME(b) FROM TABLENAME WHERE COLUMNNAME(c) <= 5000); 
            
-- 2. VEIW 삭제
DROP VIEW VIEWNAME;

-- 3. VIEW 자체 조회
SELECT VIEWNAME FROM USER_VIEWS;

-- 4. 생성된 VIEW 내용 조회
SELECT * FROM VIEWNAME;

/*
5. VIEW 내용 입력 : 테이블도 뷰도 수정된다
6. VIEW 내용 수정 : 테이블 내용은 수정되지만, 
		뷰 생성시 조건이 다를 경우 수정된 내용이 출력되지 않는다. */

 

4️⃣ 테이블

 

1. 테이블 생성, 제거 등

-- 1-1. TABLENAME에서 COLUMNNAME(A), COLUMNNAME(B), COLUMNNAME(C)의 '구조'만 복사
CREATE TABLE TABLENAME
AS
SELECT COLUMNNAME(A), COLUMNNAME(B), COLUMNNAME(C)
FROM TABLENAME
WHERE 1=2;  -- 예를 들어

-- 1-2. TABLENAME에서 COLUMNNAME(A), COLUMNNAME(B), COLUMNNAME(C)의 '구조'와 '데이터'를 복사
CREATE TABLE TABLENAME
AS
SELECT COLUMNNAME(A), COLUMNNAME(B), COLUMNNAME(C)
FROM TABLENAME;

-- 2-1. 테이블 내용 제거(DELETE FROM 이용)
DELETE FROM TABLENAME;
ROLLBACK;   -- 가능성 있음

-- 2-2. 테이블 내용 제거(ROLLBACK 이용)
TRUNCATE TABLE TABLENAME;
ROLLBACK;   -- 가능성 없음!!

-- 3. 테이블 자체 제거
DROP TABLE TABLENAME;

-- 4. 테이블 수정 (제약조건을 예시로)
ALTER TABLE TABLENAME
    ADD CONSTRATINT 제약조건NAME NOT NULL;
ALTER TABLE TABLENAME
    MODIFY COLUMNNAME CONSTRATINT 제약조건NAME NOT NULL;
ALTER TABLE TABLENAME    
    DROP contraint 제약조건NAME;

2. 다중테이블 데이터 입력

INSERT ALL	-- 또는 FIRST
    WHEN CONDITION1 THEN
        INTO TABLENAME(COLUMNNAME) VALUES(VALUE..)
    WHEN CONDITION2 THEN
        INTO TABLENAME(COLUMNNAME) VALUES(VALUE..)
    ELSE
        INTO TABLENAME(COLUMNNAME) VALUES(VALUE..)
SUB-QUERY;

3. 제약조건

CREATE TABLE TABLENAME(
    T_NO		NUMBER(4)		CONSTRAINT TABLENAME_TNO_PK PRIMARY KEY,
    T_PHONE		NUMBER(15)		CONSTRAINT TABLENAME_TPHONE_UK UNIQUE KEY,
    T_NAME		VARCHAR2(30)		CONSTRAINT TABLENAME_TNAME_NN NOT NULL,
    T_FAM		NUMBER(1000)		CONSTRAINT TABLENAME_TFAM_CK CHECK(T_FAM>=0),
    T_DATE		DATE			DEFAULT SYSDATE
    A_NO		NUMBER(4)		CONSTRAINT TABLENAME_ANO_FK FOREIGN KEY
    ON DELETE CASCADE		

    /* on delete cascade (구글 참조)
       when any entry is deleted from the parent table 
       then all the dependent rows in the child table should also get deleted */
);
COMMENT ON COLUMN "UESRNAME"."TABLENAME"."T_NO" IS 'Primary key of TABLENAME TABLE.';

- primary key : 유일하고 null이 아닌 값이 들어간다. 테이블을 대표하는 특징이 있다.

- unique key : 유일한 값이며, 중복될 수 없는 특징을 가지고 있다.

- not null : null 값이 입력되지 않도록 하기 위해 사용된다.

- check : 입력할 수 있는 범위를 제한하는 특징이 있다.

- foreign key : 다른 테이블과 연결시켜준다(reference).