Oracle SQL multi-Table query

For a while, I was confused about Oracle multi-table queries. The reason is that Oracle's own syntax is mixed with SQL's international standard syntax. ThisArticleIt is only suitable for Oracle cainiao, and old birds fly directly...

Multi-Table connection type (SQL 1999 standard)

•Cross joins

•Natural joins

•Using clause

•Full (or two-sided) Outer joins

•Arbitrary join conditions for outer joins

Sql1999 Syntax:

Select table1.column, table2.column

From Table1

[Cross join Table2] |

[Natural join Table2] |

[Join Table2 using (column_name)] |

[Join Table2

On (table1.column _ name = table2.column _ name)] |

[Left | right | Full outer join Table2

On (table1.column _ name = table2.column _ name)] |

[Cross join Table2];

Syntax explanation:

Table1.column -- specify the tables and columns from which data is retrieved

Cross join -- returns the Cartesian set of two tables.

Natural join -- join two tables based on the same column name

Join table

Using column_name -- execute equivalent join Based on column name

Join table on

Table1.column _ name -- execute equivalent join based on conditions in the on Clause

= Table2.column _ name

Left/right/full outer

In general, data display methods are divided into internal connections and external connections.

Internal Connection: only data that meets the connection conditions is returned.

Outer Join: In addition to returning rows that meet the connection conditions, rows that do not meet the conditions in the left (right) table are also returned,

It is called the left (right) connection.

Demonstration, mainly based on SQL standards, compared with Oracle writing. Example users are Scott and HR

Unlock these two user statements:

Alter user Scott identified by tiger account unlock;

Alter user HR identified by HR account unlock;

Internal Connection

-- Oracle statement

Select empno, ename, Sal, dname, LOC from EMP, Dept

Where EMP. deptno = Dept. deptno;

-- SQL 99 standard writing

Select empno, ename, job, Sal, Dept. deptno, dname, Loc

From EMP join dept on EMP. deptno = Dept. deptno;

Or change join to inner join.

Outer Join left Outer Join

Oracle Outer Join Syntax:

Select table1.column, table2.column -- right Outer Join

From Table1, Table2

Where table1.column (+) = table2.column;

Select table1.column, table2.column -- left Outer Join

From Table1, Table2

Where table1.column = table2.column (+ );

For SQL 1999 standard outer join syntax, see the above sql1999 syntax

-- Oracle statement

The outer join symbol is (+), and (+) should be placed after the field name. The table opposite to (+) is displayed in all.

When the left outer join, the plus sign is on the right of the equal sign

SQL> select D. dname, E. ename, E. deptno

From dept D, EMP E

Where D. deptno = E. deptno (+)

Order by D. deptno;

Dname ename deptno

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

Accounting Clark 10

Accounting King 10

Accounting Miller 10

Research Jones 20

Research Ford 20

Research Adams 20

Research Smith 20

Research Scott 20

Sales ward 30

Sales Turner 30

Sales Allen 30

Sales James 30

Sales Blake 30

Sales Martin 30

Operations

15 rows selected

-- SQL 99 standard Writing Method

SQL> select D. dname, E. ename, E. deptno

From dept d

Left join EMP E

On D. deptno = E. deptno

Order by D. deptno;

Dname ename deptno

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

Accounting Clark 10

Accounting King 10

Accounting Miller 10

Research Jones 20

Research Ford 20

Research Adams 20

Research Smith 20

Research Scott 20

Sales ward 30

Sales Turner 30

Sales Allen 30

Sales James 30

Sales Blake 30

Sales Martin 30

Operations

15 rows selected

Outer right connection

-- Oracle statement

SQL> select empno, ename, job, Sal, Dept. deptno, dname, Loc

2 from EMP, Dept

3 where EMP. deptno (+) = Dept. deptno;

Empno ename job Sal deptno dname Loc

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

7782 Clark manager 2450.00 10 Accounting New York

7839 King President 5000.00 10 Accounting New York

7934 Miller clerk 1300.00 10 Accounting New York

7566 Jones manager 2975.00 20 research Dallas

7902 Ford analyst 3000.00 20 research Dallas

7876 Adams clerk 1100.00 20 research Dallas

7369 Smith clerk 800.00 20 research Dallas

7788 Scott analyst 3000.00 20 research Dallas

7521 ward salesman 1250.00 30 sales Chicago

7844 Turner salesman 1500.00 30 sales Chicago

7499 Allen salesman 1600.00 30 sales Chicago

7900 James Clerk 950.00 30 sales Chicago

7698 Blake manager 2850.00 30 sales Chicago

7654 Martin salesman 1250.00 30 sales Chicago

40 operations Boston

15 rows selected

-- Standard syntax of sql1999

SQL> select empno, ename, job, Sal, Dept. deptno, dname, Loc

2 from EMP

3 right join Dept

4 on EMP. deptno = Dept. deptno;

Empno ename job Sal deptno dname Loc

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

7782 Clark manager 2450.00 10 Accounting New York

7839 King President 5000.00 10 Accounting New York

7934 Miller clerk 1300.00 10 Accounting New York

7566 Jones manager 2975.00 20 research Dallas

7902 Ford analyst 3000.00 20 research Dallas

7876 Adams clerk 1100.00 20 research Dallas

7369 Smith clerk 800.00 20 research Dallas

7788 Scott analyst 3000.00 20 research Dallas

7521 ward salesman 1250.00 30 sales Chicago

7844 Turner salesman 1500.00 30 sales Chicago

7499 Allen salesman 1600.00 30 sales Chicago

7900 James Clerk 950.00 30 sales Chicago

7698 Blake manager 2850.00 30 sales Chicago

