[SQL#3] 문자열 패턴 매칭, NULL을 포함하는 연산/비교, 쿼리 결과 정렬

참고: Database Systems: The Complete Book (2nd edition)

이번 글에서는 SQL 쿼리 작성 중 WHERE 구문에서의 비교 연산을 다룰 것이다. 문자열에서의 패턴 매칭과, NULL을 포함하는 값을 비교하는 경우를 어떻게 처리하는지 알아볼 것이다. 또한, 마지막에는 쿼리 요청 결과를 정렬하는 방법도 간단히 알아볼 것이다.

---

패턴 매칭(Pattern Matching): LIKE

문자열은 패턴 매칭에 기반하여 비교될 수 있는데, 다음과 같이 LIKE와 NOT LIKE를 사용한다.

• s LIKE p
• s NOT LIKE p

이때 s는 문자열을, p는 패턴을 의미한다. 이때 패턴 p에는 %와 _ 두 개의 special character를 사용할 수 있다.

• %: 0개 이상의 어떤 시퀀스여도 가능 (matches any sequence of 0 or more characters)
• _ : 딱 1개의 chararcter만 일치 (matches one character)

가령, 다음과 같은 쿼리를 요청하는 경우 'Star ' 뒤에 정확히 4개의 글자(_를 4번 입력하였다.)를 갖는 튜플만 그 대상이 된다. (e.g. 'Star Wars')

SELECT title
FROM Movies
WHERE title LIKE 'Star ____';

하지만 다음과 같이 작성하는 경우 'Star'로 시작하는 제목이기만 하면 모두 요청 대상이 된다. (e.g. 'Star', 'Star ', 'Star Wars', 'Star       ' 등 모두 가능)

SELECT title
FROM Movies
WHERE title LIKE 'Star%';

따옴표(')의 활용

문자열 내 따옴표와 %/_를 같이 사용할 수 있다.

예를 들어, 's를 포함하는 문자열을 찾고 싶은 경우 다음과 같은 쿼리를 작성할 수 있다. 

SELECT title
FROM Movies
WHERE title LIKE '%''s%';

이 쿼리의 경우 Alice's Restaurant, Logan's Run과 같이 's를 포함하는 문자열이 리턴된다.

일반적으로 문자열을 나타날 때 'Disney'와 같이 따옴표로 감싸게 된다. 하지만 따옴표 자체가 글자인 경우에는 일반 문자열을 나타내기 위한 따옴표와 구분하기 위해 위와 같이 따옴표를 두 개 연속으로 사용하는 것이다.(''s)

The convention taken by SQL is that two consecutive apostrophes in a string represent a single apostrophe and do not end the string. Thus, ''s in a pattern is matched by a single apostrophe followed by an s.

 

NULL을 포함하는 값의 연산/비교

NULL 값은 다음과 같은 세 가지 경우로 해석될 수 있다.

• 알려지지 않은 값 Value unknown
• 적용할 수 없는 값 Value inapplicable
• 보류된 값 Value withheld

특정 값 x가 NULL인지 아닌지 알아보는 쿼리는 IS NULL/IS NOT NULL을 사용한다.

• x IS NULL / x is NOT NULL

NULL을 포함하는 연산(operation)의 결과는 NULL이며, NULL을 포함하는 비교(comparison)의 결과는 UNKNOWN이다.

• (연산) x가 NULL이면, x+3, x*0, x-x도 NULL이다. NULL+3은 유효하지 않다.
• (비교) x가 NULL이면 x=3, x=x는 UNKNOWN이다. NULL=3, NULL=NULL은 유효하지 않다.

비교 연산의 경우, TRUE, FALSE, UNKNOWN 모든 경우에 대한 결과는 다음과 같다. 

• TRUE=1, FALSE=0, UNKNOWN=1/2로 하여 AND는 min, OR는 max, NOT은 1-x 로 생각하면 된다.

x	y	x AND y	x OR y	NOT x
TRUE	TRUE	TRUE	TRUE	TRUE
TRUE	UNKNOWN	UNKNOWN	TRUE	FALSE
TRUE	FALSE	FALSE	TRUE	FALSE
UNKNOWN	TRUE	UNKNOWN	TRUE	UNKNOWN
UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN
UNKNOWN	FALSE	FALSE	UNKNOWN	UNKNOWN
FALSE	TRUE	FALSE	TRUE	TRUE
FALSE	UNKNOWN	FALSE	UNKNOWN	TRUE
FALSE	FALSE	TRUE	TRUE	TRUE

예를 들어, 다음과 같은 쿼리를 생각해 보자.

SELECT *
FROM Movies
WHERE length <= 120 OR length > 120;

조건 자체는 가능한 모든 length를 의미하고 있다. 하지만, 한 가지 예외가 있다. length가 NULL 값인 경우를 생각해보면 비교의 결과는 UNKNOWN이 될 것이다. (UNKNOWN OR UNKNOWN → UNKNOWN) 따라서 마지막 WHERE clause를 다음과 같이 바꿀 수 있다.

SELECT *
FROM Movies
WHERE length IS NOT NULL;

 

쿼리 결과 정렬(Ordering the Output): ORDER BY

쿼리를 통해 나온 아웃풋 튜플들을 특정한 순서로 정렬하고 싶은 경우 ORDER BY <list of attribute>를 사용한다. 기본적인 정렬 순서는 오름차순(ascending/키워드: ASC)이므로, 특별히 내림차순으로 정렬하고 싶은 경우 DESC 키워드를 추가하면 된다. 

정렬 과정은 SELECT에 적용되기 직전, FROM, WHERE, 기타 다른 clause가 수행된 후에 적용된다. 결과로 나온 튜플들은 ORDER BY에 의해 정렬되어 SELECT로 넘겨지는 것이다. 

예를 들어 다음과 같은 쿼리를 살펴보자. 튜플들을 length가 짧은 순서대로(오름차순) 먼저 정렬한 후, 동일한 length를 가지는 튜플끼리는 title의 알파벳 순서로(오름차순) 정렬하는 것이다.

SELECT *
FROM Movies
WHERE studioName = 'Disney'
ORDER BY length, title;

만약 length는 오름차순으로, title은 내림차순으로 정렬하고 싶은 경우 다음과 같이 title 뒤에 DESC를 쓰면 된다.

SELECT *
FROM Movies
WHERE studioName = 'Disney'
ORDER BY length, title DESC;

ORDER BY: expression 추가하기

SELECT에서처럼 ORDER BY에도 선택적으로 expression을 추가할 수 있다. 

가령 relation R(A,B)의 튜플을 A와 B의 합을 기준으로 내림차순 정렬하고 싶다면 다음과 같이 쿼리를 작성할 수 있다.

SELECT *
FROM R
ORDER BY A+B DESC;