SQL의 TRUNCATE 대 DELETE: 차이점 이해

작성자:

데이터베이스 테이블을 다루다 보면 특정 조건에 맞는 일부 행이나 테이블 내의 모든 행을 제거해야 할 때가 있습니다. 이러한 상황에서 SQL의 TRUNCATE 또는 DELETE 명령어를 적절하게 활용할 수 있습니다. 본 튜토리얼에서는 각 명령어를 자세히 살펴보고, 작동 방식 및 어떤 경우에 TRUNCATE 또는 DELETE를 선택해야 하는지에 대해 알아봅니다.

본격적으로 시작하기 전에 SQL 명령어의 세 가지 하위 집합에 대해 간략히 알아보는 것이 좋습니다.

  • 데이터 정의어(DDL): 테이블과 같은 데이터베이스 객체를 생성하고 관리하는 데 사용됩니다. SQL CREATE, DROP, TRUNCATE 명령어가 DDL의 대표적인 예시입니다.
  • 데이터 조작어(DML): 데이터베이스 객체 내부의 데이터를 조작하는 데 사용됩니다. 레코드 생성, 읽기, 업데이트 및 삭제와 같은 작업을 수행하는 데 사용됩니다.
  • 데이터 질의어(DQL): 데이터베이스 테이블에서 데이터를 검색하는 데 사용됩니다. 모든 SELECT 문이 DQL에 속합니다.

SQL TRUNCATE 명령어 사용법

SQL TRUNCATE 명령어 구문

SQL TRUNCATE 명령어의 기본 구문은 다음과 같습니다.

TRUNCATE TABLE table_name;

위의 TRUNCATE 명령어를 실행하면 table_name으로 지정된 테이블의 모든 행이 삭제됩니다. 이때, 테이블 자체는 삭제되지 않습니다. TRUNCATE 작업은 테이블의 전체 레코드를 스캔하지 않으므로 대량의 데이터가 있는 테이블에서도 빠르게 처리됩니다.

SQL TRUNCATE 사용 예시

참고: MySQL 또는 PostgreSQL 등의 DBMS가 설치되어 있다면, 직접 명령줄 클라이언트를 통해 실습해볼 수 있습니다.

먼저, 실습을 위한 데이터베이스를 생성합니다.

mysql> CREATE DATABASE db1;
Query OK, 1 row affected (1.50 sec)

생성한 데이터베이스를 선택합니다.

mysql> use db1;
Database changed

다음으로, 테이블을 생성합니다. CREATE TABLE 구문을 사용하여 간단한 작업 테이블을 만듭니다.

-- 작업 테이블 생성
CREATE TABLE tasks (
    task_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    due_date DATE,
    status ENUM('Pending', 'In Progress', 'Completed') DEFAULT 'Pending',
    assignee VARCHAR(100)
);

이 예시에서 tasks 테이블은 다음과 같은 열을 갖습니다.

  • task_id: 각 작업에 대해 자동으로 증가하는 고유 식별자입니다.
  • title: 작업의 제목으로, 최대 255자까지 입력할 수 있습니다.
  • due_date: 작업의 마감일을 나타냅니다.
  • status: 작업의 상태로, ‘Pending’, ‘In Progress’, ‘Completed’ 중 하나를 가질 수 있으며 기본값은 ‘Pending’입니다.
  • assignee: 특정 작업의 담당자를 나타냅니다.

이제 tasks 테이블에 몇 가지 레코드를 삽입합니다.

-- 여러 레코드를 tasks 테이블에 삽입
INSERT INTO tasks (title, due_date, status, assignee)
VALUES
    ('Task 1', '2023-08-10', 'Pending', 'John'),
    ('Task 2', '2023-08-11', 'In Progress', 'Jane'),
    ('Task 3', '2023-08-12', 'Completed', 'Mike'),
    ('Task 4', '2023-08-13', 'Pending', 'Alice'),
    ('Task 5', '2023-08-14', 'In Progress', 'Bob'),
    ('Task 6', '2023-08-15', 'Completed', 'Emily'),
    ('Task 7', '2023-08-16', 'Pending', 'David'),
    ('Task 8', '2023-08-17', 'In Progress', 'Olivia'),
    ('Task 9', '2023-08-18', 'Pending', 'Daniel'),
    ('Task 10', '2023-08-19', 'Completed', 'Sophia'),
    ('Task 11', '2023-08-20', 'Pending', 'Matthew'),
    ('Task 12', '2023-08-21', 'In Progress', 'Ava'),
    ('Task 13', '2023-08-22', 'Completed', 'William'),
    ('Task 14', '2023-08-23', 'Pending', 'Ella'),
    ('Task 15', '2023-08-24', 'In Progress', 'James'),
    ('Task 16', '2023-08-25', 'Completed', 'Lily'),
    ('Task 17', '2023-08-26', 'Pending', 'Benjamin'),
    ('Task 18', '2023-08-27', 'In Progress', 'Mia'),
    ('Task 19', '2023-08-28', 'Pending', 'Henry'),
    ('Task 20', '2023-08-29', 'Completed', 'Isabella');

