본문 바로가기
Database/Database

SQL(4)

by curious week 2025. 3. 28.

 

1. 중첩 질의(Subquery)란?

하나의 SQL 문 안에 또 다른 SELECT 문이 포함된 것

  • 보통 괄호 () 안에 작성
  • 메인 쿼리가 서브쿼리(=중첩 질의)의 결과를 사용
  • WHERE, FROM, SELECT, HAVING 절 등 다양한 위치에 들어갈 수 있음

2. 기본 구조

SELECT 컬럼1, 컬럼2, ...
FROM 테이블명
WHERE 컬럼 비교연산자 (SELECT ... FROM ... WHERE ...);

3. 중첩 질의 위치별 설명

(1) WHERE절의 중첩 질의

조건식으로 사용하는 서브쿼리
→ "이 값과 같은 것", "이 목록에 포함된 것" 등

구조

SELECT 컬럼1, 컬럼2
FROM 테이블
WHERE 컬럼명 = (SELECT ... FROM ... WHERE ...);

최고 점수를 받은 학생

SELECT name, score
FROM Student
WHERE score = (SELECT MAX(score) FROM Student);

(2) FROM절의 중첩 질의

서브쿼리를 가상의 테이블로 간주
→ 복잡한 집계 결과를 한 번 더 조회하고 싶을 때 사용

구조

SELECT 컬럼1, 컬럼2
FROM (
    SELECT ...
    FROM ...
    WHERE ...
) AS 서브테이블
WHERE 조건;

학과별 평균점수가 80 이상인 학과만 보여주기

SELECT department, avg_score
FROM (
    SELECT department, AVG(score) AS avg_score
    FROM Student
    GROUP BY department
) AS dept_avg
WHERE avg_score >= 80;

고급 중첩 질의 

EXISTS / ANY / ALL / 스칼라 서브쿼리 / 상관 서브쿼리

1. EXISTS

서브쿼리의 결과 존재 여부를 확인하는 조건문

구조

SELECT 컬럼
FROM 테이블
WHERE EXISTS (
    SELECT * FROM 다른_테이블 WHERE 조건
);

특징

  • 서브쿼리가 결과를 한 행이라도 반환하면 TRUE
  • NOT EXISTS는 반대 의미

주문한 기록이 있는 고객만 조회

SELECT name
FROM Customer c
WHERE EXISTS (
    SELECT * FROM Orders o
    WHERE o.customer_id = c.id
);

2. ANY

서브쿼리의 어떤 값과라도 조건이 성립하면 TRUE

구조

SELECT 컬럼
FROM 테이블
WHERE 컬럼비교연산자 ANY (
    SELECT ... FROM ...
);

특징

  • > + ANY: 서브쿼리 값 중 하나보다 크면 TRUE
  • = + ANY: IN과 유사

어떤 부서 평균보다 높은 급여

SELECT name
FROM Employee
WHERE salary > ANY (
    SELECT AVG(salary)
    FROM Employee
    GROUP BY department
);

3. ALL

서브쿼리의 모든 값에 대해 조건이 성립하면 TRUE

구조

SELECT 컬럼
FROM 테이블
WHERE 컬럼비교연산자 ALL (
    SELECT ... FROM ...
);

특징

  • > + ALL: 서브쿼리의 모든 값보다 커야 함
  • = + ALL: 모든 값이 같을 때만 TRUE

모든 부서 평균보다 높은 급여

SELECT name
FROM Employee
WHERE salary > ALL (
    SELECT AVG(salary)
    FROM Employee
    GROUP BY department
);

4. 스칼라 서브쿼리 (Scalar Subquery)

단일 값만 반환되는 서브쿼리 → 마치 컬럼처럼 사용

구조

SELECT 컬럼, (SELECT ... FROM ...) AS 새컬럼
FROM 테이블;

각 직원의 부서 평균 급여 함께 출력

