英文:
Converting SQL-86 join to ANSI
问题
I am currently working on a internal legacy project and to get more familiar with the backend logic and database design, I have decided to rewrite all queries from the old fashion SQL-86 standard into a more modern one.
But I am stuck on one big view and one participial join (this example is very simplified):
select t4.id
from
t1,
t2,
t3,
t4
where
t2.t1id = t1.id(+)
and t2.tmpid = t3.tmpid(+)
and t4.id = t3.t4id(+)
group by t4.id
having
count (t2.t1id) = count(t3.tmpid);
which is based on the following (example) table structure:
create table t1 (
id number generated by default on null as identity primary key,
title varchar(100)
);
create table t2 (
t1id number not null,
tmpid number not null
);
create table t3 (
tmpid number not null,
t4id number not null
);
create table t4 (
id number generated by default on null as identity primary key,
title varchar(100)
);
insert into t1(id, title) values(1, 'Order1');
insert into t1(id, title) values(2, 'Order2');
insert into t4(id, title) values(1, 'Attribute 1');
insert into t4(id, title) values(2, 'Attribute 2');
insert into t2(t1id, tmpid) values(1, 1);
insert into t2(t1id, tmpid) values(1, 2);
insert into t2(t1id, tmpid) values(2, 1);
insert into t2(t1id, tmpid) values(2, 2);
insert into t3(tmpid, t4id) values(1, 1);
insert into t3(tmpid, t4id) values(2, 2);
insert into t3(tmpid, t4id) values(2, 1);
The query in question results in the following result when removing the "group by" and "having" statement:
select *
from
t1,
t2,
t3,
t4
where
t2.t1id = t1.id(+)
and t2.tmpid = t3.tmpid(+)
and t4.id = t3.t4id(+)
order by t1.id
First of all I thought the and t4.id = t3.t4id(+)
was just a "wrong" (well it works for over a decade now so it can't really be wrong) specified old fashioned join and just replaced it with a and t3.t4id = t4.id(+)
so it would be a "left join" on ANSI SQL, but then the rows with "null" values just disappears and the having count(t2.t1id) = count(t3.tmpid)
results to true on every row.
So my question is to what resolves the and t4.id = t3.t4id(+)
in a ANSI SQL and why? Out of desperation I have tried it with every join and I can't reproduce the null values which are mandatory for the "having".
Or is there is there another way to implement this query in a modern way?
英文:
I am currently working on a internal legacy project and to get more familiar with the backend logic and database design, I have decided to rewrite all queries from the old fashion SQL-86 standard into a more modern one.
But I am stuck on one big view and one participial join (this example is very simplified):
select t4.id
from
t1,
t2,
t3,
t4
where
t2.t1id = t1.id(+)
and t2.tmpid = t3.tmpid(+)
and t4.id = t3.t4id(+)
group by t4.id
having
count (t2.t1id) = count(t3.tmpid);
which is based on the following (example) table structure:
create table t1 (
id number generated by default on null as identity primary key,
title varchar(100)
);
create table t2 (
t1id number not null,
tmpid number not null
);
create table t3 (
tmpid number not null,
t4id number not null
);
create table t4 (
id number generated by default on null as identity primary key,
title varchar(100)
);
insert into t1(id, title) values(1, 'Order1');
insert into t1(id, title) values(2, 'Order2');
insert into t4(id, title) values(1, 'Attribute 1');
insert into t4(id, title) values(2, 'Attribute 2');
insert into t2(t1id, tmpid) values(1, 1);
insert into t2(t1id, tmpid) values(1, 2);
insert into t2(t1id, tmpid) values(2, 1);
insert into t2(t1id, tmpid) values(2, 2);
insert into t3(tmpid, t4id) values(1, 1);
insert into t3(tmpid, t4id) values(2, 2);
insert into t3(tmpid, t4id) values(2, 1);
The query in question results in the following result when removing the "group by" and "having" statement:
select *
from
t1,
t2,
t3,
t4
where
t2.t1id = t1.id(+)
and t2.tmpid = t3.tmpid(+)
and t4.id = t3.t4id(+)
order by t1.id
First of all I thought the and t4.id = t3.t4id(+)
was just a "wrong" (well it works for over a decade now so it can't really be wrong) specified old fashioned join and just replaced it with a and t3.t4id = t4.id(+)
so it would be a "left join" on ANSI SQL, but then the rows with a "null" values just disappears and the having count(t2.t1id) = count(t3.tmpid)
results to true on every row.
So my question is to what resolves the and t4.id = t3.t4id(+)
in a ANSI SQL and why? Out of desperation I have tried it with every join and I can't reproduce the null values which are mandatory for the "having".
Or is there is there another way to implement this query in a modern way?
答案1
得分: 3
以下是要翻译的内容:
有两个表使用 (+) 连接:t1
和 t3
。所以你有的是
来自 t2
交叉连接 t4
左连接 t1,条件为 t1.id = t2.t1id
左连接 t3,条件为 t3.tmpid = t2.tmpid
以及 t3.t4id = t4.id
在你的创建表语句中,t2.t1id
是一个非空列。通过适当的外键约束,t2.t1id
总是会有一个在 t1.id
中的匹配父行,因此 t1
连接实际上就是一个内连接。
如果你正在使用 Oracle 的 SQL Developer,你可以让程序将旧的 Oracle 专有连接重写为 ANSI 连接。请参考这里的thatjeffsmith的答案:https://stackoverflow.com/a/69284420/2270762。
英文:
There are two tables joined with (+): t1
and t3
. So what you have is
from t2
cross join t4
left join t1 on t1.id = t2.t1id
left join t3 on t3.tmpid = t2.tmpid
and t3.t4id = t4.id
In your create table statements t2.t1id
is a not null column. With a proper foreign key constraint, t2.t1id
would always have a parent row match in t1.id
and the t1
join would just be an inner join really.
If you are working with Oracle's SQL Developer, you can have the program re-write the queries from old Oracle proprietary joins to ANSI joins by the way. See thatjeffsmith's answer here: https://stackoverflow.com/a/69284420/2270762.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论