시작하기
CLI 기초
SQLite는 전체 데이터베이스를 하나의 파일에 저장합니다. sqlite3 CLI는 .sqlite/.db 파일을 필요할 때 열거나 만듭니다. MySQL/PostgreSQL과 달리 서버 프로세스가 없고 애플리케이션이 SQLite 라이브러리를 직접 링크합니다. .dump는 전체 데이터베이스를 텍스트 SQL로 내보내 백업이나 마이그레이션에 사용합니다.
# open/create a database file
sqlite3 mydb.sqlite
# at the sqlite> prompt
.help # show help
.tables # list tables
.schema users # show CREATE statement for users
.databases # list attached databases
.dump # dump entire DB as SQL text
.quit # exit
# execute SQL from a file
sqlite3 mydb.sqlite < script.sql
# execute a query from command line
sqlite3 mydb.sqlite "SELECT COUNT(*) FROM users;"도트 명령
도트 명령은 SQL 엔진이 아니라 sqlite3 CLI가 해석합니다. 세미콜론이 필요 없고 프로그래밍 API에서는 사용할 수 없습니다. .mode는 출력 형식을 제어합니다(가독성을 위해 .header on과 함께 'column'을 사용해 보세요). .schema는 CREATE 문을 보여주고 .fullschema는 인덱스, 뷰, 트리거를 추가합니다. .help로 더 알아보세요.
-- dot commands are CLI-only, NOT SQL (no semicolon needed)
.help -- list all dot commands
.tables -- list all tables
.schema -- schema for all tables
.schema users -- schema for the users table
.fullschema -- schema incl. indexes, triggers, views
.indices users -- list indexes on users
.header on -- show column headers
.mode column -- column-aligned output
.mode list -- default, pipe-separated
.mode csv -- CSV output
.mode json -- JSON output
.show -- show current settings
.width 10 20 30 -- set column widths
.timer on -- show query execution time데이터베이스 파일 관리
ATTACH DATABASE를 쓰면 한 연결이 최대 10개 데이터베이스 파일을 스키마(main, temp, 추가 연결)로 동시에 질의할 수 있습니다. :memory: URI는 종료 시 사라지는 순수 인메모리 데이터베이스를 만들어 테스트나 임시 계산에 이상적입니다. URI 파일 이름(file:...)은 읽기 전용, 공유 캐시 등의 모드를 가능하게 합니다. 연결한 DB는 각각 디스크의 별도 파일입니다.
-- attach another database file to the current connection
ATTACH DATABASE 'archive.sqlite' AS archive;
DETACH DATABASE archive;
-- list attached databases (main + attached)
.databases
-- the in-memory database (never written to disk)
sqlite3 :memory:
-- temporary database (deleted when connection closes)
sqlite3 "" -- empty arg => temp DB
-- open read-only
sqlite3 "file:mydb.sqlite?mode=ro" --readonly
-- query the underlying page size / file format
PRAGMA page_size;
PRAGMA journal_mode;헤더와 출력 형식
출력 형식은 CLI에만 영향을 주며 데이터 자체는 바뀌지 않습니다. .headers on과 'column' 모드가 사람이 읽기 가장 좋습니다. 'list'(기본)는 셸 파이프라인에 좋습니다. 'json'과 'csv'는 SQLite를 편리한 변환기로 만듭니다. .output은 결과를 파일로 보내므로 .output stdout으로 되돌리세요. .mode box/table은 ASCII 테두리를 그립니다.
-- readable interactive mode
.headers on
.mode column
.width 15 20 10
SELECT id, name, email FROM users LIMIT 5;
-- pipe-separated (good for scripts)
.mode list
.separator "|"
SELECT id, name FROM users;
-- boxed table output (sqlite 3.36+)
.mode box
.mode table
-- JSON output for piping to other tools
.mode json
SELECT id, name FROM users;
-- write output to a file
.output users.txt
SELECT id, name FROM users;
.output stdout스크립트와 SQL 파일 실행
.read는 열린 CLI 세션 안에서 SQL 파일을 실행하고, <로 리다이렉트하면 대화형 프롬프트 전에 실행합니다. --bail은 첫 오류에서 배치를 멈춥니다(그렇지 않으면 SQLite가 계속 진행해 실패를 가릴 수 있음). .parameter(3.44+)는 셸에서 명명 매개변수를 바인딩해 문자열 보간을 피합니다. 개발 중에는 .echo on으로 스크립트를 항상 점검하세요.
-- run a .sql file from the shell
sqlite3 mydb.sqlite ".read setup.sql"
-- or piped in
sqlite3 mydb.sqlite < setup.sql
-- inside the CLI
.read setup.sql
-- stop on first error (shell flag)
sqlite3 mydb.sqlite --bail < script.sql
-- echo commands as they run
.echo on
.read migrations.sql
-- show EXPLAIN before each statement (debug)
.explain on
-- parameterize from the shell (sqlite 3.44+)
sqlite3 mydb.sqlite \
-cmd ".parameter set @min 18" \
"SELECT name FROM users WHERE age >= @min"테이블과 데이터 타입
테이블 만들기
INTEGER PRIMARY KEY는 rowid의 별칭입니다. 자동 증가하며 가능한 가장 빠른 키입니다. AUTOINCREMENT는 삭제된 ID를 절대 재사용하지 않도록 알고리즘을 바꿉니다(약간 느리고 보통 불필요). DEFAULT (expr)은 datetime('now') 같은 임의 표현식을 허용합니다. CREATE TABLE AS SELECT는 쿼리 결과로 채워진 테이블을 만들지만 제약 조건과 인덱스는 복사하지 않습니다.
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
email TEXT NOT NULL UNIQUE,
age INTEGER CHECK (age >= 0),
role TEXT NOT NULL DEFAULT 'user',
created TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
total REAL NOT NULL DEFAULT 0,
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE ON UPDATE CASCADE
);
-- quick table from a query
CREATE TABLE archive AS
SELECT * FROM orders WHERE created < '2024-01-01';저장 클래스와 타입 선호
SQLite는 동적 타입을 씁니다. 어떤 컬럼이든 5개 저장 클래스 중 무엇이든 담을 수 있습니다. '타입 선호'는 가능하면 값을 변환하지만(예: INTEGER 컬럼 의 '42'는 정수 42) 불일치 시 오류를 내지 않습니다. STRICT 테이블(3.37+)은 컬럼별 전통적인 엄격 타입 검사를 복원합니다. typeof()는 실제 저장 클래스를 보고합니다. 이 유연성은 강력하지만 규율이 필요합니다. 데이터 무결성이 중요한 새 스키마에는 STRICT를 사용하세요.
-- SQLite has 5 storage classes (not strict types):
-- NULL, INTEGER, REAL, TEXT, BLOB
CREATE TYPE demo... -- not supported
-- columns have 'type affinity', not enforced types
CREATE TABLE t (
a INTEGER, -- affinity INTEGER
b TEXT, -- affinity TEXT
c REAL, -- affinity REAL
d BLOB, -- affinity BLOB (no conversion)
e -- no affinity (accepts anything)
);
-- this WORKS despite 'INTEGER' column (dynamic typing)
INSERT INTO t (a) VALUES ('hello');
SELECT typeof(a) FROM t; -- 'text'
-- STRICT tables (SQLite 3.37+) actually enforce types
CREATE TABLE strict_t (a INTEGER, b TEXT) STRICT;
INSERT INTO strict_t VALUES ('hi', 1); -- error흔한 데이터 타입
SQLite에는 네이티브 DATE/TIME이나 BOOLEAN 타입이 없습니다. 타임스탬프는 정렬 가능하도록 ISO-8601 TEXT('YYYY-MM-DD HH:MM:SS')로 저장하고 date/time 함수로 조작하세요. 불리언은 정수 0/1입니다. VARCHAR(n) 길이 제한은 무시됩니다(호환성용). NUMERIC 선호는 INTEGER를 먼저 시도하고 그다음 REAL을 시도하며 가능하면 정확한 소수를 보존합니다. BLOB은 원시 바이트를 그대로 저장합니다.
-- integers
CREATE TABLE numerics (
small INTEGER, -- 1/2/4/8 bytes depending on value
big INTEGER, -- up to 64-bit
-- no separate BIGINT/SMALLINT, all map to INTEGER affinity
flag BOOLEAN -- stored as 0 (false) or 1 (true)
);
-- text & blobs
CREATE TABLE blobs (
name TEXT, -- variable-length UTF-8, no length limit
data BLOB, -- raw bytes, no conversion
note VARCHAR(255) -- length ignored, affinity TEXT
);
-- real numbers
CREATE TABLE floats (
price REAL, -- 8-byte IEEE float
precise NUMERIC(10,2) -- affinity NUMERIC, decimal preserved if exact
);
-- dates: SQLite has no native DATE type — store as TEXT (ISO-8601)
CREATE TABLE events (
ts TEXT -- 'YYYY-MM-DD HH:MM:SS' recommended
);테이블 변경
SQLite의 ALTER TABLE은 서버리스 엔진답게 의도적으로 제한적입니다. ADD COLUMN은 O(1)이라 테이블 재작성이 없습니다. RENAME COLUMN(3.25+)과 DROP COLUMN(3.35+)은 현대적 편의 기능입니다. 타입 변경이나 제약 추가에는 12단계 테이블 재구축 패턴(새로 만들기, 복사, 삭제, 이름 변경, 인덱스 재구축)을 사용하세요. 재구축 중에는 외래 키를 일시적으로 비활성화해야 합니다(PRAGMA legacy_alter_table 참고).
-- add a column (fast, appends to end)
ALTER TABLE users ADD COLUMN bio TEXT;
-- rename a column (SQLite 3.25+)
ALTER TABLE users RENAME COLUMN name TO username;
-- rename a table
ALTER TABLE users RENAME TO accounts;
-- drop a column (SQLite 3.35+)
ALTER TABLE users DROP COLUMN deprecated_field;
-- SQLite CANNOT: change a column type, add constraints to
-- an existing column, or reorder columns. Workaround:
-- 1) create new table with desired schema
-- 2) INSERT INTO new SELECT * FROM old
-- 3) DROP old; ALTER TABLE new RENAME TO old
-- 4) recreate indexes/triggers제약 조건
제약 조건은 엔진 수준에서 데이터 무결성을 강제합니다. PRIMARY KEY는 NOT NULL과 UNIQUE를 함의합니다. CHECK는 행의 어떤 컬럼이든 참조할 수 있습니다. ON CONFLICT 절(OR IGNORE / OR REPLACE / OR ABORT / OR FAIL / OR ROLLBACK)은 문장별 충돌 해결을 제어합니다. OR REPLACE는 충돌 행을 삭제한 뒤 삽입하므로 rowid가 초기화됩니다. UPSERT 의미가 필요하면 ON CONFLICT ... DO UPDATE를 권장합니다(CRUD 섹션 참고).
CREATE TABLE products (
id INTEGER PRIMARY KEY,
sku TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
price REAL NOT NULL CHECK (price >= 0),
stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
category TEXT CHECK (category IN ('a','b','c')),
-- composite unique constraint
UNIQUE (name, category),
-- table-level check
CHECK (price * stock < 1000000)
);
-- name a constraint for clearer error messages
CREATE TABLE t (
x INTEGER CONSTRAINT x_positive CHECK (x > 0)
);
-- conflict handling
INSERT OR IGNORE INTO products (sku, name, price) VALUES (...);
INSERT OR REPLACE INTO products (...) VALUES (...);
UPDATE OR ABORT products SET stock = stock - 1 WHERE id = 1;CRUD 연산
INSERT
다중 행 INSERT는 루프에서 행별로 삽입하는 것보다 훨씬 빠르므로 배치로 처리하세요. INSERT ... SELECT는 테이블 간에 데이터를 복사합니다. last_insert_rowid()는 현재 연결(트랜잭션 아님)에서 가장 최근 INSERT의 rowid를 반환합니다. 앱에서는 SQL 인젝션과 따옴표 문제를 피하려고 매개변수화된 삽입(통합 섹션 참고)을 권장합니다. 컬럼 기본값을 명시적으로 쓰려면 DEFAULT를 사용하세요.
-- single row
INSERT INTO users (username, email, age)
VALUES ('alice', '[email protected]', 30);
-- multiple rows in one statement (fast)
INSERT INTO users (username, email) VALUES
('bob', '[email protected]'),
('carol', '[email protected]'),
('dave', '[email protected]');
-- insert from a query
INSERT INTO archive_users
SELECT * FROM users WHERE active = 0;
-- explicit NULL / default
INSERT INTO users (username, email, age)
VALUES ('eve', '[email protected]', DEFAULT);
-- return the new rowid (handy in apps)
INSERT INTO users (username) VALUES ('frank');
SELECT last_insert_rowid(); -- returns the new idUPSERT(ON CONFLICT)
ON CONFLICT(UPSERT)는 '있으면 삽입 또는 갱신'을 위한 SQLite의 관용구입니다. 기존 행을 삭제하는(그래서 DELETE 트리거를 발생시키고 rowid를 초기화하는) INSERT OR REPLACE보다 훨씬 안전합니다. 'excluded'는 삽입하려는 행을 가리킵니다. DO NOTHING은 조용히 건너뜁니다. 충돌 대상은 모든 UNIQUE 또는 PRIMARY KEY 제약이 될 수 있습니다. 카운터, 마지막 확인 시각 갱신, 멱등 가져오기에 올바른 패턴입니다.
-- insert, but update on conflict
INSERT INTO users (id, username, email)
VALUES (1, 'alice', '[email protected]')
ON CONFLICT(id) DO UPDATE SET
email = excluded.email,
username = excluded.username;
-- ignore on conflict
INSERT INTO users (id, username)
VALUES (1, 'alice')
ON CONFLICT(id) DO NOTHING;
-- conflict on a specific unique constraint
INSERT INTO users (username, email)
VALUES ('alice', '[email protected]')
ON CONFLICT(email) DO UPDATE SET
username = excluded.username;UPDATE
정말로 모든 행을 갱신하려는 게 아니라면 항상 WHERE를 포함하세요. SQLite에는 드라이런 모드가 없습니다. UPDATE ... FROM(3.33+)과 UPDATE ... RETURNING(3.35+)은 SQLite를 PostgreSQL의 편의성에 가깝게 만듭니다. RETURNING은 별도 SELECT 없이 갱신 후 상태가 필요한 앱에 매우 유용합니다. CASE는 행별 로직이 있는 단일 문장 대량 갱신을 가능하게 하며 행별 갱신보다 훨씬 빠릅니다.
-- basic update (ALWAYS use WHERE in production!)
UPDATE users SET age = 31 WHERE id = 1;
-- multiple columns
UPDATE users
SET age = age + 1, role = 'admin'
WHERE username = 'alice';
-- conditional update with CASE
UPDATE products
SET price = CASE
WHEN category = 'a' THEN price * 0.9
WHEN category = 'b' THEN price * 0.95
ELSE price
END;
-- update from another table (subquery)
UPDATE orders
SET total = (SELECT SUM(qty * price)
FROM items WHERE order_id = orders.id);
-- UPDATE ... RETURNING (SQLite 3.35+)
UPDATE users SET role = 'admin'
WHERE id IN (1,2,3)
RETURNING id, username, role;DELETE
SQLite에는 TRUNCATE가 없습니다. DELETE FROM table은 모든 행을 제거하지만 행 수준 트리거를 발생시키고 AUTOINCREMENT 카운터(sqlite_sequence에 저장)를 초기화하지 않습니다. DELETE ... RETURNING(3.35+)은 제거된 내용을 기록합니다. 감사 데이터에는 하드 삭제보다 소프트 삭제(deleted_at 컬럼)를 권장합니다. 삭제로 생긴 공간은 이후 삽입에서 재사용되고 VACUUM이 OS로 반환합니다.
-- basic delete
DELETE FROM users WHERE id = 1;
-- delete with a subquery condition
DELETE FROM orders
WHERE user_id IN (SELECT id FROM users WHERE active = 0);
-- DELETE ... RETURNING
DELETE FROM sessions
WHERE expires < datetime('now')
RETURNING id, user_id;
-- delete ALL rows (fast, but fires triggers)
DELETE FROM logs;
-- TRUNCATE equivalent: delete all + reset rowid
DELETE FROM logs;
DELETE FROM sqlite_sequence WHERE name = 'logs';
-- soft delete pattern
UPDATE users SET deleted_at = datetime('now') WHERE id = 1;SELECT 기초
SELECT는 핵심입니다. LIMIT count OFFSET skip은 페이지네이션을 구현합니다(큰 오프셋에는 키셋 페이지네이션이 더 빠름, 쿼리 섹션 참고). 별칭(AS, 선택)은 가독성을 높이고 뷰의 계산 컬럼에는 필수입니다. DISTINCT는 동일한 행을 합칩니다. 더 세밀한 제어가 필요하면 GROUP BY를 고려하세요. CASE는 결과 집합에 if/else 로직을 추가하며 어느 절에서든 사용할 수 있습니다.
-- basic select
SELECT id, username, email FROM users;
-- limit and offset
SELECT id, username FROM users LIMIT 10 OFFSET 20;
SELECT id, username FROM users LIMIT 20, 10; -- offset, count
-- aliasing columns and tables
SELECT u.id AS user_id, u.username AS name
FROM users AS u;
-- expressions
SELECT
username,
age,
age * 365 AS days_alive,
UPPER(username) AS upper_name
FROM users;
-- distinct rows
SELECT DISTINCT category FROM products;
-- conditional output
SELECT name,
CASE WHEN age >= 18 THEN 'adult' ELSE 'minor' END AS status
FROM users;조회와 필터링
WHERE와 연산자
SQL에서 NULL은 특별합니다. 값이 아니라 '알 수 없음'입니다. = NULL은 항상 NULL(거짓으로 취급)을 반환하므로 IS NULL / IS NOT NULL을 사용하세요. NULL과 AND/OR를 결합하면 3값 논리를 따릅니다. BETWEEN은 양 끝을 포함합니다. IN은 OR 동등 비교의 축약형입니다. IN (NULL, ...)은 이상하게 동작하므로 피하세요. 복합 조건을 명확히 하려면 괄호를 적극적으로 사용하세요.
-- comparison operators
SELECT * FROM products WHERE price < 50;
SELECT * FROM products WHERE price BETWEEN 10 AND 100;
SELECT * FROM users WHERE age IN (18, 21, 25);
SELECT * FROM users WHERE age NOT IN (18, 21);
-- NULL handling (NEVER use = NULL)
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;
-- logical operators
SELECT * FROM users
WHERE age >= 18 AND age <= 65 AND role = 'admin';
SELECT * FROM users
WHERE role = 'admin' OR role = 'editor';
-- range with multiple conditions
SELECT * FROM orders
WHERE (status = 'shipped' AND total > 100)
OR (status = 'pending' AND total > 500);ORDER BY와 LIMIT
고유한 타이브레이커 없는 ORDER BY는 실행마다 비결정적 순서를 줍니다. 항상 고유 컬럼(id 등)을 마지막 정렬 키로 추가하세요. OFFSET 페이지네이션은 행을 스캔해 버리므로 O(n)입니다. 키셋 페이지네이션(WHERE id > last_seen_id)은 O(limit)이라 큰 테이블에 올바른 선택입니다. RANDOM()은 전체 정렬이 필요하므로 작은 결과 집합이나 샘플링된 부분 집합에만 사용하세요.
-- ascending (default) / descending
SELECT * FROM users ORDER BY username;
SELECT * FROM users ORDER BY created DESC;
SELECT * FROM users ORDER BY age DESC, username ASC;
-- sort with NULLs first or last (SQLite 3.30+)
SELECT * FROM tasks ORDER BY due_date ASC NULLS FIRST;
SELECT * FROM tasks ORDER BY due_date DESC NULLS LAST;
-- pagination: keyset is faster than OFFSET
-- slow (offset scans & discards rows):
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10000;
-- fast (keyset):
SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 10;
-- random sample
SELECT * FROM users ORDER BY RANDOM() LIMIT 5;패턴 매칭
LIKE는 기본적으로 ASCII에서 대소문자를 구분하지 않습니다(의외의 함정, 대소문자 구분에는 COLLATE BINARY 사용). GLOB은 항상 대소문자를 구분하고 [A-Z] 같은 문자 클래스를 지원합니다. REGEXP는 내장이 아니므로 함수를 등록하거나 확장을 로드해야 합니다. 대규모 전체 텍스트 검색에는 인덱스를 쓸 수 없는 LIKE '%term%' 대신 FTS5(성능 섹션 참고)를 사용하세요.
-- LIKE: case-insensitive by default for ASCII,
-- wildcards % (any chars) and _ (single char)
SELECT * FROM users WHERE username LIKE 'al%';
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE username LIKE '_lice';
-- case-sensitive LIKE
SELECT * FROM users WHERE username LIKE 'AL%' COLLATE BINARY;
-- GLOB: Unix shell-style, case-SENSITIVE
-- wildcards * and ?, plus [abc] character classes
SELECT * FROM users WHERE username GLOB 'Al*';
SELECT * FROM users WHERE username GLOB '[A-D]*';
-- REGEXP (requires extension or custom function)
SELECT * FROM users WHERE username REGEXP '^a.*e$';
-- substring match
SELECT * FROM users WHERE INSTR(username, 'lic') > 0;CASE와 조건 로직
CASE는 SQL의 if/else이며 SELECT, WHERE, ORDER BY, GROUP BY, HAVING에서 동작합니다. '단순' 형식은 값을 일치시키고, '검색' 형식은 불리언 조건을 순서대로 평가합니다. SUM(CASE WHEN ... THEN 1 ELSE 0 END)는 고전적인 피벗/조건부 집계 관용구입니다. CASE는 첫 번째로 일치하는 WHEN에서 단락되므로 순서가 중요합니다. ELSE를 생략하면 기본값은 NULL입니다.
-- simple CASE (value match)
SELECT name,
CASE role
WHEN 'admin' THEN 'Administrator'
WHEN 'editor' THEN 'Editor'
ELSE 'Regular User'
END AS role_label
FROM users;
-- searched CASE (conditions)
SELECT name,
CASE
WHEN age < 18 THEN 'minor'
WHEN age < 65 THEN 'adult'
ELSE 'senior'
END AS age_group
FROM users;
-- CASE in aggregate (conditional count)
SELECT
COUNT(*) AS total,
SUM(CASE WHEN active = 1 THEN 1 ELSE 0 END) AS active_count,
SUM(CASE WHEN active = 0 THEN 1 ELSE 0 END) AS inactive_count
FROM users;
-- CASE in ORDER BY (custom sort order)
SELECT * FROM products
ORDER BY CASE category
WHEN 'featured' THEN 0
WHEN 'new' THEN 1
ELSE 2
END;DISTINCT와 집합 연산
UNION ALL은 UNION보다 빠릅니다. UNION은 중복 제거 정렬을 하기 때문입니다. 중복이 불가능하거나 허용되면 UNION ALL을 권장하세요. 모든 집합 연산은 호환되는 컬럼 수와 타입을 요구합니다. SQLite는 INTERSECT/EXCEPT를 UNION/UNION ALL보다 높은 우선순위로 적용하므로 괄호로 순서를 제어하세요. 복합 쿼리의 ORDER BY는 마지막 SELECT가 아니라 전체 결과 집합에 적용됩니다.
-- distinct rows
SELECT DISTINCT category FROM products;
SELECT DISTINCT city, country FROM users;
-- UNION (dedup) and UNION ALL (faster, keeps dups)
SELECT 'user' AS type, id FROM users WHERE active = 1
UNION ALL
SELECT 'admin', id FROM admins;
-- INTERSECT: rows in both
SELECT id FROM users
INTERSECT
SELECT user_id FROM orders;
-- EXCEPT: rows in first but not second
SELECT id FROM users
EXCEPT
SELECT user_id FROM orders;
-- combine with ORDER BY (applies to whole result)
SELECT name FROM users WHERE active = 1
UNION
SELECT name FROM archived_users
ORDER BY name;JOIN
INNER JOIN
INNER JOIN은 양쪽 테이블에 일치가 있는 행만 반환합니다. 어느 쪽이든 일치하지 않는 행은 버려집니다. USING (col)은 두 테이블에 같은 이름의 컬럼이 있을 때의 축약형이며 병합된 컬럼 하나를 만듭니다. NATURAL JOIN은 같은 이름의 모든 컬럼을 암묵적으로 사용하므로 편리하지만 취약합니다(스키마 변경이 조인 동작을 조용히 바꿀 수 있음). 명확성을 위해 명시적 ON을 권장하세요.
-- only matching rows from both tables
SELECT u.username, o.id AS order_id, o.total
FROM users AS u
INNER JOIN orders AS o ON o.user_id = u.id;
-- join with additional filter
SELECT u.username, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.total > 100 AND u.active = 1;
-- USING shorthand (when join columns share a name)
SELECT u.username, o.total
FROM users u
JOIN orders o USING (user_id);
-- natural join (joins on all same-named columns — risky)
SELECT * FROM users NATURAL JOIN profiles;LEFT JOIN
LEFT JOIN은 왼쪽 테이블의 모든 행을 보존하고 오른쪽 컬럼이 없으면 NULL이 됩니다. 'WHERE right.id IS NULL' 패턴(안티 조인)은 일치가 없는 행을 찾으며, 특히 NULL이 관련될 때 서브쿼리가 있는 NOT IN보다 명확하고 빠른 경우가 많습니다. LEFT JOIN을 가로질러 집계할 때 COUNT(right.id)(COUNT(*) 아님)는 일치한 행만 셉니다. 주문이 없는 사용자는 1이 아니라 0을 줍니다.
-- all users, with their orders (NULLs if no orders)
SELECT u.username, o.id AS order_id, o.total
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id;
-- find users with NO orders (anti-join pattern)
SELECT u.username
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;
-- multiple left joins
SELECT u.username,
COUNT(o.id) AS order_count,
SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;CROSS JOIN과 RIGHT/FULL
CROSS JOIN은 카티션 곱을 만듭니다(size × color 조합 생성에 유용하지만 큰 테이블에서는 n×m 행이라 위험). SQLite는 RIGHT JOIN(3.39+)을 지원하지만 드물게 쓰이므로 명확성을 위해 LEFT JOIN으로 다시 쓰세요. 네이티브 FULL OUTER JOIN은 없습니다. LEFT JOIN ... UNION ... RIGHT JOIN으로 에뮬레이트하며 UNION이 겹치는 일치 행을 중복 제거합니다.
-- CROSS JOIN: cartesian product (every combination)
SELECT s.size, c.color
FROM sizes s CROSS JOIN colors c;
-- explicit CROSS JOIN (same as comma join)
SELECT a.name, b.name FROM teams a, teams b;
-- RIGHT JOIN (preserves right table) — supported but unusual
SELECT u.username, o.total
FROM orders o
RIGHT JOIN users u ON u.id = o.user_id;
-- SQLite has no FULL OUTER JOIN; emulate with LEFT + UNION:
SELECT u.username, o.id AS order_id
FROM users u LEFT JOIN orders o ON o.user_id = u.id
UNION
SELECT u.username, o.id
FROM users u RIGHT JOIN orders o ON o.user_id = u.id;셀프 조인
셀프 조인은 같은 테이블을 두 번 질의하며 별칭으로 각 복사본에 역할(직원 vs 관리자, user_a vs user_b)을 부여합니다. 'a.id < b.id' 기법은 행이 자기 자신과 일치해 거울 중복 쌍이 생기는 것을 피합니다. 깊은 계층(임의 깊이)에는 재귀 CTE(CTE 섹션 참고)가 올바른 도구입니다. 셀프 조인은 고정 깊이만 처리합니다.
-- employees and their managers (same table)
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT,
manager_id INTEGER REFERENCES employees(id)
);
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- find pairs of users in the same city
SELECT a.username AS user_a, b.username AS user_b, a.city
FROM users a
JOIN users b ON a.city = b.city AND a.id < b.id;
-- recursive hierarchy (tree traversal) — see CTE section
-- multi-level category paths
SELECT c.name, p.name AS parent
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.id;다중 테이블 조인
조인은 왼쪽에서 오른쪽으로 연결되며 각 JOIN은 누적된 결과를 봅니다. LEFT와 INNER를 섞을 때 LEFT JOIN 뒤의 INNER JOIN이 NULL을 다시 걸러 LEFT의 목적을 무산시킬 수 있으므로 순서에 주의하세요. COUNT(DISTINCT col)은 일대다 조인에서 이중 집계를 피합니다. 조인 다중도에 유의하세요. 일대다 테이블 두 개를 조인하면 부모당 m × n 행의 폭발이 생길 수 있습니다.