Oracle 11g R1 (11.1) join clause (join_clause)

The focus of this article is "Join clause (join_clause)", rather than table join itself.

Content

• The table and its data used in this article
• Oracle 11g R1 (11.1) join clause (join_clause)
• Inner Joins)
• Cross-join)
• Outer Join (Outer Joins)
• Natural Joins)
• References

The table and its data used in this article

• Department table DEPT

• Employee table EMP

 

Oracle 11g R1 (11.1) join clause (join_clause)

Use the appropriate join clause (join_clause) to obtain part of the connection data.

UseInner_cross_join_clauseThe syntax can specify an inner joins or cross joins.Outer_join_clauseThe syntax can specify an external connection.

To connect more tables, use parentheses to overwrite the default priority, as shown in the following syntax:

SELECT ... FROM a JOIN (b JOIN c) ...

Note: The SQL statement first connects B and c, and then connects the result with.

Inner Joins)

"Inner join" only returns rows that meet the connection conditions.

The INNER keyword indicates an INNER connection.

If the JOIN keyword is displayed, a connection is executed. This syntax can be used insteadFROMClause join syntax, that is, inFROMThe clause uses commas to separate the expressions of the "table ".WHEREClause write join conditions.

ON condition UseONClause specifies the connection conditions. This connection condition is independent of any search, orWHEREFilter condition in the clause. You can useONClause.

Example 1:

select emp.empno, emp.ename, dept.dname, dept.loc

  from emp

 inner join dept on (emp.deptno = dept.deptno)

 order by emp.empno

Execution result:

Note:

• There are 14 employees in the EMP table, of which 3 have no department. Therefore, the results only contain employees of the Department and no employees of the Department.

USINGUSING columnClause specifies this column. This clause can be used only when two tables have the same columns. This clause does not validate the column name with the table name or table alias when the column in The injoin operation is performed.

Example 2:

select emp.empno, emp.ename, dept.dname, dept.loc

  from emp

 inner join dept

 using (deptno)

 order by emp.empno

The result is the same as that in Example 1.

1 2 3 Next Page