Summary of connection queries in Oracle

Connection query:

A connection query is a query that is based on more than one table or view. When you use a connection query, you should specify a valid query condition, or you might cause a Cartesian product to be generated. If the existing Department table Dept, Staff table EMP, the following query due to invalid query conditions, and produce Cartesian product: (The fields in each statement do not explain, mainly show the logical relationship)

Select Dept.dname,emp.ename from dept,emp where dept.name = ' sales ';

Valid query criteria should specify the connection relationship between the Dept table and the EMP table. The connection relationships between tables are as follows:

1. Equal connection (=)

The equivalent connection is primarily used to query the related data between master and slave tables, as follows:

Select Table1.col1,table2.col2 from table1,table2 where table1.col1 = Table2.col2  ;

You can also specify other query conditions with and, such as modifying the first sentence:

Select Dept.dname,emp.ename from dept,emp where Dept.deptno = Emp.deptno and dept.name = ' sales ';

2. Unequal connections

Unequal joins refers to the use of other comparison operations in the join condition, except for equality comparisons, which are used to display specific range information between different tables. As follows:

Select A.enma, A.sal b.grade from emp A, Salgrade b where a.sal between B.losal and B.hisal;

3. Self-connect

Refers to the connection query in the same table, mainly used in the self-referential table to show the subordinate relationship or hierarchical relationship. A self-referencing table is a table that has a referential relationship or a master-slave relationship between different columns. As below, the EMP table contains two fields with master-slave relationship, EMPNO (employee number), MGR (manager number), to show the correspondence between employee and manager, can be used from the connection:

Select Manager.ename from  EMP Manager, EMP worker where Manager.empno = worker.mgr and worker.ename = ' KING ';

4. Internal and external connections

Select Table1.column, table2.column from table1 [Inner | left | right | full] join table2 on table1.column1 = Table2.colum N2;

The above are internal, left join, linked, fully connected, and the ON clause is used to specify the connection condition.

Internal connections: Used to return all records that meet the query criteria, by default, if no connection operation is specified when the connection query is specified, then it is an inner connection, and the above query statements are both inner joins, for example, the following two sentences are the same:

Select Dept.dname,emp.ename from dept,emp where Dept.deptno = Emp.deptno and dept.name = ' sales '; Select Dept.dname,emp.ena Me from dept INNER join emp on dept.deptno = emp.deptno and dept.name = ' sales ';

Left outer link: Returns all records that satisfy the join condition, and returns the other rows of the table to the left of the join operator that do not meet the join condition, that is, the left side returns all records, and the right table returns only the records that satisfy the query criteria. For convenience, cite other people's examples to illustrate the query effect (see: Http://www.blogjava.net/hello-yun/archive/2011/04/08/347890.html), as follows:

SELECT * from TESTA left OUTER joins Testb on TESTA. A=testb. A

The query results are as follows:

Multi-Table left outer joins:

SELECT * from TESTA left  OUTER joins Testb on TESTA. A=testb. A left OUTER joins TESTC on TESTA. A=TESTC. A

Right outer joins: such as Left outer joins, that is, the right table returns all records, coordinates only return records that satisfy the query criteria, such as:

SELECT * from TESTA right joins TESTB on TESTA. A=testb. A

The query results are as follows:

Full outer joins: Returns records that satisfy the query criteria, and also returns other records that do not meet the query criteria, for example:

SELECT * from TESTA full JOIN testb on TESTA. A=testb. A

The query results are as follows:

(+) Action: (+) to perform an outer join, placing it on one end of the display of fewer rows (fully satisfying the join condition row), note that the (+) operation is available only for columns, not on expressions, and cannot be used in conjunction with OR and in operations.

Use (+) to achieve left OUTER join: Change the above left connection statement to use (+) to implement.

SELECT * from TESTA left OUTER joins Testb on TESTA. A=testb. A;==select * from TESTA, Testb WHERE TESTA. A=testb. A (+);

SELECT * from TESTA left  OUTER joins Testb on TESTA. A=testb. A left OUTER joins TESTC on TESTA. A=TESTC. A
==
SELECT * from TESTA, TESTB,TESTC where TESTA. A=testb. A (+) and TESTA. A=TESTC. A (+);

SELECT * from TESTA on  testb. A=TESTC. A
==
Testb. A=TESTC. A (+);

Select A.dname, B.ename from dept a LEFT join EMP b on a.deptno = B.deptno and A.deptno = 10;
==
Select A.dname, B.ename from dept A, emp b where A.deptno = B.deptno (+) and A.deptno(+) = 10;

using (+) to realize the right outer connection mode with left join.

To achieve a full connection with (+):

SELECT * from TESTA full JOIN testb on TESTA. A=testb. A;==select  testa.*,testb.* from TESTA left OUTER joins Testb on TESTA. A=testb. Aunionselect testa.*,testb.* from Testb left OUTER joins TESTA on TESTA. A=testb. A

Summary of connection queries in Oracle