I am reading an SQL query in Redshift and can't understand the last part:
我正在Redshift中读取SQL查询,无法理解最后一部分:
...
LEFT JOIN (SELECT MIN(modified) AS first_modified FROM user) ue
ON 1=1
What does ON 1=1 mean here?
ON 1 = 1是什么意思?
3 个解决方案
#1
3
It's simply doing a cross join, which selects all rows from the first table and all rows from the second table and shows as cartesian product, i.e. with all possibilities.
它只是进行交叉连接,它选择第一个表中的所有行和第二个表中的所有行,并显示为笛卡尔积,即具有所有可能性。
JOIN (LEFT, INNER, RIGHT, etc.) statements normally require an 'ON ..." condition. Putting in 1=1 is like saying "1=1 is always true, do don't eliminate anything".
JOIN(LEFT,INNER,RIGHT等)语句通常需要'ON ...'条件。放入1 = 1就像说“1 = 1总是如此,不要消除任何东西”。
You could rewrite the query as
您可以将查询重写为
... CROSS JOIN (SELECT MIN(modified) AS first_modified FROM user)
And get the same result
并获得相同的结果
#2
16
The intention is an unconditional LEFT JOIN, which is different from a CROSS JOIN in that all rows from the left table expression are returned, even if there is no match in the right table expression - while a CROSS JOIN drops such rows from the result. More on joins in the manual.
意图是无条件LEFT JOIN,它与CROSS JOIN的不同之处在于,即使右表表达式中没有匹配项,也会返回左表表达式中的所有行 - 而CROSS JOIN会从结果中删除此类行。有关手册中的连接的更多信息。
However:
1=1 is pointless in Postgres and all derivatives including Amazon Redshift. Just use true. This has probably been carried over from another RDBMS that does not support the boolean type properly.
1 = 1在Postgres和包括Amazon Redshift在内的所有衍生品都毫无意义。只需使用true。这可能是从另一个不正确支持布尔类型的RDBMS继承而来的。
... LEFT JOIN (SELECT ...) ue ON true
Then again, LEFT JOIN is pointless for this particular subquery with SELECT MIN(modified) FROM user on the right, because a SELECT with an aggregate function (min()) and no GROUP BY clause always returns exactly one row. This case (but not other cases where no row might be found) can be simplified to:
然后,LEFT JOIN对于右边的SELECT MIN(已修改)FROM用户的此特定子查询毫无意义,因为具有聚合函数(min())且没有GROUP BY子句的SELECT始终只返回一行。这种情况(但不是其他没有找到行的情况)可以简化为:
... CROSS JOIN (SELECT MIN(modified) AS first_modified FROM user) ue
#3
3
I believe its used to emulate cartesian join.
我相信它曾经模仿笛卡尔式的加入。
From your query, the least modified value (It will be just 1 element) will be assigned to all the records of the left table.
从您的查询中,将修改最少的值(它将只是1个元素)分配给左表的所有记录。
PS : Left join is not much useful here. Might as well just use inner join
PS:左连接在这里没什么用处。不妨使用内连接
#1
3
It's simply doing a cross join, which selects all rows from the first table and all rows from the second table and shows as cartesian product, i.e. with all possibilities.
它只是进行交叉连接,它选择第一个表中的所有行和第二个表中的所有行,并显示为笛卡尔积,即具有所有可能性。
JOIN (LEFT, INNER, RIGHT, etc.) statements normally require an 'ON ..." condition. Putting in 1=1 is like saying "1=1 is always true, do don't eliminate anything".
JOIN(LEFT,INNER,RIGHT等)语句通常需要'ON ...'条件。放入1 = 1就像说“1 = 1总是如此,不要消除任何东西”。
You could rewrite the query as
您可以将查询重写为
... CROSS JOIN (SELECT MIN(modified) AS first_modified FROM user)
And get the same result
并获得相同的结果
#2
16
The intention is an unconditional LEFT JOIN, which is different from a CROSS JOIN in that all rows from the left table expression are returned, even if there is no match in the right table expression - while a CROSS JOIN drops such rows from the result. More on joins in the manual.
意图是无条件LEFT JOIN,它与CROSS JOIN的不同之处在于,即使右表表达式中没有匹配项,也会返回左表表达式中的所有行 - 而CROSS JOIN会从结果中删除此类行。有关手册中的连接的更多信息。
However:
1=1 is pointless in Postgres and all derivatives including Amazon Redshift. Just use true. This has probably been carried over from another RDBMS that does not support the boolean type properly.
1 = 1在Postgres和包括Amazon Redshift在内的所有衍生品都毫无意义。只需使用true。这可能是从另一个不正确支持布尔类型的RDBMS继承而来的。
... LEFT JOIN (SELECT ...) ue ON true
Then again, LEFT JOIN is pointless for this particular subquery with SELECT MIN(modified) FROM user on the right, because a SELECT with an aggregate function (min()) and no GROUP BY clause always returns exactly one row. This case (but not other cases where no row might be found) can be simplified to:
然后,LEFT JOIN对于右边的SELECT MIN(已修改)FROM用户的此特定子查询毫无意义,因为具有聚合函数(min())且没有GROUP BY子句的SELECT始终只返回一行。这种情况(但不是其他没有找到行的情况)可以简化为:
... CROSS JOIN (SELECT MIN(modified) AS first_modified FROM user) ue
#3
3
I believe its used to emulate cartesian join.
我相信它曾经模仿笛卡尔式的加入。
From your query, the least modified value (It will be just 1 element) will be assigned to all the records of the left table.
从您的查询中,将修改最少的值(它将只是1个元素)分配给左表的所有记录。
PS : Left join is not much useful here. Might as well just use inner join
PS:左连接在这里没什么用处。不妨使用内连接