That's just because SQL clients present their results that way, AFAICS. If you use a sub-query like in, say,
select * from orders where custno = (select custno from customers where name = 'John Doe');
you'll get the same result as if you'd put that scalar in your query, like
select * from orders where custno = 123456; -- John Doe's customer number
Or maybe you're right, that to SQL databases scalar values are single-row single-column tables. But so what? In mathematics, isn't any number also the single-member set of numbers that contains only that number? Where's the harm in that? (And, hey, RDBMSes are founded on set theory...)
So I don't really see what the big problem is either way. Hoping I'm not being stupid AF, maybe you could explain further?
Mathematically, they're equivalently expressive. But returning everything as a table has bad ergonomics.
Imagine the programming language equivalent. We could make a programming language where every function call returns a table. If you expect 1 return value from your function, the caller grabs the first row out of the return array, and the first column out of that row. It would absolutely work, and that its mathematically equivalent in some sense. But it would be confusing, computationally inefficient and error prone. What happens if there's more than 1 row in the table? Or more than 1 column? What happens if the type of the columns doesn't match match what you expect? What happens if the table is empty? Or you want a function which returns a two lists instead of one? We could write that programming language. But it would be pretty weird and frustrating to use.
This is the situation today with SQL. Every query returns a dynamically typed table. Its up to the caller to parse that table.
With redis, the caller expresses to the database what kind of value they expect in the query function name. (At least, list or scalar). The database guarantees that a GET request always returns a scalar value, and LRANGE always returns a list. I think this has better ergonomics because the types are more explicit.
Are they??? Not as I understand it.