INSERT 문을 실행하면 다음과 유사한 결과가 출력됩니다.

Query OK, 20 rows affected (0.18 sec)
Records: 20  Duplicates: 0  Warnings: 0

이제 TRUNCATE TABLE 명령어를 실행하여 tasks 테이블의 모든 레코드를 삭제합니다.

TRUNCATE TABLE tasks;
Query OK, 0 rows affected (0.72 sec)

이 명령어는 테이블은 유지하면서 모든 레코드만 제거합니다. SHOW TABLES 명령어를 실행하여 확인할 수 있습니다.

SHOW TABLES;
+---------------+
| Tables_in_db1 |
+---------------+
| tasks         |
+---------------+
1 row in set (0.00 sec)

tasks 테이블에서 데이터를 검색하는 SELECT 쿼리는 빈 결과를 반환합니다.

SELECT * FROM tasks;
Empty set (0.00 sec)

SQL DELETE 명령어 사용법

SQL DELETE 명령어 구문

SQL DELETE 명령어의 일반적인 구문은 다음과 같습니다.

DELETE FROM table_name
WHERE condition;

WHERE 절의 조건은 삭제할 행을 결정하는 조건자입니다. DELETE 명령어는 조건자가 참인 모든 행을 삭제합니다. 따라서 DELETE 명령어를 사용하면 삭제할 레코드를 더 세밀하게 제어할 수 있습니다.

그렇다면 WHERE 절 없이 DELETE 명령어를 사용하면 어떻게 될까요? 🤔

DELETE FROM table_name;

위와 같이 DELETE 명령어를 실행하면 데이터베이스 테이블의 모든 행이 삭제됩니다. DELETE 문 또는 일련의 DELETE 문이 커밋되지 않은 트랜잭션의 일부인 경우 변경 사항을 롤백할 수 있습니다. 하지만 데이터를 다른 곳에 백업하는 것이 좋습니다.

SQL DELETE 사용 예시

이제 SQL DELETE 명령어의 실행 과정을 살펴보겠습니다.

tasks 테이블에서 모든 레코드를 삭제했으므로, 앞서 실행한 INSERT 문을 다시 실행하여 레코드를 삽입할 수 있습니다.

-- 여러 레코드를 tasks 테이블에 삽입
INSERT INTO tasks (title, due_date, status, assignee)
VALUES
    ('Task 1', '2023-08-10', 'Pending', 'John'),
    ('Task 2', '2023-08-11', 'In Progress', 'Jane'),
    ('Task 3', '2023-08-12', 'Completed', 'Mike'),
    ...
    ('Task 18', '2023-08-27', 'In Progress', 'Mia'),
    ('Task 19', '2023-08-28', 'Pending', 'Henry'),
    ('Task 20', '2023-08-29', 'Completed', 'Isabella');

먼저 WHERE 절과 함께 DELETE 명령어를 사용해보겠습니다. 다음 쿼리는 상태가 ‘Completed’인 모든 행을 삭제합니다.

DELETE FROM tasks WHERE status="Completed";
Query OK, 6 rows affected (0.14 sec)

이제 다음 SELECT 쿼리를 실행합니다.

SELECT * FROM tasks;

이제 14개의 행이 남아 있음을 알 수 있습니다.

