SQL - does order of OR conditions matter?

Question

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

Without an ORDER BY, the rows will come back in an arbitrary order, though it's unlikely to be *random*. It's normally related to data storage or indexes used. This can be a little dangerous, as it may *look* like the data always comes back sorted the same way without an ORDER BY, but it's never guaranteed without one. The order without an ORDER BY is unlikely to be affected by the position of OR operands in a WHERE clause. — Matt Gibson, Aug 04 '14 at 15:48

score 4 · Accepted Answer · answered Aug 04 '14 at 15:42

4

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.

answered Aug 04 '14 at 15:42

Tab Alleman

31,483
7
36
52

1

Yup. See http://www.postgresql.org/docs/8.1/static/queries-limit.html for an explanation of why it's generally a good idea to use an ORDER BY of some kind with LIMIT, anyway. – Matt Gibson Aug 04 '14 at 15:46
1

@MattGibson: If you don't want to address a specific version, please link to the `/current` manual. Version 8.1 is ancient. So: http://www.postgresql.org/docs/current/interactive/queries-limit.html. [Details here.](http://meta.stackexchange.com/questions/108714/best-way-to-reference-the-postgresql-manual) – Erwin Brandstetter Aug 04 '14 at 15:59
@ErwinBrandstetter Thanks. I just went for the top hit on Google for postgresql limit, not being a PostgreSQL aficionado. Is there even a link on the outdated pages that will yield the /current manual link? – Matt Gibson Aug 04 '14 at 16:02
@MattGibson: `/current` is a meta-link that always links to the current version. So you get to the same place if you click on the link for Postgres 9.3 - currently. – Erwin Brandstetter Aug 04 '14 at 16:07

score 3 · Answer 2 · edited May 23 '17 at 12:22

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:

Way to try multiple SELECTs till a result is available?

That's a very smart solution. – Matt Gibson Aug 04 '14 at 16:08 — Matt Gibson, Aug 04 '14 at 16:08

SQL - does order of OR conditions matter?

2 Answers2

Linked