The difference between inner join and outer join in oracle

The connection types in Oracle are divided into inner connections and outer connections. Inner joins return results for matching rows only, while outer joins return matching rows and rows that appear in only one table. There are three types of outer joins: left outer join (returns all rows from the left table), right outer join (returns all rows from the right table), and full outer join (returns all rows from both tables). The characteristic of inner join is to match rows. The left outer join uses NULL to fill the null values ​​of the right table, the right outer join uses NULL to fill the left table null values, and the full outer join uses NULL to fill the null values ​​of both sides of the table.

The difference between inner joins and outer joins in Oracle

Definition:

• INNER JOIN: Only return data from all matching rows in two or more tables.
• Outer Join: Returns data from all matching rows in two or more tables, as well as from rows that exist in only one table.

Type:

There are three types of outer joins:

• LEFT OUTER JOIN: Returns all rows in the left table and matching rows in the right table.
• RIGHT OUTER JOIN: Returns all rows in the right table and matching rows in the left table.
• Full OUTER JOIN: Returns rows from both tables, even if there is no match between the rows.

Difference:

Example:

• Table A: id, name
• Table B: id, address

Inner join:

<code>SELECT *
FROM A
INNER JOIN B
ON A.id = B.id;</code>

Returns: Only rows with matching id.

Left outer join:

<code>SELECT *
FROM A
LEFT OUTER JOIN B
ON A.id = B.id;</code>

Returns: All rows from table A, and those with matching id Rows of Table B, if any. Non-matching rows are filled with NULL.

Sample query:

<code>SELECT customers.name, orders.order_date, products.product_name
FROM customers
LEFT OUTER JOIN orders
ON customers.id = orders.customer_id
LEFT OUTER JOIN products
ON orders.product_id = products.id;</code>

The above is the detailed content of The difference between inner join and outer join in oracle. For more information, please follow other related articles on the PHP Chinese website!