How to use on in oracle

The ON clause joins rows in the table to create matching relationships based on common columns. It is used in table join operations, and the syntax is: ON . This condition compares columns in the table to establish the connection.

Usage of ON statement in Oracle

Use of ON clause

The ON clause is used to join rows in a table to create a matching relationship based on one or more common columns. It plays a vital role in table join operations.

Syntax

<code>ON <join_condition></join_condition></code>

Where, <join_condition></join_condition> is the condition used to compare columns in the table.

Usage

The ON clause can appear in the following SQL statements:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN

These statements are used to join two or more tables and return matching rows based on specified conditions.

Example

Suppose there are two tables, "Customers" and "Orders":

<code>Customers
----------
cust_id | cust_name
---------+----------
1       | John Doe
2       | Jane Smith

Orders
--------
order_id | cust_id | product_name
----------+---------+-------------
101       | 1       | Laptop
102       | 2       | Phone
103       | 1       | Tablet</code>

To join these two tables and get each For customer orders, you can use the following query:

<code>SELECT *
FROM Customers
INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id;</code>

Note:

• The ON clause must appear before the WHERE clause of the JOIN statement.
• Ensure that the join condition compares columns and that the data types of these columns are compatible.
• The ON clause can contain multiple comparison conditions, connected using the AND or OR operator.
• It is important to understand the different types of table joins (INNER, LEFT, RIGHT, and FULL) in order to choose the join operation correctly.

The above is the detailed content of How to use on in oracle. For more information, please follow other related articles on the PHP Chinese website!