SELECT name,
       salary,
       (SELECT AVG(salary)
        FROM Employee e2
        WHERE e2.department = e1.department) AS dept_avg
FROM Employee e1;

→ SELECT 절 안의 서브쿼리는 반드시 하나의 값만 반환해야 함 (여러 행 반환 시 오류)

5. 상관 서브쿼리 (Correlated Subquery)

메인 쿼리의 행 하나하나에 대해 서브쿼리가 반복 실행됨

구조

SELECT 컬럼
FROM 테이블 메인
WHERE 컬럼비교연산자 (
    SELECT ... FROM 테이블 서브
    WHERE 서브.컬럼 = 메인.컬럼
);

특징

  • 서브쿼리가 메인 쿼리의 컬럼을 참조
  • 실행 성능에 주의 필요

자기 부서 평균보다 급여가 높은 직원

SELECT name, salary
FROM Employee e1
WHERE salary > (
    SELECT AVG(salary)
    FROM Employee e2
    WHERE e2.department = e1.department
);

 

1. 조인(Join)의 개념

조인은 둘 이상의 테이블을 공통 컬럼을 기준으로 연결하여, 하나의 결과로 관련 데이터를 함께 조회하는 질의 기법입니다.

  • 테이블 간의 관계를 통해 더 풍부한 데이터를 조회할 수 있음
  • 기본키–외래키 관계가 많지만 꼭 외래키가 아니어도 조인 가능
  • 대표적인 조인: INNER, OUTER, CROSS, SELF

2. 기본 조인 문법 구조

SELECT A.컬럼1, B.컬럼2, ...
FROM 테이블A A
[INNER | LEFT OUTER | RIGHT OUTER | FULL OUTER | CROSS] JOIN 테이블B B
ON A.공통컬럼 = B.공통컬럼;

 

셀프 조인 (Self Join)

같은 테이블을 자기 자신과 조인
→ 계층 구조나 참조 관계 표현에 자주 사용

개념

  • 테이블에 자체 참조 관계가 있을 때 사용 (예: 사원의 상사, 폴더 안의 폴더)
  • 테이블에 별칭을 부여해 두 번 등장시켜 조인

구조

SELECT A.컬럼, B.컬럼
FROM 테이블 A
JOIN 테이블 B
ON A.참조컬럼 = B.기준컬럼;

 

내부 조인 (Inner Join)

양쪽 테이블 모두 조건을 만족하는 행만 결과에 포함
→ 공통되는 데이터만 조회 (교집합)

개념

  • 두 테이블의 공통 컬럼 값을 기준으로 연결
  • 조건이 일치하지 않으면 해당 행은 제거

구조

SELECT A.컬럼, B.컬럼
FROM A
INNER JOIN B
ON A.공통컬럼 = B.공통컬럼;

 

외부 조인 (Outer Join)

한쪽 테이블의 모든 행을 유지, 다른 쪽은 조건에 맞을 때만
→ 부족한 데이터는 NULL로 채움

 

FULL OUTER JOIN – 둘 다 유지 (합집합)

SELECT A.컬럼, B.컬럼
FROM A
FULL OUTER JOIN B
ON A.공통컬럼 = B.공통컬럼;

 

 

3. 조인 종류

(1) INNER JOIN – 교집합

양쪽 테이블 모두에서 조건을 만족하는 행만 반환
→ 가장 일반적인 조인

SELECT s.name, d.name AS dept_name
FROM Student s
INNER JOIN Department d
ON s.dept_id = d.id;

Student와 Department 테이블에서 dept_id = id인 학생–학과 정보를 함께 조회

외부조인(OUTER JOIN)은 값 없으면 NULL로 표현된다.

(2) LEFT OUTER JOIN – 왼쪽 기준 포함

왼쪽 테이블의 모든 행 + 조건 만족하는 오른쪽 데이터
→ 오른쪽 값 없으면 NULL로 표현된다.

