ORACLE multiple table joins and subqueries

Oracle Table Connection

Sql/oracle querying data from multiple tables using table joins

Syntax format:

Select Field List
From  Table1,table2
where Table1.column1=table2.column2;

Description

Specifying join conditions in the WHERE clause

When the same name field exists in multiple tables that are connected, you must prefix the field with "Table name".

Types of connections

Table connections prior to oracle8i:

Equivalent connection (equijoin)

Non-equivalent connection (non-equijoin)

Outer JOIN (Outer join):--> left outer connection--right outer join

Self-connect (auto Join)

 

Newly introduced connection form after Oracle9 (supports SQL99 specification)

Cross Join

Natural connection (Natural join)

Using a using clause to establish a connection

Using the ON clause to establish a connection

Outer JOIN (Outer join):--> left outer connection--right outer connection--full outer connection

Equivalent connection (equijoin)

Select Empno,ename,sal,emp.deptno,dname from Emp,dept
where Emp.deptno = Dept.deptno;

in a multi-table connection:

• You can use the AND operator to increase query conditions
• Use table aliases to simplify queries
• Use table name (table alias) prefixes to improve query efficiency
• In order to connect n tables, at least one n-1 connection condition is required

Non-equivalent connection (non-equijoin)

Outer JOIN (Outer join)

Use an outer join to see a record of a party participating in a connection that does not meet the join condition

The outer join operator is (+)

The traditional outer connection is divided into left outer connection and right outer connection two kinds

Syntax format:

Select Field List
From  Table1,table2
where Table1.column1 (+) =table2.column2;

Select Field List
From  Table1,table2
where Table1.column1=table2.column2 (+);

Self-connect (auto Join)

Select A.enpno,a.ename,a.ngr,b.ename
From EMP A,emp b
where A.ngr = B.enpno;

SQL99 Connection Syntax

Connection query syntax specified in the SQL1999 specification

Select  Field List
From   table1
[Cross join table2] |
[Natural Join table2] |
[Join Table2 using (field name)] |
[Join Table2 on (table.column_name=table2.column_name)] |
[(left | right | full out) JOIN Table2
On (Table1.column_name=table2.column_name)];

Cross Join

The cross join produces a Cartesian set that has the same effect as a two-table connection without using a WHERE clause to qualify the join condition;

Select Empno,ename,sal,emp.deptno,dname
From the EMP Cross join dept;

Natural connection (Natural join)

Natural join establishes a connection based on all the same names in two tables

• Selects all rows with equal values for the same column from the two tables
• Error if all data types for columns of the same name in two tables are different
• The use of table names or aliases as prefixes on reference columns is not allowed

Select Empno,ename,sal,emp.deptno,dname
From EMP Natural Join dept;

using clause

If you do not want to reference the equivalent of all the same names of the connected tables, the natural connection will not meet the requirements, and you can use the Using clause to set the name of the column (Reference column) for the equivalent connection when connecting.

Select Empno,ename,sal,emp.deptno,dname
From EMP Join Dept
using (DEPTNO);

The use of table names or aliases as prefixes on reference columns is not allowed

ON clause

If you want to make an equivalent connection to a column that does not have the same name, or if you want to set any connection conditions, you can use the ON clause

Select Empno,ename,sal,emp.deptno,dname
From EMP Join Dept
On (EMP.DEPTNO=DEPT.DEPTNO);

Multi-table Connection

Using the SQL99 connection syntax, you should specify the connection conditions between the two tables that are present in each of the two + tables when you connect them.

Select  Field List   
From   table1   
[Cross join table2] |   
[Natural Join table2] |   
[Join Table2 using (field name)] |   
[Join Table2 on (table.column_name=table2.column_name)] |   
[(left | right | full out) JOIN Table2   
On (Table1.column_name=table2.column_name)]   
[Cross join Table3] |   
[Natural Join Table3] |   
[Join Table3 using (field name)] |   
[Join Table3 on (table.column_name=table3.column_name)] |   
[(left | right | full out) Join Table3   
On (Table2.column_name=table3.column_name)] ...;  

internal and external connections

Internal connection (Inner join)

In the SQL99 specification, the INNER join only returns data that satisfies the join condition.

Outer JOIN (Outer join)

Left outer connection (Outer join)

Two tables in addition to returning the behavior that satisfies the join condition in the connection process, the behavior that does not satisfy the condition in the left table is returned, which is called the left outer join.

Right outer join (Outer join)

Two tables in addition to returning the behavior that satisfies the join condition in the connection process, the behavior that does not satisfy the condition in the right table is returned, which is called the right outer join.

Full outer join (Outer join)

Oracle9 began to add functionality, two tables in the connection process, in addition to return to meet the conditions of the connection behavior, but also return two tables do not meet the conditions of all the behavior, this connection is called full outer join.

Subquery (sub query)

Sub-query subqueries are executed once before the main query

Results of a subquery using a primary query

Select Field List
From table
Where Expression operator (select field List from table);

Considerations for using sub-queries

• You should consider using subqueries when you are based on an unknown query
• Subqueries must be enclosed within parentheses
• Place the subquery on the right side of the comparison operator to enhance readability.
• Do not use the ORDER BY clause in subqueries unless TOP-N analysis is performed
• Using single-line operators for single-line subqueries
• Using the multiline operator with multiple rows of subqueries

Single-line subquery

Single-line subquery returns only one row of records

Single-line subquery can be used for single-line subqueries comparison operators

=--------------equals

>--------------greater than

>=------------greater than or equal to

<--------------less than

<=--------------less than or equal to

<>--------------Not equal to

SELECT * FROM emp
where sal> (select Sal from EMP where empno=7000);

Subquery NULL/multivalued problem

• If the subquery does not return any rows, the main query page does not return any results
• If the subquery returns a single row of results, it is a single-line subquery that can use the corresponding single-line record comparison operator in the main query
• If the subquery returns multiple rows of results, it is a multiline subquery and is not allowed to use a single-line record comparison operator at this time

Multi-row subqueries

Multiline subquery returns multiple rows of records

You can use multiple Row Records comparison operators for multiple rows of subqueries only

In--------------equals any one of the list

Any of the values that are returned by the any--------------and the subquery are compared

All--------------and all values returned by the subquery are compared

SELECT * FROM emp
Where Sal>any (select AVG (SAL) from the EMP Group by DEPTNO);

SELECT * FROM emp
Where Sal>all (select AVG (SAL) from the EMP Group by DEPTNO);

SELECT * FROM emp
where job in (the Select Job from emp where ename= ' Martin ' or ename= ' ssss ');

TOPN Query

Top n queries are typically implemented in Oracle in the form of subqueries

Select Field List
From (select field List from Table order by sort field)
where RowNum <=n;
------------------------------------------------------------
SELECT *
From (SELECT * from emp ORDER BY sal Desc)
where RowNum <=5;

Excerpt from: http://www.cnblogs.com/ddatsh/archive/2010/12/09/1901423.html

ORACLE multiple table joins and subqueries