Inner Join vs. WHERE Clause in Oracle: Do They Produce Different Performance?

Performance comparison of inner joins and WHERE clauses in Oracle database

In Oracle Database, inner joins and WHERE clauses show a high degree of consistency in performance, and the execution plans generated by both are almost identical.

Let’s look at an example, assuming we have two tables:

<code class="language-sql">CREATE TABLE table1 (
  id INT,
  name VARCHAR(20)
);

CREATE TABLE table2 (
  id INT,
  name VARCHAR(20)
);</code>

The query execution plan using inner joins is as follows:

<code class="language-sql">-- 使用内连接
EXPLAIN PLAN FOR
SELECT * FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id;

SELECT *
FROM TABLE (DBMS_XPLAN.DISPLAY);

-- 0 select statement
-- 1 hash join (access("T1"."ID"="T2"."ID"))
-- 2 table access full table1
-- 3 table access full table2</code>

The equivalent query execution plan using the WHERE clause is similar:

<code class="language-sql">-- 使用 WHERE 子句
EXPLAIN PLAN FOR
SELECT * FROM table1 t1, table2 t2
WHERE t1.id = t2.id;

SELECT *
FROM TABLE (DBMS_XPLAN.DISPLAY);

-- 0 select statement
-- 1 hash join (access("T1"."ID"="T2"."ID"))
-- 2 table access full table1
-- 3 table access full table2</code>

As can be seen from the execution plan, both methods use hash joins and perform full table scans on the table1 and table2 tables. Therefore, their performance characteristics are almost identical.

The above is the detailed content of Inner Join vs. WHERE Clause in Oracle: Do They Produce Different Performance?. For more information, please follow other related articles on the PHP Chinese website!