Operator ANY dan ALL dapat digunakan dengan klausa WHERE atau
HAVING.
Operator ANY akan mengembalikan true jika salah satu nilai subquery memenuhi kondisi.
Operator ALL akan mengembalikan true jika semua nilai subkueri memenuhi kondisi.
Syntax ANY
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name FROM table_name WHERE condition);
Syntax ALL
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name FROM table_name WHERE condition);
Catatan: Operator harus menjadi operator perbandingan
standar (=, <>,! =,>,> =, <, Atau <=).
Contoh Database
Di bawah ini adalah pilihan dari tabel “Produk” di database contoh Northwind:
IdProduk
NamaProduk
IdSupplier
IdCategory
Unit
Harga
1
Chais
1
1
10 boxes x 20 bags
18
2
Chang
1
1
24 – 12 oz bottles
19
3
Aniseed Syrup
1
2
12 – 550 ml bottles
10
4
Chef Anton’s Cajun Seasoning
2
2
48 – 6 oz jars
22
5
Chef Anton’s Gumbo Mix
2
2
36 boxes
21.35
Dan pilihan dari tabel “OrderDetails”:
IdOrderDetail
IdOrder
IdProduct
Quantity
1
10248
11
12
2
10248
42
10
3
10248
72
5
4
10249
14
9
5
10249
51
40
Contoh SQL ANY
Operator ANY akan mengembalikan TRUE jika salah satu nilai subkueri memenuhi ketentuan.
Pernyataan SQL berikut mengembalikan TRUE dan mencantumkan nama produk jika menemukan catatan ANY di tabel OrderDetails bahwa Quantity = 12:
Contoh
SELECT NamaProduk
FROM Products
WHERE IdProduct = ANY (SELECT IdProduct FROM OrderDetails WHERE Quantity = 12);
Pernyataan SQL berikut mengembalikan TRUE dan
mencantumkan nama produk jika menemukan rekaman ANY di tabel OrderDetails bahwa kuantitas > 99:
Contoh
SELECT NamaProduk
FROM Products
WHERE IdProduct = ANY (SELECT IdProduct FROM OrderDetails WHERE Quantity > 99);
Contoh SQL ALL
Operator ALL akan mengembalikan TRUE jika semua nilai subkueri memenuhi kondisi.
Pernyataan SQL berikut mengembalikan TRUE dan mencantumkan nama produk jika ALL rekaman dalam tabel OrderDetails memiliki quantitas = 10 (jadi, contoh ini akan mengembalikan FALSE, karena tidak SEMUA
rekaman dalam tabel OrderDetails memiliki quantitas= 10):
Contoh
SELECT NamaProduk
FROM Products
WHERE IdProduct = ALL (SELECT IdProduct FROM OrderDetails WHERE Quantity = 10);
12.4.2 Comparison Functions and Operators
Table 12.4 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 argument
LIKE
Simple pattern matching
NOT BETWEEN ... AND ...
Whether a value is not within a range of values
NOT IN()
Whether a value is not within a set of values
NOT LIKE
Negation of simple pattern matching
STRCMP()
Compare two strings
Comparison operations result in a value of 1 (TRUE), 0 (FALSE), or NULL. These operations work for both numbers and strings. Strings are automatically converted to numbers and numbers to strings as necessary.
The following relational comparison operators can be used to compare not only scalar operands, but row operands:
= > < >= <= <> !=
The descriptions for those operators later in this section detail how they work with row
operands. For additional examples of row comparisons in the context of row subqueries, see Section 13.2.11.5, “Row Subqueries”.
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; Section 12.3, “Type Conversion in Expression Evaluation”,
describes the rules for comparison operations performed by these and similar functions for determining their return values.
Note
In previous versions of MySQL, when evaluating an expression containing LEAST() or GREATEST(), the server attempted to guess the context in which the function was used, and to coerce the function's arguments to the data type of the expression as a whole. For example, the arguments to LEAST("11",
"45", "2") are evaluated and sorted as strings, so that this
expression returns "11". In MySQL 8.0.3 and earlier, when evaluating the expression LEAST("11", "45", "2") + 0, the server converted the arguments to integers (anticipating the addition of integer 0 to the result) before sorting them, thus returning 2.
Beginning with MySQL 8.0.4, the server no longer attempts to infer context in this fashion. Instead, the function is executed using the arguments as provided, performing data type conversions to one or more of the arguments if and only if they are not
all of the same type. Any type coercion mandated by an expression that makes use of the return value is now performed following function execution. This means that, in MySQL 8.0.4 and later, LEAST("11", "45", "2") +
0 evaluates to "11" + 0 and thus to integer 11. (Bug #83895, Bug #25123839)
To convert a value to a specific type for comparison purposes, you can use the CAST() function. String
values can be converted to a different character set using CONVERT(). See Section 12.11, “Cast Functions and Operators”.
By default, string comparisons are not case-sensitive and use the current character set. The default is utf8mb4.
For row comparisons, (a, b) = (x, y) is equivalent to:
(a = x) AND (b = y)
<=>
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.
The
<=> operator is equivalent to the standard SQL IS NOT
DISTINCT FROM operator.
For row comparisons, (a, b) <> (x,
y) and (a, b) != (x, y) are equivalent to:
(a <> x) OR (b <> y)
<=
Less than or equal:
mysql> SELECT 0.1 <= 2;
-> 1
For row comparisons, (a, b) <= (x, y) is equivalent to:
(a < x) OR ((a = x) AND (b <= y))
<
Less than:
mysql> SELECT 2 < 2;
-> 0
For row comparisons, (a, b) < (x, y) is equivalent to:
(a < x) OR ((a = x) AND (b < y))
>=
Greater than or equal:
mysql> SELECT 2 >= 2;
-> 1
For row comparisons, (a, b) >= (x, y) is equivalent to:
(a > x) OR ((a = x) AND (b >= y))
>
Greater than:
mysql> SELECT 2 > 2;
-> 0
For row comparisons, (a, b) > (x, y) is equivalent to:
(a > x) OR ((a = x) AND (b > y))
expr BETWEEN min AND max
If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN
returns 1, otherwise it returns 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 according to the rules described in Section 12.3, “Type Conversion in Expression
Evaluation”, but applied to all the three arguments.
mysql> SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1;
-> 1, 0
mysql> SELECT 1 BETWEEN 2 AND 3;
-> 0
mysql> SELECT 'b' BETWEEN 'a' AND 'c';
-> 1
mysql> SELECT 2 BETWEEN 2 AND '3';
-> 1
mysql> SELECT 2 BETWEEN 2 AND 'x-3';
-> 0
For best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a
DATETIME to two 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 the string to a
DATE.
expr NOT BETWEEN min AND max
This is the same as NOT
(expr BETWEEN min AND
max).
COALESCE(value,...)
Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.
The return type of
COALESCE() is the aggregated type of the argument types.
Returns 1 (true) if expr is equal to any of the values in the IN() list, else returns 0 (false).
Type conversion takes place according to the rules described in Section 12.3, “Type Conversion in Expression Evaluation”, applied to all the arguments. If no type conversion is needed for the values in the IN() list, they are all non-JSON constants of the same type, and expr can be compared to each of them as a value of the same type (possibly after type conversion), an optimization takes place. The values the list are sorted and the search for expr is done using a binary search,
which makes the IN() operation very quick.
mysql> SELECT 2 IN (0,3,5,7);
-> 0
mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');
-> 1
IN() can be used to compare row constructors:
mysql> SELECT (3,4) IN ((1,2), (3,4));
-> 1
mysql> SELECT (3,4) IN ((1,2), (3,5));
-> 0
You should never mix quoted and unquoted values in an IN() list because the comparison rules for quoted values (such as strings) and unquoted values (such as numbers) differ. Mixing types may therefore lead to inconsistent results. For example, do not write an IN() expression like this:
SELECT val1 FROM tbl1 WHERE val1 IN (1,2,'a');
Instead, write it like this:
SELECT val1 FROM tbl1 WHERE val1 IN ('1','2','a');
Implicit type
conversion may produce nonintuitive results:
mysql> SELECT 'a' IN (0), 0 IN ('b');
-> 1, 1
In both cases, the comparison values are converted to floating-point values, yielding 0.0 in each case, and a comparison result of 1 (true).
The number of values in the IN() list is only limited by the max_allowed_packet value.
To comply with the SQL standard, IN() returns NULL not only if the
expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.
IN() syntax can also be used to write certain types of subqueries. See Section 13.2.11.3, “Subqueries with ANY, IN, or SOME”.
expr NOT IN (value,...)
This is the same as NOT
(expr IN (value,...)).
INTERVAL(N,N1,N2,N3,...)
Returns 0 if N < N1, 1 if N < N2 and so on or -1 if N is NULL. All arguments are treated as integers. It is
required that N1 < N2 < N3 < ... < Nn for this function to work correctly. This is because a binary search is used (very fast).
Tests a value against a boolean value, where boolean_value can be TRUE, FALSE, or UNKNOWN.
mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;
-> 1, 1, 1
IS NOT
boolean_value
Tests a value against a boolean value, where boolean_value can be TRUE, FALSE, or UNKNOWN.
mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;
-> 1, 1, 0
IS NULL
Tests whether a
value is NULL.
mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
-> 0, 0, 1
To work well with ODBC programs, MySQL supports the following extra features when using IS
NULL:
If sql_auto_is_null variable is set to 1, then after a
statement that successfully inserts an automatically generated AUTO_INCREMENT value, you can find that value by issuing a statement of the following form:
SELECT * FROM tbl_name WHERE auto_col IS NULL
If the statement returns a row, the value returned is the same as if you invoked the LAST_INSERT_ID() function. For details, including the return value after a multiple-row insert, see
Section 12.16, “Information Functions”. If no AUTO_INCREMENT value was successfully inserted, the SELECT statement returns no row.
The behavior of retrieving an AUTO_INCREMENT value by using an
IS NULL comparison can be disabled by setting sql_auto_is_null = 0. See Section 5.1.8, “Server System Variables”.
The default
value of sql_auto_is_null is 0.
For DATE and DATETIME columns that are declared as
NOT NULL, you can find the special date '0000-00-00' by using a statement like this:
SELECT * FROM tbl_name WHERE date_column IS NULL
This is needed to get some ODBC applications to work because ODBC does not support a '0000-00-00' date value.
See Obtaining Auto-Increment Values, and the description for the FLAG_AUTO_IS_NULL option at
Connector/ODBC Connection Parameters.
IS NOT NULL
Tests whether a value is not NULL.
mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
-> 1, 1, 0
ISNULL(expr)
If expr is NULL, ISNULL() returns 1, otherwise it returns 0.
ISNULL() can be used instead of = to test whether a value is NULL. (Comparing a value to NULL using = always yields NULL.)
The
ISNULL() function shares some special behaviors with the IS NULL comparison operator. See the description of IS NULL.
LEAST(value1,value2,...)
With two or more arguments, returns the smallest (minimum-valued) argument. The arguments are compared using the following rules:
If any argument is NULL, the result is NULL. No comparison is needed.
If all arguments are integer-valued, they are compared as
integers.
If at least one argument is double precision, they are compared as double-precision values. Otherwise, if at least one argument is a DECIMAL value, they are compared as
DECIMAL values.
If the arguments comprise a mix of numbers and strings, they are compared as strings.
If any argument is a nonbinary (character) string, the arguments are compared as nonbinary strings.
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.