[MariaDB discuss] Joining two tables on a list of values

13 Sep 2024

      Hello,

I would like to know if is there some (efficient!) way to join two 
tables on a list of values
contained in (likely a single column) of the joined table, whatever form 
could have
this list of values ?

For example, I have a main table containing:

main_table:
+------------+------------------+------+-----+------------+----------------+
| Field      | Type             | Null | Key | Default    | Extra          |
+------------+------------------+------+-----+------------+----------------+
| index | int(11)          | NO   | PRI | 0 |                |
| to_join    | int(11)          | NO   |     | 0          |                |
| <other fields, irrelevant>    |      |     ||                |
+------------+------------------+------+-----+------------+----------------+

… and a secondary table containing:

joined_table:
+------------+------------------+------+-----+------------+----------------+
| Field      | Type             | Null | Key | Default    | Extra          |
+------------+------------------+------+-----+------------+----------------+
| index      | int(11)          | NO   | PRI |            | auto_increment |
| int_list   | <list_of_ints>   | YES  |     | (empty)    |                |
| <other fields, irrelevant>    |      |     |            |                |
+------------+------------------+------+-----+------------+----------------+

We could suppose, here, as a simple test case, that main_table has five rows
where to_join contains respectively 1, 2, 3, 4, and 5,
and the joined_table has a single row with the single column int_list 
combining 2 and 4
in any form (but of course, this list could actually have any length).

I'd like to join these tables using a statement like:

SELECT
    <anything>
FROM main_table AS M
LEFT JOIN joined_table AS J
    ON M.to_join <some_operator to> J.int_list;

… in such a way that main_table would be joined to joined_table using 
any value
contained in int_list, and the statement is expected to give two rows as 
a result.

I tried to set int_list as a TINYTEXT containing "2,4" and use:

SELECT
    <anything>
FROM main_table AS M
LEFT JOIN joined_table AS J
    ON M.to_join IN (J.int_list);

… but this doesn't work, of course, my comma separated list is seen as a 
whole single value.

I also tried, still using TINYTEXT, the list as "[2][4]" and use:

SELECT
    <anything>
FROM main_table AS M
LEFT JOIN joined_table AS J
    ON J.int_listLIKE CONCAT('%[', M.to_join , ']%');

This works (more or less), but it is excruciatingly slow, especially 
knowing that main_table
can actually contain millions of rows, and joined_table several thousands.

Is there a better solution for doing that?

As the final goal implies, somewhere later, associating to_join in 
main_table, via int_list,
to the index auto increment primary key of the joined_table, using 
multiple rows there
is not an option.

Regards,

Gingko