To understand what a query does, I have always thought the following is the procedure on evaluating a query:
Form the table as specified in the FROM clause.
Pick the rows from that table as specified in the WHERE clause.
Show the columns of the resulting rows, as specified in the SELECT clause.
But the problem is; all around, I see that SQL is a declarative language. That is, there is no guarantee how a particular query will be executed. Given that, my question is:
Is reasoning about the result of a query in the way I do wrong? If so, how am I supposed to reason about the result of a query? In other words, if I am not able to reason in a step by step basis, how am I supposed to predict what will the result of a particular query be?
Thing is, although this is not a big problem in trivial queries, as queries gets more complex, such as by incorporating various correlated subqueries, I can't see how am I supposed to understand what a query does, without thinking about the query in a step-by-step execution. So, I would like to learn how to reason about SQL queries.