Using SELECT in the WHERE clause of another SELECT
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
SELECTclauses, using joins. But I don't remember the syntax for using the result of a
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'
SELECTwould 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
There are several
How would you write that?
What is the name for describing the action of redirecting one select into another (if any)?
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)
can it be that straight forward ?? does it still work if `SELECT someID FROM table WHERE blahblahblah` has multiple records ? I'm gonna check that right now.
Which query is selecting multiple records? It can work if you're selecting multiple records, but if you could show us your table layouts that would help us refine the answer.
You can just rewrite that as another
JOIN. This is normally 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'
I also simplified somewhat and did away with the gratuitous CamelCase spelling of identifiers.
@MarkStorey-Smith Do you mean that it's more than simpler and faster: this a standard of sql coding to use another `join` instead of a `in ( select...)` In such case I should also attribute the good answer to Erwin.
@StephaneRolland Whether its faster or not will be platform and version dependent. SQL Server 2008+ for example will generate identical execution plans for INNER JOIN and IN (SELECT ...) syntax. No idea on whether same applies to PostgreSql. Performance aside, the IN (SELECT ...) style leaves me wondering if the author has fully grasped the semantics and concepts of SQL. AngrySpartan has answered your original question correctly. ErwinBrandstetter has shown you the way you *should* do it :).