[멋사 백엔드 19기] TIL 19일차 SQL JOIN, Schema, Key

Seonghun Park

Developer.

[멋사 백엔드 19기] TIL 19일차 SQL JOIN, Schema, Key

Sep 11, 2025

멋쟁이사자처럼
멋사
백엔드
TIL
Database

📂 목차

JOIN
Sub Query
Correlated Subquery
다양한 기능들
- Set Operation
- Window 함수
Schema
- Relation Schema
- Key
Schema Diagrams
Relational Query Languages
- SQL Languages Parts
SQL Data Definition
- Basic Types
- Basic Schema Definition

📚 본문

JOIN

JOIN은 여러 테이블의 데이터들을 연결시켜 하나의 결과 집합으로 만드는 방법이다.

table_references:
    escaped_table_reference [, escaped_table_reference] ...

escaped_table_reference: {
    table_reference
  | { OJ table_reference }
}

table_reference: {
    table_factor
  | joined_table
}

table_factor: {
    tbl_name [PARTITION (partition_names)]
        [[AS] alias] [index_hint_list]
  | [LATERAL] table_subquery [AS] alias [(col_list)]
  | ( table_references )
}

joined_table: {
    table_reference {[INNER | CROSS] JOIN | STRAIGHT_JOIN} table_factor [join_specification]
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
  | table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor
}

join_specification: {
    ON search_condition
  | USING (join_column_list)
}

join_column_list:
    column_name[, column_name] ...

index_hint_list:
    index_hint[ index_hint] ...