SELECT s.name, d.name AS dept_name
FROM Student s
LEFT OUTER JOIN Department d
ON s.dept_id = d.id;

학과가 없는 학생도 NULL로 표시됨

(3) RIGHT OUTER JOIN – 오른쪽 기준 포함

오른쪽 테이블의 모든 행 + 조건 만족하는 왼쪽 데이터

SELECT s.name, d.name AS dept_name
FROM Student s
RIGHT OUTER JOIN Department d
ON s.dept_id = d.id;

학과는 존재하지만, 소속 학생이 없는 경우도 함께 조회

(4) FULL OUTER JOIN – 합집합

(MySQL은 지원하지 않음, UNION으로 우회 가능)

양쪽 테이블 모두의 모든 행을 포함하며 조건이 맞지 않으면 NULL 채워짐

SELECT s.name, d.name AS dept_name
FROM Student s
FULL OUTER JOIN Department d
ON s.dept_id = d.id;

(5) CROSS JOIN – 데카르트 곱

조건 없이 모든 조합 생성 (예: 3행 × 2행 → 6행 생성)

SELECT s.name, d.name
FROM Student s
CROSS JOIN Department d;

실전에서는 거의 사용되지 않음 (조건 없이 곱하기 때문에 위험할 수 있음)

(6) SELF JOIN – 자기 자신과의 조인

같은 테이블에서 계층 구조, 참조 관계 등을 위해 사용

SELECT A.name AS 사원, B.name AS 상사
FROM Employee A
LEFT JOIN Employee B
ON A.manager_id = B.id;

사원과 그 사원의 상사 이름을 한 줄에 조회

(7) NATURAL JOIN – 공통 컬럼을 기준으로 자동 조인

두 테이블에서 이름이 같은 컬럼을 자동으로 찾아 조인하는 방식
→ ON 조건 필요 없이 조인됨

실무에서는 주의 필요!  컬럼명이 같은 경우 자동 매칭되므로 컬럼명이 우연히 같지만 의미가 다르면 오류 가능성 있음

구조

SELECT 컬럼1, 컬럼2, ...
FROM 테이블1
NATURAL JOIN 테이블2;

 

학생과 학과 정보를 공통 컬럼 dept_id 기준으로 자동 조인

-- 공통 컬럼 dept_id를 기준으로 자동 조인
SELECT s.name AS 학생이름, d.name AS 학과명
FROM Student s
NATURAL JOIN Department d;

이 쿼리는 Student와 Department 테이블에서 컬럼명이 같은 dept_id를 기준으로 자동 조인하여, 학생의 이름과 소속 학과명을 한 줄에 함께 보여줍니다.

4. 종합 정리표

INNER JOIN 양쪽 테이블 모두 조건 만족 교집합
LEFT OUTER JOIN 왼쪽 테이블 전체 + 오른쪽 일치 왼쪽 기준 포함
RIGHT OUTER JOIN 오른쪽 테이블 전체 + 왼쪽 일치 오른쪽 기준 포함
FULL OUTER JOIN 양쪽 테이블 전체 합집합
CROSS JOIN 조건 없이 모든 조합 데카르트 곱
SELF JOIN 자기 자신과 조인 계층 관계 표현

1. 뷰(View)의 개념

뷰(View)는 하나 이상의 테이블에서 SELECT 결과를 이름 붙여 저장한 가상의 테이블입니다.
실제 데이터를 저장하지 않고, 원본 테이블을 참조합니다.

뷰의 특징

  • 테이블처럼 조회 가능
  • 복잡한 쿼리를 단순하게 만들어줌
  • 보안 목적(민감한 컬럼 숨기기)에 사용 가능
  • DML(SELECT, INSERT, UPDATE, DELETE)이 제한적으로 가능 (원본 테이블에서 실행)

2. 뷰 생성 (CREATE VIEW)

구조

CREATE VIEW 뷰이름 AS
SELECT 컬럼1, 컬럼2, ...
FROM 테이블
WHERE 조건;

