The format of the sql statement currently used in Oracle has been changed so that it can be used in sqlserver.
Therefore, I am trying to rewrite the following sql statements.
The select result is not the same
I don't understand the behavior of sql statements using (+) and
I'm sorry, but please let me know.
rewrite from --oracle --
SELECT
A.id,
A.name,
B.address,
C.tel1,
C.tel2,
D.sex
FROM TBL_AA, TBL_BB, TBL_CC, TBL_DD
WHERE
A.id='000001',
A.id = D.id,
A.id = B.id(+),
A.id = C.id(+),
B.address = C.address(+),
B.customer_name = D.customer_name(+)
Connect internally as shown above and
Sql for external coupling using (+) in multiple tables
I'd like to rewrite it to sql using left join.
The select result will be different.
Please tell me how to rewrite it.
I understand the following simple rewriting, but
I don't know more than one (a combination of internal and external bonds) at all.
(+) external coupling
SELECT
A.id,
B.address
FROM TBL_AA, TBL_BB
A.id = B.id(+)
left join rewrite
SELECT
A.id,
B.address
FROM TBL_AA
LEFT JOIN TBL_BB
ON A.id = B.id
Please let me know if anyone knows the above.
Also, if there is something missing or difficult to understand,
Please point it out.I will correct it.
Thank you for your cooperation.
sql sql-server oracle
As you seem to understand the basic one-to-one external coupling, the one-to-n external coupling should be resolved by applying left join to the external table (applicable to this table A).
We will start with a brief description of useful information for third parties.
The SQL listed is slightly different from the question.See SQL Fiddle for MySQL table configuration and results.
In addition, the SQL of Oracle before conversion mentioned in the question is a multiple external connection, so the following error occurred before Oracle 11g and it does not work.
ORA-01417—The table may be externally bound to at least one other table.
SQL Fiddle is redundant but substitutes the table with clause with 11g because 11g is not an example.
Of course, you should run at 12c or later.
First, combine tables A and B.(similar to SQL already shown in the question)
-- 2 tables in Oracle
with A as
(select 1 id, 'name1' name from dual union all
select2id, 'name2' name from dual union all
select3id, 'name3' name from dual union all
select4 id, 'name4' name from dual union all
select5id, 'name5' name from dual)
, Bas
(select 1 id, 'customer1' customer_name, 'address1' address from dual union all
select2 id, 'customer2' customer_name, 'address2' address from dual union all
select4 id, 'customer4' customer_name, 'address4' address from dual)
select A.id,name,
B.address
from A, B
where A.id = B.id(+)
order by A.id
-- 2 tables in SQL Server
select A.id,name,
B.address
from A
left join Bon A.id = B.id
order by A.id
Next is the external association of multiple tables.
In addition to Tables A and B, Tables A and C are externally coupled.
Multiple rows of left join allow multiple tables to be concatenated into an external table.
Tables B and C have not yet merged.
-- A on B, Cin Oracle
with A as
(select 1 id, 'name1' name from dual union all
select2id, 'name2' name from dual union all
select3id, 'name3' name from dual union all
select4 id, 'name4' name from dual union all
select5id, 'name5' name from dual)
, Bas
(select 1 id, 'customer1' customer_name, 'address1' address from dual union all
select2 id, 'customer2' customer_name, 'address2' address from dual union all
select4 id, 'customer4' customer_name, 'address4' address from dual)
,Cas
(select 1 id, 'address 1' address, 'tel 1' tel from dual union all
select3id, 'address3' address, 'tel3' tel from dual union all
select 5 id, 'address 5' address, 'tel 5' tel from dual) select A.id, name,
B.address,
C.tel
from A, B, C
where A.id = B.id(+)
and A.id= C.id(+)
order by A.id
-- A on B,Cin SQL Server
select A.id,name,
B.address,
C.tel
from A
left join Bon A.id = B.id
left joinCon A.id = C.id
order by A.id
Now that it's time to get into trouble, Oracle is finally able to handle SQL at 12c.
However, if it is an ANSI-compliant external bond, then simply add and to the A and C bond conditions to join B and C.
--Aon B,Cand BonCin Oracle12c-
with A as
(select 1 id, 'name1' name from dual union all
select2id, 'name2' name from dual union all
select3id, 'name3' name from dual union all
select4 id, 'name4' name from dual union all
select5id, 'name5' name from dual)
, Bas
(select 1 id, 'customer1' customer_name, 'address1' address from dual union all
select2 id, 'customer2' customer_name, 'address2' address from dual union all
select4 id, 'customer4' customer_name, 'address4' address from dual)
,Cas
(select 1 id, 'address 1' address, 'tel 1' tel from dual union all
select3id, 'address3' address, 'tel3' tel from dual union all
select5 id, 'address5' address, 'tel5' tel from dual)
select A.id,name,
B.address,
C.tel
from A, B, C
where A.id = B.id(+)
and A.id= C.id(+)
and B.address = C.address (+)
order by A.id
--Aon B,Cand Bon Bin SQL Server
select A.id,name,
B.address,
C.tel
from A
left join Bon A.id = B.id
left joinCon A.id = C.id
and B.address = C.address
order by A.id
Finally, we combine Table D based on the previous content.
If you combine A and D with a where clause, you will get an error in the left join of B and D, so you must first combine A and D with the inner join.
It is different from the SQL in question, but if Bon D externally combines B, records that are not in B disappear and the meaning of external coupling is diminished, so Don B externally combines D.
Also, there are records with null in customer_name and sex of D to indicate that it is an external bond.
--A,D and A on Bon C and Don B in Oracle 12c-
with A as
(select 1 id, 'name1' name from dual union all
select2id, 'name2' name from dual union all
select3id, 'name3' name from dual union all
select4 id, 'name4' name from dual union all
select5id, 'name5' name from dual)
, Bas
(select 1 id, 'customer1' customer_name, 'address1' address from dual union all
select3 id, 'customer3' customer_name, 'address3' address from dual union all
select4 id, 'customer4' customer_name, 'address4' address from dual)
,Cas
(select 1 id, 'address 1' address, 'tel 1' tel from dual union all
select3id, 'address3' address, 'tel3' tel from dual union all
select5 id, 'address5' address, 'tel5' tel from dual)
,Das
(select 1 id, 'customer1' customer_name, '1'sex from dual union all
select2id, 'customer2' customer_name, '2'sex from dual union all
select3id, 'customer3' customer_name, '3' sex from dual union all
select4 id, 'customer4' customer_name, '4' sex from dual union all
select 5 id, 'customer5' customer_name, '5'sex from dual)
select A.id,name,
B.address,
C.tel,
D.sex
from A, B, C, D
where A.id = D.id
and A.id= B.id(+)
and A.id= C.id(+)
and B.address = C.address (+)
and B.customer_name = D.customer_name(+)
order by A.id
--A,D and A on Bon C and A on Bin SQL Server
select A.id,name,
B.address,
C.tel,
D.sex
from A
inner join Don A.id = D.id
left join Bon A.id = B.id
and D.customer_name = B.customer_name -- Don B
left joinCon A.id = C.id
and B.address = C.address
order by A.id
© 2024 OneMinuteCode. All rights reserved.