Using SELECT in the WHERE clause of another SELECT

Question

I have made a draft remote application on top of libpq for PostrgreSQL. It behaves well, but I have profiled the general functioning of the application. For each final business result that I produce, it happens that I call something like 40 select clause (over tcpip).

I have reminiscences from SQL-Server reminding me to minimize the number of interactions between my remote application and the database. Having analyzed my selects, I do think I could reduce this number to 3 SELECT clauses, using joins. But I don't remember the syntax for using the result of a SELECT in another SELECT.

E.g.:

SELECT * FROM individual
INNER JOIN publisher
ON individual.individual_id = publisher.individual_id
WHERE individual.individual_id = 'here I would like to use the results of a another select'

This other SELECT would be simply of the kind:

SELECT identifier FROM another_table WHERE something='something'

Here is the simplified tables layout, declined a number of times for different item_types ... (3 totally different types, hence the 3 SQL queries if optimized).

table passage
  id_passage PK
  business_field_passage bytea

table item
  id_item PK
  id_passage FK
  business_field_item text

table item_detail
  id_item_detail PK
  id_item FK
  business_field_item_detail text
  image_content bytea

There are several id_item for one id_passage.
There are several id_item_detail for one id_item.

How would you write that?
What is the name for describing the action of redirecting one select into another (if any)?

Angry Spartan · Accepted Answer · 2013-01-25T16:42:38.940

Is this what you're aiming for? Make sure the fields that are being compared are comparable (i.e. both fields are numeric, text, boolean, etc).

SELECT * FROM Individual
INNER JOIN Publisher
ON Individual.IndividualId = Publisher.IndividualId
WHERE Individual.IndividualId = (SELECT someID FROM table WHERE blahblahblah)

If you wish to select based on multiple values:

SELECT * FROM Individual
INNER JOIN Publisher
ON Individual.IndividualId = Publisher.IndividualId
WHERE Individual.IndividualId IN (SELECT someID FROM table WHERE blahblahblah)

Erwin Brandstetter · Answer 2 · 2023-01-16T22:28:09.327

You can just rewrite that as another JOIN. This is typically simplest and fastest:

SELECT i.*, p.*
FROM   individual    i
JOIN   publisher     p USING (individualid)
JOIN   another_table a ON a.identifier = i.individualid
WHERE  a.something = 'something';

Only equivalent if there is at most one match in another_table. (This is true in any case if a.identifier is the PK).

I also simplified somewhat and did away with the gratuitous CaMel-case spelling of identifiers.

Using SELECT in the WHERE clause of another SELECT

2 Answers2