+---------+---------+------------+-------------+----------+
| task_id | title   | due_date   | status      | assignee |
+---------+---------+------------+-------------+----------+
|       1 | Task 1  | 2023-08-10 | Pending     | John     |
|       2 | Task 2  | 2023-08-11 | In Progress | Jane     |
|       4 | Task 4  | 2023-08-13 | Pending     | Alice    |
|       5 | Task 5  | 2023-08-14 | In Progress | Bob      |
|       7 | Task 7  | 2023-08-16 | Pending     | David    |
|       8 | Task 8  | 2023-08-17 | In Progress | Olivia   |
|       9 | Task 9  | 2023-08-18 | Pending     | Daniel   |
|      11 | Task 11 | 2023-08-20 | Pending     | Matthew  |
|      12 | Task 12 | 2023-08-21 | In Progress | Ava      |
|      14 | Task 14 | 2023-08-23 | Pending     | Ella     |
|      15 | Task 15 | 2023-08-24 | In Progress | James    |
|      17 | Task 17 | 2023-08-26 | Pending     | Benjamin |
|      18 | Task 18 | 2023-08-27 | In Progress | Mia      |
|      19 | Task 19 | 2023-08-28 | Pending     | Henry    |
+---------+---------+------------+-------------+----------+
14 rows in set (0.00 sec)

다음 DELETE 명령어를 실행하면 테이블의 나머지 14개 레코드가 모두 삭제됩니다.

DELETE FROM tasks;
Query OK, 14 rows affected (0.20 sec)

이제 tasks 테이블은 비어 있습니다.

SELECT * FROM tasks;
Empty set (0.00 sec)

SQL DROP 명령어

지금까지 우리는 다음과 같은 내용을 학습했습니다.

  • TRUNCATE 명령어는 테이블에서 모든 행을 삭제합니다.
  • WHERE 절이 없는 DELETE 명령어는 테이블에서 모든 레코드를 삭제합니다.

하지만 TRUNCATE 및 DELETE 명령어는 테이블 자체를 삭제하지 않습니다. 데이터베이스에서 테이블을 삭제하려면 다음과 같이 DROP TABLE 명령어를 사용할 수 있습니다.

DROP TABLE table_name;

이제 데이터베이스에서 tasks 테이블을 삭제해 보겠습니다.

mysql> DROP TABLE tasks;
Query OK, 0 rows affected (0.43 sec)

SHOW TABLES를 통해 빈 결과 집합이 반환됨을 확인할 수 있습니다(데이터베이스에 있는 유일한 테이블을 삭제했기 때문입니다).

mysql> SHOW TABLES;
Empty set (0.00 sec)

SQL에서 TRUNCATE와 DELETE를 사용해야 하는 경우

기능 TRUNCATE DELETE
구문 TRUNCATE TABLE table_name; WHERE 절 포함: DELETE FROM table_name WHERE condition;
WHERE 절 미포함: DELETE FROM table_name;
SQL 하위 집합 데이터 정의어(DDL) 데이터 조작어(DML)
효과 데이터베이스 테이블의 모든 행을 삭제합니다. WHERE절 없이 실행하면, 데이터베이스 테이블의 모든 레코드를 삭제합니다.
성능 큰 테이블에서 DELETE 문보다 효율적입니다. TRUNCATE 문보다 효율성이 떨어집니다.

요약하자면 다음과 같습니다.

  • 대규모 데이터베이스 테이블에서 모든 행을 삭제해야 하는 경우 TRUNCATE 명령어를 사용합니다.
  • 특정 조건에 따라 행의 하위 집합을 삭제하려면 DELETE 명령어를 사용합니다.

결론

이제 논의를 마무리하겠습니다.

  • 데이터베이스 테이블 작업 시 특정 행 또는 테이블의 모든 행을 제거해야 할 때 TRUNCATE 또는 DELETE 명령어를 사용할 수 있습니다.
  • TRUNCATE 명령어는 TRUNCATE TABLE table_name; 구문을 사용합니다. 테이블은 삭제하지 않고, table_name으로 지정된 테이블의 모든 행을 삭제합니다.
  • DELETE 명령어는 DELETE FROM table_name WHERE condition; 구문을 사용합니다. 조건에 부합하는 행을 제거합니다.
  • WHERE절 없이 SQL DELETE 명령어를 실행하면 테이블의 모든 행이 삭제되므로 SQL TRUNCATE 명령어와 기능적으로 동일한 결과를 얻게 됩니다.
  • TRUNCATE 실행은 테이블 전체를 스캔하지 않기 때문에 더 큰 테이블에서 특히 빠릅니다. 따라서 대규모 데이터베이스 테이블의 모든 행을 삭제해야 하는 경우 TRUNCATE 명령어를 사용하는 것이 더 효율적일 수 있습니다.
  • 특정 조건에 따라 행의 하위 집합을 삭제해야 하는 경우 SQL DELETE 명령어를 사용할 수 있습니다.

자주 사용되는 SQL 명령어에 대해 빠르게 복습하려면 SQL 치트 시트를 참조하세요.