[MySQL] 데이터베이스의 이해 및 JOIN

데이터베이스란?

데이터베이스는 특정 목적을 위해 구조화된 데이터의 모음입니다. 이 뜻을 제대로 이해하기 위해서는 먼저데이터에 대해서 알아야 합니다. 데이터(Data)란 단순한 사실이나 값들의 모음입니다. 예를 들어 숫자 '10', 날짜 '2026-02-01', '민순' 등 의미나 맥락이 없는 상태입니다. 이러한 값들 자체로는 어떤 의미인지 알기 어렵습니다.

그래서 우리는 이러한 데이터를 가공하거나 분석하여 의미를 부여하는 것을 정보(Information)라고 하며, 정보를 이용하여 상황에 맞게 데이터들을 활용할 수 있습니다. 이렇게 만들어진 정보들을 체계적이고 효율적으로 저장하고 관리하는 곳이 바로 데이터 베이스입니다. ex) 제품, 가격, 수량, 고객 정보 등을 특정 형식의 파일에 정리해 둔 것

데이터베이스 관리 시스템(DBMS)이란?

데이터베이스 관리 시스템(DBMS)이란, 데이터를 구조를 정의하고, 저장하며 조작하고 관리하기 위한 소프트웨어(시스템)입니다.

이 때 DBMS에게 요청하기 위해 DDL 과 DML등의 언어를 사용하고, 이러한 명령어들의 집합이 바로 SQL(Structured Query Language)입니다. SQL은 그 목적에 따라 여러 종류로 나뉩니다. 그러면 이 언어들을 알아보기 전에, DBMS를 구성하는 요소의 개념을 먼저 간단히 알아보겠습니다.

DBMS 구성 요소

구분	설명
SQL	DBMS와 통신하는 언어
DATABASE	데이터가 저장되는 논리적 공간
SCHEMA	테이블 구조 정보
TABLE 테이블	데이터가 저장되는 기본 단위
Column 열	데이터의 속성(열)
Row 행	데이터의 속성(행)

SQL 명령어의 4가지 종류

데이터 정의어 (DDL - Data Definition Language)

DDL은 데이터베이스의 구조를 정의하는 언어입니다. 이 뜻은 테이블 생성 및 삭제, 구조를 바꾸는 '설계 단계'에 해당하는 SQL이며, 데이터가 들어갈 '그릇'을 만드는 언어라고 생각하면 됩니다. 즉 데이터 자체보다 구조(Structure)를 다루며 각 테이블에 어떤 항목(ID, 이름 등), 타입(정수, 문자열 등), 제약조건(NOT NULL 등)을 가져야 할지 등을 정의 합니다. 대표적인 명령어는 아래와 같습니다.

명령어	설명
CREATE	객체 생성
ALTER	객체 구조 변경
DROP	객체 삭제
TRUNCATE	데이터 전체 삭제(구조 유지)

--user 테이블 생성
CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

--user 테이블에 email 컬럼 추가
ALTER TABLE user ADD email VARCHAR(100);

-- user 테이블 삭제
DROP TABLE user;

*ALTER TABLE은 구조를 바꾸는 명령이기에, 열(column)만 다루며, 데이터인 행(row)는 다루지 않습니다.

즉 열(Column)은 테이블의 구조(id, name, age 등)이고 Row(행)은 테이블의 내용물(minsoonId, 김민순,20)입니다.

데이터 조작어 (DML - Data Manipulation Language)

DML은 테이블 안의 데이터를 실제로 조작하는 언어입니다. 데이터베이스의 구조가 정의 되면(DDL), 사용자는 이 구조에 맞춰 데이터를 등록, 조회, 수정, 삭제를 하는 '운영 단계'에 해당합니다. 테이블의 구조는 조작하지 않으며, 오직 데이터만 조작 합니다.

명령어	설명
INSERT	데이터 추가
SELECT	데이터 조회
UPDATE	데이터 수정
DELETE	데이터 삭제

-- 데이터 행(Row) 추가
INSERT INTO user (id, name, age)
VALUES (1, '민재', 25);

-- 데이터 조회
SELECT * FROM user;

--데이터 수정
UPDATE user
SET age = 26
WHERE id = 1;

