JOIN (SELECT … ) ue ON 1=1?

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?

Answer

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