The relationship between the usage of Oracle left concatenation (+) plus sign and common syntax

The purpose of this article is:

By analyzing the relationship between the writing of the left concatenated (+) plus sign and some common grammars, we know the usage of the Oracle plus sign.

Analysis steps:

1. First create the structure of the test table:

Create table test_left_a (a varchar2 (50), b varchar2 (50)) create table test_left_b (a varchar2 (50), b varchar2 (50))

two。 Insert the appropriate test data:

Insert into test_left_a select'a, 21' from dual;commit;insert into test_left_a select, 211111from dual;commit;insert into test_left_b select, 121from dual;commit;insert into test_left_b select, 13from dual;commit.

3. Several commonly used grammars for implementing left join queries are listed for comparative analysis.

Several syntax for implementing left join queries (without where):

A:

Select * from test_left_a a left join test_left_b b on a.a = b.a

B:

Select * from test_left_a a, test_left_b b where a.a = b.a (+)

C:

Select * from test_left_a ainner join test_left_b b on a.a = b.a (+)

Several syntax for implementing left join queries (plus where):

D:

Select * from test_left_a aleft join test_left_b b on a.a = b.awhere a.a = b.a

E:

Select * from test_left_a a, test_left_b bwhere a.a = b.a (+) and a.a = b.a

F:

Select * from test_left_a ainner join test_left_b b on a.a = b.a (+) and a.a = b.a

G:

Select * from test_left_a ainner join test_left_b b on a.a = b.a (+) where a.a = b.a

The purpose of distinguishing where is to go from shallow to deep, and to avoid misleading on when understanding similar E-writing.

4. Conclusion:

The above queries (do not consider performance for the time being, only consider usage)

An is equivalent to B is equivalent to C

Query results:

A 21 a 12

C 2111

D is equivalent to E and F is equivalent to G.

Query results:

A 21 a 12

5. Warm reminder:

When using inner join, writing the condition directly after on is the same as writing the condition after where, because the inner connection matches the record where the on condition is true (see F and G).

When using left join or right join, writing a condition directly after on is different from writing a condition after where because:

Left join shows all the records in the left table even if the condition after on is false.

Right join shows all the records in the table on the right even if the condition after on is false.

Summary

The above is the relationship between the usage of the Oracle left link (+) plus sign and the common grammar introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to the website!