Sql choose from a large number of identifiers

Question

I have a table, Foo. I run a query on Foo to get identifiers from a subset of Foo. Then I want to run a more complex set of queries, but only by these identifiers. Is there an effective way to do this? The best I can think of is to create a query, for example:

SELECT ... --complicated stuff
WHERE ... --more stuff
  AND id IN (1, 2, 3, 9, 413, 4324, ..., 939393)

That is, I am creating a huge sentence "IN". Is it effective? Is there a more efficient way to do this, or is it the only way to join an internal query that receives identifiers? If this helps, I use SQLObject to connect to the PostgreSQL database, and I have access to the cursor that executed the query to get all IDs.

UPDATE: I have to mention that more complex queries either rely on these identifiers or create more identifiers to search in other queries. If I made one big query, I would end up joining six tables at the same time or in a way that might be too slow.

Answer 1

One of the methods I've used in the past is to put the identifiers in a temporary table and then use them to control the sequence of queries. Sort of:

BEGIN;
CREATE TEMP TABLE search_result ON COMMIT DROP AS
  SELECT entity_id
  FROM entity /* long complicated search joins and conditions ... */;
-- Fetch primary entities
SELECT entity_id, entity.x /*, ... */
FROM entity JOIN search_result USING (entity_id);
-- Fetch some related entities
SELECT entity_id, related_entity_id, related_entity.x /*, ... */
FROM related_entity JOIN search_result USING (entity_id);
-- And more, as required
END;

, " ", , : ) N * M + 1 ) .

Answer 2

, VIEW. , ID. IN.

, IN , EXISTS.

Answer 3

, , . , , postgresql .

Answer 4

You will almost certainly be better off joining the connection, however another option is to use sub select, i.e.

SELECT ... --complicated stuff
WHERE ... --more stuff
  AND id IN (select distinct id from Foo where ...)