What's better: joins or multiple sub-select statements as part of one query

Performance wise, what is better?

If I have 3 or 4 join statements in my query or use emb开发者_JAVA技巧edded select statements to pull the same information from my database as part of one query?

I would say joins are better because:

1. They are easier to read.
2. You have more control over whether you want to do an inner, left/right outer join or full outer join
3. join statements cannot be so easily abused to create query abominations
4. with joins it is easier for the query optimizer to create a fast query (if the inner select is simple, it might work out the same, but with more complicated stuff joins will work better).
5. embedded select's can only simulate left/right outer join.

Sometimes you cannot do stuff using joins, in that case (and only then) you'll have to fall back on an inner select.

It rather depends on your database: sizes of tables particularly, but also the memory parameters and sometimes even how the tables are indexed.

On less than current versions of MySQL, there was a real possibility of a query with a sub-select being considerably slower than a query that would return the same results structured with a join. (In the MySQL 4.1 days, I have seen the difference to be greater than an order of magnitude.) As a result, I prefer to build queries with joins.

That said, there are some types of queries that are extremely difficult to build with a join and a sub-select is the only way to really do it.

Assuming the database engine does absolutely no optimization, I would say it depends on how consistent you need your data to be. If you're doing multiple SELECT statements on a busy database, where the data you are looking at may change rapidly, you may run into issues where your data does not match up, between queries.

Assuming your data contains no inter-dependencies, then multiple queries will work fine. However, if your data requires consistency, use a single query.

This viewpoint boils down to keeping your data transactionally safe. Consider the situation where you have to pull a total of all accounts receivable, which is kept in a separate table from the monetary transaction amounts. If someone were to add another transaction in between your two queries, the accounts receivable total would not match the sum of the transaction amounts.

Most databases will optimize both queries below into the same plan, so whether you do:

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

It ends up being the same. However, in most cases for non-trivial queries you'd better stick with joins whenever you can, especially since some types of joins (such as an inner join) are not possible to achieve using sub-selects.