Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, AB.

Asked: November 11, 2018 - 4:12 pm UTC

Last updated: November 12, 2018 - 10:19 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

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.

and Chris said...

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.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Nice explaination

A reader, November 12, 2018 - 12:39 pm UTC

Thanks Chris for the elaborate and clear explanation.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.