How to cache subquery in MySQL select?

Question

MySQL

There is a SELECT of this type of database - MySQL:

SELECT * FROM T1 WHERE item='B' AND

T1.item1 IN (SELECT item_id FROM T2 WHERE itype='A') AND 
T1.item2 IN (SELECT item_id FROM T2 WHERE itype='A') AND 
T1.item3 IN (SELECT item_id FROM T2 WHERE itype='A')

It turns out that in one query there are three identical subqueries " SELECT item_id IN T2 WHERE ityp='A' ". How can this be done with only one subquery?
Is it possible to store the results of a subquery in a single array?
Thank you.

Answer 1

Option with one join
(MS SQL - but there will probably be something similar in MySql)
The query plan will be faster than the original version only in combination with the index
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20151120-180424] ON [dbo].[T1]
(
[ id] ASC,
[item1] ASC,
[item2] ASC,
[item3]
ASC [PRIMARY]

-- Создаем таблицы
CREATE TABLE T1 (id INT IDENTITY(1,1),item1 int,item2 int,item3 INT)
CREATE TABLE T2 (id INT IDENTITY(1,1),item_id INT, itype VARCHAR(10) )

-- Наполняем данными 
-- Таблица T1
INSERT INTO [dbo].[T1] ([item1],[item2],[item3])VALUES(11,12,13)
INSERT INTO [dbo].[T1] ([item1],[item2],[item3])VALUES(21,22,23)
INSERT INTO [dbo].[T1] ([item1],[item2],[item3])VALUES(31,32,33)
INSERT INTO [dbo].[T1] ([item1],[item2],[item3])VALUES(41,42,43)
INSERT INTO [dbo].[T1] ([item1],[item2],[item3])VALUES(51,52,53)
INSERT INTO [dbo].[T1] ([item1],[item2],[item3])VALUES(11,32,53)
INSERT INTO [dbo].[T1] ([item1],[item2],[item3])VALUES(17,32,53)

-- Таблица T2
INSERT INTO [dbo].[T2]([item_id],[itype]) VALUES(21,'A')
INSERT INTO [dbo].[T2]([item_id],[itype]) VALUES(11,'B')
INSERT INTO [dbo].[T2]([item_id],[itype]) VALUES(13,'B')
INSERT INTO [dbo].[T2]([item_id],[itype]) VALUES(41,'A')
INSERT INTO [dbo].[T2]([item_id],[itype]) VALUES(42,'A')
INSERT INTO [dbo].[T2]([item_id],[itype]) VALUES(43,'A')
INSERT INTO [dbo].[T2]([item_id],[itype]) VALUES(17,'A')


-- Исходный запрос
SELECT * FROM T1 WHERE 
T1.item1 IN (SELECT item_id FROM T2 WHERE itype='A') AND 
T1.item2 IN (SELECT item_id FROM T2 WHERE itype='A') AND 
T1.item3 IN (SELECT item_id FROM T2 WHERE itype='A')

-- Запрос с одним джойном
;WITH CTE_OneSubQuery
AS
(

SELECT DISTINCT
tt1.id, tt1.item1,tt1.item2,tt1.item3
,CASE (COUNT (tt2.item_id)  OVER (PARTITION BY tt1.id)) WHEN 3 THEN 1 END AS [kol]
FROM T1 tt1
JOIN t2 tt2 ON tt2.item_id =CASE
  WHEN tt2.item_id = tt1.item1 THEN tt1.item1
  WHEN tt2.item_id = tt1.item2 THEN tt1.item2
  WHEN tt2.item_id = tt1.item3 THEN tt1.item3
END 

WHERE  tt2.itype = 'A'
)

SELECT  id, item1,item2,item3 FROM CTE_OneSubQuery 
WHERE kol =1

Answer 2

SELECT * FROM T1
LEFT JOIN
(
   SELECT 
   DISTINCT item_id 
   FROM T2 
   WHERE itype='A'
) t ON t.item_id = T1.item1
   AND t.item_id = T1.item2
   AND t.item_id = T1.item3