Karmen Sali Karmen Sali - 1 month ago 6
SQL Question

SQL Queries Processing order is not consistent

Having two tables

products
and
product_types
, both have the following rows :

products


enter image description here

product_types


enter image description here

Executing the following query :

SELECT p.name, pt.name
FROM products p, product_types pt
WHERE p.product_type_id = 1;


yields the following result :

enter image description here

But executing this query :

SELECT p.name, pt.name
FROM products p, product_types pt
WHERE p.product_type_id = pt.product_type_id
OR p.product_type_id = 1;


yields the following result :

enter image description here

Normally, the SQL interpreter starts with one table and spans the other, what's the rule for that ? In the 1st query, it started with the
products
table and spanned
product_types
, but in the 2nd query, it started with the
product_types
table and spanned the
products
table (as can be seen from the order). Why is it not consistent, normally, I'd expect the result of the 2nd query to be something like this :

enter image description here

Why ? Does it choose the table to start with randomly or what ?

NOTE: Don't mind the queries, it may not make much sense, I'm only interested in the order of the results.

Answer

Does it choose the table to start with randomly or what ?

It may appear random, but it's not. It's just that the query optimizer is free to consider different ways of executing the query, and to pick the execution plan it feels will return the expected results (as defined by the query) in the most optimal way.

Even the slightest change to the definition of a query may cause the optimizer to consider a different execution plan to be more efficient. And even when executing the same query multiple times, it may pick different execution plans at different times if, for instance, the distribution of the data in the different tables involved has changed (and that's just one of many possible reasons).

The fact that it can pick whatever execution plan is optimal in a given situation will, of course, impact the order of the rows that are returned. And that is well within the rights of what the query optimizer can do, because you did not request an explicit result order by including an ORDER BY clause in your query. The only way that you can guarantee consistent ordering of results is by explicitly requesting that order via an ORDER BY clause. Don't let anyone tell you otherwise, they're lying.