Term: ON
Definition:
Oracle provides the ON clause to specify join conditions in SELECT statements joining multiple tables together. It is independent of column names and data types. Like the USING clause, the ON clause and NATURAL JOIN are mutually exclusive.
In cases where the participating tables are more than two ON clauses, Oracle uses the first ON clause specification to join two tables. It then uses the second ON clause specification to combine the next table with the result of the first join.
The
ON keyword is also used in other contexts, such as in the
GRANT and
INDEX statements.
Notes:
- After the keyword ON join condition is written, generally it can contain predicates connected with Boolean AND, OR, and NOT
- The order of the tables is not relevant
- The use of parentheses are optional in the syntax, but increases readability
Example Syntax:
SELECT
<column list>
FROM TABLE_1
JOIN TABLE_2
ON (join condition)
Example Usage:
The SQL query below shows the employee name and their working department name:
SQL> SELECT E.ENAME, D.DNAME
FROM EMPLOYEE E JOIN DEPT D
ON (E.DEPTNO =D.DEPTNO)
Related Links:
Related Code Snippets:
- WITH with CONNECT BY - The WITH query_name clause provides a way assign a name to a subquery block...
