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))
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.