outer join with row archival

View All Public Scripts Login to Run Script

Script Name outer join with row archival
Visibility Unlisted - anyone with the share link can access
Description there is an example with false result, if you using ora_archival_state and left/right joins
Area Oracle 12c
Contributor evgenij.gorelin@gmail.com
Created Thursday September 27, 2018

Statement 1

create table test_table (id number(10),name varchar2(10)) row archival

Table created.

Statement 2

insert into test_table (id,name) values (1,'name1')

1 row(s) inserted.

Statement 3

insert into test_table (id,name) values (2,'name2')

1 row(s) inserted.

Statement 4

insert into test_table (id,name) values (3,'name3')

1 row(s) inserted.

Statement 5

insert into test_table (id,name) values (4,'name4')

1 row(s) inserted.

Statement 6

select * from test_table


ID NAME
1 name1
2 name2
3 name3
4 name4

4 rows selected.

Statement 7

update test_table set ora_archive_state = 1 where id = 1

1 row(s) updated.

Statement 9

select * from test_table where id = 1

no data found

Statement 10

select * from test_table t left join test_table t1 on t.id = t1.id and t.id = 1


ID NAME ID NAME
4 name4  -  - 
1 name1  -  - 
3 name3  -  - 
2 name2  -  - 

4 rows selected.

Statement 11

select * from test_table t left join test_table t1 on (t.id = t1.id) where t.id = 1


ID NAME ID NAME
1 name1  -  -

ID	NAME	ID	NAME
1	name1	-	-

Statement 12

select * from test_table t join test_table t1 on t.id = t1.id and t.id = 1

no data found

Additional Information