What’s difference between MySql IN and ANY operator?

Data

Dump

CREATE TABLE `customers` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `birth_date` date DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `address` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` char(2) NOT NULL,
  `points` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;
INSERT INTO `customers` VALUES (1,'Babara','MacCaffrey','1986-03-28','781-932-9754','0 Sage Terrace','Waltham','MA',2273);
INSERT INTO `customers` VALUES (2,'Ines','Brushfield','1986-04-13','804-427-9456','14187 Commercial Trail','Hampton','VA',947);
INSERT INTO `customers` VALUES (3,'Freddi','Boagey','1985-02-07','719-724-7869','251 Springs Junction','Colorado Springs','CO',2967);
INSERT INTO `customers` VALUES (4,'Ambur','Roseburgh','1974-04-14','407-231-8017','30 Arapahoe Terrace','Orlando','FL',457);
INSERT INTO `customers` VALUES (5,'Clemmie','Betchley','1973-11-07',NULL,'5 Spohn Circle','Arlington','TX',3675);
INSERT INTO `customers` VALUES (6,'Elka','Twiddell','1991-09-04','312-480-8498','7 Manley Drive','Chicago','IL',3073);
INSERT INTO `customers` VALUES (7,'Ilene','Dowson','1964-08-30','615-641-4759','50 Lillian Crossing','Nashville','TN',1672);
INSERT INTO `customers` VALUES (8,'Thacher','Naseby','1993-07-17','941-527-3977','538 Mosinee Center','Sarasota','FL',205);
INSERT INTO `customers` VALUES (9,'Romola','Rumgay','1992-05-23','559-181-3744','3520 Ohio Trail','Visalia','CA',1486);
INSERT INTO `customers` VALUES (10,'Levy','Mynett','1969-10-13','404-246-3370','68 Lawn Avenue','Atlanta','GA',796);

Following works fine

SELECT * FROM sql_store.customers
where customer_id in(
    1,2
);

and

SELECT * FROM sql_store.customers
where customer_id = any(
    select 1
);

but following not

SELECT * FROM sql_store.customers
where customer_id = any(
    1,2
);

and

SELECT * FROM sql_store.customers
where customer_id in(
    select 1,2
);

Is ANY made to be used with subqueries which returns table and IN which returns list?

What’s difference between MySql IN and ANY operator?

There is 2 differences between them.

1. IN is an operator whereis ANY is not, it is a keyword and may be treated as a comparison operator modifier (the same for ALL and SOME).

See Operators and Operator Precedence. IN is listed in both whereas ANY, ALL and SOME are not.

2. IN may process an expression list whereas ANY cannot.

For example, the code

WHERE t1.id IN ((SELECT id FROM t2), (SELECT id FROM t3))

cannot be converted into = ANY form without subqueries code rewrite.

In all other respects they are complete synonyms.

dbfiddle

The ANY keyword, which must follow a comparison operator, means “return TRUE if the comparison is TRUE for ANY of the values in the column that the subquery returns.” docs

The docs also say that SOME is an alias for ANY