--데이터 삭제
DELETE FROM user
WHERE id = 1;

그릇(DDL / 구조)이 없으면, 데이터(DML / 물건)을 담을 수 없고, 반대로 그릇만 있고, 담을 데이터가 없다면 의미가 없습니다.

그래서 DDL과 DML은 역할을 나눠 항상 함께 쓰입니다.

데이터 제어어(DCL - Data Control Language)

DCL은 데이터에 대한 보안과 접근 권한을 제어 하는 언어입니다. 사용자에게 권한 부여 및 회수를 하고 데이터베이스의 보안을 유지합니다.

명령어	설명
GRANT	권한 부여
REVOKE	권한 회수

--app_user에게 user 테이블 조회 권한 부여
GRANT SELECT ON user TO app_user;

-- app_user에게 데이터 삽입 권한 제거
REVOKE INSERT ON user FROM app_user;

트랜잭션 제어어(TCL- Transaction Control Language)

TCL은 DML에 의해 수행된 데이터 변경 작업들을 하나의 거래(Transcation) 단위로 묶어서 관리하는 언어입니다.

예를 들어 A계좌에서 B 계좌로 50,000을 이체한다고 생각 하겠습니다. 해당 작업은 아래와 같은 단계로 나누어 처리됩니다.

1. A 계좌에서 출금

2. B 계좌로 입금

그런데 1번(A 계좌 출금)은 성공하고, 2번(B 계좌 입금)에서 에러가 발생한다고 가정을 하면, A계좌에서는 돈이 빠졌으나, B계좌에서는 돈이 들어오지 않아 50,000원이 사라지게 됩니다.

이러한 상황을 방지하기 위해 TCL은 1번과 2번의 작업을 한 개의 단위로 묶어서 관리하며 2번 작업까지 성공을 해야, 결과가 반영이 되며, 만약 위 조건에서 한 개의 조건이라도 실패한다면, 반영되지 않습니다.

즉 여러 SQL을 하나의 작업으로 만들고 성공하면 확정(COMMIT), 실패하면 없던일로 되돌립니다.(ROLLBACK / SAVEPOINT)

명령어	설명
COMMIT	변경 사항 확정
ROLLBACK	변경 사항 취소
SAVEPOINT	중간 저장 지점

SQL 명령어 정리

SQL 명령어
DDL	구조 설계
DML	데이터 사용
DCL	보호
TCL	안정성

SQL 실행 순서

이제는 SELECT FROM 등 다양한 SQL절의 실행 순서에 대해서 알아 보겠습니다.

집계 함수(SUM,MAX 등)는 '그룹이 만들어진 뒤'에만 사용할 수 있기에, HAVING과 SELECT에서 사용이 가능합니다.

GROUP BY가 없어도 전체 테이블을 하나의 그룹으로 인식하기에 집계 함수를 사용할 수 있지만, 해당 경우에는 SELECT 절에 집계 함수만 올 수 있습니다.(GROUP BY는 데이터를 묶고 그룹 하나당 결과 행이 하나씩 생성)

SQL 실행 순서
FROM	어떤 테이블에서 데이터를 가져올지 결정(JOIN 포함)
WHERE	FROM에서 가져온 테이블의 '개별 행' 필터링
GROUP BY	필터링된 행들을 기준으로 그룹 형성
HAVING	형성된 그룹들을 필터링 *집계 함수 사용 가능
SELECT	보여줄 컬럼 결정 *집계 함수 사용 가능 및 별칭(AS) 부여
ORDER BY	SELECT 결과 정렬 *실행 순서가 SELECT 뒤이기에 AS 사용 가능
LIMIT	최종적으로 반환할 행의 개수 제한

-- COMMIT(계좌 이체가 정상적으로 마무리)
BEGIN;

UPDATE account SET balance = balance - 100000 WHERE id = 'A';
UPDATE account SET balance = balance + 100000 WHERE id = 'B';

COMMIT;

-- ROLLBACK (중간에 에러 발생)
BEGIN;

UPDATE account SET balance = balance - 100000 WHERE id = 'A';

UPDATE account SET balance = balance + 100000 WHERE id = 'B';


