does equi join return duplicate column

Consider following points:

1. As we know, inner join returns the duplicate column for which we have defined the joining condition.

2. And we know natural join it removes the duplicate.

3. I know we can use equi join:

   select * from table1, table2 where id1=id2
   

   or

   select * from table1 join table2 on id1 = id2
   

I have confusion in this third point. I want to know what is the scenario in equi join what it returns? Does it return with duplicate column where we have defined the joining condition like inner join.

Answer

To answer titled question: Yes, if your query uses SELECT *. While your focus is on the JOIN clause, you are forgetting fundamentals about the SELECT clause in SQL.

By the way your #3 (your definition of equi join) is equivalent to #1 (inner join). In #3, the former using WHERE is the older, de-emphasized (ANSI-89) implicit join style and the latter using JOIN is the current, standard (ANSI-92) explicit join. Both return same results and should render same performance but readability arguably differs. See Explicit vs implicit SQL joins.

The reason you receive duplicate columns for these equi-join cases is the SELECT clause requests for all columns of all joined tables since you do not qualify the asterisk,*:

select * from table1 join table2 on id1 = id2

However, using asterisk alone is an abbreviated, convenience method but the recommended way is to explicitly control your resultset by selecting specific columns. This is crucial for application builds where column order and existence is required and helps in code readability and maintainability. See Why is SELECT * considered harmful?

Below are examples that can be used to avoid duplicate columns: qualifying the asterisks, subsetting/omiting columns, and even renaming/re-ordering columns with aliases .

-- ONLY table1 COLUMNS
select table1.* from table1 join table2 on id1 = id2

-- ONLY table2 COLUMNS
select table2.* from table1 join table2 on id1 = id2


-- SUBSET OF table1 AND table2 COLUMNS
select table1.Col1, table1.Col2, table1.Col3
     , table2.Col4, table2.Col5, table2.Col6 
from table1 
join table2 on id1 = id2


-- RENAMED COLUMNS
select table1.Col1 as t1_col1, table1.Col2 as t1_col2, table1.Col3 as t1_col3
     , table2.Col3 as t2_col3, table2.Col2 as t2_col2, table2.Col1 as t2_col1
from table1 
join table2 on id1 = id2