Note This is deliberately database agnostic, I'm interested in how different implementations, er, differ.
I came across Joins are for lazy people on SO, and it's made me question an assumption I'd made.
Joining in the app server can be more efficient if joining on the database causes severe redundancy in the result set sent over the network. Consider tables A and B, where each row in A is associated with 20 rows in B, B has only 100 rows, and we want to fetch the first 1000 rows from A with associated rows from B. Joining in the database will result in 20 * 1000 tuples sent across the network. If the join is done in the app server (first fetching the entire B table into memory), a mere 100 + 1000 rows are sent across the network.
I kinda assumed that a cross-join would be optimised in the client library, so that less data was sent over the network, and then merged, so that the app didn't need to glue the data together. Am I plain wrong, or does it vary depending on the database?