The difference between join and left join in oracle

JOIN returns only rows that match, while LEFT JOIN retains all rows in the left table, even if there are no matching rows in the right table. JOIN: Joins the left table and the right table, returning only rows with matching rows. LEFT JOIN: Join the left table and the right table, retain all rows in the left table, and fill unmatched rows in the right table with NULL values.

JOIN and LEFT JOIN in Oracle

JOIN and LEFT JOIN are used in Oracle to combine data from different tables Two important operators for related data. The main difference between them is that LEFT JOIN retains all rows from the left table even if there are no matching rows in the right table, whereas JOIN returns only rows with matching rows.

JOIN

• JOIN returns rows only if there are matching rows in both the left and right tables.
• If there is no matching row in either table, the row will be excluded from the results.

• JOIN syntax:

  <code>SELECT * FROM left_table JOIN right_table ON left_table.column = right_table.column</code>

  Copy after login

LEFT JOIN

• LEFT JOIN returns all items in the left table rows, even if there are no matching rows in the right table.
• Unmatched rows in the right table will be filled with NULL values.

• LEFT JOIN Syntax:

  <code>SELECT * FROM left_table LEFT JOIN right_table ON left_table.column = right_table.column</code>

  Copy after login

Usage

• JOIN: Used to find matching rows between two tables. For example, find all customers who have orders in the orders table but not in the shipping table.
• LEFT JOIN: Used to find matching rows and retain all rows in the left table. For example, find all customers and their corresponding latest orders, even if some customers have no orders.

Example

<code>SELECT * FROM customers JOIN orders ON customers.id = orders.customer_id</code>

This JOIN will return all customers with matching orders.

<code>SELECT * FROM customers LEFT JOIN orders ON customers.id = orders.customer_id</code>

This LEFT JOIN will return all customers, even if they have no orders. For customers who do not have orders, the columns in the orders table will be populated with NULL values.

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