Shulou(Shulou.com)06/01 Report--

This article introduces what the union all rewriting method of full external connection is like, the content is very detailed, interested friends can refer to, hope to be helpful to you.

As for the connection operation in SQL, it is more convenient and efficient to achieve business requirements. Here we show several writing methods in Oracle for "full external connection".

Each method of writing varies from person to person, with the aim of meeting the needs.

For a simple demonstration of inner connection, left connection and right connection, please refer to "[experiment] Inner connection, left connection, right connection, full external connection" http://space.itpub.net/519536/viewspace-563019

1. Create the experimental table and initialize the experimental data

SQL > create table a (a number (1), b number (1), c number (1))

SQL > create table b (a number (1), d number (1), e number (1))

SQL > insert into a values (1pm 1pm 1)

SQL > insert into a values (2pm 2pm 2)

SQL > insert into a values (3pm 3pm 3)

SQL > insert into b values (1, 4, 4)

SQL > insert into b values (2, 5, 5)

SQL > insert into b values (4, 6, 6)

SQL > commit

two。 Check the contents of the initialization data

Sec@ora10g > select * from a

A B C

1 1 1

2 2 2

3 3 3

Sec@ora10g > select * from b

A D E

1 4 4

2 5 5

4 6 6

3. The first way to write, which is also the standard SQL. The function is clear, but there is a slight obstacle to understanding.

Sec@ora10g > select * from a full outer join b on a.a = b.a

A B C A D E

--

1 1 1 4 4

2 2 2 5 5

3 3 3

4 6 6

4. The second way of writing

Idea: "full external connection from a to b" = "inner connection from a to b" + "anti-connection from b to a" + "anti-connection from a to b".

Sec@ora10g > select *

2 from a, b

3 where a.a = b.a

4 union all

5 select *

6 from a, b

7 where a.a (+) = b.a

8 and a.an is null

9 union all

10 select *

11 from a, b

12 where a.a = b.a (+)

13 and b.an is null

14 /

A B C A D E

--

1 1 1 4 4

2 2 2 5 5

4 6 6

3 3 3

5. The third way of writing

Idea: "full external connection from a to b" = "external connection from b to a" + "anti-connection from a to b".

Sec@ora10g > select *

2 from a, b

3 where a.a (+) = b.a

4 union all

5 select *

6 from a, b

7 where a.a = b.a (+)

8 and b.an is null

9 /

A B C A D E

--

1 1 1 4 4

2 2 2 5 5

4 6 6

3 3 3

6. Summary

Note that "union all" in the rewriting method can also be changed to "union". The reason why we use union all in the above rewriting method is that for the sake of performance convenience, union all is much more efficient than union operation.

What is shown here is a change of thinking, which can be seen at a glance.

On the full external connection of the union all rewriting method is shared here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.