The answer, as with most things, is ‘it depends’. 
In my particular application, the bom info for a production order comes from a file. Since my lookups are defined in a dictionary, I don’t have the round trip to the db. Until model year changes, it’s relatively unchanging.
That said, I also use dictionaries to manipulate datasets. You’ll notice I have a lot of such posts on here.
Joins on db tables are great and useful and are more efficient if they are in the same data source, but if you need to use datasets from two different sources, you’re hosed.
I find that db people will tend to do more with joins and stored procedures, and programmers will tend to do more through scripting.