But a list comprehension is a declarative construct, which can be best appreciated when porting some list comprehensions into loops. Especially nested comprehensions.

Totally meaningful difference! With the list comprehension, you're still telling the machine how to go about getting the data; there is an explicit loop construct. With SQL, I'm simply declaring what results I want, and the implementation is left to the execution engine.

For instance, the SQL query can be parallelized, but not so with the Python list comprehension. If you wanted to create a version that could be run in parallel in Python, you'd have to do it with a map()/filter() construct. Ignoring readability for a sec (pretend it's nice and elegant, like it would be in e.g. Clojure), you are still specifying how the machine should accomplish the goal, not the goal itself.

    filter(lambda x: x is not None, map(lambda u: u.first_name if u.is_active else None, table_users))