I think that FathomDB's product is scalable to the degree that the existing database vendors are able to scale using multi-node clusters. Oracle's RAC tops out at 100 nodes. All of the other vendors (Teradata, Greenplum, etc) have some niche or secret sauce that allows them to scale vertical markets like OLAP. I don't see them scaling to Google or Facebook scale with an ACID-compliant relational database. They aren't really bringing anything new to the table other than a cheaper price point.
You bring up a very good point with the join issue. In addition, I'm wondering how they're going to scale writes. This is the bottleneck that eventually chokes RAC out, as it has to hold true to ACID principles. Even without ACID guarantees, eventually the system would spiral down into a chaotic quagmire of inconsistency as it scales. You've essentially got to have an locking and/or arbitration system that can quickly return an absolutely-positively we-wrote-this-in-a-consistent-way after a write query is executed. Nevermind trying to execute MVCC transactions over a cluster of nodes.
SQL can be scaled, ACID can't. Without ACID, the warm fuzzy feeling SQL gives you isn't quite as warm and fuzzy.