I have a user table with a couple as identifier : id and type, like this :
id | type | name
----------------
15 | 1 | AAA
16 | 1 | BBB
15 | 2 | CCC
I would like to get a list, matching both id and type.
I currently use a concat system, which works :
SELECT u.id,
u.type,
u.name
FROM user u
WHERE CONCAT(u.id, '-', u.type) IN ('15-1', '16-1', '17-1', '10-2', '15-2')
But, I have the feeling it could be better, what would be the proper way to do it ?
Thank you !
You may use the following approach in mysql
with dat as
(
select 17 id, 1 type, 'AAA' t
union all
select 16 id, 1 type, 'BBB' t
union all
select 17 id, 2 type, 'CCC' t
)
-- end of testing data
select *
from dat
where (id, type) in (
-- query data
(17, 1), (16, 1)
)
You can separate them out. Not sure if it's more efficient but at least you would save the concat part :
SELECT u.id,
u.type,
u.name
FROM user u
WHERE (u.id = 15 AND u.type = 1)
OR (u.id = 16 AND u.type = 1)
OR (u.id = 17 AND u.type = 1)
OR (u.id = 10 AND u.type = 2)
OR (u.id = 15 AND u.type = 2)
IN can operate on "tuples" of values, like this (a, b) IN ((c,d), (e,f), ....). Using this method is (should be) faster as you are not doing a concat operation on "a" and "b" and then comparing strings; instead you are comparing pairs of values, unprocessed and with an appropriate comparison operation (i.e. not always string compares).
Additionally, if "a" and/or "b" are string values themselves using the concat technique risks ambiguous results. ("1-2","3") and ("1","2-3") pairs concat to the same result "1-2-3"
I think it depends a lot on how you obtain the values for id and type that you use for filtering
If they are results of another computation they can be saved in a temporary table and used in a join
create TEMPORARY TABLE criteria as
select 15 as id, 1 as type
UNION
select 16 as id, 1 as type
UNION
select 17 as id, 1 as type
UNION
select 10 as id, 2 as type
UNION
select 15 as id, 2 as type
SELECT u.id,
u.type,
u.name
FROM user u
inner join criteria c on u.type = c.type and u.id = c.id
The other option is an inner query and then a join or a WITH clause (which is rather late addition to Mysql arsenal of tricks)
