RethinkDB handles joins just fine :)

As does couchbase. :-) Personally I like map-reduces.

So does the multi-model database ArangoDB. https://docs.arangodb.com/3.2/AQL/Examples/Join.html

And some NoSQL databases speak SQL as well - without being relational.

I like the JSON support in PostgreSQL a lot. Very easy to deal with unstructured JSON data while still using common attributes in a relational format. But there are more cases that one might think about - as a relational guy - that benefit from graph databases, document stores or optimized time-series databases.