SQL - does the order of the OR clauses really matter?
I have to select one line that matches condition1 OR condition2
. However, it condition1
is preferred. If there are two lines where the first meets condition1
(and does not match condition2) and the second matches condition2
(and does not match condition1
), then the first must be returned.
So for SQL:
SELECT * FROM table WHERE col1 = 1 OR col2 = 5 LIMIT 1
Will it first return a string that satisfies the condition col1 = 1
? Or will it return the lines in random order? If this is random, how do I achieve what I want? I have to use something like this:
SELECT * FROM table WHERE col1 = 1 OR col2 = 5
ORDER BY (col1 = 1)::boolean DESC LIMIT 1
source to share
The ordering of conditions WHERE
is irrelevant to a set-based language such as SQL.
Without, ORDER BY
you are returning strings in no particular order. You also cannot rely on truly random results, Postgres can return the most convenient lines like @Matt commented out .
However, there is a cheaper way to achieve what you want with : UNION ALL
SELECT * FROM table WHERE col1 = 1
UNION ALL
SELECT * FROM table WHERE col1 = 5
LIMIT 1;
LIMIT
applies to external request. Postgres stops evaluating more query tags UNION ALL
as soon as it finds enough rows to satisfy LIMIT 1
. Thus, the condition col1 = 5
will only be evaluated if col1 = 1
there is no row for. You can see this with EXPLAIN ANALYZE
. It won't be cheaper than that.
Please note that this will not work in combination with ORDER BY
. Postgres then needs to get all the lines to see which form first. Related answer with more details:
source to share