7654 Martin salesman 1250.00 30 sales Chicago

40 operations Boston

15 rows selected

Full connection

-- Standard syntax of sql1999

SQL> select empno, ename, job, Sal, D. deptno, dname, Loc

2 from EMP E

3 full join dept d

4 on E. deptno = D. deptno;

Empno ename job Sal deptno dname Loc

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

7369 Smith clerk 800.00 20 research Dallas

7499 Allen salesman 1600.00 30 sales Chicago

7521 ward salesman 1250.00 30 sales Chicago

7566 Jones manager 2975.00 20 research Dallas

7654 Martin salesman 1250.00 30 sales Chicago

7698 Blake manager 2850.00 30 sales Chicago

7782 Clark manager 2450.00 10 Accounting New York

7788 Scott analyst 3000.00 20 research Dallas

7839 King President 5000.00 10 Accounting New York

7844 Turner salesman 1500.00 30 sales Chicago

7876 Adams clerk 1100.00 20 research Dallas

7900 James Clerk 950.00 30 sales Chicago

7902 Ford analyst 3000.00 20 research Dallas

7934 Miller clerk 1300.00 10 Accounting New York

40 operations Boston

15 rows selected

Self-connection

Use the image of the table as another table

-- Oracle statement

SQL> select e. ename | 'Works for '| M. ename

2 from emp e, EMP m

3 where E. empno = M. Mgr;

E. ename | 'worksfor' | M. ename

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

Jones works for Ford

Jones works for Scott

Blake works for Turner

Blake works for Allen

Blake works for ward

Blake works for James

Blake works for Martin

Clark works for Miller

Scott works for Adams

King works for Blake

King works for Jones

King works for Clark

Ford works for Smith

13 rows selected

-- Standard syntax of sql1999

SQL> select e. ename | 'Works for '| M. ename

2 from EMP E

3 join EMP m

4 on E. empno = M. Mgr;

E. ename | 'worksfor' | M. ename

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

Jones works for Ford

Jones works for Scott

Blake works for Turner

Blake works for Allen

Blake works for ward

Blake works for James

Blake works for Martin

Clark works for Miller

Scott works for Adams

King works for Blake

King works for Jones

King works for Clark

Ford works for Smith

13 rows selected

Natural connection

Use natural join as the basis for all columns with the same fields in two tables)

It selects the rows in the two tables that have the same values in all matched columns.

If a column has the same name but different data types, an error is returned.

-- Standard syntax of sql1999

SQL> select empno, ename, job, Sal, deptno, dname, Loc

2 from EMP natural

3 join dept;

Empno ename job Sal deptno dname Loc

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

7782 Clark manager 2450.00 10 Accounting New York

7839 King President 5000.00 10 Accounting New York

7934 Miller clerk 1300.00 10 Accounting New York

7566 Jones manager 2975.00 20 research Dallas

7902 Ford analyst 3000.00 20 research Dallas

7876 Adams clerk 1100.00 20 research Dallas

7369 Smith clerk 800.00 20 research Dallas

7788 Scott analyst 3000.00 20 research Dallas

7521 ward salesman 1250.00 30 sales Chicago

7844 Turner salesman 1500.00 30 sales Chicago

7499 Allen salesman 1600.00 30 sales Chicago

7900 James Clerk 950.00 30 sales Chicago

7698 Blake manager 2850.00 30 sales Chicago

7654 Martin salesman 1250.00 30 sales Chicago

14 rows selected

Use the using clause to create a connection

If several columns have the same name but the data type does not match, you can use the using clause to modify the natural join clause to specify the columns to be used for equijoin. When multiple columns match, use the using clause to match only one column. Do not use the table name or alias in the reference column. Natural join and using clauses are incompatible.

Select L. City, D. department_name

From locations l join orders ments D using (location_id)

Where location_id = 1400;

-- The following statement is invalid because the where Clause limits location_id to D. location_id.

Select L. City, D. department_name

From locations l join orders ments D using (location_id)

Where D. location_id = 1400;

ORA-25154: column part of using clause cannot have qualifier

Note: The columns with the same names in the two tables cannot have any delimiters during use. This restriction also applies to natural join.

Cross join

The result set generated by the cross join is the Cartesian product.

-- Oracle statement

Select empno, ename, dname

From EMP, DEPT;

In Oracle syntax, the two directly from tables are cross join.

-- Standard syntax of sql1999

Select empno, ename, dname

From EMP

Cross join dept;

Add conditions for equijoin

All the above multi-Table connections are equivalent connections (most commonly used). You can add conditions after the equijoin.

-- Oracle statement

Select E. empno, E. ename, D. dname

From emp e, DEPT d

Where D. deptno = E. deptno

And E. ename = 'Scott ';

-- Standard syntax of sql1999

Select E. empno, E. ename, D. dname

From EMP E

Inner join dept d

On D. deptno = E. deptno

And E. ename = 'Scott ';

Non-equivalent join

-- Oracle statement

Select ename, Sal, grade

From EMP, salgrade

Where Sal between losal and hisal

And EMP. deptno = 20;

-- Standard syntax of sql1999

Select E. ename, E. Sal, S. Grade

From EMP E

Join salgrade s

On E. Sal between losal and hisal

And E. deptno = 20;

Summary

The Oracle syntax is more user-friendly, except the left Outer and right outer connections. If you wantCodeIt is simple and clear. If you want to make the written code more compatible (may be transplanted to DB2, etc.), try to use the standard SQL syntax.

For more information, see Oracle official documentation.

Oracle Database

SQL Language Reference

11g Release 2 (11.2)

E17118-04

9 SQL queries and subqueries