create table table1 (rownum1 int, c1 char, primary key (rownum1));
create table table2 (rownum2 int, c2 char, primary key (rownum2));
insert into table1(rownum1,c1) values(1,'a'); insert into table1(rownum1,c1) values(2,'a'); insert into table1(rownum1,c1) values(3,'b'); insert into table1(rownum1,c1) values(4,'c'); insert into table1(rownum1,c1) values(5,'c'); insert into table1(rownum1,c1) values(6,'d'); COMMIT;
insert into table2(rownum2,c2) values(1,'a'); insert into table2(rownum2,c2) values(2,'b'); insert into table2(rownum2,c2) values(3,'b'); insert into table2(rownum2,c2) values(4,'e'); insert into table2(rownum2,c2) values(5,'e'); insert into table2(rownum2,c2) values(6,'f'); COMMIT;
Table1
"a" "a" "b" "c" "c" "d" |
Table2
"a" "b" "b" "e" "e" "f" |
Table1 | Table1 / Table2 | Table2 |
![]() |
select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1 inner join table2 t2 on t1.c1=t2.c2 order by t1.rownum1,t2.rownum2
for (rownum1,c1) in table1 for (rownum2,c2) in table2 if c1==c2 print rownum1,c1,rownum2,c2
rownum1 | c1 | rownum2 | c2 |
---|---|---|---|
1 | "a" | 1 | "a" |
2 | "a" | 1 | "a" |
3 | "b" | 2 | "b" |
3 | "b" | 3 | "b" |
Table1 | Table1 / Table2 | Table2 |
![]() |
select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1 left outer join table2 t2 on t1.c1=t2.c2 order by t1.rownum1,t2.rownum2
for (rownum1,c1) in table1 flag=not-found for (rownum2,c2) in table2 if c1==c2 { print rownum1,c1,rownum2,c2 flag=found } if flag==not-found print rownum1,c1,(null),(null)
rownum1 | c1 | rownum2 | c2 |
---|---|---|---|
1 | "a" | 1 | "a" |
2 | "a" | 1 | "a" |
3 | "b" | 2 | "b" |
3 | "b" | 3 | "b" |
4 | "c" | (null) | (null) |
5 | "c" | (null) | (null) |
6 | "d" | (null) | (null) |
Table1 | Table1 / Table2 | Table2 |
![]() |
select t1.rownum1,t1.c1 from table1 t1 left outer join table2 t2 on t1.c1=t2.c2 where t2.rownum2 is null order by t1.rownum1
for (rownum1,c1) in table1 flag=not-found for c2 in table2 if c1==c2 { flag=found break } if flag==not-found print rownum1,c1
rownum1 | c1 |
---|---|
4 | "c" |
5 | "c" |
6 | "d" |
Table1 | Table1 / Table2 | Table2 |
![]() |
select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1 right outer join table2 t2 on t1.c1=t2.c2 order by t1.rownum1,t2.rownum2
for (rownum2,c2) in table2 flag=not-found for (rownum1,c1) in table1 if c1==c2 { print rownum1,c1,rownum2,c2 flag=found } if flag==not-found print (null),(null),rownum2,c2
rownum1 | c1 | rownum2 | c2 |
---|---|---|---|
1 | "a" | 1 | "a" |
2 | "a" | 1 | "a" |
3 | "b" | 2 | "b" |
3 | "b" | 3 | "b" |
(null) | (null) | 4 | "e" |
(null) | (null) | 5 | "e" |
(null) | (null) | 6 | "f" |
Table1 | Table1 / Table2 | Table2 |
![]() |
select t2.rownum2,t2.c2 from table1 t1 right outer join table2 t2 on t1.c1=t2.c2 where t1.rownum1 is null order by t2.rownum2
for (rownum2,c2) in table2 flag=not-found for c1 in table1 if c1==c2 { flag=found break } if flag==not-found print rownum2,c2
rownum2 | c2 |
---|---|
4 | "e" |
5 | "e" |
6 | "f" |
Table1 | Table1 / Table2 | Table2 |
![]() |
select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1 full outer join table2 t2 on t1.c1=t2.c2 order by t1.rownum1,t2.rownum2
select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1 left outer join table2 t2 on t1.c1=t2.c2 union select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1 right outer join table2 t2 on t1.c1=t2.c2
for (rownum1,c1) in table1 flag=not-found for (rownum2,c2) in table2 if c1==c2 { print rownum1,c1,rownum2,c2 flag=found } if flag==not-found print rownum1,c1,(null),(null) for (rownum2,c2) in table2 flag=not-found for c1 in table1 if c1==c2 { flag=found break } if flag==not-found print (null),(null),rownum2,c2
rownum1 | c1 | rownum2 | c2 |
---|---|---|---|
1 | "a" | 1 | "a" |
2 | "a" | 1 | "a" |
3 | "b" | 2 | "b" |
3 | "b" | 3 | "b" |
4 | "c" | (null) | (null) |
5 | "c" | (null) | (null) |
6 | "d" | (null) | (null) |
(null) | (null) | 4 | "e" |
(null) | (null) | 5 | "e" |
(null) | (null) | 6 | "f" |
Table1 | Table1 / Table2 | Table2 |
![]() |
select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1 full outer join table2 t2 on t1.c1=t2.c2 where t1.rownum1 is null or t2.rownum2 is null order by t1.rownum1,t2.rownum2
select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1 left outer join table2 t2 on t1.c1=t2.c2 where t2.rownum2 is null union select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1 right outer join table2 t2 on t1.c1=t2.c2 where t1.rownum1 is null
for (rownum1,c1) in table1 flag=not-found for c2 in table2 if c1==c2 { flag=found break } if flag==not-found print rownum1,c1,(null),(null) for (rownum2,c2) in table2 flag=not-found for c1 in table1 if c1==c2 { flag=found break } if flag==not-found print (null),(null),rownum2,c2
rownum1 | c1 | rownum2 | c2 |
---|---|---|---|
4 | "c" | (null) | (null) |
5 | "c" | (null) | (null) |
6 | "d" | (null) | (null) |
(null) | (null) | 4 | "e" |
(null) | (null) | 5 | "e" |
(null) | (null) | 6 | "f" |
Table1 | Table1 / Table2 | Table2 |
![]() |
select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1,table2 t2 order by t1.rownum1,t2.rownum2
for (rownum1,c1) in table1 for (rownum2,c2) in table2 print rownum1,c1,rownum2,c2
rownum1 | c1 | rownum2 | c2 |
---|---|---|---|
1 | "a" | 1 | "a" |
1 | "a" | 2 | "b" |
1 | "a" | 3 | "b" |
1 | "a" | 4 | "e" |
1 | "a" | 5 | "e" |
1 | "a" | 6 | "f" |
2 | "a" | 1 | "a" |
2 | "a" | 2 | "b" |
2 | "a" | 3 | "b" |
2 | "a" | 4 | "e" |
2 | "a" | 5 | "e" |
2 | "a" | 6 | "f" |
3 | "b" | 1 | "a" |
3 | "b" | 2 | "b" |
3 | "b" | 3 | "b" |
3 | "b" | 4 | "e" |
3 | "b" | 5 | "e" |
3 | "b" | 6 | "f" |
4 | "c" | 1 | "a" |
4 | "c" | 2 | "b" |
4 | "c" | 3 | "b" |
4 | "c" | 4 | "e" |
4 | "c" | 5 | "e" |
4 | "c" | 6 | "f" |
5 | "c" | 1 | "a" |
5 | "c" | 2 | "b" |
5 | "c" | 3 | "b" |
5 | "c" | 4 | "e" |
5 | "c" | 5 | "e" |
5 | "c" | 6 | "f" |
6 | "d" | 1 | "a" |
6 | "d" | 2 | "b" |
6 | "d" | 3 | "b" |
6 | "d" | 4 | "e" |
6 | "d" | 5 | "e" |
6 | "d" | 6 | "f" |
Table1 | Table1 / Table2 | Table2 |
![]() |
select t1.rownum1,t1.c1 from table1 t1 where t1.c1 in ( select t2.c2 from table2 t2 ) order by t1.rownum1
select t1.rownum1,t1.c1 from table1 t1 where exists ( select 1 from table2 t2 where t2.c2=t1.c1 ) order by t1.rownum1
for (rownum1,c1) in table1 for c2 in table2 if c1==c2 { print rownum1,c1 break }
rownum1 | c1 |
---|---|
1 | "a" |
2 | "a" |
3 | "b" |
Table1 | Table1 / Table2 | Table2 |
![]() |
select t1.rownum1,t1.c1 from table1 t1 where t1.c1 not in ( select t2.c2 from table2 t2 ) order by t1.rownum1
select t1.rownum1,t1.c1 from table1 t1 where not exists ( select 1 from table2 t2 where t2.c2=t1.c1 ) order by t1.rownum1
for (rownum1,c1) in table1 flag=not-found for c2 in table2 if c1==c2 { flag=found break } if flag==not-found print rownum1,c1
rownum1 | c1 |
---|---|
4 | "c" |
5 | "c" |
6 | "d" |
Table1 | Table1 / Table2 | Table2 |
![]() |
select t1.rownum1 as "rownum",t1.c1 as "c" from table1 t1 union select t2.rownum2 as "rownum",t2.c2 as "c" from table2 t2
for (rownum1,c1) in table1 for (rownum2,c2) in table2 if rownum1==rownum2 && c1==c2 { put (rownum1,c1) in set-without-duplicates break } for (rownum1,c1) in set-without-duplicates print rownum1,c1 for (rownum1,c1) in table1 if (rownum1,c1) not in set-without-duplicates print rownum1,c1 for (rownum2,c2) in table2 if (rownum2,c2) not in set-without-duplicates print rownum2,c2
rownum | c |
---|---|
1 | "a" |
2 | "a" |
2 | "b" |
3 | "b" |
4 | "c" |
4 | "e" |
5 | "c" |
5 | "e" |
6 | "d" |
6 | "f" |
Table1 | Table1 / Table2 | Table2 |
![]() |
select t1.rownum1 as "rownum",t1.c1 as "c" from table1 t1 intersect select t2.rownum2 as "rownum",t2.c2 as "c" from table2 t2
for (rownum1,c1) in table1 for (rownum2,c2) in table2 if rownum1==rownum2 && c1==c2 { put (rownum1,c1) in set-without-duplicates break } for (rownum1,c1) in set-without-duplicates print rownum1,c1
rownum | c |
---|---|
1 | "a" |
3 | "b" |
|
|||||||||||||||||||||||||
|
|
||||||||||||||||||||||||
|
|
||||||||||||||||||||||||
|
|
||||||||||||||||||||||||
|
|||||||||||||||||||||||||
|
|