Hi All, I am sorry if this is pretty basic,but it is intriguing me a bit. I saw a join written
like Inner Join table B on (1=1)
Why join like this should be written and under what scenario.Thanks in advance.
Joins return the rows from the first table linked to each row in the second where the join criteria are true.
1 = 1 is always true.
So this returns every row joined to every other row. aka a cross join:
create table t1 (
c1 int
);
create table t2 (
c1 int
);
insert into t1 values ( 1 );
insert into t1 values ( 2 );
insert into t2 values ( 1 );
insert into t2 values ( 2 );
select *
from t1
join t2
on ( 1 = 1 );
C1 C1
1 1
1 2
2 1
2 2
I can't think of a good reason for doing this*. If the intent is a cross join, then you should write that:
select *
from t1
cross join t2;
C1 C1
1 1
1 2
2 1
2 2
* It's possible this is part of dynamic SQL. When constructing join and where clauses, it's handy to start with a base, always true condition. This make it easier to tack on extra predicates. Without having to sort out the where/on/and to start:
stmt := 'select * from t1 join t2 on 1=1 ';
if ... then
stmt := stmt || ' and <some other test> ';
end if;
if ... then
stmt := stmt || ' and <yet another test> ';
end if;
Though join criteria are usually fixed. So it's rare this is useful in your join clauses.