Going through a book, Learning SQL by Alan Beaulieu. On topic of inner joins, it tells that whatever be the order of tables in a INNER JOIN, results are same and gives reason as follows:
If you are confused about why all three versions of the account/employee/customer query yield the same results, keep in mind that SQL is a nonprocedural language, meaning that you describe what you want to retrieve and which database objects need to be involved, but it is up to the database server to determine how best to execute your query. Using statistics gathered from your database objects, the server must pick one of three tables as a starting point (the chosen table is thereafter known as the driving table), and then decide in which order to join the remaining tables. Therefore, the order in which tables appear in your from clause is not significant.
So does it imply that if statistics gathered from database objects change, then results would also change?
Advertisement
Answer
So does it imply that if statistics gathered from database objects change, then results would also change?
No. The same query will always produce the same results (provided, of course, that the underlying data is the same). What the author is explaining is that the database may choose a strategy or another to process the query (starting from one table or another, using a this or that algorithm to join the rows, and so on). That decision is made based on many factors, some of them being based on information that is available in the statistics.
The key point is that SQL is a declarative language, not a procedural language: you don’t get to chose how the database handles the query, you just tell it what result you want.
However, regardless of the algorithm that the database chooses, the result is guaranteed to be consistent.
Note that there are edge case where the database does not guarantee that results are the same for consecutive executions of the same query (like a query without a row limiting clause but without an order by): it’s the responsibility of the client to provide a query whose results are properly defined (the language does gives you enough rope to hang yourself, if you really want to).