CODEWITHSUNDEEP
sqloracle-database
mtnp
mtnp

Reputation: 369

Join more than two tables by the same columns with Oracle USING clause

I have 3 tables : Table1, Table2 and Table3. The three tables contains 3 same columns SAMECOL1, SAMECOL2 and SAMECOL3. Both tables Table1 and Table2 have the MY_PK_COL column which is the primary key of the Table1.

When I use this query in SQL Developer I get an error :

SELECT * FROM Table1
INNER JOIN Table2 ON Table1.MY_PK_COL = Table2.MY_PK_COL 
JOIN Table3 USING (SAMECOL1, SAMECOL2, SAMECOL3)
  1. 00000 - "column ambiguously defined"

But when I use this it's ok :

SELECT * FROM Table1
JOIN Table2 USING (MY_PK_COL, SAMECOL1, SAMECOL2, SAMECOL3)
JOIN Table3 USING (SAMECOL1, SAMECOL2, SAMECOL3)

If I understand well, in the first query the 3 columns SAMECOLx are renamed with aliases such as SAMECOL1_1, SAMECOL2_1, SAMECOL3_1 so it should not be a problem for the second join with the USING clause (ambiguous thing).

So, where is the problem with the first query ?

Upvotes: 1

Views: 78

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21063

If you use only the first join you see the obvious difference

SELECT * FROM Table1
JOIN Table2 USING (MY_PK_COL, SAMECOL1, SAMECOL2, SAMECOL3);

 MY_PK_COL   SAMECOL1   SAMECOL2   SAMECOL3
---------- ---------- ---------- ----------
         1          1          1          1
         
SELECT * FROM Table1
INNER JOIN Table2 ON Table1.MY_PK_COL = Table2.MY_PK_COL;

 MY_PK_COL   SAMECOL1   SAMECOL2   SAMECOL3  MY_PK_COL   SAMECOL1   SAMECOL2   SAMECOL3
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          1          1          1          1          1

So the point is while using the ON condition the select * returns all columns from both tables - which leads to your problem.

Join with USING column returns the using columns only once - unfortunately I can't find a documentation of this behavior, the closest description is

In an outer join with the USING clause, the query returns a single column that coalesces the two matching columns in the join.

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191245

If I understand well, in the first query the 3 columns SAMECOLx are renamed with aliases such as SAMECOL1_1, SAMECOL2_1, SAMECOL3_1

No. The join operation does not rename the columns. If you queried just those two tables, with that join, then your client is choosing to add the _1 part when it displays the results; it isn't part of the result set.

What you see depends on your client and how you run it. For example, SQL Developer will change the column headings as you describe in the 'Query Result' window if you run it as a query (run statement, or control-enter); but if you run it as a script (run script, or F5) then it doesn't do that in the 'Script Output' window. Neither does SQL*Plus, or SQLcl.

So the first part

Table1
INNER JOIN Table2 ON Table1.MY_PK_COL = Table2.MY_PK_COL

effectively makes all of the columns from both tables available for later processing, including two columns still called SAMECOL1, and then the next part

JOIN Table3 USING (SAMECOL1, SAMECOL2, SAMECOL3)

doesn't know which of the two columns you mean - it can't distinguish between Table1.SAMECOL1 and Table2.SAMECOL2. You know they are the same data, but there is nothing in the query that says that is the case.

In the second form the first USING eliminates that ambiguity:

Table1
JOIN Table2 USING (MY_PK_COL, SAMECOL1, SAMECOL2, SAMECOL3)

only has a single SAMECOL1 projected - which is kind of from both and neither of the source tables.

You can see the difference in this fiddle, which does not alias the duplicate columns in the first query as you assumed (and as your client does):

SELECT * FROM Table1
INNER JOIN Table2 ON Table1.MY_PK_COL = Table2.MY_PK_COL
MY_PK_COL SAMECOL1 SAMECOL2 SAMECOL3 MY_PK_COL SAMECOL1 SAMECOL2 SAMECOL3
42 1 2 3 42 1 2 3 
SELECT * FROM Table1
JOIN Table2 USING (MY_PK_COL, SAMECOL1, SAMECOL2, SAMECOL3)
MY_PK_COL SAMECOL1 SAMECOL2 SAMECOL3
42 1 2 3

Upvotes: 1

Related Questions