CODEWITHSUNDEEP
phpmysql
Ted
Ted

Reputation: 3875

MySQL left join with given ids

So I need to left join a table from MySQL with a couple of thousands of ids.

It’s like I need to temporarily build a table for the join then delete it, but that just sounds not right.

Currently the task is done by code but proves pretty slow on the results, and an sql query might be faster.

My thought was to use ...WHERE ID IN (“.$string_of_values.”);

But that cuts off the ids that have no match on the table.

So, how is it possible to tell MySQL to LEFT JOIN a table with a list of ids?

Upvotes: 0

Views: 890

Answers (1)

Akina
Akina

Reputation: 42632

As I understand your task you need to leftjoin your working table to your ids, i.e. the output must contain all these ids even there is no matched row in working table. Am I correct?

If so then you must convert your ids list to the rowset.

You already tries to save them to the table. This is useful and safe practice. The additional points are:

  1. If your dataset is once-used and may be dropped immediately after final query execution then you may create this table as TEEMPORARY. Than you may do not care of this table - it wil be deleted automatically when the connection is closed, but it may be reused (including its data edition) in this connection until it is closed. Of course the queries which creates and fills this table and final query must be executed in the same connection in that case.
  2. If your dataset is small enough (approximately - not more than few megabytes) then you may create this table with the option ENGINE = Memory. In this case only table definition file (small text file) will be really written to the disk whereas the table body will be stored in the memory only, so the access to it will be fast enough.
  3. You may create one or more indexes in such table and improve final query performance.

All these options may be combined.

Another option is to create such rowset dynamically.

In MySQL 5.x the only option is to create such rowset in according subquery. Like

SELECT ...
FROM ( SELECT 1 AS id UNION SELECT 2 UNIO SELECT 22 ... ) AS ids_rowset
LEFT JOIN {working tables}
...

In MySQL 8+ you have additional options.

You may do the same but use CTE:

WITH ids_rowset AS ( SELECT 1 AS id UNION SELECT 2 UNIO SELECT 22 ... )
SELECT ...
FROM ids_rowset
LEFT JOIN {working tables}
...

Alternatively you may transfer your ids list in some serialized form and parse it to the rowset in the query (in recursive CTE, or by using some table function, for example, JSON_TABLE).

All these methods creates once-used rowset (of course, CTE can be reused within the query). And this rowset cannot be indexed for query improvement (server may index this dataset during query execution if it finds this reasonable but you cannot affect this).

Upvotes: 1

Related Questions