Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 380
kee Avatar asked Feb 13 '16 01:02

kee


People also ask

How do I combine left and right joins?

The FULL JOIN keyword creates the result by combining the result of both a LEFT JOIN and a RIGHT JOIN . For any rows that aren't matching, the result will contain null values. This keyword is rarely used, but can be used to find duplicates, missing rows, or similar rows between two tables.

Can I join two select statements?

To combine two or more SELECT statements to form a single result table, use the set operators: UNION, EXCEPT or INTERSECT.

Which join is faster left or right?

If you dont include the items of the left joined table, in the select statement, the left join will be faster than the same query with inner join. If you do include the left joined table in the select statement, the inner join with the same query was equal or faster than the left join. Show activity on this post.


4 Answers

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
like image 200
Erwin Brandstetter Avatar answered Oct 05 '22 17:10

Erwin Brandstetter


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

like image 27
Buddy Yaussy Avatar answered Oct 05 '22 19:10

Buddy Yaussy


My answer illustrates on top of Erwin's answers with an example.

Suppose you have three tables A1, B1, & C1(empty)

A1 -

+-+
|a|
+-+
|2|
|1|
|3|
+-+

B1 -

+----+
|b   |
+----+
|a   |
|b   |
|c   |
|NULL|
+----+

C1 -

+----+
|c   |
+----+

When joining table A1 & B1, on 1=1 behaves same as CROSS JOIN.

select * from a1 left join b1 on 1=1;
select * from a1 cross join b1;

Result -

+-+----+
|a|b   |
+-+----+
|1|NULL|
|1|a   |
|1|b   |
|1|c   |
|2|NULL|
|2|a   |
|2|b   |
|2|c   |
|3|NULL|
|3|a   |
|3|b   |
|3|c   |
+-+----+

However, when we join A1 with C1, you get two different results

select * from a1 left join c1 on 1=1;

Result -

+-+----+
|a|c   |
+-+----+
|1|NULL|
|3|NULL|
|2|NULL|
+-+----+

For cross join -

select * from a1 cross join c1;

Result -

+-+-+
|a|c|
+-+-+
like image 33
Pirate X Avatar answered Oct 05 '22 18:10

Pirate X


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.

PS : Left join is not much useful here. Might as well just use inner join

like image 35
data_set Avatar answered Oct 05 '22 18:10

data_set