Basically "SQL" or relational algebra is a programming language. All the arguments are against weaknesses in the implementation, not problems with the language itself. NoSQL seems better, but does not implement the complete language only part of it. If I try and implement SQL or a relational programming language on top of "NoSQL" databases you will find there are features missing. Eventually NoSQL will need to implement the complete language and will become relationally complete, but the result will probably not be as elegant.
As for the join example, you compare a table join against a single lookup... not the same thing. How many seeks would objectivity need to find the phone number for the employers of all aunts (on the fathers side) of every person in the database (which is what a join is for)? And as for the virtual addresses, Oracle (for example) can use raw disk access to optimise the layout of data on the disk to minimise head movements when executing common queries. It can do this because relational algebra hides all details of the implementation from the user, allowing the database code more freedom to choose how it stores and processes the data).