MYSQL: JOIN (SELECT … ) ue ON 1=1 的含义

Question

I am reading an SQL query in Redshift and can’t understand the last part:

...
LEFT JOIN (SELECT MIN(modified) AS first_modified FROM user) ue
ON 1=1

What does ON 1=1 mean here?

It just ensures the join will return a match – 1=1 is the same as true. Given the subquery, it will only return a single row – min(modified). That value will be combined to the other joins. Almost acts like a cross join, but with only a single value. – sgeddes Feb 13 '16 at 1:11

Answer

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.

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.

... 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:

... CROSS JOIN (SELECT MIN(modified) AS first_modified FROM user) ue

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！