John Davidson

Message:

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?

---

My Answer:
You can achieve this by creating a temporary table in MySQL and then performing a LEFT JOIN with that temporary table. Here's how you can do it:

1. Create a temporary table with the list of IDs:

sql
CREATE TEMPORARY TABLE temp_ids (id INT);
INSERT INTO temp_ids (id) VALUES (1), (2), (3), ...; -- Add all your IDs here


2. Perform a LEFT JOIN with the temporary table:

sql
SELECT t1.*, t2.*
FROM your_main_table t1
LEFT JOIN temp_ids t2 ON t1.id = t2.id;


3. Drop the temporary table after you're done with the query:

sql
DROP TEMPORARY TABLE temp_ids;


This way, you can perform a LEFT JOIN with a list of IDs in MySQL without losing any IDs that have no match in the table. This should also help improve the performance of your query compared to doing it in code.