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:

##Matching principleOnly match rowsLeft side table matchingRight side table matchingBoth sides table matchingReturn rowMatching rowAll rows in the left tableAll rows in the right tableAll rows in the two side tablesNull value processingOnly display matching rowsUse NULL to fill the empty values ​​of the right tableUse NULL to fill the left table empty valuesUse NULL to fill the empty values ​​of both sides of the table

Features	Inner join	Left outer join	Right outer join	Full outer join

Example:

Suppose we have two tables:

• 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:

The following query uses a left outer join to join the data from two tables and display all customers and their addresses:

<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>

By understanding the difference between inner and outer joins, developers can effectively use these joins to extract data from different tables and meet specific data query requirements.

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!