Inner join in Oracle with WHERE clause

In Oracle database, the difference between joining two tables using an in-network (INNER JOIN) and a WHERE clause is a common problem. While there may be slight differences between the two in certain situations, overall performance differences are often negligible.

The following example:

Select * from Table1 T1 
Inner Join Table2 T2 On T1.ID = T2.ID

as well as

Select * from Table1 T1, Table2 T2 
Where T1.ID = T2.ID

Both queries perform the same operation: concatenate rows in Table1 with rows in Table2 based on the equality of the ID column. To better understand this, let's create two sample tables:

CREATE TABLE table1 (
  id INT,
  name VARCHAR(20)
);

CREATE TABLE table2 (
  id INT,
  name VARCHAR(20)
);

Run execution plan for query using in-connections:

-- 使用内连接
EXPLAIN PLAN FOR
SELECT * FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id;

SELECT *
FROM TABLE (DBMS_XPLAN.DISPLAY);

...get the following output:

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

Similarly, the execution plan of a query using the WHERE clause:

-- 使用 WHERE 子句
EXPLAIN PLAN FOR
SELECT * FROM table1 t1, table2 t2
WHERE t1.id = t2.id;

SELECT *
FROM TABLE (DBMS_XPLAN.DISPLAY);

...Returns the following output:

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

You can see that both queries use hash connections to perform connection operations, and there is no significant difference in execution plan.

Therefore, choosing to use an intra-join or a WHERE clause in Oracle depends mainly on personal preferences or the specific needs of the database schema or the query used.