행 값
행 값 (Row Values)
이 문서는 SQLite의 "행 값"(row value) 개념과 그 구문, 그리고 실제 사용 예를 설명해요.
출처: 문서
본문
1. 정의 (Definitions)
"값"(value)은 단일 숫자, 문자열, BLOB 또는 NULL이에요. 때로는 단일 양만 관련된다는 점을 강조하기 위해 "스칼라 값"(scalar value)이라는 한정된 이름을 사용해요.
"행 값"(row value)은 두 개 이상의 스칼라 값으로 이루어진 순서 있는 목록이에요. 다시 말해 행 값은 벡터 또는 튜플이에요.
행 값의 "크기"(size)는 그 행 값이 포함하는 스칼라 값의 수예요. 행 값의 크기는 항상 2 이상이에요. 열이 하나인 행 값은 그냥 스칼라 값이에요. 열이 없는 행 값은 구문 오류예요.
2. 구문 (Syntax)
SQLite는 행 값을 두 가지 방식으로 표현할 수 있게 해요.
- 괄호로 감싸고 쉼표로 구분된 스칼라 값 목록.
- 두 개 이상의 결과 열을 가진 서브쿼리 표현식.
SQLite는 행 값을 두 가지 문맥에서 사용할 수 있어요.
- 같은 크기의 두 행 값은 연산자
<,<=,>,>=,=,<>,IS,IS NOT,IN,NOT IN,BETWEEN또는CASE로 비교할 수 있어요. - UPDATE 문에서 열 이름 목록을 같은 크기의 행 값으로 설정할 수 있어요.
행 값의 구문과 사용 가능한 상황은 아래 예에서 설명돼요.
2.1. 행 값 비교 (Row Value Comparisons)
두 행 값은 구성 요소 스칼라 값을 왼쪽에서 오른쪽으로 보며 비교돼요. NULL은 "알 수 없음"을 뜻해요. 구성 요소 NULL 대신 대체 값을 넣어 결과를 참 또는 거짓 중 하나로 만들 수 있다면, 비교의 전체 결과는 NULL이에요. 다음 쿼리는 몇 가지 행 값 비교를 보여 줘요.
SELECT
(1,2,3) = (1,2,3), -- 1
(1,2,3) = (1,NULL,3), -- NULL
(1,2,3) = (1,NULL,4), -- 0
(1,2,3) < (2,3,4), -- 1
(1,2,3) < (1,2,4), -- 1
(1,2,3) < (1,3,NULL), -- 1
(1,2,3) < (1,2,NULL), -- NULL
(1,3,5) < (1,2,NULL), -- 0
(1,2,NULL) IS (1,2,NULL); -- 1
"(1,2,3)=(1,NULL,3)"의 결과가 NULL인 이유는 NULL→2로 대체하면 참이 되고 NULL→9로 대체하면 거짓이 될 수 있기 때문이에요. "(1,2,3)=(1,NULL,4)"의 결과가 NULL이 아닌 이유는 구성 요소 NULL을 대체해도 표현식을 참으로 만들 수 없기 때문이에요. 세 번째 열에서 3은 결코 4와 같지 않으니까요.
이전 예의 행 값들 중 아무 것이나 세 개의 열을 반환하는 서브쿼리로 바꿔도 같은 답이 돼요. 예를 들어:
CREATE TABLE t1(a,b,c);
INSERT INTO t1(a,b,c) VALUES(1,2,3);
SELECT (1,2,3)=(SELECT * FROM t1); -- 1
2.2. 행 값 IN 연산자 (Row Value IN Operators)
행 값 IN 연산자의 경우 왼쪽(LHS)은 괄호로 묶인 값 목록이거나 여러 열의 서브쿼리일 수 있어요. 하지만 오른쪽(RHS)은 반드시 서브쿼리 표현식이어야 해요.
CREATE TABLE t2(x,y,z);
INSERT INTO t2(x,y,z) VALUES(1,2,3),(2,3,4),(1,NULL,5);
SELECT
(1,2,3) IN (SELECT * FROM t2), -- 1
(7,8,9) IN (SELECT * FROM t2), -- 0
(1,3,5) IN (SELECT * FROM t2); -- NULL
2.3. UPDATE 문의 행 값 (Row Values In UPDATE Statements)
행 값은 UPDATE 문의 SET 절에서도 사용할 수 있어요. LHS는 열 이름 목록이어야 하고, RHS는 모든 행 값일 수 있어요. 예를 들어:
UPDATE tab3
SET (a,b,c) = (SELECT x,y,z
FROM tab4
WHERE tab4.w=tab3.d)
WHERE tab3.e BETWEEN 55 AND 66;
3. 행 값 사용 예 (Example Uses Of Row Values)
3.1. 스크롤링 윈도 쿼리 (Scrolling Window Queries)
응용 프로그램이 연락처 목록을 lastname, firstname 알파벳 순으로 한 번에 7개만 보여 주는 스크롤링 윈도로 표시한다고 가정해 보아요. 스크롤링 윈도를 처음 7개 항목으로 초기화하는 것은 쉽지:
SELECT * FROM contacts
ORDER BY lastname, firstname
LIMIT 7;
사용자가 아래로 스크롤하면 응용 프로그램은 두 번째 7개 항목 집합을 찾아야 해요. 한 가지 방법은 OFFSET 절을 사용하는 것이에요:
SELECT * FROM contacts
ORDER BY lastname, firstname
LIMIT 7 OFFSET 7;
OFFSET은 올바른 답을 줘요. 하지만 OFFSET은 오프셋 값에 비례하는 시간이 필요해요. "LIMIT x OFFSET y"에서 실제로 일어나는 일은 SQLite가 쿼리를 "LIMIT x+y"로 계산하고 응용 프로그램에 반환하지 않고 처음 y개의 값을 버리는 것이에요. 그래서 윈도가 긴 목록의 아래쪽으로 스크롤되고 y 값이 점점 커질수록, 연속된 오프셋 계산은 점점 더 많은 시간이 걸려요.
더 효율적인 방법은 현재 표시된 마지막 항목을 기억하고 WHERE 절에서 행 값 비교를 사용하는 것이에요:
SELECT * FROM contacts
WHERE (lastname,firstname) > (?1,?2)
ORDER BY lastname, firstname
LIMIT 7;
이전 화면의 맨 아래 행의 lastname과 firstname을 ?1과 ?2에 바인딩하면, 위 쿼리는 다음 7개 행을 계산해요. 그리고 적절한 인덱스가 있다면 OFFSET보다 훨씬 효율적으로 계산해요.
3.2. 별도 필드로 저장된 날짜 비교 (Comparison of dates stored as separate fields)
데이터베이스 테이블에 날짜를 저장하는 일반적인 방법은 하나의 필드로 저장하는 것이에요—unix 타임스탬프, 율리우스 날짜 번호, 또는 ISO-8601 날짜 문자열로요. 하지만 일부 응용 프로그램은 날짜를 연(year), 월(month), 일(day)의 세 개의 별도 필드로 저장해요.
CREATE TABLE info(
year INT, -- 4 digit year
month INT, -- 1 through 12
day INT, -- 1 through 31
other_stuff BLOB -- blah blah blah
);
이런 방식으로 날짜를 저장할 때, 행 값 비교는 날짜를 비교하는 편리한 방법을 제공해요:
SELECT * FROM info
WHERE (year,month,day) BETWEEN (2015,9,12) AND (2016,9,12);
3.3. 다중 열 키에 대한 검색 (Search against multi-column keys)
주문 번호 365에 있는 어떤 항목의 product number와 quantity와 일치하는 product number와 quantity를 가진 모든 항목의 주문 번호, 제품 번호, 수량을 알고 싶다고 가정해 보아요:
SELECT ordid, prodid, qty
FROM item
WHERE (prodid, qty) IN (SELECT prodid, qty
FROM item
WHERE ordid = 365);
위 쿼리는 조인으로 다시 쓰고 행 값을 사용하지 않을 수도 있어요:
SELECT t1.ordid, t1.prodid, t1.qty
FROM item AS t1, item AS t2
WHERE t1.prodid=t2.prodid
AND t1.qty=t2.qty
AND t2.ordid=365;
같은 쿼리를 행 값 없이도 작성할 수 있으므로, 행 값이 새로운 기능을 제공하는 것은 아니에요. 하지만 많은 개발자들이 행 값 형식이 읽고, 쓰고, 디버깅하기 더 쉽다고 말해요.
JOIN 형태에서도 행 값을 사용하면 쿼리를 더 명확하게 만들 수 있어요:
SELECT t1.ordid, t1.prodid, t1.qty
FROM item AS t1, item AS t2
WHERE (t1.prodid,t1.qty) = (t2.prodid,t2.qty)
AND t2.ordid=365;
이 마지막 쿼리는 이전 스칼라 형태와 정확히 같은 바이트코드를 생성하지만, 더 깔끔하고 읽기 쉬운 구문을 사용해요.
3.4. 쿼리를 기반으로 테이블의 여러 열 업데이트 (Update multiple columns based on a query)
행 값 표기법은 단일 쿼리의 결과로 테이블의 두 개 이상의 열을 업데이트하는 데 유용해요. 그 예가 Fossil 버전 관리 시스템의 전문 검색 기능이에요.
Fossil 전문 검색 시스템에서 전문 검색에 참여하는 문서(위키 페이지, 티켓, 체크인, 문서 파일 등)는 "ftsdocs"(full text search documents)라는 테이블로 추적돼요. 새 문서가 저장소에 추가되면 바로 색인되지 않아요. 색인은 검색 요청이 있을 때까지 지연돼요. ftsdocs 테이블에는 문서가 색인됐으면 true, 아니면 false인 "idxed" 필드가 있어요.
검색 요청이 발생하고 대기 중인 문서가 처음으로 색인될 때, ftsdocs 테이블은 idxed 열을 true로 설정하고 검색과 관련된 다른 여러 열도 채워야 해요. 그 다른 정보는 조인으로 얻어져요. 쿼리는 이렇게 돼요:
UPDATE ftsdocs SET
idxed=1,
name=NULL,
(label,url,mtime) =
(SELECT printf('Check-in [%%.16s] on %%s',blob.uuid,
datetime(event.mtime)),
printf('/timeline?y=ci&c=%%.20s',blob.uuid),
event.mtime
FROM event, blob
WHERE event.objid=ftsdocs.rid
AND blob.rid=ftsdocs.rid)
WHERE ftsdocs.type='c' AND NOT ftsdocs.idxed
ftsdocs 테이블의 9개 열 중 5개가 업데이트돼요. 수정된 두 열 "idxed"와 "name"은 쿼리와 독립적으로 업데이트할 수 있어요. 하지만 "label", "url", "mtime" 세 열은 모두 "event"와 "blob" 테이블에 대한 조인 쿼리가 필요해요. 행 값이 없었다면, 동등한 UPDATE는 조인을 열마다 한 번씩 세 번 반복해야 했을 거예요.
3.5. 표현의 명확성 (Clarity of presentation)
때로는 행 값을 사용하는 것이 SQL을 읽고 쓰기 더 쉽게 만들기 때문이에요. 다음 두 UPDATE 문을 생각해 보아요:
UPDATE tab1 SET (a,b)=(b,a);
UPDATE tab1 SET a=b, b=a;
두 UPDATE 문은 정확히 같은 일을 해요. (동일한 바이트코드를 생성해요.) 하지만 첫 번째 형태(행 값 형태)가 A열과 B열의 값을 교환하려는 의도를 더 명확하게 보여 주는 것 같아요.
또는 이 동일한 쿼리들을 생각해 보아요:
SELECT * FROM tab1 WHERE a=?1 AND b=?2;
SELECT * FROM tab1 WHERE (a,b)=(?1,?2);
다시 말해, 이 SQL 문들은 동일한 바이트코드를 생성해서 정확히 같은 방식으로 같은 일을 해요. 하지만 두 번째 형태는 쿼리 매개변수들을 WHERE 절 전체에 흩뜨리는 대신 단일 행 값으로 묶어서 사람이 읽기 더 쉽게 만들어 줘요.
4. 하위 호환성 (Backwards Compatibility)
행 값은 SQLite 3.15.0(2016-10-14) 버전에 추가됐어요. 이전 버전의 SQLite에서 행 값을 사용하려고 하면 구문 오류가 발생해요.