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

sql postgresql join left-join amazon-redshift
kee picture kee · Feb 13, 2016 · Viewed 42.7k times · Source

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

Erwin Brandstetter picture Erwin Brandstetter · Feb 13, 2016

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

Related questions

Multiple left joins on multiple tables in one query

I've got one master table, which has items stored in multiple levels, parents and childs, and there is a second table which may or may not have additional data. I need to query two levels from my master table and …

Read More
Deleting using LEFT JOIN

I want to delete from a table depending on data that exists on another table that references the first, however, I have the code that works and shows the value to be deleted when I run it as a SELECT …

Read More
Join table twice - on two different columns of the same table

I have a very confusing database with a table that holds two values I need in a separate table. Here is my issue: Table1 - id Table2 - id - table1_id - table3_id_1 - table3_id_2 Table3 - …

Read More