I'd say using databases and SQL is one way to do the job, but it's not necessarily.
While it is true that the built-in Data Grid manipulation functions are sometimes limited, on our projects we do sometimes develop DS2 or Groovy "SAS processes" that cover for the missing functionality not yet available with built-in functions.
Basically, the DS2 or Groovy process can take two (or more) data grids as an input and return one as an output. Inside, you do some basic programming with cycles, hash tables e.t.c. to implement whatever processing you need (Left Join for instance). Then on a diagram, you just place the node wherever you need to do the processing and refer to its output instead of a function call.
-------
Dmitriy Alergant, Tier One Analytics