SQL is not so much a language as a standard, that is supported and extended by each database vendor. what do you mean by not terribly expressive? It is an extremely explicit way of defining and manipulating sets of data.
I Use Oracle and I can say that it is an extremely powerful way of manipulating sets of data. when a statement is executed on an Oracle database, the optimiser chooses how the JOIN will work with the information it has at statement execution, whether that be a merge join or a hash join or something else, so I don't know what you mean by your first sentance.
Generally people who hate on SQL are OO programmers who are unable to wrap their heads around relational database set processing.
Further, saying "We don't program in Cobol anymore, so why the hell are we still using SQL?"
is perhaps equivalent to saying "We don't run around bashing eachother with swords and shields anymore, why the hell are we still planting food in the ground?"