[펌][MySQL] 비교 연산자

[원본] http://randa.tistory.com/

MySQL에서 비교연산자는 1(TRUE), 0(FALSE), NULL값 또는 연산에 대한 결과를 리턴한다. 이 포스팅에서는 비교연산자의 설명 및 예제를 설명하도록 한다.

1. BETWEEN min AND max

해당 연산자는 AND 좌우의 값을 포함한 범위안에 값이 있는지를 체크한다. 이는 수식으로 "expr >= ... AND expr <= ..." 동일한 결과를 나타낸다.

?

1 2 3 4 5

SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1; SELECT 1 BETWEEN 2 AND 3; SELECT 'b' BETWEEN 'a' AND 'c'; SELECT 2 BETWEEN 2 AND '3'; SELECT 2 BETWEEN 2 AND 'x-3';

1line - BETWEEN은 반드시 범위 값 중 작은게 왼쪽 큰게 오른쪽에 있어야 한다. 따라서 2는 1~3범위 안에 있음으로 

    2 BETWEEN 1 AND 3은 1을 리턴하고, 2 BETWEEN 3 and 1은 비교대상 중 우측이 더 작음으로 0을 리턴한다.

2line - 1은 2~3 범위안에 없기 때문에 0을 리턴한다.

3line - a~c중에는 b가 있기때문에 1이 리턴된다. BETWEEN은 숫자 뿐만 아니라 문자도 비교 가능한 것을 알 수 있다.

4line - 범위 중 3을 '' 로 묶어 문자열로 치환하였으나, 숫자만이 존재 할 경우 내부적으로 숫자로 취급한다. 따라서 2~3중에는 

          2가 포함되어 있기에 1을 리턴한다.

5line - 숫자와 문자열의 범위를 주었다. 이 경우 정상적으로 값을 취할 수 없기 때문에 0을 리턴한다. 5line의 예제는 비교연산

          을 위해서는 동일한 형태의 데이터 타입끼리 비교를 해야 정상적인 결과를 줌을 알 수 있다.

2. COALESCE(value1,value2 ...)

COALESCE는 범위안에 값 중 NULL이 아닌 첫번째 인수를 리턴한다. 만약 모든 값 이 NULL일 경우는 NULL을 리턴한다.

?

1 2	SELECT COALESCE(NULL,1,2,3); SELECT COALESCE(NULL,NULL,NULL);

1line - 좌측부터 시작해서 첫번째로 NULL이 아닌 것은 1이기 때문에 1을 리턴한다.

2line - value 값이 전부 NULL로만 이루어져 있기 때문에 NULL을 리턴한다.

3. GREATEST(value1,value2 ...)

GREATEST는 범위안에 값 중 가장 큰 인수를 리턴한다.

?

1 2 3 4

SELECT GREATEST(2,0); SELECT GREATEST(34.0,3,5.0,767.99); SELECT GREATEST('B','A','C'); SELECT GREATEST(NULL,'B','C');

1line - 2, 0중 가장 큰 수인 2를 리턴한다.

2line - 가장 큰 수치인 767.99를 리턴한다.

3line - a, b, c중 가장 뒤인 c를 리턴한다.

4line - 값 중 NULL이 포함될 경우 NULL을 리턴한다.

4. expr [NOT] IN (value1,value2 ...)

IN 구문은 expr에 해당하는 값이 ( ) 안에 존재할 경우 1(TRUE)을 리턴한다. NOT이 붙을 경우 반대로 해당하는 값이 없어야 1을 리턴한다.

?

1 2 3 4

SELECT 2 IN (0,3,5,7); SELECT 'wefwf' IN ('wee','wefwf','weg'); SELECT 2 NOT IN (0,3,5,7); SELECT 'wefwf' NOT IN ('wee','wefwf','weg');

1line - 0, 3, 5, 7 중 2가 존재하지 않기때문에 0을 리턴한다.

2line - 'wefwf'는 ( ) 안에 존재하기 때문에 1을 리턴한다.

3line - 1line 결과와 반대로 1을 리턴한다.

4line - 2line 결과와 반대로 0을 리턴한다.

IN절안에는 서로 다른 데이터 타입을 쓰지않는 것이 좋다. 이는 MySQL 내부적으로 비교하는 기준이 다를 수 있기 때문이다. 따라서 만약 숫자와 문자열을 혼합한다면 

?

1	SELECT val1 FROM tbl1 WHERE val1 IN (1,2,'a');

