SQL -顺序或条件重要吗?

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.

我必须选择符合条件1或条件2的一行。然而,condition1是可取的。如果有两行，第一行满足条件1(不满足条件2)第二行满足条件2(不满足条件1)，那么第一行应该返回。

So for SQL:

所以对于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?:

它会先返回满足条件col1 = 1的行吗?还是以随机顺序返回行?如果它是随机的那么如何实现我想要的?我必须用这样的东西吗?

SELECT * FROM table WHERE col1 = 1 OR col2 = 5
ORDER BY (col1 = 1)::boolean DESC LIMIT 1

2 个解决方案

#1

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.

WHERE子句中条件的顺序对结果的优先级没有影响。你必须用一个订单一个条款来做。

#2

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.

在基于集合的语言(如SQL)中，条件不相关的顺序。没有顺序，你就会以任意的顺序返回行。您也不能依赖真正的随机结果，Postgres可以*地返回最方便的行，如@Matt评论的那样。

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.

对外部查询应用LIMIT。一旦找到足够的行来满足限制，Postgres就会停止对UNION ALL查询的更多分支进行评估。这样，只有当col1 = 1没有行时，才会计算col1 = 5的条件。你可以通过解释分析看到。不会再便宜了。

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:

注意，这不能与ORDER BY结合使用。然后，Postgres必须检索所有的行，以确定先进行哪种排序。更详细的相关回答:

Way to try multiple SELECTs till a result is available?
尝试多个选择直到结果可用的方法?

#1