[SQL] EXISTS 와 NOT EXISTS / 조인 / 뷰

EXISTS 와 NOT EXISTS 

일반적으로 중첩질의의 경우 내부질의가 우선적으로 수행되고, 그 결과를 외부질의에서 사용하지만
EXISTS나 NOT EXISTS가 사용되면 외부질의의 각 레코드에 대해 내부 질의의조건을 만족하는지 검사하게된다.

 

EXISTS

외부질의의 각 레코드에 대해 내부질의의 결과에 레코드가 존재하면 참을 반환하고,존재하지않으면 거짓을반환

 

NOT EXISTS

존재하지않으면 참, 존재하면 거짓을 반환

• 특정 제품을 구매한 고객의 모든 정보를 조회

SELECT *
FROM customers
WHERE EXISTS (
    SELECT *
    FROM orders
    WHERE orders.customer_id = customers.id
    AND orders.product_id = 123
);

 

• 특정 제품을 구매하지 않은 고객의 모든 정보를 조회

SELECT *
FROM customers
WHERE NOT EXISTS (
    SELECT *
    FROM orders
    WHERE orders.customer_id = customers.id
    AND orders.product_id = 123
);

 

---

 

조인 질의

질의결과에 서로다른테이블에 존재하는 컬럼들이 포함되어있을때 사용
FROM절에는 테이블이 최소 2개이상 열거되어야함
ON 또는 WHERE절에는 각 테이블의 레코드들간의 연관성을 기술
일반적으로 조인에서는 PK와 FK를 사용하여 연관성을 표현
대표적으로 내부조인과 외부조인이있음
 

내부조인 

두개 이상의 테이블에서 조인조건을 만족하는 레코드만 결합하여 결과에 포함시키는 연산
ON절- 관련성을 표현하기위한 조인조건을 기술

SELECT 컬럼1, 컬럼2, ...
FROM 테이블1 INNER JOIN 테이블2
ON 조인조건1
[WHERE 조건]

 

• ANSI SQL 표현

SELECT 
	학생.학생이름, 
	학생.나이,
	계좌.계좌번호,
	계좌.계좌잔액
FROM 학생 INNER JOIN 계좌
ON 학생.학생번호 = 계좌.계좌번호
WHERE 학생.나이 >= 30

 

• 오라클사가 제안한 JOIN 형식

SELECT 
	학생.학생이름, 
	학생.나이,
	계좌.계좌번호,
	계좌.계좌잔액
FROM 학생,계좌
WHERE 학생.학생번호 = 계좌.계좌번호
AND 학생.나이 >= 30

 

자연조인

내부조인은 두개이상의 테이블을 하나의 테이블로 결합하는 내부조인과 매우 유사한기능
두테이블에 동일한 이름의 컬럼에 대해 값이 같은 레코드를 결합하는 내부조인
내부조인단점해결 - 조인조건을 기술해야하고 동일한 컬럼에 대한 추가적인 조치가 SELECT절에 필요한 번거로운 단점이있음. 자연조인질의를 사용하면 간단히 해결가능
 

SELECT 컬럼1, 컬럼2, ...
FROM 테이블1 NATURAL JOIN 테이블2
[WHERE 조건]

 

외부조인

내부조인은 조인조건에 일치하는 레코드만 결합하여 결과를생성하므로 조인결과에 정보의 손실이 발생
외부조인은 조인조건이 맞지않는 레코드도 질의 결과에 포함시킨다외부조인의 종류
- LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN

SELECT 
	별칭1.컬럼1,
	별칭1.컬럼2,
	...,
	별칭1.컬럼m,
FROM 
	테이블1 AS 별칭1
	LEFT | RIGHT [OUTER] JOIN
	테이블2 AS 별칭2
ON 별칭1.컬럼i = 별칭2.컬럼j

 

셀프조인

한 테이블이 자기자과 조인되는 형태
동일한 이름의 테이블에 대한 조인이므로 반드시 테이블이름에 대한 별칭이 의무적으로 사용

SELECT 별칭1.컬럼1, 별칭1.컬럼2...
	별칭2.컬럼2, 별칭2.컬럼2....
FROM 테이블1 AS 별칭1
	INNER | OUTER JOIN 테이블2 AS 별칭2
ON 조인조건
[WHERE절]

 

---

 

뷰 

뷰의 개념

1. 데이터를 저장하고 있는 하나이상의 테이블을 유도하여 생성하는 가상의 테이블
뷰 사용이유

• 데이터 독립성: 원본 테이블의 구조가 바뀌어도 뷰를 이용한 작업은 정의만 변경되어 응용프로그램에 영향이없음
• 데이터보안 : 사용자에게 원본 테이블의 일부컬럼에 대한 접근을 허용하여 보안 효과를 향상
• 다양한 구조의 테이블 사용 : 사용자의 요구사항에 맞는 테이블 구조를 제공
• 작업의 단순화 : 복잡한 질의문을 뷰로 단순화
• 데이터 무결성 : WITH CHECK OPTION을 이용하여 뷰생성에 위배되는 수정작업을 거부

뷰의 생성

1. 생성되는 뷰의 구조는 SELECT문의 결과로 결정

CREATE VIEW 뷰이름 AS (
SELECT ...
FROM 테이블
[WHERE 조건]
)
[WITH CHECK OPTION]

 

뷰의 수정 및 삭제

뷰의 수정은 생성과 동일하게 새로운 SELECT문의 결과로 변경

ALTER VIEW 뷰이름(컬럼1,컬럼2...) AS
(
SELECT 컬럼1, 컬럼2...
FROM 테이블
[WHERE조건]
)

DROP VIEW 뷰이름

 

뷰 생성 예제

컴퓨터과학과 소속의 학생정보와 학과이름 및 이수학점을 출력하는 '컴퓨터과학과_학생'뷰를 생성하시오.

SELECT 
	학생.*,
	전공.학과이름,
	전공.이수학점,
FROM 학생
NATURAL JOIN 전공
WHERE 전공.학과이름 = '컴퓨터과학과'

 

 

CREATE VIEW 컴퓨터과학과_학생 AS
(SELECT 
	학생.*,
	전공.학과이름,
	전공.이수학점
 FROM 학생
 NATURAL JOIN 전공
 WHERE 전공.학과이름 = '컴퓨터과학과'
 )

 

뷰는 가상의 테이블이므로 데이터 조작은 테이블 조작과 동일하게 수행한다

SELECT 
	컬럼1,
	...,
	컬럼2 
FROM 뷰이름
WHERE 조건

SELECT 
	컬럼1,
	...,
	컬럼2
FROM 뷰이름
WHERE 조건
AND 뷰정의조건

뷰를 통한 데이터 삽입

뷰에 대한 INSERT 문은 원본테이블에서 실행

PRIMARY KEY, NOT NULL등의 제약사항이 위배되는경우 삽입이불가능
원본테이블에 존재하는 컬럼이지만 뷰에는 없는 컬럼에 삽입하는 경우 실행불가능
조인 질의 또는 그룹질의가 적용된 뷰는 데이터 삽입 및 수정이 불가능
WITH CHECK OPTION이 적용된 뷰는 위배되는 사항은 없지만 뷰에 맞지않는 조건일 경우 실행 불가능