86 differences between standard SQL and 92 standard SQL

In the development of Oracle
When 9i is used, the database still uses the ansl SQL/92 standard link syntax. We recommend that you use the SQL/92 standard syntax when using Oracle 9i and later; the standard SQL/86 syntax should be used when Oracle 8i and earlier are used. ----- Oracle Database 10g SQL Development Guide

86 standard SQL is a traditional table join statement, that is, writing the table directly behind the from statement and writing the table join conditions and filtering conditions behind the where statement.
92 standard SQL statements use join table join methods. It can be divided into left join, right join, and inner join. Each join method can use version 86 SQL to convert to get the corresponding statement, but it is not exactly the same.

The following example describes how to connect two tables:

Test data:

Create Table A (ID integer, name varchar (20 ));

Create Table B (ID integer, name varchar (20 ));

Create Table C (ID integer, name varchar (20 ));

Insert into a values (1, 'a1 ');

Insert into a values (2, 'a2 ');

Insert into a values (3, 'a3 ');

Insert into a values (4, 'a4 ');

Insert into a values (5, 'a5 ');

Insert into B values (2, 'b2 ');

Insert into B values (3, 'b3 ');

Insert into B values (4, 'b4 ');

Insert into B values (5, 'b5 ');

Insert into B values (6, 'b6 ');

Insert into C values (1, 'c1 ');

Insert into C values (2, 'c2 ');

Insert into C values (3, 'c3 ');

Insert into C values (4, 'csv ');

1: equijoin inner join
SQL> select a. ID, A. Name, B. ID, B. Name, C. ID, C. Name

2 from a inner join B on A. ID = B. ID

3 Inner join C on A. ID = C. ID;

ID name

-------------------------------------------------------------------------

2 A2 2 B2 2 C2

3 A3 3 B3 3 C3

4 A4 4 B4 4 C4

The results of the above 92 editions of SQL are similar to those of the following 86 editions;

Select a. ID, A. Name, B. ID, B. Name, C. ID, C. Name

From a, B, c

Where a. ID = B. ID

And a. ID = C. ID

2: left join

First, let's take a look at the comparison between the 92 statements.

SQL> select a. ID, A. Name, B. ID, B. Name, C. ID, C. Name

2 from a left join B on A. ID = B. ID

3 left join C on B. ID = C. ID;

ID name

---------------------------------------------------------------------------

---------------

2 A2 2 B2 2 C2

3 A3 3 B3 3 C3

4 A4 4 B4 4 C4

1 A1

5 A5 5 B5

SQL> select a. ID, A. Name, B. ID, B. Name, C. ID, C. Name

2 from a left join B on A. ID = B. ID

3 left join C on A. ID = C. ID;

ID name

---------------------------------------------------------------------------

---------------

2 A2 2 B2 2 C2

3 A3 3 B3 3 C3

4 A4 4 B4 4 C4

5 A5 5 B5

1 A1 1 C1

The two methods are left join. The only difference is that the connection conditions are different when table C is connected. However, the results are completely different.

When processing Table C, the database determines that the left successive benchmark tables are based on the connection conditions behind on. For the first SQL statement (B. ID = C. ID), Table C is the result set of the processed table B.

Join. The IDs in the result set obtained from table B are 2, 3, 4, and 5. The result is used as a benchmark to connect to Table C. Therefore, the IDs of Qualified Data obtained from Table C are 2, 3, and 4.

For the second SQL statement, Table C is connected to Table A. Since Table A is a reference table in the left join, the data volume in the table remains unchanged, when Table C is connected to it left, the result set ID is 1, 2.

, 3, 4

The preceding two SQL statements can be replaced by the following statement:

The first SQL statement:

Select a. ID, A. Name, B. ID, B. Name, C. ID, C. Name

From a, B, c

Where a. ID = B. ID (+)

And B. ID = C. ID (+)

Article 2 SQL

Select a. ID, A. Name, B. ID, B. Name, C. ID, C. Name

From a, B, c

Where a. ID = B. ID (+) -- join table B to Table A on the left. Table A prevails and is called the left join. Note that (+) is placed on the right.

And a. ID = C. ID (+) -- connect Table C to Table A on the left. Table A prevails.
3: Right join

When writing 92 SQL statements, you only need to change left to right. For version 86 SQL, you only need to change the location of (+.

4: Where and on conditions in SQL 92
The connection conditions and filtering conditions in version 86 are placed behind the where condition.

In SQL 92, the join condition is placed behind on, and the filter condition is placed behind where.

Note that the results of the following two SQL statements are different:

SQL> select a. ID, A. Name, B. ID, B. Name

2 from a left join B on A. ID = B. ID

3 and B. ID> 2;

ID name

--------------------------------------------------

3 A3 3 B3

4 A4 4 B4

5 A5 5 B5

1 A1

2 A2


SQL> select a. ID, A. Name, B. ID, B. Name

2 from a left join B on A. ID = B. ID

3 where B. ID> 2;

ID name

---------------------------------------------

3 A3 3 B3

4 A4 4 B4

5 A5 5 B5

Although the conditions are the same, but the positions are different, the results are different.

For the first SQL statement. B. ID> 2 is used to filter data in Table B during table join.

For the second SQL statement, B. ID> 2 filters the result set.

The preceding two SQL statements correspond to the 86 SQL statements:

Select a. ID, A. Name, B. ID, B. Name

From a, B

Where a. ID = B. ID (+)

And B. ID> 2

It is consistent with the results of the second SQL statement (this is a bit of suspicion that the database should be applied according to different situations B. id> 2 is the driving condition or filtering condition. If multiple data sources are tested, B is not obtained. id> 2 is used as the driving condition ).

Original article address:

Http://hi.baidu.com/420350501/blog/item/dd5344f15d010c3cbd3109fc.html

Http://www.itpub.net/thread-919997-1-1.html