考虑使用arrayJoin函数:
SELECT *
FROM
(
SELECT arrayJoin(['id1', 'id2', 'id3-missing']) AS id
) AS input
LEFT JOIN details ON details.id = input.id
SETTINGS join_use_nulls = 1
/*
┌─id──────────┬─details.id─┬─detail_one─┬─detail_two─┐
│ id1 │ id1 │ 5 │ 10 │
│ id2 │ id2 │ 20 │ 30 │
│ id3-missing │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │
└─────────────┴────────────┴────────────┴────────────┘
*/
或
SELECT *
FROM details
RIGHT JOIN
(
SELECT arrayJoin(['id1', 'id2', 'id3-missing']) AS id
) AS input ON details.id = input.id
SETTINGS join_use_nulls = 1
/*
┌─id──┬─detail_one─┬─detail_two─┬─input.id─┐
│ id1 │ 5 │ 10 │ id1 │
│ id2 │ 20 │ 30 │ id2 │
└─────┴────────────┴────────────┴──────────┘
┌─id───┬─detail_one─┬─detail_two─┬─input.id────┐
│ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ id3-missing │
└──────┴────────────┴────────────┴─────────────┘
*/
SELECT
*,
input.tuple.1 AS array_id,
input.tuple.2 AS array_value
FROM
(
SELECT arrayJoin([('id1', 100), ('id2', 200), ('id3-missing', 300)]) AS tuple
) AS input
LEFT JOIN details ON details.id = (input.tuple.1)
/*
┌─tuple───────────────┬─id──┬─detail_one─┬─detail_two─┬─array_id────┬─array_value─┐
│ ('id1',100) │ id1 │ 5 │ 10 │ id1 │ 100 │
│ ('id2',200) │ id2 │ 20 │ 30 │ id2 │ 200 │
│ ('id3-missing',300) │ │ 0 │ 0 │ id3-missing │ 300 │
└─────────────────────┴─────┴────────────┴────────────┴─────────────┴─────────────┘
*/