I am joining a large table in postgresql to a table with 1 row in it. Yes, I know I could just take the values from this single row table and put them in my query written out, but there's 210 columns.
我正在将postgresql中的一个大表连接到一个包含1行的表中。是的,我知道我可以从这个单行表中获取值并将它们写入我的查询中,但是有210列。
So my question is this: should I join up the single row table to everything using a cross join or using a regular join on a tautology (1 = 1 or something). Is either of those ways bound to be slower?
所以我的问题是这样的:我应该将单行表连接到使用交叉连接的所有内容,还是在重言式上使用常规连接(1 = 1或其他)。这些方式中的任何一种都必然会变慢吗?
Or is there a third faster way?
还是有第三种更快的方式?
2 个解决方案
#1
4
The reason 1=1 exists is to make it easier to create dynamic sql statements by concatenating strings together (with the usual safeguards like parameterization, of course).
存在1 = 1的原因是通过将字符串连接在一起(通常使用参数化等常用安全措施)来创建动态sql语句更容易。
Having a predefined WHERE clause with 1=1 in it allows additional WHERE conditions to be added to the SQL without having to check for the existence of a WHERE clause first, and the SQL engine will generally optimize out the 1=1 so there's no performance difference.
具有1 = 1的预定义WHERE子句允许将其他WHERE条件添加到SQL,而不必首先检查是否存在WHERE子句,并且SQL引擎通常会优化1 = 1,因此没有性能区别。
In any other context, 1=1 is generally harmless, but not particularly useful.
在任何其他情况下,1 = 1通常是无害的,但不是特别有用。
#2
3
One thing to note is a cross join will result in an empty table if one of the tables is empty. If one of your tables might be empty and you still want records, you may want an outer join (e.g. left, right, or full) on 1=1.
需要注意的一点是,如果其中一个表为空,则交叉连接将导致空表。如果您的某个表可能为空并且您仍然需要记录,则可能需要1 = 1的外部联接(例如,左,右或完整)。
#1
4
The reason 1=1 exists is to make it easier to create dynamic sql statements by concatenating strings together (with the usual safeguards like parameterization, of course).
存在1 = 1的原因是通过将字符串连接在一起(通常使用参数化等常用安全措施)来创建动态sql语句更容易。
Having a predefined WHERE clause with 1=1 in it allows additional WHERE conditions to be added to the SQL without having to check for the existence of a WHERE clause first, and the SQL engine will generally optimize out the 1=1 so there's no performance difference.
具有1 = 1的预定义WHERE子句允许将其他WHERE条件添加到SQL,而不必首先检查是否存在WHERE子句,并且SQL引擎通常会优化1 = 1,因此没有性能区别。
In any other context, 1=1 is generally harmless, but not particularly useful.
在任何其他情况下,1 = 1通常是无害的,但不是特别有用。
#2
3
One thing to note is a cross join will result in an empty table if one of the tables is empty. If one of your tables might be empty and you still want records, you may want an outer join (e.g. left, right, or full) on 1=1.
需要注意的一点是,如果其中一个表为空,则交叉连接将导致空表。如果您的某个表可能为空并且您仍然需要记录,则可能需要1 = 1的外部联接(例如,左,右或完整)。