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?
Best Answer
There is 2 differences between them.
IN
is an operator whereisANY
is not, it is a keyword and may be treated as a comparison operator modifier (the same forALL
andSOME
).See Operators and Operator Precedence.
IN
is listed in both whereasANY
,ALL
andSOME
are not.IN
may process an expression list whereasANY
cannot.For example, the code
cannot be converted into
= ANY
form without subqueries code rewrite.In all other respects they are complete synonyms.