CODEWITHSUNDEEP
sqlpostgresqlsql-order-byunion-all
user606521
user606521

Reputation: 15434

SQL - does order of OR conditions matter?

I have to SELECT one row which meets condition1 OR condition2. However, condition1 is preferable. If there are two rows, where the first one meets condition1 (and does not meet condition2) and second meets condition2 (and does not meet condition1) then the first one should be returned.

So for SQL:

SELECT * FROM table WHERE col1 = 1 OR col2 = 5 LIMIT 1

Will it return the row that meets condition col1 = 1 first? Or will it return rows in random order? If it will be random then how to 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

Upvotes: 3

Views: 1696

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656576

The order of WHERE conditions is irrelevant in a set-based language like SQL.
Without ORDER BY you get back rows in arbitrary order. You cannot rely on truly random results either, Postgres is free to return the most convenient row(s), like @Matt commented.

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 is applied to the outer query. Postgres stops evaluating more legs of the UNION ALL query as soon as it has found enough rows to satisfy the LIMIT 1. This way, the condition col1 = 5 will only be evaluated if there is no row for col1 = 1. You can see that with EXPLAIN ANALYZE. Won't get cheaper than that.

Note that this won't work in combination with ORDER BY. Then Postgres has to retrieve all rows to find out which sort first. Related answer with more details:

Upvotes: 3

Tab Alleman
Tab Alleman

Reputation: 31775

The order of the conditions in the WHERE clause does nothing to affect the priority of the results. You have to do that with an ORDER BY clause.

Upvotes: 4

Related Questions