Which is better: joins or several sub-select statements as part of a single query

Question

Performance wise, which is better?
Should I have 3 or 4 join statements in my query, or use the built-in select statements to pull the same information from my database as part of a single query?

Answer 1

I would say that connections are better because:

• They are easier to read.
• You have more control over whether you want to make an internal, left / right outer join or full outer join.
• Accession agreements cannot be abused so easily as to create an abomination of requests
• with joins, it’s easier for the query optimizer to create a quick query (if the internal selection is simple, it can work the same, but with more complex connections, the connections will work better).
• embedded select can only simulate left/right outer join .

Sometimes you cannot do things with the help of unions, in this case (and only after that) you will have to abandon the internal choice.

Answer 2

It rather depends on your database: especially on the size of the tables, as well as on the memory parameters, and sometimes even on how the tables are indexed.

Compared to current versions of MySQL, there was a real possibility that a query with a subsample would be much slower than a query that would return the same results structured using a join. (In MySQL 4.1 days, I saw that the difference is more than an order of magnitude). As a result, I prefer to build queries with joins.

However, there are several types of queries that are extremely difficult to build with a join, and a subselect is the only way to do this.

Answer 3

Most databases will optimize both of the queries below into the same plan, so do the following:

 select A.a1, B.b1 from A left outer join B on A.id = B.a_id 

or

 select A.a1, (select B.b1 from B where B.a_id = A.id) as b1 from A 

In the end, it's the same. However, in most cases, for nontrivial queries, it is better to stick to joins when possible, especially since some types of joins (for example, inner join) cannot be achieved using subselects.

Answer 4

Assuming the database engine is completely non-optimized, I would say that it depends on how consistently you need your data. If you execute multiple SELECT in a loaded database where the data you are looking for can change quickly, you may run into problems when your data does not match between the queries.

Assuming your data does not contain interdependencies, then multiple queries will work fine. However, if your data requires consistency, use one query.

This view boils down to making your data safe for transactions. Consider a situation where you need to deduce the total amount of all receivables, which are stored in a separate table from the amount of cash transactions. If someone had to add another transaction between your two requests, the amount of receivables will not correspond to the amount of transaction amounts.