MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

MySQL Membership

without comments

MySQL membership conditions are in the MySQL 8 Documentation. They’re found in the 13.2.11.3 Subqueries with ANY, IN, or SOME section. The IN and =ANY operators both perform equality matches with one twist. The IN operator works with a set of values or a subquery but the =ANY operator only works with a subquery.

I created the digits, letters, and words tables for this example. They hold the following values respectively:

  • The numbers table holds the values of 1, 2, 3, and 4
  • The letters table holds the values of 'a', 'b', 'c', and 'd'
  • The words table holds the values of 'Captain America', 'Iron Man', 'Thor', and Ant-Man

The following examples show the IN and =ANY membership comparison operators:

The IN membership operator:

The first example shows you how to use the IN operator with a set of values and the second example shows you how to use the IN operator with a subquery:

SELECT 'True Statement' AS result
WHERE  'a' IN ('a','b','c','d');
SELECT 'True Statement' AS result
WHERE  'a' IN (SELECT letter FROM letters);

The previous evaluations are case insensitive membership comparisons against a set of values and a return set from a subquery. You can make a case sensitive membership comparison by putting the BINARY keyword before one of the strings in a comparison. The BINARY keyword converts the string to a binary string and forces a binary string comparison of the two strings (MySQL 8.0 Reference Manual: 12.8.1 – String Comparison Operators and Functions).

SELECT 'True Statement' AS result
WHERE   BINARY 'a' IN (SELECT letter FROM letters);

The =ANY membership operator:

The same approach doesn’t work when you try to perform a membership comparison against a result set:

SELECT 'True Statement' AS result
WHERE  'a' =ANY ('a','b','c','d');

It returns the following error because the ANY, SOME, and ALL membership operators only work with subqueries. They all fail when you try to have them work with sets:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''a','b','c','d')' at line 2

The following math operators work with the ANY, SOME, and ALL membership operators:

  • >
  • >=
  • <
  • <=
  • <>
  • !=

While the ANY and SOME membership operators work alike, the AND operator works differently. The ANY and SOME membership operators work like an OR logical operator in the WHERE clause.

For example, the following <ANY comparison works when the right operand is in the set but not the largest element returned by the subquery.

SELECT 'True Statement' AS result
WHERE  2 <SOME (SELECT digit FROM numbers);

You can use a <=ANY or <=SOME when the left operand is equal to the highest digit in the subquery’s result set. The <ALL comparison only works when the left operand is a digit lower than the smallest element returned by the subquery.

SELECT 'True Statement' AS result
WHERE   0 <ALL (SELECT digit FROM numbers);

Here’s a set of SQL commands to setup the test cases for membership operators:

DROP TABLE letters;
CREATE TABLE letters (letter VARCHAR(10));
DROP TABLE numbers;
CREATE TABLE numbers (digit int);
DROP TABLE words;
CREATE TABLE words (word VARCHAR(20));
INSERT INTO letters VALUES ('a'),('b'),('c'),('d'),('e');
INSERT INTO numbers VALUES (1),(2),(3),(4),(5);
INSERT INTO words VALUES ('Captain America'),('Thor'),('Iron Man'),('Ant-Man');

As always, I hope this helps those looking for more examples and good solutions.

Written by maclochlainn

January 24th, 2021 at 1:25 am

Posted in macOS,MySQL 8,sql

Tagged with

«
»