위와 같이 사용하는 대신 아래와 같이 써야 한다.

?

1	SELECT val1 FROM tbl1 WHERE val1 IN ('1','2','a');

IN절에서 ( ) 안의 값은 MySQL 환경변수 중 max_allow_packet에 영향을 받는다. IN절 안의 목록을 마음껏 늘려도 되지만 DB에서 설정 한 max_allow_packet 값을 초과할 수 는 없다.(사실 해당 값을 초과할 정도로 SQL을 작성할 경우는 극히 드물고 매우 비효율 적일 것이다. 그정도라면 JOIN이 더 효율적일 것임으로.)

5. INTERVAL(N,N1,N2,N3,...)

INTERVAL은 값 N과 그 뒤의 값을 비교하여 결과를 리턴한다. 최소 1개 이상의 N(num) 값이 있어야 한다.

 * N < N1 일 경우 뒤 N과 관계없이 무조건 0을 리턴한다.

 * N1 < N2 < N3 ... 와 같이 N(num)은 뒤로갈 수록 값이 커야 한다. 내부적으로 Binary 형태로 결과를 구하기 때문이 고 만약 

   그렇지 않을 경우 원하는 결과가 나오지 않을 수 있다.

 * N이 NULL 일 경우는 -1을 리턴한다.

 * N > N(num)이 될때까지의 N(num)에 대한 개수를 구한다. 왼쪽부터 읽는다.

?

1 2 3 4 5

SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200); SELECT INTERVAL(10, 1, 10, 100, 1000); SELECT INTERVAL(22, 23, 30, 44, 200); SELECT INTERVAL(NULL, 3, 5, 7, 9); SELECT INTERVAL(100, 1, 10, 100, 10, 1000, 10);

1line - N(23)보다 큰 N(num)은 30이다. 그럼 그 사이의 1, 15, 17의 개수 3. 따라서 3을 리턴한다.

2line - N(10)보다 큰 N(num)은 100이다. INTERVAL에서 >= 는 조건에 포함되지 않는다. 따라서 1, 10 2개. 2를 리턴한다.

3line - N(22) < N1(23)이다. 따라서 0을 리턴한다.

4line - N이 NULL값이기 때문에 -1을 리턴한다.

5line - N(100)보다 큰 N(num)은 1, 10, 100, 10 이 있다. N < N(num)이 만족한 1000에서 종료되기 때문에 뒤에 10은 무시된다. 

          따라서 4를 리턴한다.

6. IS [NOT] NULL

값이 NULL인가를 비교하여 값을 리턴한다. IS NULL의 경우 NULL인 경우 1 NULL이 아닌경우 0을 리턴한다. 반대로 NOT이 붙으면 부정의 의미가 되어 NULL이 아닌경우 1, NULL인 경우 1을 리턴한다.

?

1 2	SELECT 1 IS NULL, '' IS NULL, NULL IS NULL; SELECT 1 IS NOT NULL, '' IS NOT NULL, NULL IS NOT NULL;

1line - NULL(공백은 NULL이 아니다)은 NULL로 명시할 경우에만 존재함으로 0 / 0 / 1을 리턴한다.

2line - NULL(공백은 NULL이 아니다)인 경우에만 0을 리턴함으로 1 / 1 / 0을 리턴한다.

7.  IS [NOT] boolean_value

불리안 값에 대응해서 값을 테스트 하는데, 여기에서 boolean_value 은 TRUE, FALSE, or UNKNOWN이 될 수 있다. 

IS NOT boolean_value 신텍스는 MySQL 5.0.2에서 추가 되었다.

?

1 2	SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN; SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;

1line - 1, 0, NULL은 각각 TRUE/FALSE/UNKNOWN과 대응됨으로 전부 1을 리턴한다.

2line - 1, 0은 각각 TRUE/FALSE에 대응함으로 참이다. NULL은 UNKNOWN인데 NOT을 붙여 부정했기에 거짓이 된다. 따라서 

          1/1/0을 리턴한다.

8. ISNULL(expr)

IS NULL과 비슷한 역활을 한다. expr에 대한 결과가 NULL인지 아닌가에 대하여 확인하여 NULL일 경우 1, 아닐경우 0을 리턴한다.

?

1 2	SELECT ISNULL(1+1); SELECT ISNULL(1/0);

1line - 1+1의 결과는 2다. NULL이 아님으로 0을 리턴한다.

