Below is query has 3 filters in where condition, which filter is applied first on table ? does filters applied on table in the order right to left written in query.
I want product_key to be filtered out first and then remaining filters.
select *
from order_history
where product_key in (select product_key from products
where status = 'avilable' )
and province = 'Texas'
and category_key in (3,4,5)
which filter is applied first on table ? does filters applied on table in the order right to left written in query.
Since "AND" is transitive, the order has no meaning. This is like saying "1+2" is the same as "2+1". "x=1 and y=2" is equivalent to "y=2 and x=1". This is different from C/C++ where operator && is not transitive: "if( pointer && *pointer )" tests if the pointer is null first, which is what you want, and is completely different from "if( *pointer && pointer )" which will crash if the pointer is null.
The optimizer will try to apply the conditions in the order that it thinks will give best performance. This usually means picking the most selective indexable condition first. For example, if "province='Texas'" only selects 1% of the table, and there is an index on it, then it's a big win to use that first and only apply the rest of the filter conditions to the 1% of rows that passed the first filter. If table statistics are up to date (run VACUUM ANALYZE) the optimizer usually comes up with pretty good choices.