ROLLBACK;

-- SAVEPOINT (작업 중 특정 지점까지만 되돌리기)
BEGIN;

INSERT INTO orders VALUES (1, '민재');
SAVEPOINT sp1;

UPDATE product SET stock = stock - 1 WHERE id = 10;
SAVEPOINT sp2;

INSERT INTO payment VALUES (1, 50000);
-- 결제 실패

제약 조건(Constraint)

제약 조건은 테이블에 저장되는 데이터의 규칙을 정해, 잘못된 데이터가 들어오는 것을 막는 장치입니다. 저희는 제약 조건을 통해 중복 데이터 및 필수 값 누락을 방지할 수 있습니다. 즉 데이터에 결점이 없는 상태인 데이터 무결성을 유지하는 것이 목표입니다.

NOT NULL

이 제약 조건은 값이 없는 것을 허용하지 않아, 제약 조건이 걸린 열은 INSERT 할때 반드시 값이 반드시 있어야 합니다. 주로 비어 있으면 안 되는 핵심 정보(주문 정보, 이름 등)에 설정이 필요합니다.

UNIQUE

이 제약 조건이 걸린 열의 값은 중복이 불가능 하여, 항상 고유해야 합니다. UNIQUE는 여러 열에 설정 할 수 있으며, 주로 중복이 되면 안되는 값(이메일 아이디, 사업자 등록번호 등)에 설정이 필요합니다.

PRIMARY KEY(PK)

PK는 '기본 키' 라고도 불리며 테이블에서 각 행을 구분하기 위한 기준이 되는 열로, 중복이 될 수 없는 값(NULL 불가)이며, 하나의 행을 정확하게 식별할 수 있습니다. NOT NULL과 UNIQUE의 특징을 동시에 가지며, 모든 테이블에는 반드시 하나의 PK가 있어야 합니다.

*AUTO_INCREMENT는 PK와 자주 사용되는 옵션으로, 정수 타입의 PK열에 이 옵션을 설정하면 새로운 데이터가 추가 될때마다 1씩 자동으로 증가하는 번호를 할당 해줍니다.

-- id에 PK 및 AUTO_INCREMENT 설정 
CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    email VARCHAR(50)
);

-- 해당 테이블에 값 추가
INSERT INTO user (name, email) VALUES ('민순', 'minjae@test.com');
INSERT INTO user (name, email) VALUES ('자바', 'java@test.com');
INSERT INTO user (name, email) VALUES ('언어', 'sql@test.com');

AUTO_INCREMENT를 하면 id의 열에는 값을 추가하지 않아도 1씩 자동 증가합니다.

FOREIGN KEY(FK)

FK는 '외래 키'라고도 불리며, 다른 테이블의 PK를 참조하는 열입니다. 즉, 다른 테이블에 있는 PK값과 연결되는 연결 고리입니다.

PK를 제공하는 쪽이 부모이며, 그 PK를 FK로 들고 있으면 자식이 됩니다.

ex) 자식 테이블(FK) -> 부모 테이블(PK) 화살표가 가리키는 쪽이 부모

--부모 테이블(PK 제공)
CREATE TABLE user (
    user_id INT PRIMARY KEY,
    name VARCHAR(20)
);

--자식 테이블(FK로 참조)
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    product VARCHAR(30),
    FOREIGN KEY (user_id) REFERENCES user(user_id)
    --user_id키가 FK이며, user테이블의 PK(user_id)를 참조
);

DEFAULT

DEFAULT는 값을 안 넣었을 때 자동으로 들어가는 기본 값입니다. DEFAULT는 항상 값이 필요한 열에 설정 하며, 주로 ID 생성 날짜 등에 많이 사용 합니다.

-- 가입 날짜 DEFAULT 설정
CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    join_date DATE DEFAULT CURRENT_DATE
);

-- 데이터 추가
INSERT INTO user (name) VALUES ('민순');

CHECK

CHECK는 둘어올 수 있는 값의 범위를 제한하는 규칙입니다. 주로 나이, 점수 수량 제한 등에 사용합니다.

--나이는 항상 양수로 설정
CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    age INT CHECK (age >= 0)
);