CS_Students 뷰는 컴퓨터공학과 학생만 보여주는 가상 테이블

CREATE VIEW CS_Students AS
SELECT id, name, dept_id
FROM Student
WHERE dept_id = 'CS';

3. 뷰 수정 (OR REPLACE)

뷰의 정의(구조)를 바꾸고 싶을 때는 덮어쓰기 방식으로 수정

구조

CREATE OR REPLACE VIEW 뷰이름 AS
SELECT ...

기존 뷰를 덮어씌움 (이전 정의는 사라짐)

CREATE OR REPLACE VIEW CS_Students AS
SELECT id, name
FROM Student
WHERE dept_id = 'CS';

4. 뷰 삭제 (DROP VIEW)

구조

DROP VIEW 뷰이름;

해당 뷰가 완전히 삭제되며, 참조도 불가

DROP VIEW CS_Students;

5. 뷰를 이용한 데이터 검색 (SELECT)

구조

SELECT * FROM 뷰이름
[WHERE 조건];

뷰를 일반 테이블처럼 사용해 필터링, 정렬 가능

SELECT * FROM CS_Students
WHERE name LIKE '김%';

6. 뷰를 통한 데이터 수정 (UPDATE, INSERT, DELETE)

뷰로 데이터를 수정하려면:

  • 단일 테이블 기반이어야 함
  • 집계 함수, GROUP BY, DISTINCT, JOIN 등이 없어야 함
  • 원본 테이블의 컬럼과 매핑이 정확해야 함

UPDATE 

UPDATE CS_Students
SET name = '김수정'
WHERE id = 101;

INSERT

INSERT INTO CS_Students (id, name, dept_id)
VALUES (999, '이신규', 'CS');

DELETE

DELETE FROM CS_Students
WHERE id = 999;

이 모든 동작은 실제 Student 테이블에 반영

보안용 뷰

CREATE VIEW Public_Student AS
SELECT name, dept_id
FROM Student;

id, 생년월일, 연락처 같은 민감한 컬럼을 제외한 부분 데이터만 외부에 노출

 

뷰(View)에서 자주 사용하는 제약 옵션

WITH CHECK OPTION과 WITH READ ONLY는 동시에 사용할 수 없음 → 둘 중 하나만 적용 가능!

WITH CHECK OPTION

뷰를 통해 수정(insert/update)할 때,
뷰의 WHERE 조건을 벗어나는 데이터는 반영되지 않도록 제한하는 옵션입니다.

개념

  • 뷰를 통해 데이터를 수정할 때
    조건을 만족하지 않는 데이터가 들어가는 걸 방지
  • 즉, 뷰에서 정의한 범위 밖의 데이터를 추가/수정할 수 없도록 강제

문법 구조

CREATE VIEW 뷰이름 AS
SELECT ...
FROM 테이블
WHERE 조건
WITH CHECK OPTION;

 

시도 (오류 발생)

UPDATE CS_Students
SET dept_id = 'ME'
WHERE id = 101;

→ 뷰 조건 dept_id = 'CS'을 벗어나므로 오류

 

WITH READ ONLY

해당 뷰를 읽기 전용으로 만들어서
INSERT / UPDATE / DELETE를 모두 차단합니다.

문법 구조

CREATE VIEW 뷰이름 AS
SELECT ...
FROM 테이블
[WHERE 조건]
WITH READ ONLY;

 

시도 (오류 발생)

INSERT INTO ReadOnly_Students (id, name)
VALUES (999, '이신규');

→ READ ONLY 뷰이므로 삽입 불가능

저작자표시 동일조건

'Database > Database' 카테고리의 다른 글

저장 객체  (1) 2025.04.16
정규화  (0) 2025.04.16
SQL(3)  (0) 2025.03.28
SQL(2)  (0) 2025.03.28
SQL(1)  (1) 2025.03.28

관련글

티스토리툴바