index_hint: {
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | {IGNORE|FORCE} {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
}

index_list:
    index_name [, index_name] ...

MySQL 문법 정의서 인데, FROM 절에서 테이블 참조 를 어떻게 쓸 수 있는지에 대한 규칙을 나열한 것이다. SELECT 문에서도 당연히 봤을 것이다.

CROSS JOIN

카테시안 곱이며, 그냥 가능한 모든 조합의 경우를 결과로 내뱉는다. 하지만 이는 잘 사용하지 않으며, 여러 열이 있다면 굉장히 많은 행이 생성되므로 성능적으로 문제가 있다.

크로스 조인은 그냥 두 테이블을 FROM 절에 놓으면 된다.

SELECT *
FROM customers c, orders o;  -- 조인 조건 없음

보통은 JOIN 을 할 때는 그래서 조건을 주는게 오버헤드가 없다.

만약 여러 테이블을 JOIN한다면, N개의 테이블을 JOIN할 때 최소 (N-1)개의 조인 조건이 필요, 그래야 불필요한 Cartesian Product 를 방지할 수 있다.

INNER JOIN

-- 전통적인 조인 방식
SELECT 
    e.first_name,
    e.last_name,
    d.department_name
FROM 
    employees e, 
    departments d
WHERE 
    e.department_id = d.department_id;

예전에는 위처럼 코딩을 했지만, 테이블을 FROM 에 나열하고 WHERE 조건을 지정하게 되면 조인 조건과 필터 조건이 섞여 있어서 헷갈릴 수가 있다. 또한 실수로 조인 조건을 빼먹으면 CROSS JOIN 이 되어버려 모든 조합의 데이터가 만들어지게 된다.

따라서 지금은 ANSI SQL 표준 JOIN 문법을 사용한다.

-- JOIN ~ ON
SELECT 
    e.first_name,
    e.last_name,
    d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- JOIN ~ USING (동일한 컬럼명일 때)
SELECT 
    e.first_name,
    e.last_name,
    d.department_name
FROM employees e
JOIN departments d USING(department_id);

-- NATURAL JOIN (자동으로 같은 이름 컬럼 조인)
SELECT * FROM employees NATURAL JOIN departments;

JOIN 성능 측면

INDEXING INNER JOIN 은 조건이 잘 걸려있으면 효율적이다. 예를 들어 대상 테이블의 index 가 걸려있었다고 해보자. 이때 필터링을 주는 테이블의 값들을 기준으로 이들을 찾아내기만 하면 되는데, 찾아낼 때도 searching 작업이 필요하기에 여기에 index 를 걸어주면 더 빠르게 찾아낼 수 있게 되는 것이다.

indexing 이 안걸려 있었다면 searching 작업이 느려지게 되어서 성능 이점을 볼 수가 없게 된다. 또한 JOIN 없이 쓰면 기본적으로 cartesian 이 수행되는데 이는 굉장히 느리다. JOIN 이 있기에 가능하다.

테이블 크기와 순서에 따른 성능

조인하는 테이블이 크면 클수록 성능 영향이 크다. 일부 DBMS는 조인 순서를 바꿔 최적화하는 기능이 있어 상관이 없지만, 큰 테이블을 먼저 JOIN 하면 임시 테이블이 커져서 느려질 수 있게 된다.

예를 들어 customers 테이블에 1000 개의 레코드가 있고, orders 테이블에 1000000 레코드가 있다고 하자.

-- orders(100만 행) 기준으로 먼저 customers 와 JOIN
SELECT o.id, o.order_date, c.name
FROM orders o
JOIN customers c
ON o.customer_id = c.id;

무조건 대상 테이블은 작아야 한다. 위처럼 order 테이블을 먼저 가져와버리면 많이 가져온 행에 대해서 검색해야 할 양이 늘어나게 된다. 또한 작은 테이블을 index 를 생성해야 index 크기가 작아져서 검색할 양이 줄어들기에 customers 의 테이블이 먼저 작성되어야 하는 것이다.

SELECT o.id, o.order_date, c.name
FROM customers c
JOIN orders o
ON c.id = o.customer_id

위처럼 작성해주면 되겠다. 그리고 WHERE 을 써서 넣는다면 대상 테이블 크기가 더 작아져서 더 효율적이게 되겠다. 밑은 그 예시다.

-- 특정 VIP 고객의 주문만 찾고 싶을 때
SELECT o.id, o.order_date, c.name
FROM customers c
JOIN orders o
ON c.id = o.customer_id
WHERE c.vip = true;

Sub Query, CTE

OUTER JOIN

아우터 조인에는 LEFT JOIN 과 RIGHT JOIN 이 있는데 기준만 다를 뿐 둘의 동작 방식은 동일하다.

LEFT JOIN 은 왼쪽 테이블의 모든 행이 전부 결과 테이블에 포함되어야 되며 RIGHT JOIN 은 그 반대다.

LEFT JOIN

-- 부서가 없는 직원도 포함
SELECT 
    e.employee_id,
    e.first_name,
    d.department_name
FROM employees e
LEFT OUTER JOIN departments d 
ON e.department_id = d.department_id;

-- LEFT JOIN으로 축약 가능
SELECT 
    e.employee_id,
    e.first_name,
    d.department_name
FROM employees e
LEFT JOIN departments d 
ON e.department_id = d.department_id;

RIGHT JOIN

-- 직원이 없는 부서도 포함
SELECT 
    e.employee_id,
    e.first_name,
    d.department_name
FROM employees e
RIGHT OUTER JOIN departments d 
ON e.department_id = d.department_id;

가령 두 테이블의 튜플들 전부를 결과 테이블에 나오도록, 즉 LEFT, RIGHT 둘 다 되도록 하게 하고 싶을 수 있다. 이를 FULL OUTER JOIN 이라고 하는데 이는 MySQL 에서는 지원하지 않기 때문에 LEFT JOIN, RIGHT JOIN, UNION 을 합쳐서 사용할 수 있겠다.

-- 방법 1: LEFT JOIN + RIGHT JOIN + UNION
SELECT 
    e.employee_id,
    e.first_name,
    d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
UNION
SELECT 
    e.employee_id,
    e.first_name,
    d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE e.department_id IS NULL;  -- 중복 제거를 위해 추가

Postgres, Oracle 등등

-- PostgreSQL, SQL Server, Oracle에서 가능한 구문
SELECT 
    e.employee_id,
    e.first_name,
    d.department_name
FROM employees e
FULL OUTER JOIN departments d 
ON e.department_id = d.department_id;

SELF JOIN

자기 자신을 그냥 JOIN 절의 테이블로 넣어버리면 된다.

-- 직원과 상사 정보 조회
SELECT 
    e.employee_id AS 사원ID,
    e.first_name AS 사원이름,
    m.employee_id AS 상사ID,
    m.first_name AS 상사이름
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;

Sub Query

nested class 처럼 여기서는 쿼리를 중첩시키도록 할 수 있다. 보통 sub query 는 잘 쓰지는 않는다. 그냥 Join 이 더 최적화에 좋고, 재사용이 어려우며, 디버깅이 불편하다는 단점이 있다. 그럼에도 불구하고 단일 값만을 가져오고 싶을 때는 sub query 를 쓰는게 더 좋을 수도 있고, 상황마다 가끔 쓰긴 한다.

Single-row Subquery

-- 평균 급여보다 적은 급여를 받는 사원
SELECT ename, sal
FROM emp
WHERE sal < (
    SELECT AVG(sal)
    FROM emp
);

-- 가장 먼저 입사한 사원
SELECT ename, hiredate
FROM emp
WHERE hiredate = (
    SELECT MIN(hiredate)
    FROM emp
);

Multi-row Subquery

-- IN 연산자 사용
SELECT ename, sal, deptno
FROM emp
WHERE deptno IN (
    SELECT deptno
    FROM dept
    WHERE loc IN ('NEW YORK', 'DALLAS')
);

-- ANY 연산자 사용
SELECT ename, sal
FROM emp
WHERE sal > ANY (
    SELECT sal
    FROM emp
    WHERE deptno = 30
);

-- ALL 연산자 사용
SELECT ename, sal
FROM emp
WHERE sal > ALL (
    SELECT sal
    FROM emp
    WHERE deptno = 30
);

Correlated Subquery

외부 쿼리와 내부 쿼리가 서로 연관되어 있는 서브쿼리이다.

-- 자신이 속한 부서의 평균 급여보다 많이 받는 사원
SELECT o.ename, o.sal, o.deptno
FROM emp o
WHERE o.sal > (
    SELECT AVG(i.sal)
    FROM emp i
    WHERE i.deptno = o.deptno
);

별 특별할건 없다.

다양한 기능들

Set Operation

UNION
MINUS
INTERSECT

Window 함수

보통 OVER 과 함께 동반되며, 사용할 때는 검색 후에 사용하는게 좋다.

RANK
ROW_NUMBER
DENSE_RANK

Schema

이제 이론을 들어가보자.

데이터를 DB에 저장할 때 그러면 테이블을 각각 만들어줘야 하는데, 이 테이블을 만들기 위해 사전 작업들(설계)이 많이 들어간다.

만약 이를 잘못 설계하면 나중에는 데이터 구조 자체를 변경하는데 굉장히 많은 비용이 들어가게 된다(이미 저장되어 있는 데이터 구조를 다른 데이터 구조로 바꿔야 하기 때문). 따라서 초기 설계가 중요하다. 그 기반이 되는 Schema 부터 본다.

데이터베이스를 얘기할 때는 항상 스키마 와 인스턴스를 구분해야 한다. 스키마는 DB 의 논리적인 설계를 의미하며, DB 인스턴스는 특정 시점에 DB 안에 담긴 데이터의 snapshot 을 의미한다.

데이터를 저장할 때는 relation 에 저장하게 되는데 relation 의 schema 를 보자.

Relation Schema

relation 이라는 개념은 프로그래밍 언어에서 변수 개념과 대응되게 되며, 릴레이션 스키마의 개념은 프로그래밍 언어에서의 타입 정의와 대응되게 된다.

Relation - Variable
Relation Schema - Type Definition

여기서 relation schema 라는 개념은 속성들과 그에 대응되는 도메인들의 목록으로 구성되게 된다. 각 속성의 도메인에 대한 정확한 정의는 추후에 다룬다.

relation instances 의 개념은 프로그래밍 언어에서 변수의 값에 해당하게 된다.

relation schema: section (course_id, sec_id, semester, …)
relation instance: (CS-101, 1, Fall, …)

Key

관계들의 인스턴스들을 보면 모든 행들은 서로 다 달라야 한다. 중복된 값이 계속해서 들어가게 되면 이는 자원 낭비이고 데이터가 우리에게 혼동을 줄 수 있다.

따라서 관게는 튜플을 구별할 수 있는 방법을 반드시 가져야 하고, 이는 Super Key 라는 하나 이상의 속성 집합을 써서 각 튜플을 유일하게 식별 가능하다.

$t1 \neq t2 \implies t1.K \neq t2.K$

Candidate Key

슈퍼키는 불필요한 속성들을 포함 할 수가 있다. 예를들어 ID 와 name 이라는 attr. 를 가지는 테이블 t1 이 있다고 하자.

슈퍼키는 ID로 두게 된다면 모든 행들을 고유하게 식별할 수 있지만, (ID, name) 으로 둬도 모든 행들을 고유하게 식별할 수 있다.

따라서 만약 K가 슈퍼키라면, K의 어떤 superset 또한 역시 슈퍼키가 된다. 보통 superset 보다는 더 이상 줄일 수 없는 최소 슈퍼키(Minimal Superkey) 에 더 관심을 가지고, 이러한 최소 슈퍼키를 Candidate Key 라고 부르게 된다.

여기서 또 다른 후보키의 예시로는 서로 다른 여러 속성 집합이 후보키가 될 수도 있는데, 예를들어 (name, dept_name) 과 ID 두 조합이 수퍼키가 될 수 있다고 해보자. 그러면 둘 다 후보키가 되는 셈이다.

Primary Key

이러한 후보키 중에서 DB Designer 가 relation 내의 tuple 을 식별하는 주요 수단으로 선택한 것을 Primary Key 로 부르게 된다.

$Super Key \subset Candidate Key \subset Primary Key$

그리고 Primary Key 를 Relation Schema 로 표기할 때 밑줄을 통해 표현하게 된다.

classroom (building, room_number, capacity)

이러한 기본키는 신중하게 선택해야 하는데, primary key 가 매우 드물게 변경되어야 하기 때문이다. 변경이 된다면 다음과 같은 문제가 생긴다.

참조 무결성: 기본키는 다른 테이블의 외래키로 참조될 수 있는데 값이 바뀌면 참조 관계가 깨질 수도 있다. 그렇다고 기본키 값을 업데이트하면 다른 참조하고 있는 외래키를 업데이트하면 되지 않냐고 하는데 이러면 비용적으로 부담이다.
데이터 일관성: 여러 테이블, 인덱스, 뷰, 어플리케이션 코드 등에서 해당 키를 기준으로 데이터를 찾는데, 계속 값이 바뀌면 시스템 전체에서 불일치가 발생할 수 있다.
성능 저하: 기본키는 보통 인덱스로 관리되는데, 기본키 값이 변경되면 인덱스도 다시 갱신해야되며, 대량 업데이트 시에 성능에 큰 영향을 주게 된다.
비즈니스 로직 혼란: 기본 키는 “변하지 않는 식별자” 라는 전제를 기반으로 여러 로직이 설계되기 때문에, 값이 바뀐다면 전제가 무너져 혼란을 초래할 수 있다.

primary key 는 그래서 다음과 같은 제약조건이 필요하다.

UNIQUE: 테이블에 오직 하나만 존재
NOT NULL: NULL 값 X
INDEX: 기본키로 등록되면 자동으로 인덱스를 생성한다.

Foreign Key

Foreign-key Constraints 를 살펴보자. t2 테이블에서 t1 테이블의 속성 값이 필요하다고 해보자. 그런데 해당 값이 t1 의 어디에도 존재하지 않는다면 이는 타당하지 않다. 즉 t2는 t1에 존재하는 값을 가져야 한다.

외래키 제약조건은 릴레이션 r1 속성 A 가 릴레이션 r2 의 기본키 B를 참조한다고 할 때, 어떤 데이터베이스 인스턴스에서도 r1 의 각 튜플이 가지는 A 값은 반드시 r2 의 어떤 튜플의 B 값과 일치해야 함을 의미한다.

Referential Integerity:
NULLITY: NULL 값을 가질 수 있음
CASCADE, RESTRICT, SET NULL: 참조 대상이 삭제되거나 변경될 경우 동작을 설정할 수 있음

Schema Diagrams

기본키는 밑줄로
릴레이션의 외래키는 참조되는 릴레이션의 기본키로 향하는 화살표로
외래키 제약조건이 아닌 참조 무결성 제약조건을 표현할 때는 양쪽 화살표로

현재는 E-R 다이어그램을 많이 쓴다.

Relational Query Languages

쿼리 언어는 사용자가 DB 로 부터 정보를 요청할 때 사용하는 언어를 말하는데, 이러한 언어들은 일반적인 프로그래밍 언어보다 높은 수준에서 동작한다.

쿼리언어는

imperative(명령형): 사용자가 DB에서 원하는 결과를 계산하기 위해 특정 연산의 순서를 시스템에 지시할 수 있음(일반적으로 상태 변수를 가짐)
functional(함수형): DB의 데이터나 다른 함수의 결과에 대해 동작하는 함수들의 평가(evaluation) 으로 표현된다. side-effect 가 거의 없어 프로그램 상태를 갱신하지 않는다.
declarative(선언형): 구체적인 연산 순서나 함수 호출을 명시하지 않고, 단순히 원하는 정보만을 기술한다.

으로 분류할 수 있다.

SQL Languages Parts

DDL
DML
Integrity
View Definition
Transaction Control
Embedded SQL & Dynamic SQL
Authorization

SQL Data Definition

DB 의 관계들의 집합은 DDL 을 사용하여 지정되는데, SQL DDL 은 단순히 릴레이션 집합뿐 아니라 각 릴레이션에 대한 다양한 정보를 정의할 수 있다.

relation schema
attribute type
integrity constraints
index set
physical storage structure

여기서는 스키마와 데이터타입만 보자.

Basic Types

char(n): 무조건 n byte 의 문자열이 할당, “ABC” 를 저장시 뒤에 공백 7개 문자가 추가됨
varchar(n): 공백이 추가되지 않음 n 이하의 문자열로 저장 가능

char, varchar 타입을 비교할 때 사용하는 DB 에 따라 직관적으로는 결과가 같아 보여도 실제로는 false, true 가 될 수도 있다. 이러한 문제 때문에 varchar 를 권장하는 편이다. 또한 다국어 데이터 저장을 위해 nvarchar 도 있다.

int
smallint
numeric(p, d): p자리.d자리
real, double precision
float(n)

각 타입은 null 값을 가질 수 있다.

Basic Schema Definition

create table department
(dept_name varchar(20),
 building varchar(15),
 budget numeric(12,2),
 primary key (dept_name));

자바랑 동일하게 sql 문은 항상 문장의 끝에 ;가 붙으며, 맨 마지막에 integrity constraints 들을 더 넣을 수 있다(primary key 처럼). 이러한 자주 쓰이는 constriaints 는 다음과 같다:

primary key ($A_1, A_2, …, A_m$)
foreign key ($A_1, A_2, …, A_m$) references
not null

다음 장에서 DDL 을 더 자세히 다루자.