--값 추가
INSERT INTO user (name, age) VALUES ('민순', 20); -- 성공
INSERT INTO user (name, age) VALUES ('민순', -5); -- 실패

조인(Join)이란?

Join은 테이블 여러 개를 하나처럼 이어서 보는 것입니다. 즉 서로 관련 있는 테이블을 공통된 컬럼(PK-FK) 기준으로 연결해서 하나의 결과로 조회하는 것입니다.

Join이 필요한 이유

저희는 중복을 없애고, 원활한 관리와 데이터 오류를 방지 즉, 데이터 무결성을 지키기 위하여 여러개의 테이블을 나누어 저장합니다. 쇼핑몰로 예를 들어 상품 테이블, 주문 테이블, 회원 테이블이 있다고 가정 하겠습니다. 저희는 '노트북을 주문한 고객의 이름과 주문 날짜는 언제인지' 와 같은 연결된, 의미있는 데이터를 조회하기 위해서는 JOIN이 필요합니다.

그렇다면 처음부터 한 개의 테이블에 모든 값을 담으면 JOIN을 안써도 될까요?

한 개의 테이블만 이용해도 되지만, 이는 절대 좋은 방법이 아닙니다.

만약 아래 이미지처럼 한 개의 주문 테이블만 만들어 관리한다고 생각 해보겠습니다. 이미 동일한 데이터(중복된 이름, 전화번호) 가 있으며, 회원 '민순'의 전화번호를 수정이 필요할 때 3개의 행 모두 수정을 해야 하기에, 실수하기 쉽고, 관리가 어렵습니다.

이제 JOIN이 필요한 이유에 대해서 알아 보았으니, JOIN에 대해서 공부 해보겠습니다. JOIN은 크게 내부 조인(INNER JOIN)과 외부 조인(OUTER JOIN)우로 나뉩니다,

내부 조인(INNER JOIN)

내부 조인은 두 테이블에 공통으로 존재하는 데이터만 조회 하는 것입니다. 쉽게 말해서는 2개 테이블을 옆으로 붙이는 것으로, 서로 일치하는 행들만 이어 붙이는 것입니다. 예를 들어 order테이블과 user테이블이 있다고 가정 하고, 각각의 테이블에서 이름과 제품명을 조회해보겠습니다.

SELECT u.name, o.product
FROM user u
INNER JOIN order o
ON u.user_id = o.user_id; -- 조건 : 회원 번호가 동일한 경우

ON절은 테이블을 어떤 조건으로 연결할지를 정의하며 ON 절의 조건이 참(true)인 경우에만 결과에 포함됩니다.

즉 내부 조인(INNER JOIN)은 2개 테이블의 교집합(공통된 값)을 기준으로 데이터를 결합하여 조회 됩니다.

외부 조인(OUTER JOIN)

외부 조인(OUTER JOIN)이란 한 쪽 테이블을 기준으로 조건에 맞지 않는 데이터까지 포함해서 조회합니다. 즉 두 개의 테이블에서 연결되지 않은 데이터까지 조회 할 때 사용 합니다. ex) 주문을 한 적 없는 회원

연결되는 데이터가 없어도 NULL로 채워 결과에 포함시켜 줍니다.

SELECT u.name, o.product
FROM user u
LEFT JOIN order o
ON u.user_id = o.user_id;

또한 외부 조인은 기준이 되는 테이블의 방향에 따라 LEFT OUTER JOIN, RIGHT OUTER JOIN으로 나뉩니다.

두 개 모두 큰 차이는 없으며, 가독성을 이유로, LEFT JOIN을 많이 사용 합니다.

내부 조인이 2개 테이블의 교집합을 기준으로 했다면, 외부 조인은 교집합에 기준이 되는 테이블의 영역도 포함합니다.

즉 기준이 되는 order 테이블의 모든 값을 미리 결과에 포함 시키고, 해당 값의 짝이 없다면 NULL로 포함 시킵니다.

조인 특징

조인을 할 때 결과로 나오는 행의 개수는 조인 방향 즉, 테이블 간의 관계에 따라 행 개수가 변화할 수 있습니다.

어떠한 상황일때 행 개수가 변하는지 알아보겠습니다.

