将SQL-86的连接转换为ANSI。

huangapple go评论57阅读模式
英文:

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

将SQL-86的连接转换为ANSI。

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

将SQL-86的连接转换为ANSI。

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

以下是要翻译的内容:

有两个表使用 (+) 连接:t1t3。所以你有的是

来自 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.

huangapple
  • 本文由 发表于 2023年2月27日 03:15:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/75574425.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定