2line - 1을 0으로 나눈다는 것은 불가능하기 때문에 NULL이다. 따라서 1을 리턴한다.

9. LEAST(value1,value2,...)

LEAST는 값 중 가장 작은 인수를 리턴한다. 인수들은 아래의 규칙에 따라서 비교가 된다

 * 만일 리턴 값이 INTEGER 문맥에서 사용되거나 또는 모든 인수가 정수 값이라면, 그 인수들은 정수로 비교가 된다.

 * 만일 리턴 값이 REAL 문맥에서 사용되거나 또는 모든 인수가 실수 값 (real value)이라면, 인수들은 실수(real)로 비교된다.

 * 만일 어떤 인수가 문자 크기에 민감한 스트링이라면, 그 인수는 문자 크기를 구분하는 스트링으로 비교가 된다.

 * 이외의 모든 경우에는, 인수들은 문자 크기와는 상관없는 스트링으로 비교가 된다.

MySQL 5.0.13 이전의 경우, LEAST() 은 모든 인수가 NULL 일 경우에만 NULL을 리턴했다. 하지만 5.0.13 이후에는, 어떤 인수라도 NULL 포함되어 있다면 NULL을 리턴한다.

?

1 2 3

SELECT LEAST(2,0); SELECT LEAST(34.0,3.0,5.0,767.0); SELECT LEAST('B','A','C');

1line - 0이 가장 작음으로 0을 리턴한다.

2line - 3.0이 가장 작음으로 3.0을 리턴한다.

3line - A가 가장 앞선 문자열임으로 A를 리턴한다.

10. value1 = value2

양쪽의 값을 경우 1 틀릴경우 0을 리턴한다. 단 하나의 값이라도 NULL이 포함되어지면 결과는 NULL을 리턴하게된다.

?

1 2 3

SELECT 1 = 0; SELECT '0' = 0; SELECT NULL = 0;

1line - 1은 0이 아님으로 0을 리턴한다.

2line - 0은 0임으로 1을 리턴한다

3line - NULL이 포함되었음으로 NULL을 리턴한다.

11. value1 <=> value2

<=>는 같은 값을 비교하는 =과 내용은 근본적으로 동일하다. 하지만 차이점은 NULL 하나의 값도 값으로 표시한다는게 다르다.

?

1 2	SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL; SELECT 1 = 1, NULL = NULL, 1 = NULL;

1line - 1, 1, 0. NULL 값도 하나의 문자로 취급 하기 때문에 NULL을 리턴하지 않는다.

2line - 1, NULL, NULL. 하나라도 비교 대상에 NULL이 포함되면 연산불가로 판정하고 NULL 값을 리턴한다.

12. value1 <> value2, 또는 value1 != value2

=와 반대이다. 일치하지 않을 경우 1을 리턴한다.

?

1 2 3

SELECT '.01' <> '0.01'; SELECT .01 <> '0.01'; SELECT 'zapp' <> 'zappp';

1line - .01은 0.01과 같음으로 0을 리턴할 것 같지만 둘다 ' 으로 묶어 문자형 만들었다. 따라서 문자 그대로 비교하여 틀림으로 

           값은 1

2line - .01은 0.01과 같음으로 0을 리턴한다. 숫자의 경우 ' 으로 묶지 않으면 앞의 값이 생략된 경우 0을 추가한다. 

3line - zapp와 zappp는 다름으로 1을 리턴한다.

13. value1 > value2

값 1이 값 2보다 클 경우 1을 리턴한다.

?

1	SELECT 2 > 1;

1line - 2가 1보다 큼으로 1을 리턴한다.

14. value1 >= value2

값 1이 값 2보다 크거나 같을 경우 1을 리턴한다. >=의 순서 바꾸면 안된다.

?

1	SELECT 2 >= 2

1line - 2보다 크거나 같을 경우에 참인데 2와 2는 같음으로 1을 리턴한다.

15. value1 < value2

값 1이 값 2보다 작을 경우 1을 리턴한다.

?

1	SELECT 2 < 1;

1line - 2는 1보다 작음으로 1을 리턴한다.

12. value1 <= value2

값 1이 값 2보다 작거나 같을 경우 1을 리턴한다. <=의 순서 바꾸면 안된다.

?

1	SELECT 2 <= 2;

1line - 2보다 작거나 같을 경우에 참인데 2와 2는 같음으로 1을 리턴한다.