<자식 → 부모 (FK → PK)>

자식 테이블(FK)이 부모 테이블(PK)로 조인 할 때는 행 개수가 유지(to - one)됩니다.

왜냐하면 자식 테이블과 연결된 부모 테이블의 PK는 중복되지 않아 오직 한 개의 행과만 연결이 되기에, 행은 유지됩니다.

FROM orders o
JOIN users u
ON o.user_id = u.user_id;

<부모 → 자식(PK → FK)>

부모 테이블(PK)이 자식 테이블(FK)로 조인 할 때는 행 개수가 증가(to - many)할수 있습니다.

왜냐하면 부모 테이블과 연결된 자식 테이블의 FK는 같은 값을 여러번 가질 수 있기에, 전체 행 수가 증가 될 수 있습니다. 즉 부모 테이블(user)의 A라는 회원이 여러번 주문을 했을 수도 있기 때문입니다.

FROM users u
JOIN orders o
ON u.user_id = o.user_id;

<정리>

위의 원리를 모른다면 데이터를 잘못 해석할 여지가 있기에, 확실히 알아야 합니다.

아래의 쿼리문은 어떤 결과를 조회하는걸까요?

SELECT COUNT(u.user_id)
FROM users u
JOIN orders o
ON u.user_id = o.user_id;

위 쿼리문을 먼저 해석 하자면 FORM절에 오는 테이블이 기준되는 테이블이 됩니다. 그러면 기준 테이블은 user(부모)테이블이 되고, 조인 대상은 orders(자식)입니다. 그렇다면 관계는 부모 -> 자식이 되어 행이 증가할 수 있기에, 결과적으로 총 주문 수를 구하는 쿼리문이 됩니다.

구분	조인 방향	결과 행 수
자식 -> 부모	FK -> PK	유지
부모 -> 자식	PK -> FK	증가 가능

셀프 조인(SELF JOIN)

SELF JOIN은 동일한 테이블을 두 번 불러와, 서로 다른 역할로 연결하는 것입니다. 주로 자기 참조 관계 및 계층 구조를 표현할 때 사용 합니다.

예를 들어 직원 테이블이 있다고 가정 하겠습니다. 직원 테이블에는 직원 고유 번호와 이름, 상사 직원 번호의 열이 있습니다.

그러면 직원과, 그 직원의 상사를 조회하고 싶을 때는 각 직원이 갖고 있는 상사 직원의 번호를 통해 알 수 있습니다.

아래 예시 코드를 보겠습니다.

SELECT
	e1.name AS 직원,
   	e2.name AS 상사
FROM
	employee e1, employee e2
WHERE 
	e1.manager_id = e2.employee_id;

크로스 조인(CROSS JOIN)

CROSS JOIN은 두 테이블의 모든 행을 서로 조합해서 결과를 생성합니다. 즉 조건 없이 모든 조합을 만들고 싶을 때 사용하며,

모든 경우의 수를 만들기 때문에 테이블1 행의 수 x 테이블2 행의 수를 곱한 값이 결과 행의 수 입니다.

ex) 옷 종류별로 모든 색상과 사이즈 조합을 보고 싶을 때

SELECT clothes.type, colors.color
FROM clothes
CROSS JOIN colors;

데이터베이스란?

데이터베이스 관리 시스템(DBMS)이란?

DBMS 구성 요소

SQL 명령어의 4가지 종류

데이터 정의어 (DDL - Data Definition Language)

데이터 조작어 (DML - Data Manipulation Language)

데이터 제어어(DCL - Data Control Language)

트랜잭션 제어어(TCL- Transaction Control Language)

SQL 명령어 정리

SQL 실행 순서

제약 조건(Constraint)

NOT NULL

UNIQUE

PRIMARY KEY(PK)

FOREIGN KEY(FK)

DEFAULT

CHECK

조인(Join)이란?

Join이 필요한 이유

내부 조인(INNER JOIN)

외부 조인(OUTER JOIN)

조인 특징

<자식 → 부모 (FK → PK)>

<부모 → 자식(PK → FK)>

<정리>

셀프 조인(SELF JOIN)

크로스 조인(CROSS JOIN)

티스토리툴바