Geek Answers Handbook

JOIN (SELECT ...) ue ON 1 = 1?

I am reading a SQL query in Redshift and cannot understand the last part:

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

What does ON 1=1 mean?

+19
sql join left-join postgresql amazon-redshift
source share
3 answers

It simply cross-connects, which selects all rows from the first table and all rows from the second table and displays them as a Cartesian product, i.e. with all the features.

JOIN operators (LEFT, INTERNAL, RIGHT, etc.) Usually require the condition "ON ...". Entering 1 = 1 is the same as saying "1 = 1 is always true, do not eliminate anything."

+8
source share

The intent is an unconditional LEFT JOIN , which differs from CROSS JOIN in that all rows from the left table expression are returned, even if there are no matches in the right table expression, while CROSS JOIN discards such rows from the result. More about connections in the manual.

Nevertheless:

1=1 pointless in Postgres and all derivatives , including Amazon Redshift. Just use true . This was probably ported from another DBMS that does not properly support the boolean type.

 ... LEFT JOIN (SELECT ...) ue ON true

On the other hand, LEFT JOIN does not make sense for this particular subquery with SELECT MIN(modified) FROM user on the right, because SELECT with the aggregate function ( min() ) and without the GROUP BY always returns exactly one row. This case (but not other cases where a string cannot be found ) can be simplified to:

 ... CROSS JOIN (SELECT MIN(modified) AS first_modified FROM user) ue
+25
source share

I believe that it was used to imitate the Cartesian conjunction.

From your query, the least modified value ( It will be just one element ) will be assigned to all entries in the left table.

PS : left connection here is not very useful. It is also possible to simply use an internal join.

+4
source share

More articles:


All Articles