MySQL 비교 연산자(MySQL 8.x 기준)

Comparison Operators

Name	Description
>	Greater than operator
>=	Greater than or equal operator
<	Less than operator
<>, !=	Not equal operator
<=	Less than or equal operator
<=>	NULL-safe equal to operator
=	Equal operator
BETWEEN  ...  AND ...	Whether a value is within a range of values
COALESCE()	Return the first non-NULL argument
GREATEST()	Return the largest argument
IN()	Whether a value is within a set of values
INTERVAL()	Return the index of the argument that is less than the first argument
IS	Test a value against a boolean
IS NOT	Test a value against a boolean
IS NOT NULL	NOT NULL value test
IS NULL	NULL value test
ISNULL()	Test whether the argument is NULL
LEAST()	RETURN the smallest arguement
LIKE	Simple pattern matching
NOT BETWEEN  ...  AND ...	Whether a value is not within a range of values
NOT IN()	Whether a value is niot within a set of values
NOT LIKE	Negation of simple pattern matching
STRCMP()	Compare two Strings

Comparison operations retult in a value of 1(TRUE), 0(FALSE), or NULL. These operations work for both numbers and strings. String are automatically converted to numbers and numbers to string as necessary.

 

Some of the functions in this section return values other than 1(TRUE), 0(FALSE), or NULL. LEAST() and GREATEST() are examples of such functions.

 

By default, string comparisons are not case-sensitve and use the current character set. 

 

 

비교연산자는 1(TRUE),  0(FALSE) , NULL 을 return 한다. 문자열, 혹은 숫자들 끼리의 비교를 하는데 필요하다면 형변환이 일어난다. 형변환이 일어나는 경우 원하는 결과값을 얻지 못하는 경우가 발생할 수 있으니 CAST() 함수를 써서 명시적으로 변환을 해줘도 된다. 

 

비교연산자의 표에 있는 일부 함수는 1(TRUE),  0(FALSE) , NULL 을 return 하지 않는다. LEAST() and GREATEST() 와 같은 것들이 그러하다. 

 

 

 

1. = 

Equal

mysql> select 1=0;
--> 0

 

mysql> SELECT '0' =0;
-->  1 

 

mysql> SELECT '0.0' =0; 

-->  1 

 

mysql> SELECT '0.01' =0; 

-->  0 

 

mysql> SELECT '.01' =0.01; 

-->  1 

 

mysql> SELECT 'A' ='a'; 

-->  1 

 

2. <=>

NULL-safe equal

This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL. and 0 rather than NULL if one operand is NULL.

 

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

-->  1 , 1, 0

 

mysql> SELECT 1=1, NULL=NULL, 1= NULL;

--> 1, NULL, NULL

 

3. expr BETWEEN min AND MAX

if expr is grater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it return 0, This is equivalent to the expression (min <=expr AND expr <=max) if all the arguments are of the same type. Otherwise type  conversion takes place .......

 

min값보다 크거나 같고, max 값보다 작거나 같다.

 

For bet results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type. Exaplles: If you compare a DATETIME to tow DATE values, convert the DATE values to DATETIME values. If you use a string constant such as '2001-1-1' in a comparison to a DATE. cast ehe string a DATE

 

4. COALESCE(value, ...)

Returns the first non-NULL value in the list, or NULL if ther are no non_NULL values.

the return type of COALESCE() is the aggregated type of the argument types.

- NULL 이 아닌 첫번째의 값을 return

 

mysql> SELECT COALESCE(NULL, 1);

   --> 1

mysql> SELECT COALESCE(NULL, NULL, NULL);

   --> NULL

 

5. GREATEST(value1, value2, ...)

With two or more areuments, returns the largest(maximim-values) argument. The arguments are compared using the same rules as for LEAST():

 

mysql> SELECT GREATEST(2, 0);

   --> 2

mysql> SELECT GREATEST('B', 'A', 'C');

   --> C

 

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

이건 처음보는 것인데 뭔지 잘 모르겠다.

어디다 쓸까 ?

 

아래내용은 불확실.. 이해를 못하고 있다보니 불확실하다.

 

대충 테스트를 해보니 

N과 나머지 값들을 비교 하는 것 같다.

N < N1 이면 0, N<N2 이면 0...

N > N1 이면 1, N>N2 이면 1...

그래서 나온 값들을 더하는 것 같다.

N > N1, N > N2 , N < N3 이면 3번째에서 멈추는 듯 하다.

 

mysql> SELECT INTERVAL(0, 1, 2, 3, 4, 5, 6);
+-------------------------------+
| INTERVAL(0, 1, 2, 3, 4, 5, 6) |
+-------------------------------+
|                             0 |
+-------------------------------+
1 row in set (0.01 sec)

 

mysql> SELECT INTERVAL(23, 1, 2, 3, 4, 5, 6);
+--------------------------------+
| INTERVAL(23, 1, 2, 3, 4, 5, 6) |
+--------------------------------+
|                              6 |
+--------------------------------+
1 row in set (0.00 sec)

 

mysql> SELECT INTERVAL(35, 2, 3, 100, 5, 6);
+-------------------------------+
| INTERVAL(35, 2, 3, 100, 5, 6) |
+-------------------------------+
|                             2 |
+-------------------------------+
1 row in set (0.00 sec)

 

7. IS NULL

Tests whether a value is NULL

mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;

+-----------+-----------+--------------+
| 1 IS NULL | 0 IS NULL | NULL IS NULL |
+-----------+-----------+--------------+
|         0 |         0 |            1 |
+-----------+-----------+--------------+
1 row in set (0.00 sec)

 

8. ISNULL(expr)

If expr is NULL, ISNULL() returns 1, otherwise it returns 0;

 

mysql> SELECT ISNULL(1+1) ;
+-------------+
| ISNULL(1+1) |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT ISNULL(1/0);
+-------------+
| ISNULL(1/0) |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.01 sec)

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

With two or more arguments, returns the smallest(minimum-valued) argument. The argements are compared using the following rules:

   1) If any argument is NULL, the result is NULL, No comparison is needed

   2) If all arguments are integer-valued, ther are eompared as integers.

   3) If at least one areuments is double precision, they are compared as double-precision values.

      Otherwise, if at least one argument is a DECIMAL value, ther are compared as DECIMAL values.

   4) If the arguments comprise a mix of mumbers as string, the are compared as strings.

   5) If any argument is nonbinary(character) string, the arguments are compared as nonbinary strings.

   6) In all other cases, the arguments are compared as binary strings.

 

The return type of LEAST() is the aggregated type of the comparison argument types.

 

mysql> SELECT LEAST(2, 0) ;
+-------------+
| LEAST(2, 0) |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT LEAST('B', 'A', 'C') ;
+----------------------+
| LEAST('B', 'A', 'C') |
+----------------------+
| A                    |
+----------------------+
1 row in set (0.00 sec)

 

 

 

한번 살펴본.. 것에. 모르는 것이 생각보다 많았다.