Sharding means you can't do "select * from a join b using (k)" unless you can prove the a record and the b record will always be hosted on the same shard. So of course you often have to write application code that knows where they are and goes and gets them separately. But that's precisely the problem that relational databases were developed to solve for you.

Then there's the whole distributed transaction thing, which is so painful that some people just live with wrong answers instead.